Thin F# wrapper for Npgsql, data provider for PostgreSQL.
This wrapper maps raw SQL data from the database into the Sql
data structure making it easy to pattern match against and transform the results.
Given the types:
type SqlValue =
| Null
| Short of int16
| Int of int
| Long of int64
| String of string
| Date of DateTime
| Bool of bool
| Number of double
| Decimal of decimal
| Bytea of byte[]
| HStore of Map<string, string>
| Uuid of Guid
| TimeWithTimeZone of DateTimeOffset
| Jsonb of string
// A row is a list of key/value pairs
type SqlRow = list<string * SqlValue>
// A table is list of rows
type SqlTable = list<SqlRow>
Configure the connection string
open Npgsql.FSharp
// construct the connection string
let defaultConnection : string =
Sql.host "localhost"
|> Sql.port 5432
|> Sql.username "user"
|> Sql.password "password"
|> Sql.database "app_db"
|> Sql.config "SslMode=Require;" // optional Config for connection string
|> Sql.str
Execute query and read results as table then map the results
type User = {
UserId : int
FirstName: string
LastName: string
}
let getAllUsers() : User list =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT * FROM \"users\""
|> Sql.executeTable
|> Sql.mapEachRow (function
| [ "user_id", SqlValue.Int id
"first_name", SqlValue.String fname
"last_name", SqlValue.String lname ] ->
let user =
{ UserId = id;
FirstName = fname;
LastName = lname }
Some user
| _ -> None)
Use option monad for reading row values:
let getAllUsers() : User list =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT * FROM \"users\""
|> Sql.executeTable
|> Sql.mapEachRow (fun row ->
option {
let! id = Sql.readInt "user_id" row
let! fname = Sql.readString "first_name" row
let! lname = Sql.readString "last_name" row
return { Id = id; FirstName = fname; LastName = lname }
})
Deal with null values and provide defaults
Notice we are not using let bang
but just let
instead
let getAllUsers() : User list =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT * FROM \"users\""
|> Sql.executeTable
|> Sql.mapEachRow (fun row ->
option {
let! id = Sql.readInt "user_id" row
let fname = Sql.readString "first_name" row
let lname = Sql.readString "last_name" row
return {
Id = id;
FirstName = defaultArg fname ""
LastName = defaultArg lname ""
}
})
the library doesn't provide an option
monad by default, you add your own or use this simple one instead:
type OptionBuilder() =
member x.Bind(v,f) = Option.bind f v
member x.Return v = Some v
member x.ReturnFrom o = o
member x.Zero () = None
let option = OptionBuilder()
Execute a function with parameters
/// Check whether or not a user exists by his username
let userExists (name: string) : bool =
defaultConnection
|> Sql.connect
|> Sql.func "user_exists"
|> Sql.parameters ["username", SqlValue.String name]
|> Sql.executeScalar // SqlValue
|> Sql.toBool
Async: Execute a function with parameters
/// Check whether or not a user exists by his username
let userExists (name: string) : Async<bool> =
defaultConnection
|> Sql.connect
|> Sql.func "user_exists"
|> Sql.parameters ["username", Sql.Value name]
|> Sql.executeScalarAsync
|> Async.map Sql.toBool
Parameterize queries with complex parameters like hstore
// Insert a book with it's attributes stored as HStore values
let bookAttributes =
Map.empty
|> Map.add "isbn" "46243425212"
|> Map.add "page-count" "423"
|> Map.add "weight" "500g"
defaultConnection
|> Sql.query "INSERT INTO \"books\" (id,title,attrs) VALUES (@bookId,@title,@attributes)"
|> Sql.parameters
[ "bookId", Sql.Value 20
"title", Sql.Value "Lord of the rings"
"attributes", Sql.Value bookAttributes ]
|> Sql.prepare // optionnal, see http://www.npgsql.org/doc/prepare.html
|> Sql.executeNonQuery
Retrieve single value safely
// ping the database
let serverTime() : Option<DateTime> =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT NOW()"
|> Sql.executeScalarSafe
|> function
| Ok (SqlValue.Date time) -> Some time
| _ -> None
Retrieve single value safely asynchronously
// ping the database
let serverTime() : Async<Option<DateTime>> =
async {
let! result =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT NOW()"
|> Sql.executeScalarSafeAsync
match result with
| Ok (SqlValue.Date time) -> return Some time
| otherwise -> return None
}
Batch queries in a single roundtrip to the database
defaultConnection
|> Sql.connect
|> Sql.queryMany
["SELECT * FROM \"users\""
"SELECT * FROM \"products\""]
|> Sql.executeMany // returns list<SqlTable>
|> function
| [ firstTable; secondTable ] -> (* do stuff *)
| otherwise -> failwith "should not happen"
Variants of returns types for the execute methods
// read results as tables
Sql.executeTable // SqlTable
Sql.executeTableSafe // Result<SqlTable, exn>
Sql.executeTableTask // Task<SqlTable>
Sql.executeTableAsync // Async<SqlTable>
Sql.executeTableSafeAsync // Async<Result<SqlTable, exn>>
Sql.executeTableSafeTask // Task<Result<SqlTable, exn>>
// read results as scalar values
Sql.executeScalar // Sql
Sql.executeScalarSafe // Result<Sql, exn>
Sql.executeScalarAsync // Async<Sql>
Sql.executeScalarTask // Task<Sql>
Sql.executeTableSafeAsync // Async<Result<Sql, exn>>
Sql.executeTableSafeTask // Task<Result<Sql, exn>>
// execute and count rows affected
Sql.executeNonQuery // int
Sql.executeNonQueryAsync // Async<int>
Sql.executeNonQueryTask // Task<int>
Sql.executeNonQuerySafe // Result<int, exn>
Sql.executeNonQuerySafeAsync // Async<Result<int, exn>>
Sql.executeNonQuerySafeTask // Task<Result<int, exn>>
You can also try automated parsing:
type User = {
UserId : int
FirstName: string
LastName: string
}
let getAllUsers() : User list =
defaultConnection
|> Sql.connect
|> Sql.query "SELECT * FROM \"users\""
|> Sql.executeTable // SqlTable
|> Sql.parseEachRow<User>
Though watch out, as this is a relatively new feature and still needs some time and love:
- The type parameter must be a record.
- Fields' names must match exactly columns headers.
- Only simple types are supported (see the definition of the "Sql" type).
- You can turn a field into an option if it's defined as "Nullable" in your table:
type User = {
UserId : int
FirstName : string
LastName : string
Nickname : string option
}
To Run tests
Docker must be installed
build(.cmd or sh) StartDatabase
build RunTests
build StopDatabase