Sample CI for F# Project w/ SQL Type Provider
This repo contains a sample CI configuration that allows an fsharp project w/ sql provider to:
- build on CI
- build a docker image
F#’s Type Providers
F#’s type providers allows for usage of strong types when accessing structured data. Different than traditional ORMs that require the programmer to manually specify the type with which to read the data, the F# compiler infers a type from samples of the data.
F# ships with a few type providers. To name a few: csv, json, xmls, and sql.
Strongly Typed SQL
By using an SQL type provider, instead of writing your SQL queries as strings, one can write them w/ F#’s computation expressions.
- compile checks
After getting used to SQL type providers one will often wonder why mankind has ever opted to write SQL queries in raw strings. After all, relational databases do have strong type definition of table columns. Why would you write queries in a language that doesn’t benefit from strong type guarantees?
Type providers and CI
Note however, that the compiler must have access to samples of the data. This is easy with a csv and json: you just point it to a file committed on the repository.
For a SQL type providers, you need to grant the compiler access to a running database.
I will write this again: your compiler needs read access to a running database.
Furthermore, the database must have the schema that you expect to read on CI. This is easily done on the developer environment, but what about CI environments? Finally, how to build a Docker image of a source code that requires a running database in order for compile?
These questions have prohibited our team from bringing sql type providers to production for a while. We have worked out our solution, which is described on the remaining of this document.
2 Databases on our production solution
Our solution to this problem uses 2 databases.
- Runtime database
- this is the database that you produce by running the migrations. On production, this contains your real data. The credentials for this database are strong (usually read from secret vaults during runtime).
- Compile time database
- this is a database that has a copy of the schema. It must be accessed by the compiler. The credentials for this database are written on the source code.
The 2 database solution emerged out of the necessity of having hard coded connection strings on the source code. Recall: every time you need the compiler, you need to access the database. Your auto complete needs it, your test suite as well. We could not figure out a way to have the compile time database use strong credentials, so we opted to leave the connection string for this database directly on the source code.
The solution above is what we use on production. On this sample project however, we have simplified a bit and use a single database. When porting to your production app, make sure to differentiate betwee compile and runtime database.
The instructions on this manual were tested on dotnet 5.0.400. I expect the instructions to work on later versions of net5, but I expect some changes to be necessary.
The technology vision of our team specifically states that we seek to leverage Hindley-Milner type systems as business value to the best of our capabilities. We want compilers to help programmers write better software at cheaper implementation costs. We are big enthusiasts of strongly typed function programming. We appreciate the fact that F# bridges modern research in programming languages and the time tested .NET platform.
We use DbUp to run our migrations. DbUp is a dotnet library for running migrations written in SQL files. The project
src/Migrations uses it to expose:
- A command line tool that runs migrations
- A library that can be called from our main application in order to run migrations during runtime.
We prefer relational databases over non relational ones. We think Postgresql is the best open source relational database.
Building the project locally
- Start the database
docker-compose up -d postgres
- Install dependencies
dotnet tool restore dotnet paket restore
- Apply the migrations
dotnet run -p src/Migrations
- You can now build your project with:
Building the project on CI
This repo contains CI instructions for Github.
The trick is that you need to:
- Declare a postgres service that will accept the same connection string that you use on your development environment
- Run the migrations before building your project
Building a Docker image
One often builds docker image with the command:
The command above fetches a base image and executes the commands outlined on Dockerfile one by one - each one building a new docker layer. The docker image we want to deploy is the final layer produced by this process.
However, when executing the commands above, we need the dotnet compiler to have access to a running database.
We could not figure out if it makes to try to solve this problem with Docker or not. We solved it by having the host machine build the dotnet .DLL, and then copying these assets into the docker image.
We created a script that will:
- Clear the environment of the host machine (changes to the repo as well as running containers)
- Start a clean database
- Apply migrations
- Publish the dotnet executable assets
- Build the docker image
- The dockerfile has a command that copies executable assets from the host machine into the Docker image
From the root of the repo execute:
You can run the app from the docker container with:
docker run -it --rm sqlprovider-demo:latest /app/build/SampleSQLProviderApp
Porting schema from runtime databse into compile time database
We use the following commands to transfer the schema from production database (on these commands, assumed to run on port 5432) into the compile database (5433).
- Create an sql dump of your schema
PGPASSWORD=admin pg_dump -U dbuser -h 127.0.0.1 -p 5432 -d demo -s -c -x -O --no-comments > db-schema.sql
- Load the sql dump on the compile time database
PGPASSWORD=admin psql -d postgres -U postgres -h 127.0.0.1 -p 5433 < db-schema.sql