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!
Top comments (1)
Beautiful! Thanks for sharing such a detailed sample!