Need help with Npgsql.FSharp?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

Zaid-Ajaj
219 Stars 33 Forks MIT License 242 Commits 11 Opened issues

Description

Thin F# wrapper around Npgsql, the PostgreSQL database driver for .NET

Services available

!
?

Need anything else?

Contributors list

# 103,486
React
F#
fable
type-sa...
105 commits
# 403,571
F#
fable
HTML
aws-lam...
7 commits
# 617,883
F#
Shell
Postgre...
HTML
4 commits
# 324,318
Shell
PHP
F#
dotnet-...
4 commits
# 98,811
F#
Visual ...
fable
mvc-fra...
4 commits
# 157,864
F#
dotnet-...
dotnet-...
model-v...
3 commits
# 579,221
F#
dotnet-...
Shell
Postgre...
2 commits
# 331,051
React
F#
model-v...
swagger
2 commits
# 72,758
F#
cpp17
getopt
React
2 commits
# 209,080
React
F#
fable
Visual ...
2 commits
# 28,066
Xcode
Vim
CSS
vuejs
1 commit
# 494,000
React
F#
fable
embedde...
1 commit
# 385,004
F#
fable
elm
Babel
1 commit
# 535,958
C#
docker-...
Windows
F#
1 commit
# 438,426
Shell
tiled-m...
swift5
F#
1 commit
# 80,454
tcl
viml
code-co...
nerdtre...
1 commit

Npgsql.FSharp Nuget

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:[email protected]/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
: ```fs 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 "userid" FirstName = read.text "firstname" LastName = read.text "last_name" }) ```

Deal with null values and provide defaults

Notice the

LastName
field becomes
string option
instead of
string
```fs 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 "userid" FirstName = read.text "firstname" 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
fsharp
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.
fs
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:
fs
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. ```fs 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
as output.
fs
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
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
as follows without using an intermediate F#
list
: ```fs let filmTitles(connectionString: string) = let titles = ResizeArray() connectionString |> Sql.connect |> Sql.query "SELECT title FROM film" |> Sql.iter (fun read -> titles.Add(read.text "title"))
titles
### Use an existing connections
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:
```fs
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.

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:
fs
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
fs
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: ```fs 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 "userid" FirstName = read.text "firstname" 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:
fs
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: ```fs 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:
fs 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 =
    let createRecord = FSharpValue.PreComputeRecordConstructor typeof
    let make values = createRecord values :?> 't
    let fields = FSharpType.GetRecordFields typeof |> 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 |&gt; box
    elif t = typeof<int option> then r.intOrNone n |&gt; box
    elif t = typeof<int16> then r.int16 n |&gt; box
    elif t = typeof<int16 option> then r.int16OrNone n |&gt; box
    elif t = typeof<int> then r.intArray n |&gt; box
    elif t = typeof<int option> then r.intArrayOrNone n |&gt; box
    elif t = typeof<string> then r.stringArray n |&gt; box
    elif t = typeof<string option> then r.stringArrayOrNone n |&gt; box
    elif t = typeof<int64> then r.int64 n |&gt; box
    elif t = typeof<int64 option> then r.int64OrNone n |&gt; box
    elif t = typeof<string> then r.string n |&gt; box
    elif t = typeof<string option> then r.stringOrNone n |&gt; box
    elif t = typeof<bool> then r.bool n |&gt; box
    elif t = typeof<bool option> then r.boolOrNone n |&gt; box
    elif t = typeof<decimal> then r.decimal n |&gt; box
    elif t = typeof<decimal option> then r.decimalOrNone n |&gt; box
    elif t = typeof<double> then r.double n |&gt; box
    elif t = typeof<double option> then r.doubleOrNone n |&gt; box
    elif t = typeof<datetime> then r.dateTime n |&gt; box
    elif t = typeof<datetime option> then r.dateTimeOrNone n |&gt; box
    elif t = typeof<guid> then r.uuid n |&gt; box
    elif t = typeof<guid option> then r.uuidOrNone n |&gt; box
    elif t = typeof<byte> then r.bytea n |&gt; box
    elif t = typeof<byte option> then r.byteaOrNone n |&gt; box
    elif t = typeof<float> then r.float n |&gt; box
    elif t = typeof<float option> then r.floatOrNone n |&gt; box
    elif t = typeof<guid> then r.uuidArray n |&gt; box
    elif t = typeof<guid option> then r.uuidArrayOrNone n |&gt; box
    else
        failwithf "Could not read column '%s' as %s" n t.FullName

fun (reader: RowReader) -&gt;
    let values = [| for (name, ty) in fields do readField reader (name.ToLowerInvariant()) ty |]
    make values

This 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

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.