F# and SQLite

Read Time: 5 minutes

Today I have a quick example of using F# with SQLite.

Using Bitcoin trade data from GDAX I will show a simple SQLite example. As fair warning, the SQLite interface is a raw sql connection targeted generically at the .NET Framework. As a quick introduction this is fine. Future posts will investigate a more F# idiomatic way of doing things. [Edit from the future: F# and Dapper]

Using Paket, here is the paket.dependencies file.

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

nuget System.Data.SQLite

As usual there is some initial setup. First, include references. Second, create the recordtype for the Bitcoin trade data. Third, create the sample data to be used for this exercise. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#r "../packages/System.Data.SQLite.Core/lib/net46/System.Data.SQLite.dll"

open System
open System.Data.SQLite

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 } 
    ]

Now it is time to create the database. I need a filename for creation, the connectionString is for later. As you can see, creating a database is straightforward. 

1
2
3
4
5
let databaseFilename = "sample.sqlite"

let connectionString = sprintf "Data Source=%s;Version=3;" databaseFilename  

SQLiteConnection.CreateFile(databaseFilename)

Once created, I open the connection for future use. As a note, this example is just a single fsx script. Because of this, I use let. In a real implementation I would most likely have this in a function somewhere. In cases like that, I use use instead of let when creating connections and commands. That way the connection properly disposes when it goes out of scope. 

1
2
let connection = new SQLiteConnection(connectionString)
connection.Open()

As an aside, perhaps I want my instance to be an in-memory database. Instead of doing the above CreateFile and connect, I can use SQLite’s :memory: datasource to spin this up in memory. For particular use-cases, this can be a nice option, so I thought it was worth mentioning.

1
2
3
let connectionStringMemory = sprintf "Data Source=:memory:;Version=3;New=True;" 
let connection = new SQLiteConnection(connectionStringMemory)
connection.Open()

Time to give the database some structure. A simple table will suffice.

1
2
3
4
5
6
7
8
9
let structureSql =
    "create table Trades (" +
    "Symbol varchar(20), " +
    "Timestamp datetime, " + 
    "Price float, " + 
    "TradeSize float)"

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

Once the structure is created, I can start to do more interesting things. First I need to insert some records. Since ExecuteNonQuery() returns records impacted, I can leverage map and sum to count the number of records added. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Add records
let insertSql = 
    "insert into trades(symbol, timestamp, price, tradesize) " + 
    "values (@symbol, @timestamp, @price, @tradesize)"

trades
|> List.map(fun x ->
    use command = new SQLiteCommand(insertSql, connection)
    command.Parameters.AddWithValue("@symbol", x.Symbol) |> ignore
    command.Parameters.AddWithValue("@timestamp", x.Timestamp) |> ignore
    command.Parameters.AddWithValue("@price", x.Price) |> ignore
    command.Parameters.AddWithValue("@tradesize", x.TradeSize) |> ignore

    command.ExecuteNonQuery())
|> List.sum
|> (fun recordsAdded -> printfn "Records added: %d" recordsAdded)

Once the records are inserted, it is trival to query them. Below is a simple select and iteration through the recordset.

1
2
3
4
5
6
7
8
9
let selectSql = "select * from trades order by timestamp desc"
let selectCommand = new SQLiteCommand(selectSql, connection)
let reader = selectCommand.ExecuteReader()
while reader.Read() do
    printfn "%-7s %-19s %.2f [%.8f]" 
        (reader.["symbol"].ToString()) 
        (System.Convert.ToDateTime(reader.["timestamp"]).ToString("s"))
        (System.Convert.ToDouble(reader.["price"])) 
        (System.Convert.ToDouble(reader.["tradesize"]))

Finally, the connection cleanup.

1
connection.Close()

Here is the output from the above actions.

Insert and query output

Hopefully you found this simple SQLite example useful. In future posts I will discuss more idiomatic methods of interacting with the database.

Related Posts: