Getting Started with Pragmatic.Migrations

Step-by-step guide to adding declarative schema migrations to your Pragmatic host.

---

Prerequisites

• A Pragmatic host with at least one [Entity] declared
• A database connection string in appsettings.json
• One of the supported database providers referenced in your host .csproj:
  • Npgsql (PostgreSQL)
  • Microsoft.Data.SqlClient (SQL Server)
  • Microsoft.Data.Sqlite (SQLite)

---

Step 1: Add the NuGet Package

dotnet add package Pragmatic.Migrations

That is the only package you need. The Source Generator in Pragmatic.SourceGenerator automatically detects Pragmatic.Migrations in your references and generates the SchemaVersion metadata at compile time.

---

Step 2: Enable Migrations in Program.cs

Zero-Config (Recommended)

await PragmaticApp.RunAsync(args, builder =>
{
    builder.UsePragmaticMigrations();
});

This migrates all databases detected in your topology. The SG reads your [Entity] attributes, computes the desired schema, and the runner diffs it against the live database at startup.

With Configuration

await PragmaticApp.RunAsync(args, builder =>
{
    builder.UsePragmaticMigrations(m =>
    {
        m.OnlyDatabase<ShowcaseAppDatabase>();  // Migrate only this DB
    });
});

---

Step 3: Verify Your Entities

The SG reads every class decorated with [Entity<TId>] and produces a SchemaVersion record containing table definitions, columns, indexes, and foreign keys. For example:

// In Showcase.Booking module
[Entity<Guid>]
public partial class Reservation
{
    public string GuestName { get; set; } = "";
    public DateOnly CheckIn { get; set; }
    public DateOnly CheckOut { get; set; }
    public decimal TotalAmount { get; set; }
}

At compile time, the SG emits a SchemaVersion with a TableSchema for Reservations (pluralized), including columns, types, nullability, and a content hash.

---

Step 4: Run the Host

dotnet run

On startup, the migration runner executes this pipeline:

1. Leader election     → Only one instance runs migrations
2. Introspect DB       → Read current schema via information_schema / sys.* / PRAGMA
3. Compute diff        → Compare desired SchemaVersion vs current
4. Generate SQL        → Idempotent, provider-specific SQL
5. Execute in TX       → Each change in its own command, all in one transaction
6. Record audit        → Write to __PragmaticSchema table
7. Run seed providers  → Optional post-migration data seeding

You will see structured log output:

info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Starting migration (PostgreSql) — desired hash: a3f7b2c1
info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Diff computed: 3 changes (0 breaking)
info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Applying 1/3: CREATE TABLE "Reservations"
info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Applying 2/3: CREATE INDEX "IX_Reservations_GuestName"
info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Applying 3/3: ADD FOREIGN KEY "Reservations" → "Guests"
info: Pragmatic.Migrations.Runner.MigrationRunner
      [default] Migration complete: 3 changes applied in 47ms

---

Step 5: Inspect with Dry Run

Before applying to production, use DryRun to see what SQL would be generated without executing:

builder.UsePragmaticMigrations(m =>
{
    m.DryRun();
});

The MigrationResult contains the full SQL script in GeneratedSql and the list of SchemaChange objects in AppliedChanges. In dry-run mode, ChangesApplied is always 0 (nothing was executed).

The progress stream reports each change with a [safe] or [BREAKING] indicator:

[default] Dry run: 3 changes
  [safe] CREATE TABLE "Reservations"
  [safe] CREATE INDEX "IX_Reservations_GuestName"
  [safe] ADD FOREIGN KEY "Reservations" → "Guests"

---

Step 6: Handle Breaking Changes

By default, breaking changes (DROP TABLE, DROP COLUMN, narrowing type changes) are blocked. The runner returns a failed MigrationResult with suggestions:

Blocked: 2 breaking changes require Force=true

To apply them explicitly:

builder.UsePragmaticMigrations(m =>
{
    m.Force();
});

In production, the recommended workflow is:

1. Run with DryRun() first to inspect the SQL
2. Review breaking changes and back up data if needed
3. Apply with Force() in a maintenance window

---

Step 7: Filter by Database

In multi-database topologies (e.g., separate databases for Booking and Billing), you can target a specific database:

builder.UsePragmaticMigrations(m =>
{
    m.OnlyDatabase<ShowcaseAppDatabase>();
});

The filter matches against SchemaVersion.DatabaseName, which is set by the SG from your [Include<TModule, TDatabase>] topology attributes.

---

Step 8: Custom Audit Table

Every migration is recorded in __PragmaticSchema by default. You can change the table name:

builder.UsePragmaticMigrations(m =>
{
    m.UseAuditTable("_MyMigrationHistory");
});

The audit table stores:

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

---

Step 9: Seed Data After Migration

Implement IMigrationSeedProvider to run data seeding after migrations complete:

public sealed class DefaultRolesSeedProvider : IMigrationSeedProvider
{
    public string? DatabaseName => null;  // null = all databases
    public int Order => 100;

    public async Task SeedAsync(DbConnection connection, CancellationToken ct)
    {
        var cmd = connection.CreateCommand();
        cmd.CommandText = """
            INSERT INTO "Roles" ("Id", "Name")
            VALUES ('admin', 'Administrator')
            ON CONFLICT DO NOTHING
            """;
        await cmd.ExecuteNonQueryAsync(ct);
    }
}

Register it in DI and the runner will invoke it after successful migration.

Data Migrations vs Seeding

IMigrationSeedProvider runs after every successful migration — use it for idempotent reference data (ON CONFLICT DO NOTHING). For a one-time data transformation — backfilling a new column, converting values, moving data between tables — implement IDataMigration instead: it runs exactly once per database, tracked by name in __PragmaticDataMigrations, inside its own transaction.

public sealed class BackfillOrderStatus : IDataMigration
{
    public string Name => "2026-05_BackfillOrderStatus";

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

Register it with builder.UsePragmaticMigrations(m => m.AddDataMigration<BackfillOrderStatus>()).

---

Step 10: Multi-Instance Deployment

When running multiple instances of the same host (e.g., behind a load balancer), only one instance should run migrations. This is handled automatically via DatabaseLeaderElection:

1. The first instance to claim the lock in __PragmaticLock becomes the leader
2. Other instances poll every 2 seconds until the leader finishes
3. After migration completes, the leader releases the lock
4. If the leader crashes, the lock expires after the configured timeout (default: 5 minutes)

No additional configuration is needed — leader election uses the same database connection as the migration itself.

---

Complete Example

// Program.cs in Showcase.Host
await PragmaticApp.RunAsync(args, builder =>
{
    builder.UsePragmaticMigrations(m =>
    {
        m.OnlyDatabase<ShowcaseAppDatabase>();
    });

    builder.UseControlPlane(cp =>
    {
        cp.WithHubUrl("https://admin:5100/_pragmatic/control-plane");
    });
});

appsettings.json

{
  "ConnectionStrings": {
    "App": "Host=localhost;Database=showcase;Username=postgres;Password=postgres"
  }
}

---

What Happens Under the Hood

Compile Time:
  [Entity<Guid>] Reservation → SG → SchemaVersion (hash: a3f7b2c1)
                                       ├── TableSchema: "Reservations"
                                       │   ├── ColumnSchema: "Id" (uuid, PK)
                                       │   ├── ColumnSchema: "GuestName" (text)
                                       │   ├── ColumnSchema: "CheckIn" (date)
                                       │   └── ...
                                       ├── IndexSchema: "IX_Reservations_GuestName"
                                       └── ForeignKeySchema: → "Guests"

Runtime:
  MigrationRunner.MigrateAsync(context)
    → DatabaseLeaderElection.TryBecomeLeaderAsync()   // claim __PragmaticLock
    → PostgreSqlSchemaIntrospector.IntrospectAsync()   // read information_schema
    → SchemaDiffEngine.ComputeDiff(desired, current)   // produce SchemaChange[]
    → PostgreSqlMigrationGenerator.GenerateScript()    // emit idempotent SQL
    → Execute each change in a transaction              // per-change commands
    → SchemaAuditStore.RecordMigrationAsync()          // write __PragmaticSchema
    → IMigrationSeedProvider.SeedAsync()               // optional data seeding
    → DatabaseLeaderElection.ReleaseLeadershipAsync()  // release lock

---

Next Steps

• Concepts — Architecture deep dive: diff engine, SQL generation, leader election
• Common Mistakes — 8 pitfalls and how to avoid them
• Troubleshooting — FAQ and error recovery