Table of Contents

CL.PostgreSQL — Schema & Migrations

Two complementary mechanisms keep a database in step with the code:

  • Declarative sync reconciles a live table to an entity class. It runs at startup, is CRC-gated so an unchanged model costs nothing, and never drops anything unless you ask.
  • Imperative migrations are ordered IMigration classes for changes sync cannot infer — backfills, data transforms, renames with logic, extension installs.

Entity attributes

[Table]

Property Default Purpose
Name class name Table name.
Schema the connection's defaultSchema PostgreSQL schema. Every statement is qualified with it. When the attribute does not name one, the entity lands in the connection's configured defaultSchema (public unless changed), which is created if missing.
Comment — Emitted as COMMENT ON TABLE.
Collation — Column collation applied to text columns.
Unlogged false Faster writes, not crash-safe, not replicated.
AccessMethod / AccessMethodName Heap Table access method, for extensions that provide one.

[Column]

Property Default Purpose
Name property name Column name.
DataType Unspecified Leave unset to infer from the CLR type.
Size 0 varchar/char length; fractional-seconds precision for time types.
Precision / Scale 10 / 2 numeric precision.
Primary false Part of the primary key.
AutoIncrement false GENERATED BY DEFAULT AS IDENTITY.
NotNull false Forces NOT NULL. Non-nullable CLR value types get it anyway.
Unique false A named unique constraint.
Index false A plain btree index.
DefaultValue — Raw SQL default, e.g. now(), 'active', 0.
Comment — Emitted as COMMENT ON COLUMN.
OnUpdateCurrentTimestamp false Creates a BEFORE UPDATE trigger (see below).
PreviousName — Triggers RENAME COLUMN instead of drop-and-add.
StorageType Default Binary / VarBinary both map to bytea.

Type inference

With no explicit DataType the CLR type decides, and it picks a native PostgreSQL type:

CLR PostgreSQL
bool boolean
byte, sbyte, short smallint (no 1-byte or unsigned integers exist)
int, ushort integer
long, uint bigint
ulong numeric(20,0) (exceeds bigint at the top of its range)
float / double real / double precision
decimal numeric(p,s)
string character varying(defaultStringSize)
Guid uuid — 16 bytes, not CHAR(36)
DateTime, DateTimeOffset timestamp with time zone
DateOnly / TimeOnly / TimeSpan date / time / interval
byte[] bytea
IPAddress inet
short[], int[], long[], string[], decimal[], Guid[], bool[] the matching array type (any other array falls back to text)
enum integer

Other attributes

  • [CompositeIndex("name", "col", "col2", Unique = true)] on the class. A unique composite becomes a table constraint, so ON CONFLICT can arbitrate on it.
  • [Index(Name, Unique, Include)] on a property. Include becomes a real INCLUDE covering clause (PostgreSQL 11+).
  • [ForeignKey("table", "column", OnDelete, OnUpdate)]. The referenced table is qualified with the owning schema unless written as schema.table.
  • [SoftDelete("DeletedUtc")] on the class — reads filter IS NULL, deletes stamp the column.
  • [RetainDays(90, "CreatedUtc")] on the class — the retention worker prunes older rows.
  • [Ignore] on a property — excluded from every operation.

Declarative sync

await pg.SyncTableAsync<User>();
await pg.SyncSchemaAsync(typeof(User), typeof(Order));

Sync modes

syncMode in config decides how destructive a reconcile may be:

Mode Behaviour
developer Reconciles aggressively, dropping removed columns, indexes and FKs.
production (default) Adds and modifies only. A change needing a drop is deferred and the table is flagged DriftPending.
migration One-shot destructive reconcile, with a schema backup taken first. Idempotent.

The CRC sentinel

Each entity's generated DDL is hashed and stored in __schema_state. On the next start an unchanged model matches its stored CRC and the whole diff is skipped — no catalog queries, no DDL. The hash is computed over normalised, sorted DDL lines, so reflection ordering cannot change it.

The trade-off is that the gate keys on the model, not the database: while the CRC still matches, schema drift introduced outside the library (someone dropping a column by hand) is not noticed. Change the model, or delete the table's row from __schema_state, to force a full diff.

Concurrency

The pass runs under a pg_advisory_lock keyed by a stable hash of the lock name. Several instances booting together contend for it; the winner runs the DDL and the rest wait, then find matching CRCs and do nothing. The lock is session-scoped, so it is released even if the process dies.

Type comparison

The diff canonicalises both sides before comparing. PostgreSQL reports types through format_type (character varying(255), timestamp with time zone) while the model generates its own spelling, and defaults come back with a cast attached ('active'::text). Comparing those literally would report a difference on nearly every column and rewrite the whole database on each boot — which is exactly what the pre-4.8 analyzer did.

OnUpdateCurrentTimestamp

PostgreSQL has no such column clause. Sync creates a per-column trigger function and a BEFORE UPDATE row trigger:

CREATE OR REPLACE FUNCTION "public"."fn_users_updated_utc_touch"() RETURNS trigger AS $$
BEGIN NEW."updated_utc" = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "trg_users_updated_utc_touch" BEFORE UPDATE ON "public"."users"
FOR EACH ROW EXECUTE FUNCTION "public"."fn_users_updated_utc_touch"();

Both statements are idempotent, so they are re-emitted rather than probed for.

Imperative migrations

public sealed class AddUserRegions : Migration
{
    public AddUserRegions() : base(appVersion: "1.4.0", order: 1, description: "Backfill regions") { }

    public override async Task UpAsync(IMigrationContext ctx, CancellationToken ct)
    {
        await ctx.ExecuteAsync("""
            UPDATE "public"."users" SET "region" = 'eu' WHERE "region" IS NULL
            """, ct: ct);
    }

    public override async Task DownAsync(IMigrationContext ctx, CancellationToken ct)
    {
        await ctx.ExecuteAsync("""
            UPDATE "public"."users" SET "region" = NULL WHERE "region" = 'eu'
            """, ct: ct);
    }
}

Register and run:

pg.RegisterMigrationsFrom(typeof(Program).Assembly);

var pending = await pg.GetPendingMigrationsAsync();
var result = await pg.Migrations.MigrateAsync();

// Rollback takes a MigrationVersion, and removes everything strictly newer than it.
await pg.Migrations.RollbackAsync(new MigrationVersion("1.3.0", 0));

Migrations are ordered by (appVersion, order), run inside a transaction, and recorded in a __migrations table. Storing history in the database rather than a local file is what makes this safe across instances: before 4.8 the tracker wrote to {dataDir}/migrations/migration_history.json, so every node kept its own history and all of them re-ran everything.

The runner takes the same advisory lock as declarative sync, so only one node migrates.

IMigrationContext gives you ExecuteAsync, QueryAsync<T>, ScalarAsync<T> and SyncTableAsync<T> (a bridge into declarative sync), plus the raw Connection and Transaction — everything but the DDL inside SyncTableAsync runs on the migration's transaction, since PostgreSQL commits DDL immediately.

Soft delete & retention

[Table(Name = "users")]
[SoftDelete(nameof(DeletedUtc))]
public sealed class User
{
    [Column(Name = "deleted_utc")] public DateTime? DeletedUtc { get; set; }
}

Reads filter "deleted_utc" IS NULL; DeleteAsync stamps it (with a client-side DateTime.UtcNow) instead of removing the row. IncludeDeleted() opts a query out, and HardDeleteAsync really deletes.

The filter applies to every repository read, CountAsync included — so CountAsync() and GetAllAsync().Count agree. Use Query<T>().IncludeDeleted().CountAsync() for the physical row count. Writes (UpdateAsync, AdjustAsync / IncrementAsync / DecrementAsync, HardDeleteAsync) target a row by primary key and are deliberately not filtered, so a soft-deleted row can still be corrected or restored.

[RetainDays(90, nameof(CreatedUtc))] is picked up by a background worker that prunes rows past the window in batches.

Registration order does not matter. The worker's entry list is live: SyncTableAsync / SyncSchemaAsync hand each entity to it as they run, and the first [RetainDays] entity to arrive starts the loop (idempotently). Syncing your models after CodeLogic.StartAsync() — the order shown in every quick-start on these pages — works.

Because PostgreSQL has no DELETE … LIMIT, each batch selects rows by ctid with FOR UPDATE SKIP LOCKED, so concurrent passes do not block one another.

The worker runs on a timer once the library starts: a first pass five minutes after startup, then once every 24 hours. RetentionWorker.RunOnceAsync() performs a single purge pass synchronously and returns the number of rows deleted — useful for a maintenance command, or for a test that should not wait out the timer.

Backups & restore

await pg.BackupManager.BackupTableSchemaAsync("users", "public");
await pg.BackupManager.BackupDatabaseSchemaAsync();
await pg.BackupManager.CleanupOldBackupsAsync(olderThanDays: 30);
await pg.RestoreSchemaAsync("users", "public");   // BackupManager.RestoreTableSchemaAsync + clears __schema_state

PostgreSQL has no SHOW CREATE TABLE, so the DDL is reconstructed from the catalogs — columns via format_type, constraints via pg_get_constraintdef, indexes via pg_get_indexdef, plus comments. The result is a replayable script.

These are schema-only backups. Restore drops and recreates the table, so its data is lost. Use pg_dump for data.

Edit this page