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 Main

Start 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.formatConnectionString

Once 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 named user_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.executeNonQuery

Sql.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"))

    titles

Sql.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) title

Use 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("fi