Npgsql.FSharp 
Thin F#-friendly layer for the Npgsql data provider for PostgreSQL.
For an optimal developer experience, this library is made to work with Npgsql.FSharp.Analyzer which is a F# analyzer that will verify the query syntax and perform type-checking against the parameters and the types of the columns from the result set.
Read the full documentation at zaid-ajaj.github.io/Npgsql.FSharp
Install from nuget
# using dotnet CLI
dotnet add package Npgsql.FSharp
# using Paket
paket add Npgsql.FSharp --group MainStart using the library
First thing to do is aquire your connection string some how. For example using environment variables, a hardcoded value or using the builder API
// (1) from environment variables
let connectionString = System.Environment.GetEnvironmentVariable "DATABASE_CONNECTION_STRING"
// (2) hardcoded
let connectionString = "Host=localhost; Database=dvdrental; Username=postgres; Password=postgres;"
// the library also accepts URI postgres connection format (NOTE: not all query string parameters are converted)
let connectionString = "postgres://username:password@localhost/dvdrental";
// (3) using the connection string builder API
let connectionString : string =
Sql.host "localhost"
|> Sql.database "dvdrental"
|> Sql.username "postgres"
|> Sql.password "postgres"
|> Sql.port 5432
|> Sql.formatConnectionStringOnce you have a connection string you can start querying the database:
Sql.execute: Execute query and read results as table then map the results
The main function to execute queries and return a list of a results is Sql.execute:
open Npgsql.FSharp
type User = {
Id: int
FirstName: string
LastName: string
}
let getAllUsers (connectionString: string) : User list =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.text "last_name"
})Deal with null values and provide defaults
Notice the LastName field becomes string option instead of string
type User = {
Id: int
FirstName: string
LastName: string option // notice option here
}
let getAllUsers (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.textOrNone "last_name" // reading nullable column
})Then you can use defaultArg or other functions from the Option to provide default values when needed.
Make the reading async using Sql.executeAsync
The exact definition is used, except that Sql.execute becomes Sql.executeAsync
let getAllUsers (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.executeAsync (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.textOrNone "last_name"
})Sql.parameters: Parameterized queries
Provide parameters using the Sql.parameters function as a list of tuples. When using the analyzer, make sure you use functions from Sql module to initialize the values so that the analyzer can type-check them against the types of the required parameters.
let getAllUsers (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users WHERE is_active = @active"
|> Sql.parameters [ "active", Sql.bit true ]
|> Sql.executeAsync (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.textOrNone "last_name"
})Sql.executeRow: Execute a query and read a single row back
Use the function Sql.executeRow or its async counter part to read a single row of the output result. For example, to read the number of rows from a table:
let numberOfUsers() =
connectionString
|> Sql.connect
|> Sql.query "SELECT COUNT(*) as user_count FROM users"
|> Sql.executeRow (fun read -> read.int64 "user_count")Notice here we alias the result of
COUNT(*)as a column nameduser_count. This is recommended when reading scalar result sets so that we work against a named column instead of its index.
Sql.executeTransaction: Execute multiple inserts or updates in a single transaction
Both queries in the example below are executed within a single transaction and if one of them fails, the entire transaction is rolled back.
connectionString
|> Sql.connect
|> Sql.executeTransaction
[
// This query is executed 3 times
// using three different set of parameters
"INSERT INTO ... VALUES (@number)", [
[ "@number", Sql.int 1 ]
[ "@number", Sql.int 2 ]
[ "@number", Sql.int 3 ]
]
// This query is executed once
"UPDATE ... SET meta = @meta", [
[ "@meta", Sql.text value ]
]
]Sql.executeNonQuery: Returns number of affected rows from statement
Use the function Sql.executeNonQuery or its async counter part to get the number of affected rows from a query. Like always, the function is safe by default and returns Result<int, exn> as output.
let getAllUsers() =
defaultConnection
|> Sql.connectFromConfig
|> Sql.query "DELETE FROM users WHERE is_active = @is_active"
|> Sql.parameters [ "is_active", Sql.bit false ]
|> Sql.executeNonQuerySql.iter: Iterating through the result set
The functions Sql.execute and Sql.executeAsync by default return you a list<'t> type which for many cases works quite well. However, for really large datasets (> 100K of rows) using F# lists might not be ideal for performance. This library provides the function Sql.iter which allows you to do something with the row reader like adding rows to ResizeArray<'t> as follows without using an intermediate F# list<'t>:
let filmTitles(connectionString: string) =
let titles = ResizeArray<string>()
connectionString
|> Sql.connect
|> Sql.query "SELECT title FROM film"
|> Sql.iter (fun read -> titles.Add(read.text "title"))
titlesSql.toSeq: Wrapping execution of the query in a sequence
The function Sql.iter works well for really large datasets (> 100K of rows) without performance issues, but it forces you to provide a callback function, so that it can push the rows to you. If you want to pull the rows, you can use Sql.toSeq to wrap the whole execution in a sequence that yields the rows. The execution of the query starts each time when you start an iteration over the sequence:
let getFilmTitlesAsSeq(connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT title FROM film"
|> Sql.toSeq (fun read -> read.text "title")
|> Seq.indexed
|> Seq.map (fun (i, title) -> sprintf "%i. %s") (i + 1) titleUse an existing connection
Sometimes, you already have constructed a NpgsqlConnection and want to use with the Sql module. You can use the function Sql.existingConnection which takes a preconfigured connection from which the queries or transactions are executed. Note that this library will open the connection if it is not already open and it will leave the connection open (deos not dispose of it) when it finishes running. This means that you have to manage the disposal of the connection yourself:
use connection = new NpgsqlConnection("YOUR CONNECTION STRING")
connection.Open()
let users =
connection
|> Sql.existingConnection
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
})Note in this example, when we write use connection = ... it means the connection will be disposed at the end of the scope where this value is bound, not internally from the Sql module.
Use a data source
.NET 7 introduced the DbDataSource type, implemented by Npgsql as NpgsqlDataSource. If you already have a constructed data source, using the function Sql.fromDataSource lets you use it to obtain connections from which the queries or transactions are executed.
use dataSource = NpgsqlDataSource.Create("YOUR CONNECTION STRING")
let users =
dataSource
|> Sql.fromDataSource
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
})Reading values from the underlying NpgsqlDataReader
When running the Sql.execute function, you can read values directly from the NpgsqlDataReader as opposed to using the provided RowReader. Instead of writing this:
let getAllUsers (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.textOrNone "last_name" // reading nullable column
})You write
let getAllUsers (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.execute (fun read ->
{
Id = read.NpgsqlReader.GetInt32(read.NpgsqlReader.GetOrdinal("user_id"))
FirstName = read.NpgsqlReader.GetString(read.NpgsqlReader.GetOrdinal("first_name"))
LastName = read.textOrNone "last_name" // reading nullable column
})Here we are using the NpgsqlReader property from the RowReader which allows you to read or convert custom values. Usually you don't need this unless when you are using custom type handlers for the NpgsqlConnection.
Custom parameters with NpgsqlParameter
When the built-in parameter constructors aren't enough for you (for example when you are using type handler plugins) then you can use the generic Sql.parameter function to provide one:
let customParameter = new NpgsqlParameter(...)
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM users"
|> Sql.parameters [ "username", Sql.parameter customParameter ]
|> Sql.execute (fun read ->
{
Id = read.int "user_id"
FirstName = read.text "first_name"
LastName = read.textOrNone "last_name" // reading nullable column
})Suppressing analyzer warnings with Sql.skipAnalysis:
When working with the Npgsql.FSharp.Analyzer, you can suppress the warnings it generates if you think it is a bug in the analyzer or you know for sure the code will actually work during runtime. To supress the warning, use Sql.skipAnalysis just before the Sql.execute* family of functions:
let badQuery (connectionString: string) =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM non_existing_table"
|> Sql.skipAnalysis
|> Sql.execute (fun read -> read.int64 "user_id")The function itself doesn't do anything in runtime as if it was never there in the first place. It simply annotates the code for the analyzer.
Executing SQL commands in the context of a transaction
Sometimes it isn't enough to simply use Sql.executeTransaction / Sql.executeTransactionAsync and you want to run arbitrary code in between SQL calls which might use intermediate results from those calls in order to determine whether or not commit the transaction or roll it back.
You can do this by creating your own NpgsqlTransaction and using it to execute the SQL commands as follows:
open Npgsql
open Npgsql.FSharp
let connectionString = " . . . "
// 1) Create the connection
use connection = new NpgsqlConnection(connectionString)
connection.Open()
// 2) Create the transaction from the connection
use transaction = connection.BeginTransaction()
// 3) run SQL commands against the transaction
for number in [1 .. 10] do
let result =
connection
|> Sql.existingConnection
|> Sql.query "INSERT INTO table (columnName) VALUES (@value)"
|> Sql.parameters [ "@value", Sql.int 42 ]
|> Sql.executeNonQuery
printfn "%A" result
// 4) commit the transaction, rollback or do whatever you want
transaction.Commit()If you don't like creating the connection yourself because you want to use the builder API, you can instead let the library create the connection as follows:
use connection =
connectionString
|> Sql.connect
|> Sql.createConnection
connection.Open()Migrating from reflection-based libraries
If you are migrating from libraries that use reflection to map database results to objects, it might seem like manually creating your mapping functions is a lot of work. Our position is that this work is worthwhile in terms of
- Clarity,
- Maintainability
- Flexibility
However, if you do need some automated reflection-based generation, writing such a wrapper is not hard. Something like this gets you almost all of the way there:
// generate a function of type RowReader -> 't that looks for fields to map based on lowercase field names
let autoGeneratedRecordReader<'t> =
let createRecord = FSharpValue.PreComputeRecordConstructor typeof<'t>
let make values = createRecord values :?> 't
let fields = FSharpType.GetRecordFields typeof<'t> |> Array.map (fun p -> p.Name, p.PropertyType)
let readField (r: RowReader) (n: string) (t: System.Type) =
if t = typeof<int> then r.int n |> box
elif t = typeof<int option> then r.intOrNone n |> box
elif t = typeof<int16> then r.int16 n |> box
elif t = typeof<int16 option> then r.int16OrNone n |> box
elif t = typeof<int []> then r.intArray n |> box
elif t = typeof<int [] option> then r.intArrayOrNone n |> box
elif t = typeof<string []> then r.stringArray n |> box
elif t = typeof<string [] option> then r.stringArrayOrNone n |> box
elif t = typeof<int64> then r.int64 n |> box
elif t = typeof<int64 option> then r.int64OrNone n |> box
elif t = typeof<string> then r.string n |> box
elif t = typeof<string option> then r.stringOrNone n |> box
elif t = typeof<bool> then r.bool n |> box
elif t = typeof<bool option> then r.boolOrNone n |> box
elif t = typeof<decimal> then r.decimal n |> box
elif t = typeof<decimal option> then r.decimalOrNone n |> box
elif t = typeof<double> then r.double n |> box
elif t = typeof<double option> then r.doubleOrNone n |> box
elif t = typeof<DateTime> then r.dateTime n |> box
elif t = typeof<DateTime option> then r.dateTimeOrNone n |> box
elif t = typeof<Guid> then r.uuid n |> box
elif t = typeof<Guid option> then r.uuidOrNone n |> box
elif t = typeof<byte[]> then r.bytea n |> box
elif t = typeof<byte[] option> then r.byteaOrNone n |> box
elif t = typeof<float> then r.float n |> box
elif t = typeof<float option> then r.floatOrNone n |> box
elif t = typeof<Guid []> then r.uuidArray n |> box
elif t = typeof<Guid [] option> then r.uuidArrayOrNone n |> box
else
failwithf "Could not read column '%s' as %s" n t.FullName
fun (reader: RowReader) ->
let values = [| for (name, ty) in fields do readField reader (name.ToLowerInvariant()) ty |]
make valuesThis reader maps the fields' lower-case name only, but if you have custom naming requirements you can of course alter that to fit your circumstances.
It would be used something like
type Car = { make: string; model: string; year: int }
let carsFromDatabase =
connectionString
|> Sql.connection
|> Sql.query "SELECT * FROM cars"
|> Sql.execute autoGeneratedRecordReader<Car>Reading and deserializing json fields
You can use the fieldValue and provide a type argument read a column of type json or jsonb
[<CLIMutable>]
type JsonBlob = { prop1: int; prop2: string }
let jsonFromDatabase =
connectionString
|> Sql.connect
|> Sql.query "SELECT * FROM json_blobs"
|> Sql.execute (fun read -> read.fieldValue<JsonBlob> "json_blob")Building the solution
Build the solution in the repository root directory:
dotnet buildRunning tests
The tests are mostly integration tests and require a live postgres server running with the following defaults
- host: localhost
- port: 5432
- username: postgres
- password: postgres
Then the tests will create and destroy test databases using ThrowawayDb.
Run the tests as follows
cd ./tests
dotnet run