As we near the end of this series we'll be looking at SQL code generation tools and just like the final Harry Potter movie I'll be splitting this final post into two! 🧙‍♂️

As always here's a reminder of the big picture.

Facil 🤖

Facil generates F# data access source code from SQL queries and stored procedures. Optimized for developer happiness.

Having used Facil on multiple projects I can confirm that I am one happy developer. It removed a lot of boilerplate from the code base and freed us to think about the domain problems.

Let's see what makes Facil so awesome

Setup 🔨

When you install Facil, it will automatically add a facil.yaml file to your project.

Facil uses a yaml file to specify configuration, the below is the yaml file I prefer to start with:

configs:
  - appSettings: appsettings.json

rulesets:
  - connectionString: $(DbConnectionString)

    filename: DbGen.fs
    namespaceOrModuleDeclaration: module DbGen
    scriptBasePath: ../some/path
    scripts:
      - include: "Queries/*.sql"

    tableDtos:
      - include: .*
        except: dbo\.__RefactorLog

    tableScripts:
      - include: .*
        except: dbo\.__RefactorLog
        scripts:
          - type: insert
          - type: update
          - type: merge
          - type: delete
          - type: getById

The full config/docs can be found here

How does this apply to our shared example throughout this series?

Taking our shared SQL from the ADO.NET extensions blog and breaking it into two SQL scripts.

Queries/GetPokemonTrainer.sql

SELECT PokemonTrainer.Id, PokemonTrainer.Name as TrainerName, PokemonTrainer.Wins, PokemonTrainer.Losses 
FROM PokemonTrainer
WHERE PokemonTrainer.Id = @Id;

Queries/GetPokemonByTrainer.sql

SELECT Pokemon.PokeIndex, Name, EvolutionName, Level, PokemonTypeName 
FROM Pokemon
JOIN PokemonType_Pokemon ON Pokemon.PokeIndex = PokemonType_Pokemon.PokeIndex
JOIN PokemonTypes ON PokemonType_Pokemon.PokemonTypeId = PokemonTypes.PokemonTypeId
WHERE Pokemon.PokemonTrainerId = @Id

On a successful build of the project Facil will produce DbGen.fs. I won't be going into this file as that could be a whole blog in itself!

Generated DTOs 🚃

Here's an example of a generated DTO:

    type ``Pokemon`` =
      {
        ``PokeIndex``: int
        ``Name``: string
        ``EvolutionName``: string option
        ``PokemonTrainerId``: Guid
        ``Level``: int
      }

      static member getPrimaryKey (dto: ``Pokemon``) =
        dto.``PokeIndex``

As you can see it has automatically mapped null to Option!

You won't be interacting directly with these types as they are part of the generated file.
Any domain <-> DTO function will need to be updated to use the Facil generated types.

Using the output 🎆

open DbGen.Scripts
open DbGen.TableDtos

As with my last post I'll only be focusing on the changes of the tryGetPokemonTrainer function rather than copying and pasting it.

...

let! pokemonTrainerDto: dbo.PokemonTrainer option =
    Queries
        .GetPokemonTrainer
        .WithConnection(connectionString)
        .WithParameters(trainerId)
        .AsyncExecuteSingle()

let! pokemonDtos: ResizeArray<dbo.Pokemon> =
    Queries
        .GetPokemonByTrainer
        .WithConnection(connectionString)
        .WithParameters(trainerId)
        .AsyncExecute()
...

That's all there is to it! Facil handles all the heavy lifting which is why it shines as a data access layer. 🌟

Worth noting 📓

With Facil your DTOs are always up to date!
You can omit fields in the generated DTOs and scripts with a skip: true setting - this is really helpful when you have a column that the business application doesn't care about i.e: auto incrementing ID on a link table
Facil regenerates if the environment variable FACIL_FORCE_REGENERATE exists or whenever there is a change in:
- The two first lines of the generated file(s)
- Included SQL scripts
- The config file
- Facil itself

If you use a parameter in multiple queries in a single SQL script you have to specify that as part of your facil.yaml

...
scripts:
  - include: "Queries/*.sql"
  - for: Queries/___.sql
    params:
      Id:
        type: uniqueidentifier
...

Summary 🌈

I have loved using Facil, I think it also acts as a reminder that writing raw SQL is okay and often easier than using a DSL. There are a bunch more configuration options that I've not covered. Facil can be used alongside any of the technologies we've previously discussed, for example you can use Facil to generate your DTOs ensuring they're in sync with the Database and Dapper.FSharp's computation expressions to query SQL.

Next time 👀

In my final post l'll be looking into SqlHydra!

SQL Code generation with F# part 1