Database Migrations — Safe Schema Changes in Production

Database migrations in production are the highest-risk operation in a web application — a migration that locks a table can take the site down, and a bad migration that drops a column cannot be undone (the data is gone). The safe approach: always write backward-compatible migrations (the old API version still works while the migration is running), run migrations before deploying the new API version, and always have a tested rollback plan. The three-phase migration pattern handles zero-downtime column changes.

Safe Production Migrations #

// ── The three-phase migration pattern for zero-downtime column changes ─────

// SCENARIO: Rename Listing.ContactPhone to Listing.PhoneNumber

// ── PHASE 1: Add new column (backward compatible — old API ignores new column) ──
// New migration: AddPhoneNumberToListings
public partial class AddPhoneNumberToListings : Migration
{
    protected override void Up(MigrationBuilder b)
    {
        b.AddColumn<string>("PhoneNumber", "Listings", nullable: true, maxLength: 20);

        // Copy existing data
        b.Sql("UPDATE Listings SET PhoneNumber = ContactPhone WHERE ContactPhone IS NOT NULL");
    }

    protected override void Down(MigrationBuilder b)
        => b.DropColumn("PhoneNumber", "Listings");
}
// DEPLOY PHASE 1: Run migration → deploy old API (reads ContactPhone) + writes to both
// Note: new API version writes to BOTH columns during transition

// ── PHASE 2: Deploy new API that reads PhoneNumber, writes to both ─────────
// Listing entity now uses PhoneNumber; EF Core still maps ContactPhone as shadow property
// Old API instances still run reading ContactPhone — still works, data is there

// ── PHASE 3: Drop old column (after all instances run new code) ────────────
// New migration: RemoveContactPhoneFromListings
public partial class RemoveContactPhoneFromListings : Migration
{
    protected override void Up(MigrationBuilder b)
        => b.DropColumn("ContactPhone", "Listings");

    protected override void Down(MigrationBuilder b)
    {
        b.AddColumn<string>("ContactPhone", "Listings", nullable: true, maxLength: 20);
        b.Sql("UPDATE Listings SET ContactPhone = PhoneNumber");
    }
}

// ── Production migration execution ────────────────────────────────────────
// In the GitHub Actions deploy workflow:
// - name: Apply EF Core migrations
//   run: |
//     dotnet tool install -g dotnet-ef
//     dotnet ef database update \
//       --project ClassifiedApp.Infrastructure \
//       --startup-project ClassifiedApp.Api \
//       --connection "${{ secrets.PROD_DB_CONNECTION }}"

// ── Emergency rollback ────────────────────────────────────────────────────
// If Phase 1 migration causes issues, roll back to previous migration:
// dotnet ef database update AddPreviousMigrationName \
//   --connection "${{ secrets.PROD_DB_CONNECTION }}"
// Note: Only works if the Down() method is implemented correctly
// For data-destructive migrations (DROP COLUMN), rollback is impossible — prevention is key

// ── Seed data as migrations ────────────────────────────────────────────────
public partial class SeedInitialCategories : Migration
{
    protected override void Up(MigrationBuilder b)
    {
        b.InsertData("Categories", ["Id", "Name", "Slug"], [
            [1, "Electronics",    "electronics"],
            [2, "Vehicles",       "vehicles"],
            [3, "Sports & Leisure", "sports-leisure"],
            [4, "Home & Garden",  "home-garden"],
            [5, "Musical Instruments", "musical-instruments"],
        ]);
    }

    protected override void Down(MigrationBuilder b)
        => b.DeleteData("Categories", "Id", [1, 2, 3, 4, 5]);
}