This marks the final part of using SQL code generation tools with F#. I started this series to improve my overall SQL knowledge both in and out of the .NET ecosystem and with each post I've felt more and more comfortable. The one constant throughout is the awesome support of the F# community - in this case with regards to tooling.

Each technology I've looked at has solved the problem of data access in their own way and all deserve praise!
Today is no different as we look at Sql Hydra.

For the final time let's take a look at the big picture:

SQL Hydra 🐍

Sql Hydra is the final tool I was really looking forward to using, so much that I prolonged this series another post!
The excitement stemmed from having looked at the awesome work Jordan Marr did on Dapper.FSharp.

Let's get started!

Setup 🛠

Since SQL Hydra is a dotnet tool so you can eaily add it as part of your project.

dotnet new tool-manifest
dotnet tool install SqlHydra.SqlServer

With that we can run...

dotnet sqlhydra-mssql

...which will prompt you to complete a few questions, you can see the output in the sqlhydra-mssql.toml that gets generated.

This is similar to how Facil uses a yaml to generate it's content

[general]
connection = "CONNECTION_STRING"
output = "DbGen.fs"
namespace = "DbGen"
cli_mutable = false
[readers]
reader_type = "Microsoft.Data.SqlClient.SqlDataReader"
[filters]
include = []
exclude = []

Generated output 🖨

Sql Hydra will then output a file which will contain all of our data access logic.

Without getting too much into the weeds let's take a look at a snippet of generated code...

module dbo =
    type Pokemon =
        { PokeIndex: int
          Name: string
          EvolutionName: Option<string>
          PokemonTrainerId: System.Guid
          Level: int }

    type PokemonReader(reader: Microsoft.Data.SqlClient.SqlDataReader, getOrdinal) =
        member __.PokeIndex = RequiredColumn(reader, getOrdinal, reader.GetInt32, "PokeIndex")
        member __.Name = RequiredColumn(reader, getOrdinal, reader.GetString, "Name")
        member __.EvolutionName = OptionalColumn(reader, getOrdinal, reader.GetString, "EvolutionName")
        member __.PokemonTrainerId = RequiredColumn(reader, getOrdinal, reader.GetGuid, "PokemonTrainerId")
        member __.Level = RequiredColumn(reader, getOrdinal, reader.GetInt32, "Level")

        member __.Read() =
            { PokeIndex = __.PokeIndex.Read()
              Name = __.Name.Read()
              EvolutionName = __.EvolutionName.Read()
              PokemonTrainerId = __.PokemonTrainerId.Read()
              Level = __.Level.Read() }

        member __.ReadIfNotNull() =
            if __.PokeIndex.IsNull() then None else Some(__.Read())

What does this mean?

I see the above in two parts:

A generated DTO
A strongly typed dataReader

It feels like you can use Sql Hydra as either a low level access technology like Donald/DustyTables or as a MicroORM like Dapper.FSharp.

Low level access 🚧

Just a refresher of our ADO.NET extensions example.

Moving that over to Sql Hydra looks like...

I'll again only focus on the differences

open DbGen // this is the namespace from the generated output

let tryGetPokemonTrainer (trainerId: PokemonTrainerId) : PokemonTrainer option =
        ...
        let pokemonReader = PokemonReader(reader, reader.GetOrdinal)
        let pokemonTypeReader = PokemonTypesReader(reader, reader.GetOrdinal)
        let pokemonTrainerReader = PokemonTrainerReader(reader, reader.GetOrdinal)
        let pokemonTrainer =
            seq {
                while reader.Read () do
                {
                    Id = trainerId
                    Name = pokemonTrainerReader.Name.Read()
                    Record =
                        {
                            Wins = uint (pokemonTrainerReader.Wins.Read()) 
                            Losses = uint (pokemonTrainerReader.Losses.Read())
                        }
                    Pokemon = []
                }
            }
            |> Seq.tryHead

        reader.NextResult () |> ignore

        let pokemon =
            seq {
                while (reader.Read()) do
                    {
                        PokeIndex =  PokeIndex (pokemonReader.PokeIndex.Read())
                        Name = pokemonReader.Name.Read() 
                        Level = pokemonReader.Level.Read()
                        PokemonTypes =
                            pokemonTypeReader.PokemonTypeName.Read()
                            |> PokemonType.Deserialise
                            |> List.singleton
                        EvolutionName = pokemonReader.EvolutionName.Read()
                    }
            }
            |> Pokemon.concatPokemonTypes
            |> Seq.toList

        ...

Sql hydra has removed a lot of the boilerplate code we have to write when using ADO.NET or ADO.NET extensions!

Not only that but we can safely dot into the dataReader for our columns, similar to using SqlTypeProvider.

MicroORM route 🦠

Sql Hydra also generates DTO types, which are fully compatible with Dapper.FSharp so you can use the two in tandem!

I won't be giving a sample as the look and feel is very similar to my previous post on MicroORMs for more information check out the docs

Worth noting 📓

The DTOs generated support Option as well as the .Read() members on the custom dataReader!
Make it part of your build process
If your database type matches your domain type then you can use the .Read() method on the generated dataReader without going into the columns
Supports computation expressions, using SqlHydra.Query
The toml configuration has a lot more options

Summary 🌈

To conclude Sql Hydra is an incredible tool with a lot of thought behind it, and it integrates well with existing tools. I've yet to use it on a project but it will definitely be my next choice!

Next time 👀

Up next I'll be wrapping up this series by zooming back out and looking at what we've covered these past few months!

SQL code generation with F# part 2