Challenges of using SQL with F#

At the end of my last post on ADO.NET extensions. I mentioned that before continuing onto looking at Micro ORMs like Dapper/Dapper.FSharp I wanted to share my thoughts on some of the difficulties when mapping from F# to SQL and back.

Rich F# types

F# has an algebraic type system so it is very easy to start with a very simple type and begin to compose them together to create something that can model our business domain.

On the other hand SQL can only handle simple types - not including the relatively new JSON feature.
This often leads to tedious wrapping/unwrapping to go from SQL to F#. My domain throughout this series has been relatively simple. Not even including Disriminated Unions with data attached which definitely would make our journey a bit tougher.

For example something like:

type PokePerson =
    | GymLeader of GymLeader
    | Trainer of Trainer
    | Nurse of Nurse

Would add another layer of complexity in the form of extra tables + extra relationships and a lot more mapping code. Assuming that the GymLeader/Trainer/Nurse types were completly different we could end up with 4 new tables to model this DU:

• PokePerson
• GymLeader
• Trainer
• Nurse

Option

Option types are also relatively tricky to handle. You can build helpful methods that exist on the DataReader like Donald (rd.ReadXXXOption) and DustyTables (rd.XXXOrNone).

But it's not something ADO.NET and SQL can handle natively and can make rebuilding your domain type a tedious task.

F# -> SQL

let evolutionName =
    match pokemon.EvolutionName with
    | Some name -> box name
    | None -> box DBNull.Value

cmd.Parameters.AddWithValue ("@EvolutionName", evolutionName)

SQL -> F#

let evolutionName =
    let canEvolve = 
        "EvolutionName"
        |> reader.GetOrdinal
        |> reader.IsDBNull
        |> not

    if canEvolve then
        "EvolutionName"
        |> reader.GetOrdinal
        |> reader.GetString
        |> Some
    else
        None

Dapper.FSharp comes with a "mapper" that can automatically handle Option types

Mapping straight into your domain vs DTOs

It can be a challenge determining which route to go down when using SQL with F#

1. Mapping directly into my rich domain type?

With this approach you'd need access to the low level DataReader to be able to manipulate the shape of the returned structure into your domain.

Challenges:

• Having to rebuild even simple domain types
• Sometimes the majority of code you write is just database mapping
• Extending the DataReader

2. Creating an intermediary type - DTO?

A Data Transfer Object (DTO) "is an object that carries data between processes". In our case from our database to our application. We can go from SQL and map into a DTO which is a database representation of the object.

For example:

type PersonDTO = { Id: Guid; FirstName: string; LastName: string } // DTO
type Person = { Id: PersonId; FirstName: FirstName; LastName: LastName } // Domain

In this example our Person type within the application is made up of things like Discriminated Unions as compared to our DTO.

With this approach you can make use of an ORM - I'll go into more depth about ORMs in a later post!

Challenges:

• Keeping your DTOs up to date if your database changes (there is a great solution to this that we'll soon see)
• Having to still map from your DTO to your domain
• Dealing with constrained types

In some cases in order to create a domain type the inputs need to be validated to ensure certain conditions are met. You may have a private constructor function that returns back an Option/Result. With ORMs you can't go from SQL to this type as it involves invoking a function in the middle.

Summary

These are just a few of the challenges I've met while using SQL with F#. Although as I've mentioned before the F# community have made this journey a lot easier for people looking to use SQL with F#.