Vp.FSharp.Sql.PostgreSql

An opinionated F# library to interact with PostgreSQL databases following Vp.FSharp.Sql principles and relying on Npsql.

✨ Slagging Hype

We aim at following highly controversial practices to the best of our ability!

Status	Package
OK
OK
TBD
TBD

📦 NuGet Package

Name	Version	Command
Vp.FSharp.Sql.PostgreSql		Install-Package Vp.FSharp.Sql.PostgreSql

📚 How to Use

📝 Note: It's worth noting that the examples below illustrating functions running asynchronously do leverage Async.RunSynchronously. This is purely for the sake of reducing the nestedness that would happen if we were using async computation expressions instead.

💿 Supported Database Values

Just a little FYI:

/// Native PostgreSQL DB types.
/// See https://www.npgsql.org/doc/types/basic.html
/// and https://stackoverflow.com/a/845472/4636721
type PostgreSqlDbValue =
    | Null
    | Bit of bool
    | Boolean of bool
    | SmallInt of int16
    | Integer of int32
    | Oid of uint32
    | Xid of uint32
    | Cid of uint32
    | BigInt of int64

    | Real of single
    | Double of double

    | Money of decimal
    | Numeric of decimal

    | ByteA of uint8 array
    | OidVector of uint32 array

    | Uuid of Guid

    | INet of IPAddress
    | MacAddr of PhysicalAddress

    | TsQuery of NpgsqlTsQuery
    | TsVector of NpgsqlTsVector

    | Point of NpgsqlPoint
    | LSeg of NpgsqlLSeg
    | Path of NpgsqlPath
    | Polygon of NpgsqlPolygon
    | Line of NpgsqlLine
    | Circle of NpgsqlCircle
    | Box of NpgsqlBox

    | HStore of Dictionary<string, string>

    | Date of DateTime
    | Interval of TimeSpan
    | Time of DateTime
    | TimeTz of DateTime
    | Timestamp of DateTime
    | TimestampTz of DateTimeOffset

    | InternalChar of uint8

    | Char of string
    | VarChar of string

    | Name of string
    | CiText of string
    | Text of string
    | Xml of string
    | Json of string
    | Jsonb of string

    /// Only if the relevant Npgsql mapping for the Enum has been set up beforehand.
    /// See: https://www.npgsql.org/doc/types/enums_and_composites.html
    | Enum of Enum
    
    | Custom of (NpgsqlDbType * obj)

🧱PostgreSqlCommand

The main module is here to help you build and execute (Postgre)SQL commands (i.e. NpgsqlCommand BTS).

🏗️ Command Construction

We are obviously going to talk about how to build NpgsqlCommand definitions.

📝 Note: the meaning of the word "update" below has to be put in a F# perspective, i.e. immutable update, as in the update returns a new updated and immutable instance.

text

Initialize a new command definition with the given text contained in the given string.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

42

textFromList

Initialize a new command definition with the given text spanning over several strings (ie. list).

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55; ]
|> List.map (sprintf "SELECT %d;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryList connection (fun _ _ read -> read.Value<int32> 0)
|> Async.RunSynchronously
|> printfn "%A"

Output:

[0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55]

noLogger

Update the command definition so that when executing the command, it doesn't use any logger. Be it the default one (Global, if any.) or a previously overriden one.

Example:

PostgreSqlConfiguration.Logger (printfn "Logging... %A")

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.noLogger
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

42

overrideLogger

Update the command definition so that when executing the command, it use the given overriding logger. instead of the default one, aka the Global logger, if any.

Example:

PostgreSqlConfiguration.NoLogger ()

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.overrideLogger (printfn "Logging... %A")
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

Logging... ConnectionOpened Npgsql.NpgsqlConnection
Logging... CommandPrepared Npgsql.NpgsqlCommand
Logging... CommandExecuted (Npgsql.NpgsqlCommand, 00:00:00.0162810)
Logging... ConnectionClosed (Npgsql.NpgsqlConnection, 00:00:00.1007513)
42

parameters

Update the command definition with the given parameters.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT @a + @b;"
|> PostgreSqlCommand.parameters [ ("a", Integer 42); ("b", Real 42.42f) ]
|> PostgreSqlCommand.executeScalar<double> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

84.0

Note: in case you want to pass some types that aren't yet supported by the library,
you can use the Custom DU case which allows you to pass whatever underlying NpgsqlDbType with the relevant obj value.

cancellationToken

Update the command definition with the given cancellation token.

This comes in handy when you need to interop with more traditional, C#-async, cancellation style.

Example:

try
    use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
    PostgreSqlCommand.text "SELECT 42;"
    |> PostgreSqlCommand.cancellationToken (CancellationToken(true))
    |> PostgreSqlCommand.executeScalar<int32> connection
    |> Async.RunSynchronously
    |> ignore
with
 | :? OperationCanceledException as e ->
     printfn "The Command execution has been cancelled, reason: %A" e.Message

Output:

The Command execution has been cancelled, reason: "A task was canceled."

timeout

Update the command definition with the given timeout.

prepare

Update the command definition and sets whether the command should be prepared or not.

As per MS Docs:

Use the Prepared property to have the provider save a prepared (or compiled) version of the query specified in the CommandText property before a Command object's first execution.

This may slow a command's first execution, but once the provider compiles a command, the provider will use the compiled version of the command for any subsequent executions, which will result in improved performance.

Example: TBD

transaction

Update the command definition and set whether the command should be wrapped in the given transaction.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

use transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

// Create a table
PostgreSqlCommand.text $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
|> PostgreSqlCommand.transaction transaction
|> PostgreSqlCommand.executeNonQuery connection
|> Async.RunSynchronously
|> printfn "%A"

// The table is created here
PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.transaction transaction
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

transaction.Rollback()

// The table creation has been rollbacked
PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

-1
1
0

⚙ Command Execution

We are obviously going to talk about how to execute NpgsqlCommand definitions.

queryAsyncSeq

Execute the command and return the sets of rows as an AsyncSeq accordingly to the command definition.

This function runs asynchronously.

Example 1:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let getCounterQuery n =
    sprintf
        """
        WITH RECURSIVE counter(value) AS (VALUES(1) UNION ALL SELECT value + 1 FROM counter WHERE value < %d)
        SELECT value FROM counter;
        """ n

let readRow set record (read: SqlRecordReader<_>) =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5 ]
|> List.map getCounterQuery
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryAsyncSeq connection readRow
|> AsyncSeq.toListSynchronously
|> List.iter (fun x -> printfn "Set = %A; Row = %A; Data = %A" x.Set x.Record x.Data)

Output 1:

Set = 0; Row = 0; Data = [1]
Set = 1; Row = 0; Data = [1]
Set = 2; Row = 0; Data = [1]
Set = 3; Row = 0; Data = [1]
Set = 3; Row = 1; Data = [2]
Set = 4; Row = 0; Data = [1]
Set = 4; Row = 1; Data = [2]
Set = 4; Row = 2; Data = [3]
Set = 5; Row = 0; Data = [1]
Set = 5; Row = 1; Data = [2]
Set = 5; Row = 2; Data = [3]
Set = 5; Row = 3; Data = [4]
Set = 5; Row = 4; Data = [5]

Notes 📝:

• The output type must be consistent across all the result sets and records.
• If you need different types you may want to either:
  • Create DU with each type you want to output
  • Use querySetList2 or querySetList3 ⬇️
• The reader can also get the Value given a certain field name:

Example 2:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55; ]
|> List.map (sprintf "SELECT %d AS cola;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryList connection (fun _ _ read -> read.Value<int32> "cola")
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

[0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55]

querySeqSync

Execute the command and return the sets of rows as a seq accordingly to the command definition.

This function runs synchronously.

Example 1:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let getCounterQuery n =
    sprintf
        """
        WITH RECURSIVE counter(value) AS (VALUES(1) UNION ALL SELECT value + 1 FROM counter WHERE value < %d)
        SELECT value FROM counter;
        """ n

let readRow set record (read: SqlRecordReader<_>) =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5 ]
|> List.map getCounterQuery
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.querySeqSync connection readRow
|> Seq.iter (fun x -> printfn "Set = %A; Row = %A; Data = %A" x.Set x.Record x.Data)

Output 1:

Set = 0; Row = 0; Data = [1]
Set = 1; Row = 0; Data = [1]
Set = 2; Row = 0; Data = [1]
Set = 3; Row = 0; Data = [1]
Set = 3; Row = 1; Data = [2]
Set = 4; Row = 0; Data = [1]
Set = 4; Row = 1; Data = [2]
Set = 4; Row = 2; Data = [3]
Set = 5; Row = 0; Data = [1]
Set = 5; Row = 1; Data = [2]
Set = 5; Row = 2; Data = [3]
Set = 5; Row = 3; Data = [4]
Set = 5; Row = 4; Data = [5]

Notes 📝:

• The output type must be consistent across all the result sets and records.
• If you need different types you may want to either:
  • Create DU with each type you want to output
  • Use querySetList2 or querySetList3 ⬇️
• The reader can also get the Value given a certain field name:

Example 2:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55; ]
|> List.map (sprintf "SELECT %d AS cola;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryList connection (fun _ _ read -> read.Value<int32> "cola")
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

[0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55]

queryList

Execute the command and return the sets of rows as a list accordingly to the command definition.

This function runs asynchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55; ]
|> List.map (sprintf "SELECT %d;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryList connection (fun _ _ read -> read.Value<int32> 0)
|> Async.RunSynchronously
|> printfn "%A"

Output:

[0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55]

queryListSync

Execute the command and return the sets of rows as a list accordingly to the command definition.

This function runs synchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55; ]
|> List.map (sprintf "SELECT %d;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.queryListSync connection (fun _ _ read -> read.Value<int32> 0)
|> printfn "%A"

Output:

[0; 1; 1; 2; 3; 5; 8; 13; 21; 34; 55]

querySetList

Execute the command and return the first set of rows as a list accordingly to the command definition.

This function runs asynchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5 ]
|> List.map (sprintf "SELECT %d;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.querySetList connection (readRow 1)
|> Async.RunSynchronously
|> List.iter (fun x -> printfn "Set = %A; Row = %A; Data = %A" x.Set x.Record x.Data)

Output:

Set = 1; Row = 0; Data = [0]

querySetListSync

Execute the command and return the first set of rows as a list accordingly to the command definition.

This function runs synchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
[ 0; 1; 1; 2; 3; 5 ]
|> List.map (sprintf "SELECT %d;")
|> PostgreSqlCommand.textFromList
|> PostgreSqlCommand.querySetListSync connection (readRow 1)
|> List.iter (fun x -> printfn "Set = %A; Row = %A; Data = %A" x.Set x.Record x.Data)

Output:

Set = 1; Row = 0; Data = [0]

querySetList2

Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.

This function runs asynchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

let printRow row = printfn "Set = %A; Row = %A; Data = %A" row.Set row.Record row.Data

let set1, set2 =
    use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
    [ 0; 1; 1; 2; 3; 5 ]
    |> List.map (sprintf "SELECT %d;")
    |> PostgreSqlCommand.textFromList
    |> PostgreSqlCommand.querySetList2 connection (readRow 1) (readRow 2)
    |> Async.RunSynchronously

List.iter printRow set1
List.iter printRow set2

Output:

Set = 1; Row = 0; Data = [0]
Set = 2; Row = 0; Data = [1]

querySetList2Sync

Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.

This function runs synchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

let printRow row = printfn "Set = %A; Row = %A; Data = %A" row.Set row.Record row.Data

let set1, set2 =
    use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
    [ 0; 1; 1; 2; 3; 5 ]
    |> List.map (sprintf "SELECT %d;")
    |> PostgreSqlCommand.textFromList
    |> PostgreSqlCommand.querySetList2Sync connection (readRow 1) (readRow 2)

List.iter printRow set1
List.iter printRow set2

Output:

Set = 1; Row = 0; Data = [0]
Set = 2; Row = 0; Data = [1]

querySetList3

Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.

This function runs asynchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

let printRow row = printfn "Set = %A; Row = %A; Data = %A" row.Set row.Record row.Data

let set1, set2, set3 =
    use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
    [ 0; 1; 1; 2; 3; 5 ]
    |> List.map (sprintf "SELECT %d;")
    |> PostgreSqlCommand.textFromList
    |> PostgreSqlCommand.querySetList3 connection (readRow 1) (readRow 2) (readRow 3)
    |> Async.RunSynchronously

List.iter printRow set1
List.iter printRow set2
List.iter printRow set3

Output:

Set = 1; Row = 0; Data = [0]
Set = 2; Row = 0; Data = [1]
Set = 3; Row = 0; Data = [1]

querySetList3Sync

Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.

This function runs synchronously.

Example:

type Row<'T> = { Set: int32; Record: int32; Data: 'T list }

let readRow set record (read: SqlRecordReader<_>)  =
    { Set = set; Record = record; Data = List.init (read.Count) (read.Value<int32>) }

let printRow row = printfn "Set = %A; Row = %A; Data = %A" row.Set row.Record row.Data

let set1, set2, set3 =
    use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
    [ 0; 1; 1; 2; 3; 5 ]
    |> List.map (sprintf "SELECT %d;")
    |> PostgreSqlCommand.textFromList
    |> PostgreSqlCommand.querySetList3Sync connection (readRow 1) (readRow 2) (readRow 3)

List.iter printRow set1
List.iter printRow set2
List.iter printRow set3

Output:

Set = 1; Row = 0; Data = [0]
Set = 2; Row = 0; Data = [1]
Set = 3; Row = 0; Data = [1]

executeScalar<'Scalar>

Execute the command accordingly to its definition and,

• return the first cell value, if it is available and of the given type.
• throw an exception, otherwise.

This function runs asynchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

42

executeScalarSync<'Scalar>

Execute the command accordingly to its definition and,

• return the first cell value, if it is available and of the given type.
• throw an exception, otherwise.

This function runs synchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output:

42

executeScalarOrNone<'Scalar>

Execute the command accordingly to its definition and,

• return Some, if the first cell is available and of the given type.
• return None, if first cell is DBNull.
• throw an exception, otherwise.

This function runs asynchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")

PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeScalarOrNone<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

PostgreSqlCommand.text "SELECT NUL;"
|> PostgreSqlCommand.executeScalarOrNone<int32> connection
|> Async.RunSynchronously
|> printfn "%A"
0

Output:

Some 42
None

executeScalarOrNoneSync<'Scalar>

Execute the command accordingly to its definition and,

• return Some, if the first cell is available and of the given type.
• return None, if first cell is DBNull.
• throw an exception, otherwise.

This function runs synchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")

PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeScalarOrNoneSync<int32> connection
|> printfn "%A"

PostgreSqlCommand.text "SELECT NULL;"
|> PostgreSqlCommand.executeScalarOrNoneSync<int32> connection
|> printfn "%A"
0

Output:

Some 42
None

executeNonQuery<'Scalar>

Execute the command accordingly to its definition and, return the number of rows affected.

This function runs asynchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeNonQuery connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

-1

executeNonQuerySync<'Scalar>

Execute the command accordingly to its definition and, return the number of rows affected.

This function runs synchronously.

Example:

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
PostgreSqlCommand.text "SELECT 42;"
|> PostgreSqlCommand.executeNonQuerySync connection
|> printfn "%A"

Output:

-1

🦮 PostgreSqlNullDbValue: Null Helpers

The module to handle options and results in parameters.

ifNone

Return PostgreSql DB Null value if the given option is None, otherwise the underlying wrapped in Some.

Example:

[ "a", PostgreSqlNullDbValue.ifNone Integer (Some 42)
  "b", PostgreSqlNullDbValue.ifNone Integer (None) ]
|> printfn "%A"

Output:

[("a", Integer 42); ("b", Null)]

ifError

Return PostgreSql DB Null value if the given option is Error, otherwise the underlying wrapped in Ok.

Example:

[ "a", PostgreSqlNullDbValue.ifError Integer (Ok 42)
  "b", PostgreSqlNullDbValue.ifError Integer (Error "meh") ]
|> printfn "%A"

Output:

[("a", Integer 42); ("b", Null)]

🚄 PostgreSqlTransaction: Transaction Helpers

This is the main module to interact with NpgsqlTransaction.

📝 Note: The default isolation level is ReadCommitted.

commit

Create and commit an automatically generated transaction with the given connection, isolation, cancellation token and transaction body.

This function runs asynchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commit (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    return!
        PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.executeScalar<int32> connection
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

1
1

commitSync

Create and commit an automatically generated transaction with the given connection, isolation and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitSync (IsolationLevel.ReadCommitted) connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.executeScalarSync<int32> connection
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output:

1
1

notCommit

Create and do not commit an automatically generated transaction with the given connection, isolation, cancellation token and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.notCommit (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);" 
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    return!
        $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

1
0

notCommitSync

Create and do not commit an automatically generated transaction with the given connection, isolation and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.notCommitSync (IsolationLevel.ReadCommitted) connection (fun connection _ -> 
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);" 
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeNonQuery connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output:

1
0

commitOnSome

Create and commit an automatically generated transaction with the given connection, isolation, cancellation token and transaction body.

The commit phase only occurs if the transaction body returns Some.

This function runs asynchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnSome (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Some 42
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 1:

Some 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnSome (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';" 
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return None
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

None
0

commitOnSomeSync

Create and commit an automatically generated transaction with the given connection, isolation and transaction body.

The commit phase only occurs if the transaction body returns Some.

This function runs synchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnSomeSync (IsolationLevel.ReadCommitted) connection (fun connection _ -> 
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Some 42
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 1:

Some 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnSomeSync (IsolationLevel.ReadCommitted) connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';" 
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return None
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 2:

None
0

commitOnOk

Create and commit an automatically generated transaction with the given connection, isolation, cancellation token and transaction body.

The commit phase only occurs if the transaction body returns Ok.

This function runs asynchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnOk (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Ok 42
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 1:

Ok 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnOk (CancellationToken.None) (IsolationLevel.ReadCommitted) connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Error "fail"
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

Error "fail"
0

commitOnOkSync

Create and commit an automatically generated transaction with the given connection, isolation and transaction body.

The commit phase only occurs if the transaction body returns Ok.

This function runs synchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnOkSync (IsolationLevel.ReadCommitted) connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Ok 42
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 1:

Ok 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.commitOnOkSync (IsolationLevel.ReadCommitted) connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Error "fail"
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 2:

Error "fail"
0

defaultCommit

Create and commit an automatically generated transaction with the given connection and transaction body.

This function runs asynchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommit connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    return!
        PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.executeScalar<int32> connection
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

1
1

defaultCommitSync

Create and commit an automatically generated transaction with the given connection and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitSync connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    PostgreSqlCommand.text $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.executeScalarSync<int32> connection
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output:

1
1

defaultNotCommit

Create and do not commit an automatically generated transaction with the given connection and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultNotCommit connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);" 
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    return!
        $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output:

1
0

defaultNotCommitSync

Create and do not commit an automatically generated transaction with the given connection and transaction body.

This function runs synchronously.

Example:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultNotCommitSync connection (fun connection _ -> 
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);" 
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeNonQuery connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output:

1
0

defaultCommitOnSome

Create and commit an automatically generated transaction with the given connection and transaction body.

The commit phase only occurs if the transaction body returns Some.

This function runs asynchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnSome connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Some 42
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 1:

Some 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnSome connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';" 
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return None
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

None
0

defaultCommitOnSomeSync

Create and commit an automatically generated transaction with the given connection and transaction body.

The commit phase only occurs if the transaction body returns Some.

This function runs synchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnSomeSync connection (fun connection _ -> 
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Some 42
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 1:

Some 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnSomeSync connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';" 
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return None
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 2:

None
0

defaultCommitOnOk

Create and commit an automatically generated transaction with the given connection and transaction body.

The commit phase only occurs if the transaction body returns Ok.

This function runs asynchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnOk connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Ok 42
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 1:

Ok 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnOk connection (fun connection _ -> async {
    do! $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
        |> PostgreSqlCommand.text 
        |> PostgreSqlCommand.executeNonQuery connection
        |> Async.Ignore

    do! $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
        |> PostgreSqlCommand.text
        |> PostgreSqlCommand.executeScalar<int32> connection
        |> Async.Ignore
    return Error "fail"
})
|> Async.RunSynchronously
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalar<int32> connection
|> Async.RunSynchronously
|> printfn "%A"

Output 2:

Error "fail"
0

defaultCommitOnOkSync

Create and commit an automatically generated transaction with the given connection and transaction body.

The commit phase only occurs if the transaction body returns Ok.

This function runs synchronously.

Example 1:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnOkSync connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Ok 42
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 1:

Ok 42
1

Example 2:

let tableName = "people"

use connection = new NpgsqlConnection("Host=localhost;Database=my_database;User ID=postgres;")
connection.Open()

PostgreSqlTransaction.defaultCommitOnOkSync connection (fun connection _ ->
    $"CREATE TABLE {tableName} (id SERIAL PRIMARY KEY, name TEXT NOT NULL);"
    |> PostgreSqlCommand.text 
    |> PostgreSqlCommand.executeNonQuerySync connection
    |> ignore

    $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
    |> PostgreSqlCommand.text
    |> PostgreSqlCommand.executeScalarSync<int32> connection
    |> ignore
    return Error "fail"
)
|> printfn "%A"

$"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'{tableName}';"
|> PostgreSqlCommand.text 
|> PostgreSqlCommand.executeScalarSync<int32> connection
|> printfn "%A"

Output 2:

Error "fail"
0

❤ How to Contribute

Bug reports, feature requests, and pull requests are very welcome! Please read the Contribution Guidelines to get started.

📜 Licensing

The project is licensed under MIT. For more information on the license see the license file.