Dapper.FSharp (pre-v3 release docs)

Lightweight F# extension for StackOverflow Dapper with support for MSSQL, MySQL and PostgreSQL

Features

No auto-attribute-based-only-author-maybe-knows-magic behavior
Support for F# records / anonymous records
Support for F# options
LINQ Query Provider
Support for SQL Server 2012 (11.x) and later / Azure SQL Database, MySQL 8.0, PostgreSQL 12.0
Support for SELECT (including JOINs), INSERT, UPDATE (full / partial), DELETE
Support for OUTPUT clause (MSSQL only)
Easy usage thanks to F# computation expressions
Keeps things simple

Installation

If you want to install this package manually, use usual NuGet package command

Install-Package Dapper.FSharp

or using Paket

paket add Dapper.FSharp

FAQ

Why another library around Dapper?

I've created this library to cover most of my own use-cases where in 90% I need just few simple queries for CRUD operations using Dapper and don't want to write column names manually. All I need is simple (anonymous) record with properties and want to have them filled from query or to insert / update data.

How does library works?

This library does two things:

Provides 4 computation expression builders for select, insert, update and delete. Those expressions creates definitions (just simple records, no worries) of SQL queries.
Extends IDbConnection with few more methods to handle such definitions and creates proper SQL query + parameters for Dapper. Then it calls Dapper QueryAsync or ExecuteAsync. How does library knows the column names? It uses reflection to get record properties. So yes, there is one (the only) simple rule: All property names must match columns in table.

Do I need to create record with all columns?

You can, but don't have to. If you need to read only subset of data, you can create special view record just for this. Also if you don't want to write nullable data, you can omit them in record definition.

And what about names mapping using Attributes or foreign keys magic?

Nope. Sorry. Not gonna happen in this library. Simplicity is what matters. Just define your record as it is in database and you are ok.

Can I map more records from one query?

Yes. If you use LEFT or INNER JOIN, you can map each table to separate record. If you use LEFT JOIN, you can even map 2nd and/or 3rd table to Option (F# records and null values don't work well together). Current limitation is 3 tables (two joins).

What if I need join more than 3 tables, sub-select or something special?

Fallback to plain Dapper then. Really. Dapper is amazing library and sometimes there's nothing better than manually written optimized SQL query. Remember this library has one and only goal: Simplify 90% of repetitive SQL queries you would have to write manually. Nothing. Else.

Getting started

First of all, you need to init registration of mappers for optional types to have Dapper mappings understand that NULL from database = Option.None

Dapper.FSharp.OptionTypes.register()

It's recommended to do it somewhere close to program entry point or in Startup class.

Example database

Lets have a database table called Persons:

CREATE TABLE [dbo].[Persons](
    [Id] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](max) NOT NULL,
    [LastName] [nvarchar](max) NOT NULL,
    [Position] [int] NOT NULL,
    [DateOfBirth] [datetime] NULL)

As mentioned in FAQ section, you need F# record to work with such table in Dapper.FSharp:

type Person = {
    Id : Guid
    FirstName : string
    LastName : string
    Position : int
    DateOfBirth : DateTime option
}

Hint: Check tests located under tests/Dapper.FSharp.Tests folder for more examples

API Overview

There are two sets of Computation Expression builders:

BASE API

The base Builders module is opened by default and contains all the core pieces for creating queries. This includes the insert, update, delete and select computation expressions and supporting query functions.

LINQ API

The new LinqBuilders module provides a full LINQ expression query provider, similar to the standard F# query computation expression, that feeds the Base API. This includes the insert, update, delete and select computation expressions and supporting query functions. (You must open Dapper.FSharp.LinqBuilders to use the Linq builders.)

BASE API

INSERT

To insert new values into Persons table, use insert computation expression:

open Dapper.FSharp
open Dapper.FSharp.MSSQL

let conn : IDbConnection = ... // get it somewhere

let newPerson = { Id = Guid.NewGuid(); FirstName = "Roman"; LastName = "Provaznik"; Position = 1; DateOfBirth = None }

insert {
    table "Persons"
    value newPerson
} |> conn.InsertAsync

If you have more Persons to insert, use values instead of value.

let newPerson1 = { Id = Guid.NewGuid(); FirstName = "Roman"; LastName = "Provaznik"; Position = 1; DateOfBirth = None }
let newPerson2 = { Id = Guid.NewGuid(); FirstName = "Jiri"; LastName = "Landsman"; Position = 2; DateOfBirth = None }

insert {
    table "Persons"
    values [newPerson1; newPerson2]
} |> conn.InsertAsync

You can insert only part of data, but keep in mind that you need to write all necessary columns or you'll get an error on SQL level:

insert {
    table "Persons"
    value {| Id = Guid.NewGuid(); FirstName = "Without"; LastName = "Birth date"; Position = 3 |}
} |> conn.InsertAsync

Note: All methods are asynchronous (returning Task) so you must "bang" (await) them. This part is skipped in examples.

WHERE condition

There are few helper functions available to make syntax shorter.

Longer syntax:

where (column "Id" (Eq updatedPerson.Id))

Shorter syntax:

where (eq "Id" updatedPerson.Id)

Note: The longer syntax is still valid and it's up to your personal taste which one you gonna use.

UPDATE

As you can insert values, you can update them:

let updatedPerson = { existingPerson with LastName = "Vorezprut" }
update {
    table "Persons"
    set updatedPerson
    where (eq "Id" updatedPerson.Id)
} |> conn.UpdateAsync

Partial updates are also possible by manually specifying one or more includeColumn properties:

// this updates only LastName, other value from updatedPerson are ignored
update {
    table "Persons"
    set updatedPerson
    includeColumn (nameof(updatedPerson.LastName))
    where (eq "Position" 1)
} |> conn.UpdateAsync

Or use anonymous record for Partial updates:

update {
    table "Persons"
    set {| LastName = "UPDATED" |}
    where (eq "Position" 1)
} |> conn.UpdateAsync

DELETE

The same goes for delete, but please, for the mother of all backups, don't forget where condition:

delete {
    table "Persons"
    where (eq "Position" 10)
} |> conn.DeleteAsync

Did I say you should never forget where condition in delete?

SELECT

Use select to read all values back from database. Please note that you need to set desired mapping type in generic SelectAsync method:

select {
    table "Persons"
} |> conn.SelectAsync<Person>

To filter values, use where condition as you know it from update and delete. Where conditions can be also combined with (+) operator (logical AND) or (*) operator (logical OR):

select {
    table "Persons"
    where (gt "Position" 5 + lt "Position" 10)
} |> conn.SelectAsync<Person>

To flip boolean logic in where condition, use (!!) operator (unary NOT):

select {
    table "Persons"
    where !! (gt "Position" 5 + lt "Position" 10)
} |> conn.SelectAsync<Person>

To use LIKE operator in where condition, use like:

select {
    table "Persons"
    where (like "FirstName" "%partofname%")
} |> conn.SelectAsync<Person>

Sorting works as you would expect:

select {
    table "Persons"
    where (gt "Position" 5 + lt "Position" 10)
    orderBy "Position" Asc
} |> conn.SelectAsync<Person>

If you need to skip some values or take only subset of results, use skip, take and skipTake. Keep in mind that for correct paging, you need to order results as well.

select {
    table "Persons"
    where (gt "Position" 5 + lt "Position" 10)
    orderBy "Position" Asc
    skipTake 2 3 // skip first 2 rows, take next 3
} |> conn.SelectAsync<Person>

select {
    table "Persons"
    where (gt "Position" 5 + lt "Position" 10)
    orderBy "Position" Asc
    skip 10 // skip first 10 rows
    take 10 // take next 10 rows
} |> conn.SelectAsync<Person>

SELECT WITH JOIN

For simple queries with join, you can use innerJoin and leftJoin in combination with SelectAsync overload:

select {
    table "Persons"
    innerJoin "Dogs" "OwnerId" "Persons.Id"
    orderBy "Persons.Position" Asc
} |> conn.SelectAsync<Person, Dog>

Dapper.FSharp will map each joined table into separate record and return it as list of 'a * 'b tuples. Currently up to 2 joins are supported, so you can also join another table here:

select {
    table "Persons"
    innerJoin "Dogs" "OwnerId" "Persons.Id"
    innerJoin "DogsWeights" "DogNickname" "Dogs.Nickname"
    orderBy "Persons.Position" Asc
} |> conn.SelectAsync<Person, Dog, DogsWeight>

Problem with LEFT JOIN is that tables "on the right side" can be full of null values. Luckily we can use SelectAsyncOption to map joined values to Option types:

// this will return seq<(Person * Dog option * DogWeight option)>
select {
    table "Persons"
    leftJoin "Dogs" "OwnerId" "Persons.Id"
    leftJoin "DogsWeights" "DogNickname" "Dogs.Nickname"
    orderBy "Persons.Position" Asc
} |> conn.SelectAsyncOption<Person, Dog, DogsWeight>

The innerLoin and leftjoin keywords also support overloading (note that currently that requires flag in fsproj file Issue#41) when the join condition requires multiple columns. In that case you provide the join condition as a list:

select {
    table "Dogs"
    innerJoin "VaccinationHistory" ["PetOwnerId", "Dogs.OwnerId"; "DogNickname", "Dogs.Nickname"]
    orderBy ["Dogs.Nickname", Asc; "VaccinationHistory.VaccinationDate", Desc]
} |> crud.SelectAsync<Dogs.View, DogVaccinationHistory.View>

Aggregate functions

Aggregate functions include count, avg, sum, min, and max. To fully support these functions in builder syntax, the groupBy, groupByMany and distinct keywords are supported as well.

See this example how to get amount of persons having position value greater than 5:

select {
    table "Persons"
    count "*" "Value" // column name and alias (must match the view record property!!!)
    where (gt "Position" 5)
} |> conn.SelectAsync<{| Value : int |}>

Or get the maximum value of Position column from table:

select {
    table "Persons"
    max "Position" "Value"
} |> conn.SelectAsync<{| Value : int |}>

Or something more complex:

select {
    table "Persons"
    leftJoin "Dogs" "OwnerId" "Persons.Id"
    count "Persons.Position" "Count"
    groupByMany ["Persons.Id"; "Persons.Position"; "Dogs.OwnerId"]
    orderBy "Persons.Position" Asc
} |> conn.SelectAsync<{| Id: Guid; Position:int; Count:int |}, {| OwnerId : Guid |}>

Please keep in mind that work with aggregate functions can quickly turn into the nightmare. Use them wisely and if you'll find something hard to achieve using this library, better fallback to plain Dapper and good old hand written queries™.

Different Schema

In case you need to work with other than default database schema, you can use schema keyword which is supported for all query builders:

select {
    schema "MySchema"
    table "Persons"
} |> conn.SelectAsync<Person>

OUTPUT clause support (MSSQL & PostgreSQL only)

This library supports OUTPUT clause for MSSQL & PostgreSQL using special methods: InsertOutputAsync, UpdateOutputAsync and DeleteOutputAsync. Please check tests located under tests/Dapper.FSharp.Tests folder for more examples.

Deconstructor

To provide better usage with plain Dapper, this library contains Deconstructor converting Dapper.FSharp queries to tuple of parametrized SQL query and Map of parameter values.

let r = {
    Id = Guid.NewGuid()
    FirstName = "Works"
    LastName = "Great"
    DateOfBirth = DateTime.Today
    Position = 1
}

let sql, values =
    insert {
        table "Persons"
        value r
    } |> Deconstructor.insert

printfn "%s" sql 
// INSERT INTO Persons (Id, FirstName, LastName, Position, DateOfBirth) 
// VALUES (@Id0, @FirstName0, @LastName0, @Position0, @DateOfBirth0)"

printfn "%A" values
// map [("DateOfBirth0", 11.05.2020 0:00:00); 
//      ("FirstName0", "Works");
//      ("Id0", 8cc6a7ed-7c17-4bea-a0ca-04a3985d2c7e); 
//      ("LastName0", "Great");
//      ("Position0", 1)]

LINQ API

Table Mappings

You can either specify your tables within the query, or you can specify them above your queries (which is recommended since it makes them sharable between your queries). The following will assume that the table name exactly matches the record name, "Person":

let personTable = table<Person>

If your record maps to a table with a different name:

let personTable = table'<Person> "People"

If you want to include a schema name:

let personTable = table'<Person> "People" |> inSchema "dbo"

INSERT

Inserting a single record:

open Dapper.FSharp
open Dapper.FSharp.LinqBuilders
open Dapper.FSharp.MSSQL

let conn : IDbConnection = ... // get it somewhere

let newPerson = { Id = Guid.NewGuid(); FirstName = "Roman"; LastName = "Provaznik"; Position = 1; DateOfBirth = None }

let personTable = table<Person>

insert {
    into personTable
    value newPerson
} |> conn.InsertAsync

Inserting Multiple Records:

open Dapper.FSharp
open Dapper.FSharp.LinqBuilders
open Dapper.FSharp.MSSQL

let conn : IDbConnection = ... // get it somewhere

let person1 = { Id = Guid.NewGuid(); FirstName = "Roman"; LastName = "Provaznik"; Position = 1; DateOfBirth = None }
let person2 = { Id = Guid.NewGuid(); FirstName = "Ptero"; LastName = "Dactyl"; Position = 2; DateOfBirth = None }

let personTable = table<Person>

insert {
    into personTable
    values [ person1; person2 ]
} |> conn.InsertAsync

Excluding Fields from the Insert:

open Dapper.FSharp
open Dapper.FSharp.LinqBuilders
open Dapper.FSharp.MSSQL

let conn : IDbConnection = ... // get it somewhere

let newPerson = { Id = Guid.NewGuid(); FirstName = "Roman"; LastName = "Provaznik"; Position = 1; DateOfBirth = None }

let personTable = table<Person>

insert {
    for p in personTable do
    value newPerson
    excludeColumn p.DateOfBirth
} |> conn.InsertAsync

NOTE: You can exclude multiple fields by using multiple excludeColumn statements.

UPDATE

let updatedPerson = { existingPerson with LastName = "Vorezprut" }

update {
    for p in personTable do
    set updatedPerson
    where (p.Id = updatedPerson.Id)
} |> conn.UpdateAsync

Partial updates are possible by manually specifying one or more includeColumn properties:

update {
    for p in personTable do
    set modifiedPerson
    includeColumn p.FirstName
    includeColumn p.LastName
    where (p.Position = 1)
} |> conn.UpdateAsync

Partial updates are also possible by using an anonymous record:

update {
    for p in personTable do
    set {| FirstName = "UPDATED"; LastName = "UPDATED" |}
    where (p.Position = 1)
} |> conn.UpdateAsync

DELETE

delete {
    for p in personTable do
    where (p.Position = 10)
} |> conn.DeleteAsync

And if you really want to delete the whole table, you must use the deleteAll keyword:

delete {
    for p in personTable do
    deleteAll
} |> conn.DeleteAsync

SELECT

To select all records in a table, you must use the selectAll keyword:

select {
    for p in personTable do
    selectAll
} |> conn.SelectAsync<Person>

NOTE: You also need to use selectAll if you have a no where and no orderBy clauses because a query cannot consist of only for or join statements.

Filtering with where statement:

select {
    for p in personTable do
    where (p.Position > 5 && p.Position < 10)
} |> conn.SelectAsync<Person>

To flip boolean logic in where condition, use not operator (unary NOT):

select {
    for p in personTable do
    where (not (p.Position > 5 && p.Position < 10))
} |> conn.SelectAsync<Person>

NOTE: The forward pipe |> operator in you query expressions because it's not implemented, so don't do it (unless you like exceptions)!

To use LIKE operator in where condition, use like:

select {
    for p in personTable do
    where (like p.FirstName "%partofname%")
} |> conn.SelectAsync<Person>

Sorting:

select {
    for p in personTable do
    where (p.Position > 5 && p.Position < 10)
    orderBy p.Position
    thenByDescending p.LastName
} |> conn.SelectAsync<Person>

If you need to skip some values or take only subset of results, use skip, take and skipTake. Keep in mind that for correct paging, you need to order results as well.

select {
    for p in personTable do
    where (p.Position > 5 && p.Position < 10)
    orderBy p.Position
    skipTake 2 3 // skip first 2 rows, take next 3
} |> conn.SelectAsync<Person>

Option Types and Nulls

Checking for null on an Option type:

select {
    for p in personTable do
    where (p.DateOfBirth = None)
    orderBy p.Position
} |> conn.SelectAsync<Person>

Checking for null on a nullable type:

select {
    for p in personTable do
    where (p.LastName = null)
    orderBy p.Position
} |> conn.SelectAsync<Person>

Checking for null (works for any type):

select {
    for p in personTable do
    where (isNullValue p.LastName && isNotNullValue p.FirstName)
    orderBy p.Position
} |> conn.SelectAsync<Person>

Comparing an Option Type

let dob = DateTime.Today

select {
    for p in personTable do
    where (p.DateOfBirth = Some dob)
    orderBy p.Position
} |> conn.SelectAsync<Person>

JOINS

For simple queries with join, you can use innerJoin and leftJoin in combination with SelectAsync overload:

let personTable = table<Person>
let dogsTable = table<Dog>
let dogsWeightsTable = table<DogsWeight>

select {
    for p in personTable do
    join d in dogsTable on (p.Id = d.OwnerId)
    orderBy p.Position
} |> conn.SelectAsync<Person, Dog>

Dapper.FSharp will map each joined table into separate record and return it as list of 'a * 'b tuples. Currently up to 2 joins are supported, so you can also join another table here:

select {
    for p in personTable do
    join d in dogsTable on (p.Id = d.OwnerId)
    join dw in dogsWeightsTable on (d.Nickname = dw.DogNickname)
    orderBy p.Position
} |> conn.SelectAsync<Person, Dog, DogsWeight>

The problem with LEFT JOIN is that tables "on the right side" can be full of null values. Luckily we can use SelectAsyncOption to map joined values to Option types:

// this will return seq<(Person * Dog option * DogWeight option)>
select {
    for p in personTable do
    leftJoin d in dogsTable on (p.Id = d.OwnerId)
    leftJoin dw in dogsWeightsTable on (d.Nickname = dw.DogNickname)
    orderBy p.Position
} |> conn.SelectAsyncOption<Person, Dog, DogsWeight>

IncludeColumn vs ExcludeColumn (there can be a 🐲)

New keywords added in v2 - excludeColumn and includeColumn are a great addition to this library, especially when you want to do partial updates / inserts. However, be aware that you should never mix both in a same computation expression!

ExcludeColumn

If used for the first time within computation expression all fields from record will be used and removed (ignored) those you provided in keyword. When used more times, already filtered fields will be filtered again.

IncludeColumn

If used, only specified columns will be used and all the others will be ignored.

With great power comes the great responsibility.

Contribution Guide

Every new idea how to make library even better is more than welcome! However please be aware that there is process we should all follow:

Create an issue with description of proposed changes
Describe expected impact on library (API, performance, ...)
Define if it's minor or breaking change
Wait for Approve / Deny
Send a PR (or wait until taken by some of contributors)

Files

README_v2.md

Latest commit

History

README_v2.md

File metadata and controls

Dapper.FSharp (pre-v3 release docs)

Features

Installation

FAQ

Why another library around Dapper?

How does library works?

Do I need to create record with all columns?

And what about names mapping using Attributes or foreign keys magic?

Can I map more records from one query?

What if I need join more than 3 tables, sub-select or something special?

Getting started

Example database

API Overview

BASE API

INSERT

WHERE condition

UPDATE

DELETE

SELECT

SELECT WITH JOIN

Aggregate functions

Different Schema

OUTPUT clause support (MSSQL & PostgreSQL only)

Deconstructor

LINQ API

Table Mappings

INSERT

UPDATE

DELETE

SELECT

Option Types and Nulls

JOINS

IncludeColumn vs ExcludeColumn (there can be a 🐲)

ExcludeColumn

IncludeColumn

Contribution Guide