/DB

Applying migrations

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

updated 3 May 20263 min readv0.1.82

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.

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. Discovers all ILocalMigration implementations in the assembly
  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 is used for ordering — breaking it will cause 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 — you must 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 auto-generated using MigrationNamer.GenerateCanonicalString(). Configure migrationNameTemplate in socigy.json to include ${Timestamp} for chronologically sortable names:

{ "database": { "migrationNameTemplate": "${Timestamp}_${Name}" } }

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 included in the deployed assembly, which the migration manager discovers at runtime via reflection.