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 |> ignoreFind 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 |> ignoreQuery 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 |> ignoreImplementation
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() = querySeqAsync<Types.Bird> { script "SELECT * FROM Bird" }
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])
}