Basic concepts

The fundamental assumption behind DbFun approach is, that the best representation of an SQL query in a functional language is a function.

Query parameters can be reflected as function parameters, its result as function return type.

E.g. the query:

select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
from Blog 
where id = @id

could be implemented as a function of type:

val getBlog: int -> IDbConnection-> Blog

or, taking into account, that IO-intensive operations should be async today:

val getBlog: int -> IDbConnection-> Async<Blog>

Additionally, instead of IDbConnection, DbFun uses IConnector type, encapsulating connection and transaction:

val getBlog: int -> IConnector -> Async<Blog>

DbFun allows to generate such a function:

let qb = QueryBuilder(config)
let getBlog =  qb.Sql<int, Blog>(
    "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
     from Blog 
     where id = @id", 
    "id")

where Sql is a method responsible for building query functions using runtime code generation. It generates:

• mappings between function parameters and query parameters
• mappings between query result and function result
• all needed type conversions
• command execution

No cache is needed. Assigning generated functions to variables is perfectly enough.

A side-effect of code generation is a validation of sql commands and all necessary type checking (i.e. whether function parameter types match query parameter types and whether function and query results are compatible).

Functions described above don't relay on any state, so they don't need a class, carrying state. That means, that they can be simply placed in modules:

module Blogging =     
    let getBlog = qb.Sql<int, Blog>(...)
    let getPosts = qb.Sql<int, Post list>(...)
    let getComments = qb.Sql<int, Comment list>(...)

And, since all variables are evaluated and assigned during the first access to the module contents, we obtain some level of type safety almost for free - it's enough to access one of them, without even calling it, to validate whole module. So, writing one unit test per module give us type safety:

    [<Test>]
    member this.``Blogging module is valid``() = 
        Blogging.getBlog |> ignore

The generated function has an IConnector object as its last parameter. The reason of placing it at the end of parameter list is to allow to manage connections efectively. After applying all preceding parameters we obtain function taking IConnector as its only parameter, e.g. result of following call

let call = getBlog id

is value of type

IConnector -> Async<Blog>

It can be passed to another function, that opens a database connection, calls query function with it, then closes connection, and returns the result of query function call:

let blog = getBlog id |> run

where the run function could be implemented like this:

let run (f: IConnector -> Async<'Result>): Async<'Result> = 
    async {
        use connection = createConnection()
        connection.Open()
        let connector = Connector(connection)
        return! f(connector)
    }

Of course, DbFun provides some connonical implementation of this function.

Opening and closing connection for each query is not always feasible. It's possible to compose queries using additional function:

(fun con ->
    let postId = insertPost post con
    insertTags postId tags con)
|> run

but it doesn't look nice.

DbFun utilizes some basic category theory knowladge and provides elegant computation expression:

dbsession {
    let! postId = Blogging.insertPost post
    do! Blogging.insertTags postId tags
}
|> run

The dbsession is just a mix of reader and async monads, where the environment of reader is IConnector type.