/DB

Applying migrations

How Socigy.OpenSource.DB tracks and applies schema migrations at runtime using the generated migration manager.

updated 6 Jun 20264 min readv0.3.2View as Markdown

How migrations are tracked

Every time a migration is generated and applied, the tool creates a tracking table named _scg_migrations in your database. This table records every migration that has been successfully applied, identified by the migration's unique Id string.

CREATE SEQUENCE IF NOT EXISTS "_scg_migrations_id_seq" AS BIGINT;
CREATE TABLE "_scg_migrations" (
    "id"          bigint    DEFAULT nextval('_scg_migrations_id_seq'),
    "human_id"    text,
    "applied_at"  timestamp without time zone,
    "is_rollback" boolean   DEFAULT false,
    "executed_by" text,
    CONSTRAINT "PK__scg_migrations" PRIMARY KEY ("id")
);

On the next run, the migration manager compares the list of generated migration classes against the rows in this table and applies only the ones that are not yet recorded.

NOTE
Each migration is applied atomically: its schema change and its _scg_migrations row are written inside a single transaction, so a failure or crash mid-migration rolls the whole step back — the database is never left changed-but-unrecorded (which would re-apply the migration) or recorded-but-not-changed.

The "current version" is resolved from the full history, honoring is_rollback: a rolled-back migration is correctly treated as not applied, so re-running after a rollback re-applies it as expected.

Applying via the generated extension method

The simplest way to apply migrations is to call the generated EnsureLatest{DbName}Migration() extension on WebApplication in Program.cs. This runs all pending migrations synchronously during startup, before the app begins accepting requests.

var app = builder.Build();

// Apply any pending migrations before serving traffic
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();

This method:

  1. Resolves IMigrationManager keyed "AuthDb" from the DI container
  2. Creates the _scg_migrations table if it does not exist
  3. Walks the migration list baked into the generated MigrationManager (every ILocalMigration in the project is collected by the source generator at build time)
  4. Applies pending migrations in order, recording each in _scg_migrations
NOTE
EnsureLatest{DbName}Migration() is generated only when generateWebAppExtensions: true in socigy.json.

Applying via IMigrationManager directly

For more control, for example in a background service, a worker process, or a test fixture, resolve IMigrationManager from the DI container directly:

// Resolve the keyed service
var migrationManager = app.Services
    .GetRequiredKeyedService<IMigrationManager>("AuthDb");

// EnsureLatestVersion() creates the DB if needed and applies all pending migrations
await migrationManager.EnsureLatestVersion();

In a hosted service

public class DatabaseStartupService(
    [FromKeyedServices("AuthDb")] IMigrationManager migrations) : IHostedService
{
    public async Task StartAsync(CancellationToken ct)
    {
        await migrations.EnsureLatestVersion();
    }

    public Task StopAsync(CancellationToken ct) => Task.CompletedTask;
}

Migration order

Migrations are ordered by the PreviousId chain recorded in each generated .g.cs file. Each migration stores the Id of the migration that preceded it, forming a linked list. The manager walks this chain from the earliest unapplied migration to the latest.

WARNING
Do not delete or modify generated .g.cs migration files after they have been applied to any environment. The Id chain drives ordering. Breaking it causes the manager to misidentify which migrations are pending.

The _scg_migrations table

Column Type Description
id BIGINT Auto-increment primary key
human_id TEXT The migration Id string (e.g. "202605011518_Added_procedures_...")
applied_at TIMESTAMP UTC timestamp when the migration was applied
is_rollback BOOLEAN true if this entry records a DOWN migration
executed_by TEXT OS user and machine name that ran the migration

You can query this table to audit which migrations have been applied and when:

SELECT human_id, applied_at, is_rollback, executed_by
FROM _scg_migrations
ORDER BY applied_at DESC;

Rollbacks

Rollback SQL is generated into each migration class as DownSql. Rollback is not applied automatically. Invoke it manually when needed:

// Access the generated migration class directly
var migration = new M_abc123();
await using var conn = factory.Create();
await conn.OpenAsync();

await using var cmd = conn.CreateCommand();
cmd.CommandText = migration.DownSql;
await cmd.ExecuteNonQueryAsync();

// Record the rollback in the tracking table
cmd.Parameters.Clear();
cmd.CommandText = """
    INSERT INTO "_scg_migrations" ("human_id", "applied_at", "is_rollback", "executed_by")
    VALUES (@humanId, NOW(), true, @executedBy)
    """;
cmd.Parameters.AddWithValue("humanId", migration.Id);
cmd.Parameters.AddWithValue("executedBy", $"{Environment.UserName} - {Environment.MachineName}");
await cmd.ExecuteNonQueryAsync();
TIP
For production rollback procedures, test the DownSql in a staging environment before applying to production. Rollbacks that DROP columns are destructive and cannot recover data.

CI / CD considerations

In CI and CD pipelines migration generation produces no interactive prompts. The migration name is derived automatically from the schema diff. Apply order does not depend on file names or timestamps — each migration records its PreviousId, and the manager applies them along that chain (ids are only minute-granularity, so name/timestamp sorting would be ambiguous). A broken or forked chain fails loudly rather than guessing an order.

Run the migration step as part of your deployment pipeline before the application starts:

dotnet build MyApp.DB/MyApp.DB.csproj -c DB_Migration

The generated .g.cs files are committed to source control and compiled into the assembly. The source generator collects every ILocalMigration at build time and bakes the ordered list into the generated MigrationManager, which the runtime then applies.