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.
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.
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.
let databaseFilename = "sample.sqlite"
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.
let connection = new SQLiteConnection(connectionString)
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.
let connectionStringMemory = sprintf "Data Source=:memory:;Version=3;New=True;"
Time to give the database some structure. A simple table will suffice.
let structureSql =
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
sum to count the number of records added.
// Add records
Once the records are inserted, it is trival to query them. Below is a simple select and iteration through the recordset.
let selectSql = "select * from trades order by timestamp desc"
Finally, the connection cleanup.
Here is the output from the above actions.
Hopefully you found this simple SQLite example useful. In future posts I will discuss more idiomatic methods of interacting with the database.