Having fun with SqlFun

SqlFun is an F# data-access library, with support for SQL Server, PostgreSQL, Oracle, MySQL and SQLite.

The basic idea is that you write a query in plain SQL inside an F# string, and the library will generate an F# function for you, accepting the query's parameters and returning an F# data type that matches the result structure.

What's unique about SqlFun is that the function generation happens at runtime. If you provide the expected function signature via type annotations, you get runtime type checking: if the signature of the generated function doesn't match the type annotation, an exception is thrown. Likewise if your query isn't valid (for example if there's a typo), you'll get an error. It's worth saying that the code generation happens only once, when the containing module is initialised. This means that you'll know before execution whether or not the query is going to fail, and don't have to worry about the performance implications of code generation on every function execution.

There's a lot to like about this combination of features:

• Because you're using SQL rather than a library's domain-specific language for data access, you don't have to worry about being constrained in what you can do.
• Similarly, you're in control of the efficiency of the SQL and number of database queries, rather than relying on what's been generated for you.
• Unlike some other tools that generate F# code from SQL strings, the query can be right next to the calling code, so you don't have to spend time navigating between files in your codebase.
• Unlike some other libraries that work with SQL strings embedded in F# code, you can be made aware of errors before executing the function call.

Let's see how it's used.

Basic usage

Setup

First up, we'll need a database for testing with. If you're using Docker, you can start a new container:

docker run --name sqlfun -e "ACCEPT_EULA=Y" -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Then, using your method of choice, execute the following DDL to create the database and a table.

CREATE DATABASE SqlFun

USE SqlFun

CREATE TABLE Things
(
    Id UNIQUEIDENTIFIER NOT NULL,
    Label NVARCHAR(100) NOT NULL,
    Ordinal INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL,
    ModifiedAt DATETIME2 NULL,
)

Next, run the following DML to insert some data into the table to test against.

INSERT INTO Things (Id, Label, Ordinal, CreatedAt, ModifiedAt)
VALUES
('a99805dc-97d1-44ff-9d4f-8d97df3ccf81', 'Widget', 1, '2023-01-01', '2023-02-01'),
('6db4e57f-abef-41b4-b610-1778ad2587a6', 'Gizmo', 2, '2023-02-02', NULL)

Using SqlFun

For simplicity, I'll show some code that you can send to F# Interactive.

The first thing we need to do is run through some configuration. The docs site has a useful page on configuration with more details.

#r "nuget: SqlFun, 2.2.0"
#r "nuget: System.Data.SqlClient"

open SqlFun.ComputationBuilder
open SqlFun.GeneratorConfig
open SqlFun.Queries

let createConnection () =
    new System.Data.SqlClient.SqlConnection "Data Source=localhost;Initial Catalog=SqlFun;User ID=sa;Password=yourStrong(!)Password"
let generatorConfig = createDefaultConfig createConnection
let sql commandText = sql generatorConfig commandText
let runAsync f = AsyncDb.run createConnection f

With that out of the way, we can finally define our function using SQL!

type Thing = {
    Id : System.Guid
    Label : string
    Ordinal : int
    CreatedAt : System.DateTime
    ModifiedAt : System.DateTime option
}

let getThing: System.Guid -> AsyncDb<Thing option> =
    "SELECT Id, Label, Ordinal, CreatedAt, ModifiedAt
    FROM Things
    WHERE Id = @Id"
    |> sql

When given an identifier that's not in the database it returns None, as expected.

getThing (System.Guid.Parse "eab5b170-0897-4c15-a39d-35734b8b0cb0")
|> runAsync
|> Async.RunSynchronously
// val it: Thing option = None

And when we give the identifiers that correspond to database records, we see the expected results, including None values where the database record has a NULL.

let widget =
    getThing (System.Guid.Parse "a99805dc-97d1-44ff-9d4f-8d97df3ccf81")
    |> runAsync
    |> Async.RunSynchronously
// val widget: Thing option = Some { Id = a99805dc-97d1-44ff-9d4f-8d97df3ccf81
//                                   Label = "Widget"
//                                   Ordinal = 1
//                                   CreatedAt = 01/01/2023 00:00:00
//                                   ModifiedAt = Some 01/02/2023 00:00:00 }

getThing (System.Guid.Parse "6db4e57f-abef-41b4-b610-1778ad2587a6")
|> runAsync
|> Async.RunSynchronously
// val it: Thing option =
//   Some { Id = 6db4e57f-abef-41b4-b610-1778ad2587a6
//          Label = "Gizmo"
//          Ordinal = 2
//          CreatedAt = 02/02/2023 00:00:00 ...
//          ModifiedAt = None }

Of course, SqlFun supports UPDATEs too:

let updateThing : Thing -> AsyncDb<unit> =
    "UPDATE Things
    SET
        Label = @Label,
        Ordinal = @Ordinal,
        ModifiedAt = GETDATE()
    WHERE Id = @Id"
    |> sql

let wodget = { widget.Value with Label = "Wodget" }

wodget
|> updateThing
|> runAsync
|> Async.RunSynchronously
// val it: unit = ()

After running the above code, we can see that the data in the database has been changed as expected:

SELECT Id, Label, Ordinal, CreatedAt, ModifiedAt
FROM Things
WHERE Id = 'a99805dc-97d1-44ff-9d4f-8d97df3ccf81'

Query validation and type checking

As mentioned, SqlFun raises exceptions if there's a problem with your query, or if the F# function generated from the query doesn't match the type annotation.

For example, SqlFun will raise an error if you try to query from a table that doesn't exist:

let getThing: System.Guid -> AsyncDb<Thing option> =
    "SELECT Id, Label, Ordinal, CreatedAt, ModifiedAt
    FROM Thing
    WHERE Id = @Id"
    |> sql
// ---> System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Thing'.

You'll also get an error if your query references a column that doesn't exist:

let getThing: System.Guid -> AsyncDb<Thing option> =
    "SELECT Id, Label, Ordinal, CreatedAt, ChangedAt
    FROM Things
    WHERE Id = @Id"
    |> sql
// ---> System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ChangedAt'.

And if the annotated return type suggest that the result set should include a column with a particular name, SqlFun will tell you if it doesn't:

type Thing' = {
    Id : System.Guid
    Label : string
    Ordinal : int
    CreatedAt : System.DateTime
    ChangedAt : System.DateTime option
}

let getThing': System.Guid -> AsyncDb<Thing' option> =
    "SELECT Id, Label, Ordinal, CreatedAt, ModifiedAt
    FROM Things
    WHERE Id = @Identifier"
    |> sql
// ---> System.Exception: No column found for ChangedAt field. Expected: ChangedAt

This is pretty great! You'll get these errors when the function is generated, which is when the containing module is first accessed. The docs have some advice on how to write small automated tests to check that your functions don't have errors.

I suppose that you could initialise all of the modules containing SqlFun functions when your app starts too, front-loading the code generation. This would mean that your app throws an exception on startup if there are errors, rather than at query time. That's probably preferable, unless development is slowed a lot because of generating functions that you don't execute. I don't have information on query generation performance, so am not in a position to quantify that.

Other features

We've only scratched the surface of what SqlFun has to offer. Some other features it has are:

• CRUD function generation
• Populating collection-type F# record fields when using multiple result sets
• Other result transformations
• Support for stored procedures
• Support for transactions
• DBMS-specific extensions

Wrap-up

Based on my initial experimentation, I really like SqlFun. The main reasons are:

• It generates F# code from SQL rather than the other way around, giving you full control but removing boilerplate.
• If there's a problem with your SQL query or the conversion into F# types, you are notified quickly.
• You don't need a separate file for your queries, so you can keep code related to a single use case together, rather than having it scattered throughout your codebase.
• The documentation is extensive.
• There are lots of quality-of-life features like populating collection-type fields by convention and generating CRUD functions.

One thing I don't know about (because I haven't looked into it) is how good the performance is, both in terms of function generation and function execution. Maybe there are some benchmarks somewhere.

Overall, I think that SqlFun looks like a great library, and I'm looking forward to experimenting further. Thanks jacentino for putting the library together!