Pragmatic.Migrations

Declarative schema migrations for Pragmatic.Design — no migration files, no EF Core migrations, no manual SQL.

How It Works

Compile Time                    Runtime
┌──────────────┐     ┌─────────────────────────────────────────┐
│ Source Gen    │     │ Introspect DB → Diff → SQL → Execute   │
│ ↓             │     │                                         │
│ SchemaVersion │────▶│ SchemaDiffEngine → SqlGenerator → Runner│
│ (desired)     │     │                                         │
└──────────────┘     └─────────────────────────────────────────┘

1. Source Generator reads [Entity] attributes and emits a SchemaVersion (desired schema) at compile time
2. Introspector reads the current database schema via information_schema / sys.* / PRAGMA
3. Diff Engine compares desired vs current, producing ordered SchemaChange[]
4. SQL Generator emits idempotent, provider-specific SQL (IF NOT EXISTS, DO $$ blocks)
5. Runner executes each change in a transaction — if one fails, everything rolls back

Quick Start

// Program.cs — zero configuration
PragmaticApp.RunAsync(builder =>
{
    builder.UsePragmaticMigrations();
});

That’s it. The SG detects Pragmatic.Migrations in your references and auto-generates the schema.

Supported Providers

Provider	Generator	Introspector	Idempotent SQL
PostgreSQL	DO $$ IF NOT EXISTS $$ blocks	information_schema + pg_catalog	Yes
SQL Server	IF NOT EXISTS (sys.*) checks	sys.* views	Yes
SQLite	CREATE IF NOT EXISTS	sqlite_master + PRAGMA	Partial

Provider is auto-detected from SchemaVersion.ProviderName (set by the SG based on your EF Core provider).

API

Database Filtering

builder.UsePragmaticMigrations(m =>
{
    m.OnlyDatabase<ShowcaseAppDatabase>();  // Migrate only this DB
});

Dry Run

builder.UsePragmaticMigrations(m =>
{
    m.DryRun();  // Generate SQL without executing
});

Force Breaking Changes

By default, breaking changes (DROP TABLE, DROP COLUMN, SET NOT NULL, narrowing type changes) are blocked. Use Force to apply:

builder.UsePragmaticMigrations(m =>
{
    m.Force();  // Allow breaking changes at startup
});

At host startup a blocked breaking change — or any migration failure — aborts startup: the host will not run on a stale or partial schema. Inspect the failure, then either revise the entity change or opt in with m.Force() for a controlled deployment.

Data Migrations

IDataMigration runs a named data transformation — backfilling a column, converting values, moving data between tables — exactly once per database. Each is tracked by name in __PragmaticDataMigrations and runs in its own transaction after the schema changes.

public sealed class BackfillOrderStatus : IDataMigration
{
    public string Name => "2026-05_BackfillOrderStatus";  // stable, immutable — renaming re-runs it
    public int Order => 0;

    public async Task MigrateAsync(DbConnection connection, DbTransaction transaction, CancellationToken ct)
    {
        var cmd = connection.CreateCommand();
        cmd.Transaction = transaction;  // required — keeps the change atomic with the tracking record
        cmd.CommandText = "UPDATE \"Orders\" SET \"Status\" = 'pending' WHERE \"Status\" IS NULL";
        await cmd.ExecuteNonQueryAsync(ct);
    }
}

builder.UsePragmaticMigrations(m => m.AddDataMigration<BackfillOrderStatus>());

Excluding Tables

Tables owned by another system, a DBA, or a database extension can be kept out of migration management — the diff engine will never propose to alter or drop them:

builder.UsePragmaticMigrations(m => m.ExcludeTable("LegacyAudit", "spatial_ref_sys"));

Concurrent Index Builds

For deployments where new indexes would lock large tables, opt into post-commit out-of-transaction index creation. PostgreSQL emits CREATE INDEX CONCURRENTLY, SQL Server emits WITH (ONLINE = ON) (Enterprise edition), SQLite is unaffected (no concurrent mode — the flag is a no-op):

builder.UsePragmaticMigrations(m => m.UseConcurrentIndexes());

The runner defers every AddIndex change to a phase that runs after the schema transaction commits. A concurrent build that fails leaves the index INVALID on the server (the schema is already committed) and the migration is reported as failed with a suggestion to drop and recreate the index after fixing the underlying cause.

Error Handling

When a migration fails, the result includes:

• Which change failed (index + description)
• The SQL that failed (not the entire batch)
• Context-aware suggestions (e.g., “Column may contain NULL values — update before applying SET NOT NULL”)
• Transaction safety — all changes are rolled back, database is in its original state

Migration failed at step 3/7: SET NOT NULL Orders.Email
Error: column "Email" of relation "Orders" contains null values

Suggestions:
  - Run with DryRun=true to inspect the generated SQL before applying
  - SET NOT NULL failed — the column may contain NULL values
  - Update existing NULL values before applying this change
  - All changes have been rolled back — the database is in its original state

Schema Audit

Every successful migration is recorded in __PragmaticSchema:

Column	Description
SchemaHash	FNV-1a hash of the desired schema
MigrationSql	The SQL that was executed
ChangesApplied	Number of changes
DurationMs	Execution time
AppliedAt	Timestamp

Schema Snapshot

The desired schema can be written to committed JSON files — one schema/<database>.schema.json per database — so schema changes are reviewable in pull requests and multi-branch schema conflicts surface as ordinary merge conflicts.

Generate it with the CLI:

pragmatic-migrate snapshot --assembly bin/Debug/net10.0/MyApp.dll --output schema

Or regenerate it automatically after every host build — opt in from the host project and the Pragmatic.Migrations MSBuild target does the rest:

<PropertyGroup>
  <PragmaticSchemaSnapshot>true</PragmaticSchemaSnapshot>
</PropertyGroup>

CI gate

Commit the schema/ folder. In CI, regenerate the snapshot and fail the build when it drifted from the entities:

pragmatic-migrate snapshot --assembly path/to/MyApp.dll --output schema
git diff --exit-code schema/

A non-empty diff means an entity changed without the snapshot being regenerated — or that two branches changed the schema in conflicting ways.

CLI Commands

The pragmatic-migrate CLI operates on a compiled host assembly. Beyond apply, status, script, snapshot, and history, two commands support client and manifest workflows:

Command	Description
manifest	Exports the manifest JSON embedded in a compiled assembly to a file (--assembly, --output).
generate	Generates a typed client project from manifest data — C# (default) or TypeScript via --language (--assembly or --manifest, --output, optional --namespace/--boundary).

# Export the manifest
pragmatic-migrate manifest --assembly bin/Debug/net10.0/MyApp.dll --output manifest.json

# Generate a typed C# client (use --language typescript for TS)
pragmatic-migrate generate --assembly bin/Debug/net10.0/MyApp.dll --output ./Client/

Multi-Tenant Migrations

ITenantMigrationOrchestrator (default TenantMigrationOrchestrator) applies the desired schema across every active tenant that has a dedicated database. It iterates the ITenantStore, runs the migration runner per tenant, marks each tenant Migrating/Active/Suspended, and reports progress to the control plane (IHostStatus/IControlPlane). Use MigrateAllTenantsAsync for the full sweep or MigrateTenantAsync(tenantId, ...) for a single tenant; shared-database tenants are skipped.

Migration Hooks

IMigrationHook runs custom logic around individual schema changes — for inline data transformation (populating new NOT NULL columns, converting types, moving data) tied to the change’s transaction:

• DatabaseName — filter to a single database, or null for all.
• BeforeChangeAsync(context, ct) — return false to skip the change (default: proceed).
• AfterChangeAsync(context, ct) — runs after the change commits successfully; create commands via MigrationStepContext.Connection and set their Transaction to MigrationStepContext.Transaction so the data migration is atomic with the schema change.

Comparison with EF Core Migrations

Feature	EF Core Migrations	Pragmatic.Migrations
Migration files	Yes (per-change)	No (declarative)
Design-time tools	Required (dotnet ef)	Not needed
ModelSnapshot	Required	Not needed (SG-generated)
Idempotent SQL	Optional (--idempotent)	Always
Multi-database	Manual	Auto-detected from topology
Transaction safety	Provider-dependent	Always (per-change execution)
Error diagnostics	Generic	Change-level with suggestions
Breaking change detection	No	Yes (blocks without Force)

Architecture

Pragmatic.Migrations/
├── Schema/          # SchemaVersion, TableSchema, ColumnSchema, IndexSchema, ForeignKeySchema
├── Diff/            # SchemaDiffEngine, SchemaDiff
│   └── Changes/     # 12 SchemaChange subtypes (CreateTable, AddColumn, RenameColumn, etc.)
├── Introspection/   # ISchemaIntrospector (3 providers), IConnectionFactory
├── Sql/             # ISqlMigrationGenerator, SqlGeneratorBase (3 providers)
├── Runner/          # IMigrationRunner, MigrationRunner, MigrationResult
└── Configuration/   # MigrationsBuilder, MigrationProviderFactory

Documentation

• Concepts — declarative schema, diff engine, provider-specific idiomatic SQL
• Getting Started — fresh DB, schema evolution, idempotent rerun, multi-provider
• Common Mistakes
• Troubleshooting

Samples:

• Pragmatic.Migrations.Samples — 4 scenarios including multi-provider SQL generation

License

Part of the Pragmatic.Design ecosystem — see Licensing. Pragmatic.Migrations is licensed under the PolyForm Small Business 1.0.0 license (free for small businesses; commercial license above the threshold).