F# and Dapper

Read Time: 7 minutes

Continuing with the database theme, here is a quick example of using F# with Dapper.

For comparison sake, I will continue to use SQLite and Bitcoin trade data as I did in the previous post. Since Dapper is a light-weight ORM that leverages classes, it makes data access much cleaner than just using a raw SqlReader. Its implementation also facilitates building some functional wrappers around the database calls. Note, there will be some repetive setup code in the beginning of the post, since the basic database creation and setup has not changed.

Using Paket, here is the paket.dependencies file.

1
2
3
4
source https://nuget.org/api/v2

nuget System.Data.SQLite
nuget Dapper

The initial setup is similar to the previous post, with the major addition of Dapper references. The code is identical to the previous post. I will use the same TradeData record type and sample data. Dapper extends the sql connection object, so I perform database creation and connection opening as I did before.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#r "../packages/System.Data.SQLite.Core/lib/net46/System.Data.SQLite.dll"
#r "../packages/Dapper/lib/net451/Dapper.dll"

open System
open System.Collections.Generic
open System.Data.SQLite
open Dapper

type TradeData = { 
    Symbol:string; 
    Timestamp:DateTime; 
    Price:float;
    TradeSize:float }

// Sample Data
let trades = [
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 33); Price = 2751.20; TradeSize = 0.01000000 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 21); Price = 2750.20; TradeSize = 0.01000000 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 21); Price = 2750.01; TradeSize = 0.40000000 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 21); Price = 2750.01; TradeSize = 0.55898959 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 03); Price = 2750.00; TradeSize = 0.86260000 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 44, 03); Price = 2750.00; TradeSize = 0.03000000 };
    { Symbol = "BTC/USD"; Timestamp = new DateTime(2017, 07, 28, 10, 43, 31); Price = 2750.01; TradeSize = 0.44120000 } 
    ]

// Initialize connectionstring
let databaseFilename = "sample.sqlite"
let connectionStringFile = sprintf "Data Source=%s;Version=3;" databaseFilename  

// Create database
SQLiteConnection.CreateFile(databaseFilename)

// Open connection
let connection = new SQLiteConnection(connectionStringFile)
connection.Open()

// Create table structure
let structureSql =
    "create table Trades (" +
    "Symbol varchar(20), " +
    "Timestamp datetime, " + 
    "Price float, " + 
    "TradeSize float)"

let structureCommand = new SQLiteCommand(structureSql, connection)
structureCommand.ExecuteNonQuery()

Now, the new stuff. I will look at inserting first. Dapper passes parameters as anonymous classes. This has several implications. For my purpose, this means I can use the TradeData type and its fields will auto-map to the parameters in the connection.Execute call. As before, I use map and sum to total up the records inserted.

1
2
3
4
5
6
7
8
let insertTradeSql = 
    "insert into trades(symbol, timestamp, price, tradesize) " + 
    "values (@symbol, @timestamp, @price, @tradesize)"

trades
|> List.map (fun x -> connection.Execute(insertTradeSql, x))
|> List.sum
|> (fun recordsAdded -> printfn "Records added  : %d" recordsAdded)

This works, but it would be nice to have a more functional interface. To do that, I create a dbExecute that takes a connection, sql string, and data object. Before I run more inserts, I delete the previously added records, just to keep things clean.

1
2
3
4
5
6
7
8
9
10
11
12
// Clear out records from insert1, so I don't end up with duplicate records
let deleteCount = connection.Execute("delete from trades")
printfn "Records deleted: %A" deleteCount

let dbExecute (connection:SQLiteConnection) (sql:string) (data:_) = 
    connection.Execute(sql, data)

trades
|> List.map (dbExecute connection insertTradeSql)
|> List.sum
|> (fun recordsAdded -> printfn "Records added  : %d" recordsAdded)

Now that there are records in the database, it is time to perform a parameterized select. Dapper’s Query uses generics to return a sequence of the specified class type, or in this case, F# type TradeData. This is a convenient method to access the results. As before, I could pass a type in for the parameters, but I would need to custom build a type for any query. A more dynamic alternative is to use an IDictionary<string, obj> for the key/value pairs, where key is the parameter name and value is the value of the parameter. The big note is the value must be an object, and it must be boxed. This isn’t a problem, more of a syntax annoyance. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
let filteredSql = 
    "select symbol, timestamp, price, tradesize From trades " +
    "where symbol = @symbol and tradesize >= @mintradesize"

let results1 = 
    connection.Query<TradeData>(
        filteredSql, 
        dict [ "symbol", box "BTC/USD"; "mintradesize", box 0.4 ])

printfn "Query (1):"
results1 
|> Seq.iter (fun x -> 
    printfn "%-7s %-19s %.2f [%.8f]" x.Symbol (x.Timestamp.ToString("s")) x.Price x.TradeSize)

This is all well and good, but it doesn’t feel very idiomatic. It can better; for this I add two supporting components. First, the fat arrow provides a cleaner syntax for building the key/value pair. Second, a dbQuery function similar to the previous dbExecute function. To make it flexible, the output type is a generic, and the input parameters are optional. After all this, the end result is the same, but the code just feels cleaner.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
let inline (=>) k v = k, box v

let dbQuery<'T> (connection:SQLiteConnection) (sql:string) (parameters:IDictionary<string, obj> option) = 
    match parameters with
    | Some(p) -> connection.Query<'T>(sql, p)
    | None    -> connection.Query<'T>(sql)

let results2 = dbQuery<TradeData> connection filteredSql (Some (dict [ "symbol" => "BTC/USD"; 
                                                                       "mintradesize" => 0.4 ]))

printfn "Query (2):"
results2 
|> Seq.iter (fun x -> 
    printfn "%-7s %-19s %.2f [%.8f]" x.Symbol (x.Timestamp.ToString("s")) x.Price x.TradeSize)

Now that I’m done, close the connection.

1
connection.Close()

Here is the output from the above actions.

Insert and query output

As you can see, leveraging Dapper not only provides a cleaner interface, but facilitates a more functional approach to database interaction. Hopefully you have found this short exploration into F# and Dapper useful. Until next time.

Related Posts: