SqlFun

Idiomatic data access for F#

SqlFun is a tool for writing data access code in F# functional way. It's fast, type safe and gives you all powers of SQL, no custom query language constraints you. It's also lightweight, you need to know a general idea and few functions (and, of course SQL).

It's available as a Nuget package There are also extensions for MS SQL ([1], [2]), PostgreSQL and Oracle databases.

Features

Works with any ADO.NET provider
All SQL features available
Type safety
High performance
Compound, hierarchical query parameters
Compound, hierarchical query results
Support for parameter conversions
Support for result transformations
Support for enum types
Asynchronous queries
Composable, template-based queries
Auto-generated CRUD operations
Computation expressions for connection and transaction handling
Support for large dataset processing

Supported databases

In its core SqlFun does not use any features specific to some db provider, so it works with any ADO.NET provider. The only limitation is possibility of execution of commands in SchemaOnly mode.

It was tested against MS SqlServer, PostgreSQL, Oracle, MySQL and SQLite.

There are four extensions, enabling provider-specific features:

the extension for MS SQL, that allows to use table valued parameters
the extension for PostgreSQL, making use of array parameters possible and adding more comfortable Bulk Copy mechanism
the extension for Oracle, adding some adaptations, like binding parameters by name, and allowing to use array parameters
the extension for SQLite, that allows to use date and time values

Limitations

Not all databases manage SchemaOnly behavior properly:

MySQL, PostgreSQL and Oracle performs well only for queries that return some results - commands not returning any are executed as with Default behavior
MS SQL doesn't recognize temporary tables in SchemaOnly executions, although, you can use table variables instead

How it works

Most of us think about data access code as a separate layer. We don't like to spread SQL queries across all the application. Better way is to build an API exposing your database, consisting of structures representing database data, and functions responsible for processing this data (great object-oriented example is Insight.Database automatic interface implementation). SqlFun makes it a design requirement.

Installation

SqlFun can be added to your solution from Package Manager Console:

PM> Install-Package SqlFun

Configuration

First step is to define function creating database connection and config record:

let createConnection () = new SqlConnection(<your database connection string>)
let generatorConfig = createDefaultConfig createConnection

and wire it up with functions responsible for generating queries (using partial application):

let sql commandText = sql generatorConfig commandText

let proc name = proc generatorConfig name

and for executing them:

let run f = DbAction.run createConnection f

let runAsync f = AsyncDb.run createConnection f

Data structures

Then, data structures should be defined for results of your queries.

type Post = {
    id: int
    blogId: int
    name: string
    title: string
    content: string
    author: string
    createdAt: DateTime
    modifiedAt: DateTime option
    modifiedBy: string option
    status: PostStatus
}
    
type Blog = {
    id: int
    name: string
    title: string
    description: string
    owner: string
    createdAt: DateTime
    modifiedAt: DateTime option
    modifiedBy: string option
    posts: Post list
}

The most preferrable way is to use F# record types. Record fields should reflect query result columns, because they are mapped by name.

Defining queries

The best way of defining queries is to create variables for them and place in some module:

module Blogging =    
 
    let getBlog: int -> DbAction<Blog> = 
        sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
             from Blog 
             where id = @id"
            
    let getPosts: int -> DbAction<Post list> = 
        sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
             from post 
             where blogId = @blogId"

The functions executing queries are generated during a first access to the module contents.

At that stage, all the type checking is performed, so it's easy to make type checking part of automatic testing - one line of code for each module is needed.

The generating process uses reflection heavily, but no reflection is used while processing a query, since generated code is executed.

Executing queries

Since your queries return DbAction<'t>, they can be passed to the run function after applying preceding parameters.

let blog = Blogging.getBlog 1 |> run

Async support

The preferrable way is to define query as asynchronous:

let getBlog: int -> AsyncDb<Blog> = 
    sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
         from Blog 
         where id = @id"

and then, execute as async:

async {
    let! blog = Blogging.getBlog 1 |> runAsync
    ...
}

Result transformations

Since the ADO.NET allows to execute many sql commands at once, it's possible to utilize it with SqlFun. The result is a tuple:

let getBlogWithPosts: int -> AsyncDb<Blog * Post list> = 
    sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
         from Blog 
         where id = @id;
         select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
         from post 
         where blogId = @id"

The call of sql returns some function, thus it can be composed with another function, possibly performing result transformations. Let extend the blog type with a posts: Post list property. In this case, two results can be combined with simple function:

let getBlogWithPosts: int -> AsyncDb<Blog> = 
   sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
        from Blog 
        where id = @id;
        select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
        from post 
        where blogId = @id"
   >> AsyncDb.map (fun b pl -> { b with posts = pl })

In simple cases, when code follows conventions, transormations can be specified more declarative way:

let getBlogWithPosts: int -> AsyncDb<Blog> = 
    sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy 
         from Blog 
         where id = @id;
         select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status 
         from post 
         where blogId = @id"
    >> AsyncDb.map combine<_, Post>

There are also functions that allow to combine multi-row results by joining many results or grouping wide results.

Compound parameters

Records can be parameters as well:

let insertPost: Post -> AsyncDb<int> = 
    sql "insert into post 
                (blogId, name, title, content, author, createdAt, status)
         values (@blogId, @name, @title, @content, @author, @createdAt, @status);
         select scope_identity()"

The record fields are mapped to query parameters by name.

Stored procedures

The result of a function calling stored procedure should be a three-element tuple (return code, output params, result):

let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> AsyncDb<int * unit * Post list> =
    proc "FindPosts"

but there are transformers, that allow to ignore parts of it:

let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> Post list AsyncDb =
    proc "FindPosts"
    >> AsyncDb.map resultOnly

Utilizing `dbaction` and `asyncdb` computation expressions

It's easy to execute one query with runAsync or run function. To execute more queries in a context of one open connection, computation expression can be used:

asyncdb {
    let! postId = Blogging.insertPost post
    do! Blogging.insertComments postId comments
    do! Blogging.insertTags postId tags
} |> runAsync

The synchronous equivalent of this expression is dbaction.

Transactions

To execute some queries in transaction, the inTransaction function should be used:

asyncdb {
    let! postId = Blogging.insertPost post
    do! Blogging.insertComments postId comments
    do! Blogging.insertTags postId tags
} 
|> AsyncDb.inTransaction
|> runAsync

Its synchronous equivalent is DbAction.inTransaction.

Documentation & examples

For more comprehensive documentation refer project github pages.

For more examples refer test project.

jacentino/SqlFun