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. Indexes are matched by name: when an existing index keeps its name but its definition differs from the model (key columns or their order, UNIQUE, INCLUDE; this covers [CompositeIndex], [Index] and Column.Index / Column.Unique), sync logs a drift warning at every level. Production (Safe) never rebuilds it and flags the table DriftPending; Developer and Migration drop and recreate it. An index on a column renamed with PreviousName follows the rename and is not drift.
  • [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.

Database and table administration

pg.Admin (also available as methods directly on the library) creates, drops and checks databases and entity tables. It is meant for first-run setup, demos, per-test databases and resets; day-to-day schema changes stay with sync and migrations.

await pg.CreateDatabaseAsync("shop_test");                         // server defaults (template1)
await pg.CreateDatabaseAsync("shop_ci", encoding: "UTF8", collation: "en_US.UTF-8");
bool exists = (await pg.DatabaseExistsAsync("shop_test")).Value;
await pg.DropDatabaseAsync("shop_test");                           // WITH (FORCE) — PostgreSQL 13+

await pg.CreateTableAsync<Order>();          // create only; never alters an existing table
bool has = (await pg.TableExistsAsync<Order>()).Value;
bool raw = (await pg.TableExistsAsync("sales.orders")).Value;      // bare name = defaultSchema
await pg.TruncateTableAsync<Order>();        // TRUNCATE TABLE … RESTART IDENTITY
await pg.TruncateTableAsync<Order>(restartIdentity: false, cascade: true);
await pg.DropTableAsync<Order>(cascade: true);   // also forgets its __schema_state row
  • All methods return Result<bool>. Create* returns false when the object already existed (with ifNotExists: true, the default) and Drop* returns false when there was nothing to drop (with ifExists: true). With the flag off, those cases are failures.
  • PostgreSQL cannot create or drop the database a session is connected to, so database operations run on an unpooled connection to the connection's maintenanceDatabase (default postgres) with the same host and credentials. The user needs CREATEDB (or ownership, to drop).
  • CreateDatabaseAsync without encoding / collation takes the server's defaults. With either, the database is copied from template0 (TEMPLATE template0), which PostgreSQL requires to pick an encoding or locale different from the template's; collation sets both LC_COLLATE and LC_CTYPE. Encoding and locale names may only contain letters, digits and _ . - @, and database names are limited to 63 bytes (PostgreSQL would silently truncate a longer one); anything else throws ArgumentException before reaching the server.
  • DropDatabaseAsync may drop any database, including the one the connection is configured for. Idle pooled connections to it are cleared first, and DROP DATABASE … WITH (FORCE) terminates other sessions still connected to it, so it needs PostgreSQL 13 or later.
  • Table operations use the entity's [Table(Schema = …)], or else the connection's defaultSchema. CreateTableAsync<T> runs the same DDL as a first sync (including CREATE SCHEMA IF NOT EXISTS), records the table in __schema_state under schema.table, so the next sync skips it, and registers the entity like SyncTableAsync<T> does.
  • DropTableAsync<T>(cascade: true) also drops the foreign-key constraints and views that depend on the table (DROP TABLE … CASCADE); without it, such dependents make the drop fail. TruncateTableAsync<T> resets identity / serial sequences by default (restartIdentity: true); cascade: true truncates the tables whose foreign keys reference it as well, where it would otherwise be refused.

Creating the database on startup

Set createDatabaseIfMissing on a database in config.postgresql.json and the library creates it (server default encoding and locale) during initialization, before it first connects. The check and the CREATE DATABASE run on maintenanceDatabase. It is off by default; a failure to create the database is logged as a warning.

"Default": { "host": "localhost", "database": "shop", "createDatabaseIfMissing": true }
Edit this page