Dapper.FSharp

<p align="center"> <img src="https://github.com/Dzoukr/Dapper.FSharp/raw/master/logo.png" width="150px"/> </p>

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

Features

• No auto-attribute-based-only-author-maybe-knows-magic behavior
• Support for (anonymous) F# 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, SQLite 3
• Support for SELECT (including JOINs), INSERT, UPDATE (full / partial), DELETE
• Support for OUTPUT clause (MSSQL only)
• Support for INSERT OR REPLACE clause (SQLite)
• 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

What's new in v4?

Reasoning behind version 4 is described in this issue, but the main changes are:

• Each database provider has its own query definition
• New database-specific keywords for MSSQL & Postgres
• Operators considered harmful (removed for functions IN, NOT IN, LIKE and NOT LIKE)
• Minimal supported version is NET 6.0

If you still need/want to use v3.0, follow the Version 3 docs.

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 a few simple queries for CRUD operations using Dapper and don't want to write column names manually. All I need is a simple record with properties and want to have them filled from the query or to insert / update data.

How does the library works?

This library does two things:

1. Provides 4 computation expression builders for select, insert, update and delete. Those expressions create definitions (just simple records, no worries) of SQL queries.
2. 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 the library know 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 the table.

Do I need to create a record with all columns?

You can, but don't have to. If you need to read a subset of data only, you can create a special view record just for this. Also if you don't want to write nullable data, you can omit them in the 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 a 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 a separate record. If you use LEFT JOIN, you can even map the 2nd and/or 3rd table to Option (F# records and null values don't work well together). The current limitation is 3 tables (two joins).

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

Fallback to plain Dapper then. Really. Dapper is an 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

// for MSSQL
Dapper.FSharp.MSSQL.OptionTypes.register()

// for MySQL
Dapper.FSharp.MySQL.OptionTypes.register()

// for PostgreSQL
Dapper.FSharp.PostgreSQL.OptionTypes.register()

// for SQLite
Dapper.FSharp.SQLite.OptionTypes.register()

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

Example database

Let's 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
}

If you prefer not exposing your records, you can use internal types:

type internal 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

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.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.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.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 setColumn keyword:

update {
    for p in personTable do
    setColumn p.FirstName "UPDATED"
    setColumn p.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.

NOTE: The type does not have to have all columns from the table. You can create a record with only the columns you need.

NOTE: This same approach will enable you to query views.

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>

You can also combine multiple where conditions with andWhere and orWhere:

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

To conditionally add where part, you can use andWhereIf and orWhereIf:

let pos = Some 10
let posOr = Some 2
select {
    for p in personTable do
    where (p.Position > 5)
    andWhereIf pos.IsSome (p.Position < pos.Value)
    orWhereIf posOr.IsSome (p.Position < posOr.Value)
} |> conn.SelectAsync<Person>

NOTE: Do not use the forward pipe |> operator in your 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>

To use IN operator in where condition, use isIn:

select {
    for p in personTable do
    where (isIn p.FirstName ["Elizabeth"; "Philipp"])
} |> conn.SelectAsync<Person>

You can also negate the IN operator in where condition, with isNotIn:

select {
    for p in personTable do
    where (isNot