postgres.md

PostgreSQL usage

Docs for when using when using PostgreSQL Npgsql.

Implementation

Postgres required track_commit_timestamp to be enabled. This can be done using ALTER SYSTEM SET track_commit_timestamp to "on" and then restarting the Postgres service

Timestamp calculation

select pg_last_committed_xact();

^{snippet source | anchor}

Usage

Example SQL schema

create table IF NOT EXISTS public."Companies"
(
    "Id" uuid not null
        constraint "PK_Companies"
            primary key,
    "Content" text
);

alter table public."Companies"
    owner to postgres;

create table IF NOT EXISTS public."Employees"
(
    "Id" uuid not null
        constraint "PK_Employees"
            primary key,
    "CompanyId" uuid not null
        constraint "FK_Employees_Companies_CompanyId"
            references public."Companies"
            on delete cascade,
    "Content"   text,
    "Age"       integer not null
);

alter table public."Employees"
    owner to postgres;

create index IF NOT EXISTS "IX_Employees_CompanyId"
    on public."Employees" ("CompanyId");

^{snippet source | anchor}

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new NpgsqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

app.MapGroup("/group")
    .UseDelta()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

var app = builder.Build();
app.UseDelta(
    shouldExecute: httpContext =>
    {
        var path = httpContext.Request.Path.ToString();
        return path.Contains("match");
    });

^{snippet source | anchor}

Custom Connection discovery

By default, Delta uses HttpContext.RequestServices to discover the NpgsqlConnection and NpgsqlTransaction:

var npgsqlConnection = Type.GetType("Npgsql.NpgsqlConnection, Npgsql");
if (npgsqlConnection != null)
{
    connectionType = npgsqlConnection;
    transactionType = npgsqlConnection.Assembly.GetType("Npgsql.NpgsqlTransaction")!;
    return;
}

^{snippet source | anchor}

static Connection DiscoverConnection(HttpContext httpContext)
{
    var provider = httpContext.RequestServices;
    var connection = (DbConnection) provider.GetRequiredService(connectionType);
    var transaction = (DbTransaction?) provider.GetService(transactionType);
    return new(connection, transaction);
}

^{snippet source | anchor}

To use custom connection discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext =>
        httpContext.RequestServices.GetRequiredService<NpgsqlConnection>());

^{snippet source | anchor}

To use custom connection and transaction discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext =>
    {
        var provider = httpContext.RequestServices;
        var connection = provider.GetRequiredService<NpgsqlConnection>();
        var transaction = provider.GetService<NpgsqlTransaction>();
        return new(connection, transaction);
    });

^{snippet source | anchor}

GetLastTimeStamp:

GetLastTimeStamp is a helper method to get the DB timestamp that Delta uses to calculate the etag.

var timeStamp = await connection.GetLastTimeStamp();

^{snippet source | anchor}