Having fun with F#, SQL Databases and ASP.NET

🗒️ You can read the source code for this article.

For some time now, I’ve been waiting for an opportunity to use SqlFun¹, a data access library for F#. Yesterday, it finally happened when a friend of mine decided to give F# a go.

What’s the deal with this library? You can watch the talk “F#, SQL and lot of fun F# SQL micro orm” to understand some of its design decisions. However, summarizing what it does in practice: it provides a quasi static analysis of your SQL queries. This is a vague statement, yes, but it means that the library will check, before you run any query, if you object is correctly mapping with your query result, if the query has correct parameters and so on.

While this might be less powerful than something as FSharp.Data.Npgsql that provides actual compile-time guarantees for PostgreSQL databases. It has the advantage of keeping your build time dependencies less complex, as you don’t need to deal with DLLs or have a running database during build-time.

Previously, the main problem I had with the library was writing code that didn’t depend on module state such as the project’s README. While I, personally, don’t like this approach, it does have an advantage that I was not able to fully reproduce on my approach. When you hold the state on your module, the only thing you need to worry about is to actually use the module once. In SqlFun’s case, this would mean that a simple open statement would validate the queries.

Let’s deep dive into the code. In order to make things easier to deal with in a F# script, the database of choice will be SQLite. Install the needed packages and open them:

#r "nuget: SqlFun, 2.2.1"
#r "nuget: Microsoft.Data.Sqlite, 8.0.0-preview.5.23280.1"

open SqlFun
open SqlFun.GeneratorConfig
open SqlFun.Queries
open Microsoft.Data.Sqlite

This is all we need to have access to SQLite and SqlFun on our script. SqlFun needs some boilerplate code to make our lives easier:

module Database =
  // string -> unit -> SqliteConnection
  let createConnection connectionStr: unit -> SqliteConnection =
      (fun () -> new SqliteConnection(connectionStr))
  // (unit -> 'a (requires :> IDbConnection)) -> GeneratorConfig
  let generatorConfig connection = createDefaultConfig connection
  // (unit -> 'a) -> string -> 'b
  let sql connection commandText = sql (generatorConfig connection) commandText
  // string -> (IDataContext -> 'a) -> 'a
  let run connectionStr f =
      DbAction.run (createConnection connectionStr) f

This is trivial, we receive a connection string, open the database, create the tables and close the database. Now comes the interesting part! SqlFun needs some boilerplate code to make our lives easier:

Breaking this code apart, we have four functions:

• createConnection: this function receives a connection string and returns another function that, when called, returns a SQLite connection.
• generatorConfig: looking at the function signature, the connection parameter expects the exact result of createConnection when you pass just the connection string. It then returns a configuration record.
• sql: here, again, we receive a connection string and a SQL query string.
• run: different from the other function, run expects an opened connection and a function that we will define later in our repositories.

Now, let’s write a small piece of code to create our database tables:

module Infrastructure =
  let createDatabase (connectionStr) =
    let db = new SqliteConnection(connectionStr)
    db.Open()

    let cmd = new SqliteCommand("
      CREATE TABLE user (
        ID INTEGER NOT NULL,
        NAME TEXT NOT NULL
      )
    ", db)
    cmd.ExecuteNonQueryAsync() |> ignore
    db.Close() |> ignore

Define a domain record to be used on our repository:

module Domain =
  type User = { Id: string; Name: string }

Here comes the time to define our Repository module:

module Repository =
  open Database

  type User(connection) =
      member val Get: int -> DbAction<Domain.User> =
        sql connection "select id, name from user where id = @id"

  type Repository =
    { User: User }

  let create connectionStr =
    let db = createConnection connectionStr ()
    let connection = (fun _ -> db)

    { User = User(connectionStr) }

The Repository module contains a User class that receives a unit -> IDbConnection as a constructor parameter. This connection is then passed to the sql function inside each member of the class. Note that the Get member is a val, not a method. This is important because it allows us to evaluate the sql function during the class instantiation.

The sql connects to the database and generates the code needed to process the query. However, note that this code generation is stored on the class member. Everything is ready for you to start calling your query through your repository methods.

As previously mentioned, the disadvantage of the class approach compared to the module one is that you have to keep track of your instance throughout your application. Thankfully, it’s possible to borrow something I learned on Alex Edwards’ Let’s Go book. In Go you would store your repositories on a single struct that would be passed around your application as a dependency:

type Models struct {
  User UserRepository
}

func NewModels(db *sql.DB) {
    return Models{
        User: NewUserRepository(db)
    }
}

It’s easy to do the same in F#, but using records instead of structs. This is exactly what the Repository record represents!

Putting this code to use looks like the following:

let connectionStr = "Data Source=sqlite.db"

Infrastructure.createDatabase connectionStr
let repository = Repository.create connectionStr

The funny part is that running this code actually showed an error on my program:

SqlFun.Exceptions.CompileTimeException: Error generating function Int32 -> IDataContext -> User for sql command select id, name from user where id = @id
 ---> System.Exception: No column found for Id field. Expected: Id
 ---> System.InvalidOperationException: No coercion operator is defined between types 'System.Int64' and 'System.String'.

Our domain is incorrect, User has an int Id, not a string Id! The correct implementation is this:

module Domain =
  type User = { Id: int; Name: string }

We are almost there

One thing to remember is that SqlFun needs to actually run a query to determine if the return type should be an option or not.

let repository = Repository.create connectionStr
repository.User.Get 123 |> Database.run connection

System.Exception: Value does not exist. Use option type.

Plugging our repository to ASP.NET

So far this library seems to be groovy but how could we proceed to plug it on an ASP.NET application? This can be done through ASP.NET’s own Dependency Injection mechanism.

let configureServices config (services: IServiceCollection) =
    let repository = Repository.create config.SqlConnectionString

    services.TryAddSingleton<Repository.Repository>
        (fun _ -> repository) |> ignore

    services

The repository is instantiated allowing SqlFun to validate the queries contained on the inner repositories as previously explained. After this, adding a Singleton service one for the repository. Why singleton? Because using the same instance throughout the lifetime of our web application avoids validating the queries again after startup.

A Giraffe handler would look like this:

let getUser config userId : HttpHandler =
  fun (next: HttpFunc) (ctx: HttpContext) -> task {
    let repository = ctx.GetService<Repository.Repository>()

    let user =
      repository.User.Get userId
      |> Database.run config.SqlConnectionString

      return!
        match user with
        | Some _ -> text "user found" next ctx
        | None -> RequestErrors.notFound (text "user not found") next ctx
  }