SQLite Database with Dapper and F#

May 2019 ยท 3 minute read

This is a tutorial on using F# with Dapper to query a SQLite database on .NET Core. It uses the fsharp-dapper library written by Alex Troshkin. While this example uses SQLite, the general pattern could be used for any other SQL database. All code from this post is on Github and can be run locally.

Overview

At its core this pattern allows you to separate your SQL queries from your domain types. You write a small API layer which does the binding of data to types, and then can call these methods to do asynchronous queries of the database. You are free to define your own interface and use your own naming conventions. Here is how you might interact with the API in your application.

With a defined business type

type Bird = {
        Id : int64
        Name: string
        Alias: string option
}

We can create the database table and insert records using

Queries.Schema.CreateTables
    |> Async.RunSynchronously
    |> ignore

Queries.Bird.New "Kereru" "Wood Pigeon" |> Async.RunSynchronously |> ignore
Queries.Bird.New "Kea" None |> Async.RunSynchronously |> ignore
Queries.Bird.New "Doose" None |> Async.RunSynchronously |> ignore

Find and return multiple records in an F# sequence (IEnumerable)

Queries.Bird.GetAll()
    |> Async.RunSynchronously
    |> Seq.iter (printfn "%A")

Update records

Queries.Bird.UpdateAliasByName "Kea" "Mountain Jester" |> Async.RunSynchronously |> ignore

Query for single records, returning a Some<Bird>

match Queries.Bird.GetSingleByName "Kea" |> Async.RunSynchronously with
| Some(bird) -> printfn "Kea alias is now %s" bird.Alias.Value
| None -> printfn "Kea record does not exist"

And finally delete records

Queries.Bird.DeleteByName "Doose" |> Async.RunSynchronously |> ignore

Implementation

As it is a simple example with a single domain (Bird) I have kept all code in a single file, Database.fs. For larger applications you’ll want to split these modules out into their own files and likely have one file per domain for queries. Below are some of the interesting bits.

Connection

This module establishes connections to the database. This is the only SQLite specific part of the code so you could easily drop in an NpgsqlConnection if you wanted to query PostgreSQL, or the appropriate connection class for any other type of SQL database.

module Connection =

    let private mkOnDiskConnectionString (dataSource: string) =
        sprintf
            "Data Source = %s;"
            dataSource

    let mkOnDisk () = new SqliteConnection (mkOnDiskConnectionString "./example.db")

Queries

We instantiate the database connection and bind it to the query API.

module Queries = 
    let private connectionF () = Connection.SqliteConnection (Connection.mkOnDisk())

    let querySeqAsync<'R>    = querySeqAsync<'R> (connectionF)
    let querySingleAsync<'R> = querySingleOptionAsync<'R> (connectionF)

Schema

Queries which create and modify the database schema.

module Schema = 
    let CreateTables = querySingleAsync<int> {
        script """
            DROP TABLE IF EXISTS Bird;
            CREATE TABLE Bird (
                Id INTEGER PRIMARY KEY,
                Name VARCHAR(255) NOT NULL,
                Alias VARCHAR(255) NULL
            );
        """
    }

Domain Queries

Queries specific to each domain of your application. Using the query wrappers we defined above these query the database and bind the result to one of the domain types. This is the high level API that the rest of your code would interact with.

module Bird = 

    let New name alias = querySingleAsync<int> {
        script "INSERT INTO Bird (Name, Alias) VALUES (@Name, @Alias)"
        parameters (dict ["Name", box name; "Alias", box alias])
    }

    let GetSingleByName name = querySingleAsync<Types.Bird> {
        script "SELECT * FROM Bird WHERE Name = @Name LIMIT 1"
        parameters (dict ["Name", box name])
    }

    let GetAll name = querySeqAsync<Types.Bird> {
        script "SELECT * FROM Bird"
        parameters (dict ["Name", box name])
    }

    let UpdateAliasByName name alias = querySingleAsync<int> {
        script "UPDATE Bird SET Alias = @Alias WHERE Name = @Name"
        parameters (dict ["Alias", box alias; "Name", box name])
    }

    let DeleteByName name = querySingleAsync<int> {
        script "DELETE FROM Bird WHERE Name = @Name"
        parameters (dict ["Name", box name])
    }