Sean's Stories

F# SQL Provider in .NET Core 3.1

Sean Allin Newell -

Photo by Ryan Quintal on Unsplash.

Preface

Type providers have long fascinated me as a killer feature of F#, but I haven't gone whole hog since I was more focused on wanting to use .NET Core F# since .NET Core 2.1 LTS was released. I didn't want to have to finangle with mono or framework on either my work windows 10 box or my linux laptop at home. Luckily, the SQL Provider type provider that lets F# programmers write F# flavored LINQ with strong typings right from the database does work! It works with MySQL and Postgres, on .NET Core 3.1, today! (and probably yesterday and tomorrorw as well).

Setting up

You'll need to install a few things:

  • .NET Core 3.1 SDK
  • Local Db server (or use Docker!)
    • Ex: Postgres in a container
  • Editor with F# intellisense
    • Ex: VSCode + Ionide
  • Ability to run scripts
    • Ex: .sh or .ps1
  • Terminal

I'm just going to use VSCode here, but in theory this works in Visual Studio as well - I've tested this on a windows 10 box connected to a remote MySQL server, and on a debian laptop connected to a local postgres server running in a docker container. We'll just be making a small console app, but this works for apps written with Suave, Giraffe, Saturn, and in any netstandard project (although for simplicity sake we'll be making netcore3.1 projects).

If you're unfamilar with all the hooplah microsoft is throwing our way with netframework, netstandard, netcore, and the lts releases and f# versions I'd encourage you to peruse this blog post and these docs. Something that confused me recently was the shift from nuget metapackages to bundled packages shipped in the runtime. The gist is:

  • .NET 5 will unify everything into .NET Core
  • Netstandard will likely not apply when everything is net5
  • .NET 3 changed how nuget metapackages are handled, and bundles a lot more dlls into the runtime

These changes are pertinent to F# Type Providers since (especially the SQL Provider) the libraries we use often need to be pointed to a place to pull in compile-time dependency libraries to connect to databases.

Setup

Setup is pretty easy once you have an editor and the dotnet sdk since it comes with a nice CLI tool.

mkdir test-sql-provider
dotnet net console -lang f#

# make sure this runs
dotnet run

# and if you have VSCode + Ionide go ahead and fire it up!
code .

Now you have a nice starting point.

Note - on linux you may want to ensure the .fs and .fsproj file are in plain utf-8 (without bom) and with lf line endings. ;)

Type Provider Setup

In order to work with the SQL Provider, we'll have to create a stable location for the compiler to look for libraries as it is compiling to use in the type provider (ie make connections to the database to generate the types from our sql database).

I like having conventions enforced by tools, and what better tool than the dotnet build tooling! You can create a 'hook' of sorts that runs an arbitrary command - and that command can populate a well known place (./lib/) with the appropriate dlls required by the F# type provider so the compiler can consume the types it creates. Nice!

Let's first create an executable script that just prints something out and wire up the 'hook' in the .fsproj file. touch prebuild.sh && chmod +x prebuild.sh to get the file and put an echo in there:

#!/bin/sh

echo "Ahoy!"

This should work in windows if windows understands how to run .sh files - but your mileage may vary, so feel free to swap out the shell file with powershell or even use a node script if you want to.

Now open the .fsproj file in your editor and add the following.

   </PropertyGroup>
 
+  <Target Name="TypeProviderSetup" **BeforeTargets="Build"**>
+    <Exec Command="./prebuild.sh"/>
+  </Target>
+
   <ItemGroup>

To see if it works run a quick dotnet build to see your echo!

▶ dotnet build
Microsoft (R) Build Engine version 16.4.0+e901037fe for .NET Core
Copyright (C) Microsoft Corporation. All rights reserved.

  Restore completed in 42.01 ms for /home/sean/repos/test-sql-provider/test-sql-provider.fsproj.
  test-sql-provider -> /home/sean/repos/test-sql-provider/bin/Debug/netcoreapp3.1/test-sql-provider.dll
  Ahoy!

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:03.53

Now it's time to add packages and figure out how to get the necessary dlls from nuget into the lib folder. I'll just use the dotnet CLI to add the packages for Postgres and SQLProvider:

# gotta do em one at a time
dotnet add package SQLProvider
dotnet add packages Npgsql

Here is the nuget page for the postgres Npgsql package. We are particularly interested in the 3.0 dependencies this package requires:

So this tells us that for apps that target netcoreapp3.x we need System.Runtime.CompilerServices.Unsafe (>= 4.6.0). Since we added the package to our project with dotnet add package (which uses nuget under the hood) - nuget added the dependnecies to our machine's global nuget cache already. So we actually already have the dll on our machine, and our script can locate it and copy it for us.

In order to do this in a somewhat robust way, we can leverage nuget to figure out where it stores packages, but then we'll have to figure out the directory structure once we're in the package.

To get nuget to tell us where it puts things, run dotnet nuget locals global-packages -l. To figure out where System.Runtime.CompilerServices.Unsafe lives you can explore the directory structure to reveal this path:

▶ ls ~/.nuget/packages/system.runtime.compilerservices.unsafe/4.6.0/lib/netstandard2.0/
System.Runtime.CompilerServices.Unsafe.dll  System.Runtime.CompilerServices.Unsafe.xml

Now that we have these two pieces we can finish our bash script (we actually need to use bash if we want to use arrays - you could read from a txt file that lists the same info out to loop over by reading line by line):

-#!/bin/sh
+#!/bin/bash
 
-echo "Ahoy!"
+# Run a restore to be sure we have all the dlls on the system.
+dotnet restore web > /dev/null
+
+# Find nuget's global directory
+nugetRootPath=$(dotnet nuget locals global-packages -l | cut -d' ' -f4)
+nugetRootPath="${nugetRootPath%?}"
+
+# array of ${nameOfDep}|${pathToDllFolder}
+packagesAndPaths=("System.Runtime.CompilerServices.Unsafe|4.6.0/lib/netstandard2.0")
+
+mkdir -p lib
+
+for i in "${packagesAndPaths[@]}"
+do
+  # all these vars split out for clarity
+  name=$(echo "$i" | cut -d'|' -f1)
+  path=$(echo "$i" | cut -d'|' -f2)
+  nugetDll="$name.dll"
+  pathFromNugetRoot="$(echo "$name" | awk '{print tolower($0)}')/$path"
+  fullPath="$nugetRootPath/$pathFromNugetRoot/$nugetDll"
+
+  cp "$fullPath" "./lib/$nugetDll"
+  echo "  > Copied $nugetDll into lib for type provider."
+done

Run this manually to verify that it populates the lib folder, than rm -rf lib and run dotnet build to see...

▶ dotnet build
Microsoft (R) Build Engine version 16.4.0+e901037fe for .NET Core
Copyright (C) Microsoft Corporation. All rights reserved.

  Restore completed in 32.34 ms for /home/sean/repos/test-sql-provider/test-sql-provider.fsproj.
  test-sql-provider -> /home/sean/repos/test-sql-provider/bin/Debug/netcoreapp3.1/test-sql-provider.dll
    > Copied System.Runtime.CompilerServices.Unsafe.dll into lib for type provider.

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:02.03

SQL Time

Let's write a fun SQL Schema! (Did'ya ever think SQL Schemas could be fun? 😹)

-- pre-sql - have these run as the postgres user
CREATE USER test_app WITH PASSWORD 'pass';
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test_app;


-- schema sql - run these as the test_app user
CREATE TABLE IF NOT EXISTS test.attributes(
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE,
  description TEXT
);

CREATE TABLE IF NOT EXISTS test.breeds(
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS test.breed_attributes(
    attributeId INTEGER,
    breedId INTEGER,
    FOREIGN KEY (attributeId) REFERENCES test.attributes,
    FOREIGN KEY (breedId) REFERENCES test.breeds
);

CREATE TABLE IF NOT EXISTS test.cats(
  id SERIAL PRIMARY KEY,
  name TEXT,
  breedId INTEGER,
  FOREIGN KEY (breedId) REFERENCES test.breeds
);

CREATE TABLE IF NOT EXISTS test.owners(
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INTEGER
);

CREATE TABLE IF NOT EXISTS test.owner_cats(
    ownerId INTEGER,
    catId INTEGER,
    FOREIGN KEY (ownerId) REFERENCES test.owners,
    FOREIGN KEY (catId) REFERENCES test.cats
);

Maybe I went too far... Let's load this up in a docker container with a new volume and then execute the script with psql - all wrapped up nice and neat in a script.

./run-postgres-container.sh

Now normally at this point you'd copy some huge SQL file to load data... but let's use the SQL Provider to do that! Who wants to write or copy inserts anyway.

F# |> SQL

Following the getting started and the postgres instructions for the Type Provider should yield boilerplate like this:

open FSharp.Data.Sql

[<Literal>]
let DbVendor = Common.DatabaseProviderTypes.POSTGRESQL

[<Literal>]
let ConnString = "Host=localhost;Port=5432;Database=postgres;Username=test_app;Password=pass"

[<Literal>]
let Schema = "test"

[<Literal>]
let ResPath = __SOURCE_DIRECTORY__ + @"./lib"

[<Literal>]
let IndivAmount = 1000

[<Literal>]
let UseOptTypes = true

type DB =
    SqlDataProvider<DatabaseVendor=DbVendor, ConnectionString=ConnString, ResolutionPath=ResPath, IndividualsAmount=IndivAmount, UseOptionTypes=UseOptTypes, Owner=Schema>

let ctx =
    DB.GetDataContext(selectOperations = SelectOperations.DatabaseSide)

let catDb = ctx.Test

In VSCode+Ionide that looks like:

Now let's load some cats in our database - with only F# code and the schema loaded!

First let's write a sort of 'domain layer' - this is just normal F# modelling with records and such:

// Domain modelling - normal F#!

/// A type of cat (ignoring attributes for now)
type Breed = {
    id : int option
    name : string
}

/// KITTEH
type Cat = {
    id : int option
    name : string
    breed : Breed
}

/// Make a cat with just a name and breed name, with None for ids
let mkCat catName breedName =
    { id = None; name = catName; breed = { id = None; name = breedName } }

/// Turn a cat to a string - my how the turn tables!
let catToStr cat =
    sprintf "%s(%d)" cat.name (Option.defaultValue -1 cat.id)

Second, let's write some db F# code as 'our db layer' using the SQLProvider boilerplate we wrote above:

// plain tables - query on these or get other functions
let Attributes = catDb.Attributes
let Cats = catDb.Cats
let Breeds = catDb.Breeds
let Owners = catDb.Owners

// join tables
let BreedAttrs = catDb.BreedAttributes
let OwnerCats = catDb.OwnerCats

// creating entity functions
let createBreed = Breeds.``Create(name)``
let createAttribute = Attributes.``Create(description, name)``
let createCat = Cats.``Create(breedid, name)``
let createOwner = Owners.``Create(age, name)``
let createBreedAttr = BreedAttrs.``Create(attributeid, breedid)``
let createOwnerCat = Owners.``Create(age, name)``

let findBreedIdByName bn =
    query {
        for b in Breeds do
        where (b.Name = bn)
        take 1
        select b.Id
    }

// DB functions
let insertBreed breedName () =
    let breedEntity = createBreed (breedName)
    ctx.SubmitUpdates()
    breedEntity.Id

let insertCat cat =
    let breedId =
        findBreedIdByName cat.breed.name
        |> Seq.tryHead
        |> Option.defaultWith (insertBreed cat.breed.name)

    let catEntity = createCat (breedId, cat.name)
    ctx.SubmitUpdates()

    { cat with id = Some catEntity.Id; breed = { cat.breed with id = Some breedId } }

So far so good, all the types line up - let's compose this into our main program with some helpers along the way to make it super 'fsharpy' and nice:

// Helpers

/// Take a function, and a tuple, and call the function with the destructured tuple
let withTuple f (a, b) =
    f a b

/// Take a tuple and make a cat in the db, then turn it into a string
let tupleToCatStr =
    withTuple mkCat
    >> insertCat
    >> catToStr

/// Take anything, and give back a zero - a success exit code
let returnZero _ = 0

let rawCatData =
    [
        ("Denton", "Gray Tabby")
        ("Mitzie", "Tuxedo")
        ("Saphire", "Blue Russian")
        ("Ailee", "Siamese")
        ("Oreo", "Tuxedo")
        ("Frisky", "Tuxedo")
    ]

[<EntryPoint>]
let main argv =
    rawCatData
    |> List.map tupleToCatStr
    |> String.concat ", "
    |> printfn "Made a bunch o cats!\n***\n%s\n***\n"
    |> returnZero

Now that we have it all setup, let's run it and see our database in action!

dotnet run && docker exec cat-postgres-db psql -U test_app -d postgres -c "SELECT c.name, b.name FROM test.cats c JOIN test.breeds b ON b.id = c.breedId"

Refactorings

That was a little messy, we can probably extract a lot of reuse from the functions we wrote so create a full fledged CLI cat catalog, so let's refactor before we really crank out some code.

Try your hand at refactoring from this commit if you want to try your refactor F# chops!

Here ate the modules I split out (these are in compile order):

The best name in all the land ;P
Helpers.fs

/// Abstract functional heplers that don't go anywhere particularly specific
module Helpers

Domain.fs

/// Domain modelling - normal F#!
module Domain

DbAcces.fs

/// Domain modelling - normal F#!
module DbAccess

Loader.fs

/// Load data into the db the easy way!
module Loader

Program.fs

/// It just calls whatever we're doin (in this case the Loader program)
module Program

All wrapped up in a beautiful commit.

Profit

Let's make a Cat Manager CLI app with the easiest data layer evaaar!

We'll need to make a new Cli module for all the... well the cli stuff! There'll be a loop in there and reading/parsing inputs, and then we can write any new db functions in our data layer and do any new modelling we need in the domain layer. If anything gets a little out of hand we can throw something into the helpers to help us out. We won't add any new functionality to the loader for this (in fact we will be removing the call to the Loader module, so it'll be dead weight for now).

The main CLI loop is as simple as:

let start() =
    let latestCatNumber = countNumOfCats()
    printfn "Cat Manager CLI 😺: Currently tracking %d cats" latestCatNumber

    let mutable keepGoing = true
    while keepGoing do
        keepGoing <-
            ()
            |> prompt
            |> getInput
            |> handleInput

    printfn "You're purrfect! 😽"
    0

Then you expand on handleInput:

/// Handles input - matches on lowered string and returns to keep going or not
let private handleInput (str: string) =
    let lowered = str.ToLowerInvariant()
    match lowered with
    | "?"
    | "help" ->
        printfn "Available Commands are:\n\t%s" commandList
        true
    | "c"
    | "count" ->
        let latestCatNumber = countNumOfCats()
        printfn "Currently tracking %d cats" latestCatNumber
        true
    | "a"
    | "add" -> handleAdd()
    | "q"
    | "quit" -> false
    | _ -> true

Here's the full commit.

Here's a screen shot of the program in action:

For the next part, we need to set up GitLab CI, I'll show you how to do it with GitLab's shared runners and your own runners (on say some kubernetes cluster) - there's some setup you'll have to do to ensure that your F# programs have access to a db with at least the schema preloaded even in the CI environment. We'll then talk about runtime configuration for production environments (often with secret passwords specified with environment variables). And after that, we'll be ready to create a full on web api project in F# using a modern CI/CD pipeline, and even do some clean architecture! Stay tuned!