In my last few posts I've mentioned that I'll be looking at Micro ORMs and what the current ecosystem looks like in F#.

Before we dive in, a reminder of the big picture and where Micro ORMs sit:

Dapper 🤵

Dapper is an object–relational mapping (ORM) product for the Microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database.

It allows us to avoid hand mapping we've had to do using ADO.NET or ADO.NET extensions. Dapper is very popular, but how will it handle the rich types we can create in F#?

Data Transfer Object

In order to use Dapper effectively we need to play into it's strengths, which means creating an intermediary type that is "simple" and made up of the types that will map nicely into SQL.
For example:

type PokemonTrainerDto = 
    { 
        Id: Guid
        Name: string
        Wins: int
        Losses: int
    }

By introducing a DTO type you'll also need some mapping code to go to and from your rich domain type.

let trainerDtoToDomain (pokemon: Pokemon list) (pokemonTrainerDto : PokemonTrainerDto) : PokemonTrainer = 
    {
        Id = PokemonTrainerId pokemonTrainerDto.Id
        Name = pokemonTrainerDto.Name
        Record = { Wins = uint pokemonTrainerDto.Wins; Losses = uint pokemonTrainerDto.Losses }
        Pokemon = pokemon
    }

As I previously mentioned, the example use case is the same throughout this series, to provide better insight to what SQL technology is the best fit for you.

let tryGetPokemonTrainer (PokemonTrainerId trainerId) = 
    use conn = getConnection ()
    conn.Open()

    use multi = conn.QueryMultiple (pokemonTrainerQuery, {| Id = trainerId |})

    let pokemonTrainerDto =
        multi.Read<PokemonTrainerDto>()
        |> Seq.toList
        |> List.tryHead

    let pokemonDtos = multi.Read<PokemonDto>()

    let pokemon =
        pokemonDtos
        |> Seq.map pokemonDtoToDomain
        |> Pokemon.concatPokemonTypes

    pokemonTrainerDto
    |> Option.map (trainerDtoToDomain pokemon)

What part of the sample is Dapper?

QueryMultiple: This method comes from Dapper and can execute multiple queries within the same command.
Read: Reads the next grid of results.

Since I've touched on the advantages and disadvantages of using a Micro ORM in my last post I'll move onto...

Dapper.FSharp 🦸‍♂️

If you like the look of Dapper or are already using it, then Dapper.FSharp is the perfect next step. It's a 'lightweight F# extension' allowing you to easily drop back to Dapper whenever you need. There are two specific areas in which Dapper.FSharp shines 🌟

Option

By adding the following line ...

Dapper.FSharp.OptionTypes.register()

... Dapper.FSharp will convert any NULL values into Option.None!

We saw option support in lower level data access layers like DustyTables and Donald, but Dapper.FSharp handles that automagically. 🧙‍♂️

Computation Expressions

Computation expressions (CEs) are a great way to create a DSL and remove some boilerplate code by offering a higher level of abstraction. Dapper.FSharp uses them to perform SQL commands and queries providing a computation expression for:

select
update
delete
insert

We'll start by defining our tables. This could be done in the query, but defining them separately allows them to be shared across multiple queries.

let pokemonTrainerTable = table<PokemonTrainerDto>
let pokemonTable = table<PokemonDto>
let pokemonType_PokemonTable = table<PokemonType_PokemonDto>
let pokemonTypeTable = table<PokemonTypeDto>

The Dapper-specific parts then become:

let! pokemonTrainerDto =
    select {
        for p in pokemonTrainerTable do 
        where (p.Id = trainerId) 
    } 
    |> conn.SelectAsync<PokemonTrainerDto>

let! pokemonDtos =
    select {
        for p in pokemonTable do 
        leftJoin type_pokemon in pokemonType_PokemonTable on (p.PokeIndex = type_pokemon.PokeIndex)
        leftJoin t in pokemonTypeTable on (type_pokemon.PokemonTypeId = t.PokemonTypeId)
        where (p.PokemonTrainerId = trainerId)
    } 
    |> conn.SelectAsync<PokemonDto>

Worth noting

You can use Option support without using any of the other features
Though Dapper.FSharp has Option support it can't automatically map to other DU types
Order matters: for example, when defining pokemonDtos above, the right-hand side of the equality check (type_pokemon.PokemonTypeId = t.PokemonTypeId) must refer to the table you're joining to.
Joining on multiple conditions isn't supported in the "traditional" sense, the following does not work:
```
    leftJoin t in pokemonTypeTable on ((type_pokemon.PokemonTypeId = t.PokemonTypeId) && (a = b))
```
To get around this you can tuple up the check. Props to Ryan for figuring that out! 🐱‍👤
```
    leftJoin t in pokemonTypeTable on (type_pokemon.PokemonTypeId, a = t.PokemonTypeId, b)
```
A single query only supports 2 joins

Summary 🌈

I really enjoyed learning and getting to use both Dapper and Dapper.FSharp. I was lucky enough to use Dapper.FSharp in a production application and it worked brilliantly! There is some overhead having to manually create DTOs and ensuring those are kept up to date with your SQL tables...but that's a problem for another time!

Next time 👀

In the next blog in the series I'll be looking to use a Full ORM!

Mirco ORMs and F#