TheUtils.Pg 1.0.0-beta

This is a prerelease version of TheUtils.Pg.
dotnet add package TheUtils.Pg --version 1.0.0-beta
                    
NuGet\Install-Package TheUtils.Pg -Version 1.0.0-beta
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="TheUtils.Pg" Version="1.0.0-beta" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="TheUtils.Pg" Version="1.0.0-beta" />
                    
Directory.Packages.props
<PackageReference Include="TheUtils.Pg" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add TheUtils.Pg --version 1.0.0-beta
                    
#r "nuget: TheUtils.Pg, 1.0.0-beta"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package TheUtils.Pg@1.0.0-beta
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=TheUtils.Pg&version=1.0.0-beta&prerelease
                    
Install as a Cake Addin
#tool nuget:?package=TheUtils.Pg&version=1.0.0-beta&prerelease
                    
Install as a Cake Tool

TheUtils.Pg

A PostgreSQL monad for functional database operations using language-ext v5.

Overview

TheUtils.Pg provides a first-class Pg<A> monad that wraps ReaderT<PgEnv, IO, A>, giving you:

  • Composable database operations using LINQ query syntax
  • Environment-based configuration via PgEnv
  • Full EF Core integration for queries and entity operations
  • Automatic transaction management via EF Core's Database.CurrentTransaction
  • Npgsql-specific features: COPY protocol, LISTEN/NOTIFY, advisory locks

Installation

dotnet add package TheUtils.Pg

Quick Start

using TheUtils;
using static TheUtils.Pg;

// Create environment with your DbContext
var env = new PgEnv(dbContext);

// Define a computation
Pg<Option<User>> getUser(int id) =>
    from users in set<User>()
    from user in head(users.Where(u => u.Id == id))
    select user;

// Run it
var result = await getUser(42).Run(env).RunAsync();

Core Concepts

The Pg Monad

Pg<A> is a monad that combines:

  • Reader (PgEnv) - read-only environment with DbContext and configuration
  • IO - async effects
// Pg<A> wraps this transformer:
ReaderT<PgEnv, IO, A>

Implements these traits:

  • Monad<Pg> - composition via LINQ
  • MonadIO<Pg> - lift IO operations
  • MonadUnliftIO<Pg> - extract IO for advanced patterns (fork, bracket)
  • Fallible<Pg> - error handling with Fail and Catch
  • Readable<Pg, PgEnv> - environment access

PgEnv (Environment)

Read-only configuration passed to all operations:

public record PgEnv(
    DbContext Context,
    Option<NpgsqlConnection> RawConnection = default,
    IsolationLevel DefaultIsolation = IsolationLevel.ReadCommitted,
    Option<TimeSpan> CommandTimeout = default
);

// Create from DbContext
var env = new PgEnv(myDbContext);

// Or with raw Npgsql connection for advanced features
var env = PgEnv.FromConnection(npgsqlConnection, myDbContext);

Usage Examples

Query Operations

using static TheUtils.Pg;

// Get all entities
Pg<Seq<User>> getAllUsers() =>
    from users in set<User>()
    from result in seq(users.Where(u => u.IsActive))
    select result;

// Get single entity (Option)
Pg<Option<User>> findUser(string email) =>
    from users in set<User>()
    from user in head(users.Where(u => u.Email == email))
    select user;

// Get single entity with OptionT (for monad transformer chaining)
Pg<Option<string>> getUserEmail(int id) =>
    from result in (
        from users in set<User>()
        from user in headT(users.Where(u => u.Id == id))
        select user.Email
    ).Run()
    select result;

// Check existence
Pg<bool> userExists(string email) =>
    from users in set<User>()
    from exists in any(users.Where(u => u.Email == email))
    select exists;

// Count
Pg<int> countActiveUsers() =>
    from users in set<User>()
    from count in count(users.Where(u => u.IsActive))
    select count;

// Raw SQL query
Pg<Seq<UserDto>> searchUsers(string term) =>
    from results in seq<UserDto>($"SELECT id, name FROM users WHERE name LIKE {term}")
    select results;

Entity Operations

// Add entity
Pg<User> createUser(string name, string email) =>
    from entry in add(new User { Name = name, Email = email })
    from _ in saveChanges
    select entry.Entity;

// Update entity
Pg<Unit> updateUserName(User user, string newName) =>
    pure(user.Name = newName)
        .Bind(_ => update(user))
        .Bind(_ => saveChanges);

// Delete entity
Pg<Unit> deleteUser(User user) =>
    delete(user).Bind(_ => saveChanges);

// Bulk operations
Pg<Unit> addUsers(Seq<User> users) =>
    addRange(users).Bind(_ => saveChanges);

Transactions

// Automatic transaction with rollback on error
Pg<Unit> transferFunds(int fromId, int toId, decimal amount) =>
    transact(
        from sender in single(set<Account>().Bind(a =>
            head(a.Where(x => x.Id == fromId))))
        from receiver in single(set<Account>().Bind(a =>
            head(a.Where(x => x.Id == toId))))
        from _ in pure(sender.Balance -= amount)
        from __ in pure(receiver.Balance += amount)
        from ___ in saveChanges
        select unit
    );

// Manual transaction control
Pg<Unit> manualTransaction() =>
    from _ in beginTransaction(Some(IsolationLevel.Serializable))
    from __ in execute($"UPDATE accounts SET balance = balance + 100")
    from ___ in commit
    select unit;

// Check current transaction
Pg<bool> isInTransaction() =>
    from tx in currentTransaction
    select tx.IsSome;

Raw SQL Execution

// Execute with affected row count
Pg<int> deactivateOldUsers(DateTime cutoff) =>
    execute($"UPDATE users SET is_active = false WHERE last_login < {cutoff}");

// Execute raw SQL with parameters
Pg<int> updateBalances(decimal amount, Seq<int> userIds) =>
    executeRaw("UPDATE users SET balance = balance + @p0 WHERE id = ANY(@p1)",
        Seq<object>(amount, userIds.ToArray()));

Npgsql-Specific Features

COPY Protocol (Bulk Import)
// Bulk import using PostgreSQL COPY
Pg<ulong> bulkImportUsers(Seq<User> users) =>
    binaryImport("users (name, email, balance)", users, (writer, user) =>
    {
        writer.Write(user.Name, NpgsqlDbType.Text);
        writer.Write(user.Email, NpgsqlDbType.Text);
        writer.Write(user.Balance, NpgsqlDbType.Numeric);
    });
LISTEN/NOTIFY (Pub/Sub)
// Subscribe to notifications
Pg<Unit> subscribeToChanges() =>
    from _ in listen("user_changes")
    from notifications in notifications
    // Process notifications...
    select unit;

// Send notification
Pg<Unit> notifyChange(string payload) =>
    notify("user_changes", payload);
Advisory Locks (Distributed Locking)
// Scoped lock - guarantees release on completion/error
Pg<Unit> processWithLock(long resourceId) =>
    withAdvisoryLock(resourceId,
        from data in loadData(resourceId)
        from _ in processData(data)
        from __ in saveResults(data)
        select unit
    );

// Manual lock control
Pg<bool> tryProcessResource(long id) =>
    from acquired in tryAdvisoryLock(id)
    from _ in acquired
        ? processResource(id).Bind(_ => advisoryUnlock(id))
        : pure(unit)
    select acquired;

Running Computations

var env = new PgEnv(dbContext);

// Run and get result
var user = await getUser(42).Run(env).RunAsync();

// Run with cancellation token
var result = await computation.Run(env).RunAsync(cancellationToken);

Error Handling

// Catch and handle errors
Pg<int> safeOperation() =>
    Pg.Catch(
        riskyOperation(),
        err => err.Message.Contains("timeout"),
        err => pure(0) // fallback value
    ).As();

API Reference

Query Operations

Method Description
seq<A>(IQueryable<A>) Execute query, return Seq<A>
head<A>(IQueryable<A>) First or None (Pg<Option<A>>)
headT<A>(IQueryable<A>) First as OptionT (for monad transformer chaining)
single<A>(IQueryable<A>) Exactly one (throws if not)
any<A>(IQueryable<A>) Existence check
count<A>(IQueryable<A>) Row count
set<A>() Get DbSet<A>
query<A>(FormattableString) Create IQueryable from SQL

Entity Operations

Method Description
add<A>(A) Add entity
addRange<A>(Seq<A>) Add multiple entities
update<A>(A) Update entity
delete<A>(A) Remove entity
saveChanges Persist changes

Transaction Operations

Method Description
currentTransaction Get current transaction (Option<IDbContextTransaction>)
beginTransaction(Option<IsolationLevel>) Start transaction
commit Commit transaction
rollback Rollback transaction
transact<A>(Pg<A>) Auto commit/rollback wrapper

Npgsql Operations

Method Description
binaryImport<A>(...) COPY FROM STDIN
listen(channel) Subscribe to notifications
notify(channel, payload) Send notification
advisoryLock(key) Acquire advisory lock
withAdvisoryLock<A>(key, Pg<A>) Scoped advisory lock

License

MIT License - see LICENSE for details.

Product Compatible and additional computed target framework versions.
.NET net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
1.0.0-beta 115 1/11/2026