Type Safe DB Access checked on every Commit
Tags: fsharp, functional, gitlab, sqlprovider
Published: 2020-02-09 04:26:51

---
title: Type Safe DB Access checked on every Commit
published: true
date: 2020-02-09 04:20:03 UTC
tags: fsharp,functional,gitlab,sqlprovider
canonical_url: https://sean.thenewells.us/type-safe-db-access-every-commit/
---

 ![Type Safe DB Access checked on every Commit](https://sean.thenewells.us/content/images/2020/02/ryan-quintal-US9Tc9pKNBU-unsplash.jpg)

Photo by [Ryan Quintal](https://unsplash.com/@ryanquintal?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) on [Unsplash](https://unsplash.com/s/photos/lego-bricks?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText).

The previous [post](https://sean.thenewells.us/f-sql-provider-in-net-core-3-1/) had a local setup for type safe SQL access with F#'s Type Provider - [SQLProvider](https://github.com/fsprojects/SQLProvider/). In this post we're going to move into CI territory from [this commit in our repo](https://gitlab.com/sNewell/fsharp-sql-provider/-/tree/5829e7742e0f05cc4455bd5b7dafb8f6fba6a26d); enforcing that every commit has to be working with the DB Schema in a valid way. In order to do that - in general for any CI provider - we're going to have to duplicate the steps we did locally on our machines

_**We'll track our work in a branch [feat/ci-capabilities](https://gitlab.com/sNewell/fsharp-sql-provider/-/tree/feat/ci-capabilities) and land it in a [Merge Request](https://gitlab.com/sNewell/fsharp-sql-provider/-/mergerequests/1).**_

# General CI

So, in general, in order to compile our F# project with SQLProvider, we need:

- A running database
  - With a schema loaded
- A valid connection string to the running database
- Nuget's restore to pull down the right dlls
  - We have a pre build script that can move those dlls into a spot for the F# compiler.

Depending on your CI provider, there are a few different ways to approach getting a db, loaded with a schema, into your pipeline.

1. Have a 'stable' external environment accessible to your builds
2. Use a feature from your provider (ex [GitLab services](https://docs.gitlab.com/ce/ci/services/postgres.html))
3. Leverage docker/custom-images/nested-docker/docker compose to set things up and orchestrate (containerization solves everything right?)

In this post we'll explore GitLab's postgres service solution on shared runners.

# GitLab Shared Runners

_Note - We'll explore how to leverage a personal k8s cluster of runners in the next post._

GitLab offers free CI/CD for all of their repositories on their [shared runner](https://docs.gitlab.com/ee/ci/README.html) infrastructure. They have a feature called services (linked above as option 2) that we can leverage to have a postgres db loaded for a job.

One of the annoying things about CI/CD (this goes for any CI provider) is trying to test out your pipeline/job before just pushing and seeing if it works. That can be... a not-so-great experience. It's one thing to test a shell script locally, but it's another to:

- Have valid yaml
- Have a valid sequence of steps
- Use a feature (aka service) correctly

And if you try to do anything complicated that propogate artifacts or has dependent jobs, it becomes a lot harder to test the pipeline before git push and pray.

Luckily, GitLab has a nice way to install a tool to help you test locally before pushing. They have a nice set of [instructions](https://docs.gitlab.com/runner/install/) on their docs, and on my debian buster system it was fairly straight forward:

```sh
curl -L https://packages.gitlab.com/install/repositories/runner/gitlab-runner/script.deb.sh \
  | sudo bash \
  && sudo apt-get install gitlab-runner \
  && which gitlab-runner
```

In order to see if this works, let's just do the simplest build step:

```yaml
build:
  script:
    - echo "Hello World"
```

Now we can run this locally with the shell executor:

```sh
▶ gitlab-runner exec shell build
Runtime platform arch=amd64 os=linux pid=6518 revision=003fe500 version=12.7.1
Running with gitlab-runner 12.7.1 (003fe500)
Using Shell executor...
executor not supported job=1 project=0 referee=metrics
Running on flantop...
Fetching changes...
Initialized empty Git repository in /home/sean/repos/fsharp-sql-provider/builds/0/project-0/.git/
Created fresh repository.
From /home/sean/repos/fsharp-sql-provider
 * [new branch] master -> origin/master
Checking out 5829e774 as master...
Skipping Git submodules setup
$ echo "Hello World"
Hello World
Job succeeded
```

OH WOWEE.

![Type Safe DB Access checked on every Commit](https://media.giphy.com/media/l4q7VhGsL6BnXJrc4/giphy.gif)

If you add an `image` to the yaml, you can use the docker executor (assuming you have docker setup locally).

```diff
  build:
+ image: alpine:3.11.3
  script:
    - echo "Hello World!"
```

Now you can use the `docker` executor instead of the shell executor, which will be 'more real' since we'll need to have the dotnet core sdk available for our F# builds: `$ gitlab-runner exec docker build`.

OH NICE!

![Type Safe DB Access checked on every Commit](https://media.giphy.com/media/13ByqbM0hgfN7y/giphy.gif)

Okay, that's all fine and dandy, but we're business-business and need to build enterprising enterprise apps (which of course means we need the dotnet core sdk).

![Type Safe DB Access checked on every Commit](https://sean.thenewells.us/content/images/2020/02/business-cat-boxes.jpg)

```diff
  build:
- image: alpine:3.11.3
+ image: mcr.microsoft.com/dotnet/core/sdk:3.1-alpine
  script:
    - dotnet build
```

Unfortunately that won't work. We need a database too - so let's set that up with some services! But wait... we need to load our schema into this database, and our current `run-postgres-container.sh` script assumes we're using docker to exec `psql` - when we really should be executing `psql` in our job, connecting to the service that gitlab will set up for us. We'll have to write a custom script the ci will run, since the service will setup the user for us, but not the schema. Let's call that `ci-pg-setup.sh` and it'll look like:

```sh
#!/bin/sh

echo "CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test_app;" \
  | PGPASSWORD=pass psql -h postgres -U test_app -d postgres -f -

cat schema.sql | PGPASSWORD=pass psql -h postgres -U test_app -d postgres -f -
```

We'll also need to make a change to our `prebuild.sh` to make it POSIX compliant, using `sh` instead of `bash` - see [this commit](https://gitlab.com/sNewell/fsharp-sql-provider/commit/74fa61c7777e2344386d4c04268df0f27b3c4122) for deets.

Now before we update the CI yaml we'll need to update our code to conditionally use the `postgres` host (due to docker networking in the docker executor). We can do that by conditionally creating a debug symbol in our `fsproj` file based on the presence and value of an environment variable, and using that symbol to switch our compile time constant `ConnString` in `DbAccess.fs`. That's done in [this commit](https://gitlab.com/sNewell/fsharp-sql-provider/commit/eda6a1234edccd340651968b45eeb625eca9045f). It's important to note that we're depending on the `psql` tool in our ci setup script, so we need to make sure we add that (`apk add postgres-client`) in our job.

Our full yaml is setup in [one commit here](https://gitlab.com/sNewell/fsharp-sql-provider/commit/475f1e950bd7f4c3f6526e8a73390217ecfb13d1) - copied here for your full pleasure:

```yaml
build:
  image: mcr.microsoft.com/dotnet/core/sdk:3.1-alpine
  services:
    - postgres:12.1-alpine
  variables:
    POSTGRES_DB: postgres
    POSTGRES_USER: test_app
    POSTGRES_PASSWORD: pass
  script:
    - apk add postgresql-client
    - ./ci-pg-setup.sh
    - CI=TRUE dotnet build
```

_**And voila! We have a [passing job](https://gitlab.com/sNewell/fsharp-sql-provider/-/jobs/430727670) that checked our data layer!**_

# Break it

Let's break our build!

One way to break our build would be to write the code without updating our schema script (maybe some dev manually changes their local schema without committing the schema changes) - or to change the schema in a way that causes F# to blow up. Let's do the latter.

**schema.sql**
```diff
@@ -20,6 +20,7 @@ CREATE TABLE IF NOT EXISTS test.breed_attributes(
 CREATE TABLE IF NOT EXISTS test.cats(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL DEFAULT '',
+ age INTEGER NOT NULL DEFAULT 1,
   breedId INTEGER NOT NULL DEFAULT 1,
   FOREIGN KEY (breedId) REFERENCES test.breeds
 );
```

With this we should expect an error like this (emphasis added by mua):

```diff
 error FS0039:
   The field, constructor or member 'Create(breedid, name)' is not defined.
   Maybe you want one of the following:
+ Create(age, breedid, name)
```

And the proof is in the pudding! A [failed build](https://gitlab.com/sNewell/fsharp-sql-provider/-/jobs/430735465) in our pipeline!

Let's fix it. Now that Cat's require an `age` as per our schema, we should update our data layer, and our domain type too. Those changes will cascade down to the loader and cli programs, but [after that](https://gitlab.com/sNewell/fsharp-sql-provider/commit/0aea4ffd84436d187e838e00b15e69bade215e56) the compiler has done all the hunting for us, and [if it compiles](https://gitlab.com/sNewell/fsharp-sql-provider/pipelines/116234606), the program will run soundly.

# Merging it in

There are a few tweaks I've made before merging:

- Bundle `psql` into the image we're using
- Fix typos here and there
- Use the module alias like we did in `Loader` in `Cli`

And that's [a wrap](https://gitlab.com/sNewell/fsharp-sql-provider/-/merge_requests/1)!

![Type Safe DB Access checked on every Commit](https://media.giphy.com/media/xNBcChLQt7s9a/giphy.gif)

# Future Work

There's still a lot to do to get to a a point where this is workable for a modern CI/CD web app, in the future I'll put up posts to:

- Instead of shared runners, use a dedicated k8s cluster
- Dockerize the F# application
- Introduce a web layer for API
- Create a deployment
- Automate the deployment

Stay tuned!