/DB

Custom migrations

Implement ILocalMigration to run arbitrary C# code alongside generated DDL migrations.

updated 3 May 20263 min readv0.1.82

When to use custom migrations

The generated DDL migration covers structural changes — CREATE TABLE, ALTER TABLE, ADD COLUMN, and so on. Custom migrations let you execute code that goes beyond DDL, such as:

  • Seeding reference data after a new table is created (put the INSERT in UpSql)
  • Backfilling a new column with computed values (put the UPDATE in UpSql)
  • Running any SQL that the DDL generator cannot express automatically

ILocalMigration interface

public interface ILocalMigration
{
    string Id { get; }
    string? PreviousId { get; }
    string UpSql { get; }
    string DownSql { get; }
}
Member Description
Id Unique string identifier for this migration (must be globally unique)
PreviousId Id of the migration that directly precedes this one in the chain — null for the first migration
UpSql DDL/SQL to apply on upgrade — may be "" if only data changes are needed
DownSql DDL/SQL to apply on rollback — must always be provided (use "" if irreversible)

Example: seed data after creating a table

public class SeedRolesMigration : ILocalMigration
{
    public string Id => "seed-roles-2026-05-01";
    public string? PreviousId => "202605011200_Initial_Migration_abc123";

    // Roles table already exists from the generated migration — only seed data here
    public string UpSql => """
        INSERT INTO "roles" ("id", "value", "description") VALUES
            (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
        ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value";
        """;

    public string DownSql => "DELETE FROM \"roles\" WHERE \"id\" IN (1, 2, 4, 8);";
}

Example: backfill a new column

public class BackfillDisplayNameMigration : ILocalMigration
{
    public string Id => "backfill-display-name-2026-05-15";
    public string? PreviousId => "seed-roles-2026-05-01";

    // The ADD COLUMN is done in a generated migration; this only backfills the data
    public string UpSql => "UPDATE \"users\" SET \"display_name\" = \"username\" WHERE \"display_name\" IS NULL;";

    public string DownSql => "UPDATE \"users\" SET \"display_name\" = NULL;";
}

Ordering custom migrations

The migration manager uses the PreviousId chain to determine execution order. Set PreviousId to the Id of the last generated migration (found in Socigy/structure.json as the top-level "id" field) or the Id of the custom migration that should run immediately before it.

WARNING
If two migrations — whether generated or custom — have the same Id, only one will be applied and the other will be silently skipped. Use descriptive, globally unique Id values that include a date or timestamp.

Discovery

The migration manager discovers ILocalMigration implementations by scanning the loaded assembly for concrete classes that implement the interface. You do not need to register them anywhere — just implement the interface in your DB project and the manager finds them automatically at runtime.

Combining with generated migrations

Generated migrations (.g.cs files) also implement ILocalMigration. The manager sees all of them together and applies them in PreviousId order regardless of whether they are hand-written or generated.

TIP
To anchor a custom migration to the very latest generated migration, look up the Id of the most recent .g.cs file in Socigy/Migrations/ and use it as your PreviousId. Each generated file exposes its Id as a public const string Id at the top of the class.