2021-12-11

Discriminated Unions and Dapper

Read Time: 12 minutes

Persisting data can be a subtle art. Today I am taking a look at how F#’s Discriminated Unions can interact with Dapper. I’ve discussed Dapper before, but never really discussed its facilities for interacting with Discriminated Unions. It is a useful bit of knowledge when determining project data structures.

For example purposes, I have a snippet from a financial tracking application. The part of interest is the definition of an Account along with it’s AccountType. For explanatory purposes, I am using abbreviated record and type definitions. Since it is so convenient, examples will leverage SQLite as a database. For reference, below are the package versions I’m using, along with .NET 5.

1
2
3

$ dotnet add package Dapper --version 2.0.123
$ dotnet add package System.Data.SQLite --version 1.0.115.5
$ dotnet add package Thoth.Json.Net --version 7.1.0

First, to start with some foundational parts. Half of this equation, is the database structure. The examples below use a simple table definition. For the first (enum) example AccountType is an int. For the other examples it is stored as a string. With the structural pieces out of the way I get to the most interesting aspects.

open System
open System.Data.SQLite
open Dapper

let databaseFileName = "../data/db.sqlite"
let connectionString = "Data Source=../data/db.sqlite;Version=3;"
let sqlEnumExample =
  """
    create table Accounts (
      AccountId varchar(100),
      AccountName varchar(200),
      AccountType int,
      primary key (accountid)
    );
  """

let sqlOtherExamples =
  """
    create table Accounts (
      AccountId varchar(100),
      AccountName varchar(200),
      AccountType varchar(100),
      primary key (accountid)
    );
  """

SQLiteConnection.CreateFile(databaseFileName)

use db = new SQLiteConnection(connectionString)
db.Open()

let command = new SQLiteCommand(sql, db)
let result = command.ExecuteNonQuery()
db.Close()

Some Discriminated Unions have a simple structure. Depending on intent, these can simply be represented as an enum. When this approach is chosen, the numeric value of the Discriminated Union is saved to the database. If you are looking at a fully normalized database structure with ids to external tables, this can be a perfectly reasonable approach. This does of course require a manual synchronization of the magic numbers between the type and database table. Like anything else, there are trade-offs. Digging in a bit deeper, below is one way this can be implemented. Because it is useful to see some of the underlying details, I include the results of a generic Dapper call as well as the one that directly casts the results into the Account type.

The example code is typical Dapper/F# example code. Database inserting and selecting map cleanly to F# record and discriminated union types. With this approach, everything is just handled.

type AccountType =
  | Checking = 100
  | CreditCard = 200
  | Investing = 300
  | Ira = 400
  | Savings = 500

[<CLIMutable>]
type Account =
  { AccountId: string
    AccountName: string
    AccountType: AccountType }
  override __.ToString() =
    sprintf "%-10s %-25s %-25s" __.AccountId __.AccountName (__.AccountType.ToString())

let accounts =
  [ { AccountId = "1001"
      AccountName = "Savings Account 1"
      AccountType = AccountType.Savings }
    { AccountId = "1002"
      AccountName = "Retirement Account 1"
      AccountType = AccountType.Ira } ]

let db = new SQLiteConnection(connectionString)
db.Open()

let insertSql = "insert into accounts (accountid, accountname, accounttype) values (@accountid, @accountname, @accounttype)"

let count =
  accounts
  |> List.sumBy (fun record -> db.Execute(insertSql, record))

printfn $"{count} records inserted."

let selectSql = "select accountid, accountname, accounttype from accounts"

for record in db.Query(selectSql) do
  printfn $"{record}"

for record in db.Query<Account>(selectSql) do
  printfn $"{record}"

2 records inserted.

{DapperRow, AccountId = '1001', AccountName = 'Savings Account 1', AccountType = '500'}
{DapperRow, AccountId = '1002', AccountName = 'Retirement Account 1', AccountType = '400'}

1001       Savings Account 1         Savings
1002       Retirement Account 1      Ira

There are multiple ways to tackle the problem, so it is time to look at a different approach. Let’s say I don’t want AccountType to be an enum. Perhaps I want to store data “truer” to the F# type shape. What options do I have. One way is to treat this simple discriminated union by mapping it’s underlying values to string values (e.g. AccountType.Savings maps to the string “Savings”). It is easy to understand, but this doesn’t work directly with Dapper, with no intervention. I need to build out a little support for this to work. First, I create Serialize and Deserialize methods on AccountType. Second, I need to create a handler to map from/to the discriminated union and the stored sql string. For this Dapper provides a TypeHandler, from that I inherit and create an AccountTypeHandler. The contained methods tell Dapper how to proxy the data between the worlds of the database and application. Parse takes the string value in the database and returns an AccountType. SetValue does the reverse, by converting an AccountType to a string. It should be noted at this point I am doing simple string serialization and deserialization. If I wanted to do something fancier in here I could, but this is a direct approach meets my goals. The third, and final, piece is to activate the AccountTypeHandler with the SqlMapper.AddTypeHandler method. Now I can do inserts and selects like I did before. But the data is stored using my specified string serialization.

type AccountType =
  | Checking
  | CreditCard
  | Investing
  | Ira
  | Savings
  static member Serialize(accountType: AccountType) = accountType.ToString()

  static member Deserialize(s: string) =
    match s with
    | nameof Checking -> Ok Checking
    | nameof Savings -> Ok Savings
    | nameof Investing -> Ok Investing
    | nameof Ira -> Ok Ira
    | nameof CreditCard -> Ok CreditCard
    | _ -> Error(sprintf "Invalid account type '%s'" s)

[<CLIMutable>]
type Account =
  { AccountId: string
    AccountName: string
    AccountType: AccountType }
  override __.ToString() =
    sprintf "%-10s %-25s %-25s" __.AccountId __.AccountName (__.AccountType.ToString())

type AccountTypeHandler() =
  inherit SqlMapper.TypeHandler<AccountType>()

  override __.Parse(value) =
    match AccountType.Deserialize(string value) with
    | Ok (y) -> y
    | Error (e) -> failwith e

  override __.SetValue(p, value) =
    p.DbType <- Data.DbType.String
    p.Size <- 100
    p.Value <- AccountType.Serialize value

let accounts =
  [ { AccountId = "1001"
      AccountName = "Savings Account 1"
      AccountType = AccountType.Savings }
    { AccountId = "1002"
      AccountName = "Retirement Account 1"
      AccountType = AccountType.Ira } ]

let db = new SQLiteConnection(connectionString)
db.Open()
SqlMapper.AddTypeHandler(typeof<AccountType>, AccountTypeHandler())

let insertSql = "insert into accounts (accountid, accountname, accounttype) values (@accountid, @accountname, @accounttype)"

let count =
  accounts
  |> List.sumBy (fun record -> db.Execute(insertSql, record))

printfn $"{count} records inserted."

let selectSql = "select accountid, accountname, accounttype from accounts"

for record in db.Query(selectSql) do
  printfn $"{record}"

for record in db.Query<Account>(selectSql) do
  printfn $"{record}"

2 records inserted.

{DapperRow, AccountId = '1001', AccountName = 'Savings Account 1', AccountType = 'Savings'}
{DapperRow, AccountId = '1002', AccountName = 'Retirement Account 1', AccountType = 'Ira'}

1001       Savings Account 1         Savings
1002       Retirement Account 1      Ira

There is a natural next question, what if the discriminated union isn’t so simple? One of F#’s strengths is modeling complex data. So this seems like another good example of the power of different approaches. The fundamental question is how to represent the data. There are a multitude of ways to address this. I’ve decided on something really simple; use Thoth to serialize the Discriminated Union. As you can see below, there are now different types of IRAs, and Savings accounts need a bank name attached. How does this change the previous approach? The Serialize and Deserialize methods of AccountType are modified to use Thoth Auto-Encoding and Auto-Decoding (respectively). Everything else stays the same as the previous example. This can be a point of debate if this is the best serialization method, but changing the serialization functions give enough flexibility to meet your specific needs. Addendum: A question was raised that reminded me of something I originally failed to mention. Like the enum solution, there are trade offs when serializing a DU for persistence. In some cases where the DU structure requires refactoring, you may need to handle data migration to support an updated representation. This is really only an issue with some structure changes, not additions to the DU. Even in these cases, there are context-specific mitigation techniques in both design and refactoring to reduce the pain, but it is still worth mentioning.

open Thoth.Json.Net

type IraType =
  | Roth
  | Simple
  | Traditional

type AccountType =
  | Checking
  | CreditCard
  | Investing
  | Ira of IraType
  | Savings of string
  static member Serialize(accountType: AccountType) = Encode.Auto.toString (2, accountType)
  static member Deserialize(s: string) = Decode.Auto.fromString<AccountType> (s)

[<CLIMutable>]
type Account =
  { AccountId: string
    AccountName: string
    AccountType: AccountType }
  override __.ToString() =
    sprintf "%-10s %-25s %-25s" __.AccountId __.AccountName (__.AccountType.ToString())

type AccountTypeHandler() =
  inherit SqlMapper.TypeHandler<AccountType>()

  override __.Parse(value) =
    match AccountType.Deserialize(string value) with
    | Ok (y) -> y
    | Error (e) -> failwith e

  override __.SetValue(p, value) =
    p.DbType <- Data.DbType.String
    p.Size <- 100
    p.Value <- AccountType.Serialize value

let accounts =
  [ { AccountId = "1001"
      AccountName = "Savings Account 1"
      AccountType = AccountType.Savings "First Bank" }
    { AccountId = "1002"
      AccountName = "Retirement Account 1"
      AccountType = AccountType.Ira Roth }
    { AccountId = "1003"
      AccountName = "Retirement Account 2"
      AccountType = AccountType.Ira Traditional }
    { AccountId = "1004"
      AccountName = "Checking Account 1"
      AccountType = AccountType.Checking } ]

let db = new SQLiteConnection(connectionString)
db.Open()
SqlMapper.AddTypeHandler(typeof<AccountType>, AccountTypeHandler())

let insertSql = "insert into accounts (accountid, accountname, accounttype) values (@accountid, @accountname, @accounttype)"

let count =
  accounts
  |> List.sumBy (fun record -> db.Execute(insertSql, record))

printfn $"{count} records inserted."

let selectSql = "select accountid, accountname, accounttype from accounts"

for record in db.Query(selectSql) do
  printfn $"{record}"

for record in db.Query<Account>(selectSql) do
  printfn $"{record}"

4 records inserted.

{DapperRow, AccountId = '1001', AccountName = 'Savings Account 1', AccountType = '[
  "Savings",
  "First Bank"
]'}
{DapperRow, AccountId = '1002', AccountName = 'Retirement Account 1', AccountType = '[
  "Ira",
  "Roth"
]'}
{DapperRow, AccountId = '1003', AccountName = 'Retirement Account 2', AccountType = '[
  "Ira",
  "Traditional"
]'}

{DapperRow, AccountId = '1004', AccountName = 'Checking Account 1', AccountType = '"Checking"'}
1001       Savings Account 1         Savings "First Bank"
1002       Retirement Account 1      Ira Roth
1003       Retirement Account 2      Ira Traditional
1004       Checking Account 1        Checking

As you can see, Dapper provides good support for flexible data type mapping. Hopefully these different approaches give you some ideas regarding how you can integrate Dapper and its type handlers with F# for your applications.