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();

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}

Suffix and Authentication

When using a suffix callback that accesses HttpContext.User claims, authentication middleware must run before UseDelta. If UseDelta runs first, the User claims won't be populated yet, and all users will get the same cache key.

Delta automatically detects this misconfiguration and throws an InvalidOperationException with a helpful message if:

• A suffix callback is provided
• The user is not authenticated (context.User.Identity?.IsAuthenticated != true)

var app = builder.Build();

// Authentication middleware must run before UseDelta
// so that User claims are available to the suffix callback
app.UseAuthentication();
app.UseAuthorization();

app.UseDelta(
    suffix: httpContext =>
    {
        // Access user claims to create per-user cache keys
        var userId = httpContext.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
        var tenantId = httpContext.User.FindFirst("TenantId")?.Value;
        return $"{userId}-{tenantId}";
    });

^{snippet source | anchor}

AllowAnonymous

For endpoints that intentionally allow anonymous access but still want to use a suffix for cache differentiation (e.g., based on request headers rather than user claims), use allowAnonymous: true:

var app = builder.Build();

// For endpoints that intentionally allow anonymous access
// but still want a suffix for cache differentiation
app.UseDelta(
    suffix: httpContext => httpContext.Request.Headers["X-Client-Version"].ToString(),
    allowAnonymous: true);

^{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}