Lightweight F# extension for StackOverflow Dapper with support for MSSQL, MySQL, and PostgreSQL
- 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
If you want to install this package manually, use usual NuGet package command
Install-Package Dapper.FSharp
or using Paket
paket add Dapper.FSharp
3rd version is rather an evolution, than revolution. The major concern was to keep the library maintainable with low possible effort while providing the best type safety. Hence version 3 contains two major (breaking) changes:
- Old API has been dropped in favor of LINQ (watch out for namespace change -
Dapper.FSharp.LinqBuilders
are no more) join
has been renamed toinnerJoin
- Minimal supported version is
NET 5.0
If you still need/want to use v2.0
, follow the Version 2 docs.
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 (anonymous) record with properties and want to have them filled from the query or to insert / update data.
This library does two things:
- Provides 4 computation expression builders for
select
,insert
,update
anddelete
. Those expressions create 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 DapperQueryAsync
orExecuteAsync
. 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.
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.
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.
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).
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.
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 the program entry point or in Startup
class.
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
}
Hint: Check tests located under tests/Dapper.FSharp.Tests folder for more examples
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"
Inserting a single record:
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 }
let personTable = table<Person>
insert {
into personTable
value newPerson
} |> conn.InsertAsync
Inserting Multiple Records:
open Dapper.FSharp
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.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.
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 {
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
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: 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 (isNotIn p.FirstName ["Charles"; "Camilla"])
} |> 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 to take a subset of results only, 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>
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>
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
innerJoin d in dogsTable on (p.Id = d.OwnerId)
orderBy p.Position
} |> conn.SelectAsync<Person, Dog>
Dapper.FSharp
will map each joined table into a 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
innerJoin d in dogsTable on (p.Id = d.OwnerId)
innerJoin 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>
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 of how to get the amount of persons having a position value greater than 5:
select {
for p in persons do
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 the Position column from the table:
select {
for p in persons do
max "Position" "Value"
} |> conn.SelectAsync<{| Value : int |}>
Please keep in mind that work with aggregate functions can quickly turn into a nightmare. Use them wisely and if you'll find something hard to achieve using this library, better fallback to plain Dapper and good old handwritten queries™.
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.
To provide better usage with plain Dapper, this library contains Deconstructor
converting Dapper.FSharp
queries to a tuple of parameterized 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 {
into personTable
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)]
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 the same computation expression!
If used for the first time within computation expression all fields from the record will be used and removed (ignored) those you provided in a keyword. When used more times, already filtered fields will be filtered again.
If used, only specified columns will be used and all the others will be ignored.
With great power comes great responsibility.
Every new idea of how to make the library even better is more than welcome! However please be aware that there is a process we should all follow:
- Create an issue with a description of proposed changes
- Describe the expected impact on the 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 the contributors)