Testing Database Migrations Against Production-Like Data

A database migration can work perfectly on a developer's machine and still fail when it reaches production. A migration that adds a column, changes a data type, creates an index, or transforms existing records may behave differently when the database contains millions of rows, unexpected values, or years of accumulated data.

For example, adding a NOT NULL column to a table is straightforward when the table is empty. On a populated table, the database needs a valid value for existing rows, and the migration may fail if no default or backfill strategy is provided. Similarly, creating an index on a large table can take significant time and affect database availability, depending on the database engine and the operation used.

Testing migrations against production-like data helps developers identify these problems before deployment. The objective is not to copy production data blindly into a test environment. It is to reproduce the database structure, realistic data characteristics, and operational conditions that can expose migration risks.

This article explains how to test database migrations using Entity Framework Core, representative datasets, automated integration tests, and deployment checks.

Why Testing Against Production-Like Data Matters

A migration changes the structure of a database or transforms its existing data. Testing only against an empty database verifies that the migration can run under ideal conditions, but it does not prove that it will work safely with existing records.

Consider a customer table with millions of rows. A migration introduces a required DisplayName column. If the application expects every customer to have a display name immediately after deployment, existing records must be handled correctly.

Several issues can appear when realistic data is involved:

Existing records may contain NULL, duplicate values, or strings that violate new constraints.
Type conversions may fail because older records contain values outside the expected range.
Unique indexes may fail because historical data contains duplicates.
Large updates may generate substantial transaction logs or hold locks for too long.
Index creation may take longer than the deployment window allows.
Foreign key constraints may expose orphaned records or inconsistent relationships.

A successful migration against a small, clean database cannot reveal all these risks.

Step 1: Identify What the Migration Changes

Before writing tests, review the migration and identify its effects on both the schema and existing data.

For an Entity Framework Core application, a migration might look like this:

public partial class AddCustomerDisplayName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "DisplayName",
            table: "Customers",
            type: "nvarchar(150)",
            maxLength: 150,
            nullable: false,
            defaultValue: "");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "DisplayName",
            table: "Customers");
    }
}

This migration adds a required column and supplies an empty string for existing rows through the migration's default-value operation.

Although the migration may execute successfully, the application could still have a data-quality problem: every existing customer initially receives an empty display name. The database constraint is satisfied, but the resulting data may not meet business requirements.

A migration review should therefore answer three questions:

Will the operation succeed with the existing data?
Will the resulting data satisfy database and business rules?
Can the operation complete within acceptable deployment and availability limits?

Do not treat a successful migration command as proof that all three conditions are satisfied.

Step 2: Build a Representative Dataset

Production-like data should reflect the characteristics that influence the migration. Exact production records are not always necessary, and sensitive production data should not be copied into test environments without appropriate authorization and safeguards.

Instead, construct a dataset that reproduces relevant conditions.

For example, when testing a new unique constraint on customer email addresses, include records with:

Valid and invalid email values.
Missing email addresses, where the existing schema permits them.
Duplicate values that differ only by letter case, if collation rules make that relevant.
Leading or trailing whitespace.
A realistic distribution of populated and optional fields.

For performance-sensitive changes, row count and data distribution also matter. A migration that updates a few hundred records may behave differently when it processes several million.

A useful test dataset should reflect the risks of the specific migration rather than simply contain a large number of random rows.

Generate Test Data Instead of Copying Sensitive Records

You can use deterministic test data to reproduce edge cases. For example, a test fixture can generate customers with repeated email addresses:

public sealed record TestCustomer(
    int Id,
    string? Email,
    string Name);

public static class CustomerTestData
{
    public static List<TestCustomer> CreateCustomers()
    {
        return
        [
            new(1, "[email protected]", "Alex"),
            new(2, "[email protected]", "Sam"),
            new(3, "[email protected]", "Alex Duplicate"),
            new(4, null, "Customer Without Email")
        ];
    }
}

This example illustrates a dataset, not a complete database migration test. The values should be inserted into a schema that permits the specified records before applying the migration under test.

Using a reserved example domain helps avoid accidentally using real customer email addresses. Deterministic fixtures also make failures easier to reproduce because the same inputs can be loaded repeatedly.

Step 3: Test the Migration Against the Real Database Engine

Entity Framework Core supports multiple database providers, but their behavior is not identical. SQL Server, PostgreSQL, and SQLite differ in data types, constraints, indexing capabilities, locking behavior, and supported migration operations.

For this reason, an in-memory test double or a different database provider is not sufficient to validate database-specific migration behavior.

If the production application uses SQL Server, run migration integration tests against SQL Server. If it uses PostgreSQL, use PostgreSQL for those tests. A disposable database or isolated test database is often a practical option.

The following example illustrates a basic integration test using an EF Core context configured for the target provider:

[Fact]
public async Task Migration_AddsDisplayNameColumn()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(
            TestConfiguration.ConnectionString)
        .Options;

    await using var db = new AppDbContext(options);

    await db.Database.MigrateAsync();

    var customers = await db.Customers
        .AsNoTracking()
        .ToListAsync();

    Assert.All(
        customers,
        customer => Assert.NotNull(customer.DisplayName));
}

This test assumes that TestConfiguration.ConnectionString points to a disposable test database and that the migration history and test setup are configured correctly.

It demonstrates applying migrations and checking the resulting data, but it is not a complete test of an upgrade against existing records. If the database starts empty, the test does not prove that historical data will be migrated correctly.

For a meaningful upgrade test, initialize the database at the previous schema version, insert representative existing records, apply the new migration, and verify both the schema and transformed data.

Step 4: Test an Upgrade from the Previous Schema

A migration test should reproduce the state that exists immediately before the migration runs.

For an EF Core project, one practical approach is to create a disposable database, migrate it to a known earlier migration, seed test records, and then apply the migration being tested.

Conceptually, the process is:

await db.Database.MigrateAsync(
    "PreviousMigrationName");

// Insert records using the previous schema.
// Save the records before the new migration runs.

await db.Database.MigrateAsync(
    "AddCustomerDisplayName");

// Query the upgraded database.
// Assert that the new schema and data are correct.

The example shows the sequence rather than a complete test implementation. In a real test, the context model, migration history, and seeding code must match the application's migration setup.

If the application has no earlier migration, use the appropriate initial migration instead. When testing a specific upgrade, avoid automatically applying every migration before seeding the old data, because doing so would bypass the migration under test.

Verify more than the presence of the new column. Check that existing records received the expected values, required constraints are enforced, and relationships remain valid.

Step 5: Validate Data Transformations and Constraints

Many migration failures come from assumptions about existing data.

Suppose an application changes a nullable Email column into a required column. Before adding the constraint, the migration must address existing rows that contain NULL.

A pre-migration query can identify those records:

SELECT COUNT(*) AS MissingEmailCount
FROM Customers
WHERE Email IS NULL;

If the migration adds a unique constraint, check for duplicate values before applying it:

SELECT Email, COUNT(*) AS DuplicateCount
FROM Customers
WHERE Email IS NOT NULL
GROUP BY Email
HAVING COUNT(*) > 1;

These queries are useful starting points, but they must reflect the database's actual comparison rules. For example, a case-insensitive collation can treat [email protected] and [email protected] as equal for uniqueness purposes.

For each data transformation, define explicit expectations:

How many records should be updated?
What should happen to missing or invalid values?
Which relationships must remain valid?
What should happen if a record cannot be transformed?
Can the migration be safely retried if deployment is interrupted?

Test both valid and invalid data. If the migration is expected to reject invalid records, verify that it fails safely and leaves the database in a known state.

Step 6: Measure Migration Performance

A migration that succeeds against realistic data can still be unsuitable for production if it takes too long or blocks important operations.

Performance testing should use a dataset large enough to exercise the relevant operation. Measure execution time, database resource consumption, transaction log growth where applicable, and the impact on concurrent reads and writes.

For example, adding an index to a large table may require substantial I/O and can affect concurrent operations. The exact impact depends on the database engine, index operation, table structure, and deployment configuration.

Record the conditions under which the migration was tested:

Test condition	Why it matters
Approximate row count	Helps estimate the scale of the operation
Data distribution	Reveals skew and unusual values
Database engine and version	Determines supported operations and behavior
Concurrent workload	Reveals blocking and contention risks
Migration duration	Helps assess deployment-window requirements
Resource and log usage	Identifies capacity and operational concerns

Do not treat the duration measured on a developer laptop as a reliable production estimate. Use an environment with comparable database configuration and resource limits, and account for differences that cannot be reproduced exactly.

For operations that could block writes, consider whether the database supports an online or concurrent alternative. Such options have engine-specific limitations and should be tested using the same approach planned for deployment.

Step 7: Test Deployment and Recovery Scenarios

Migration testing should also cover how the deployment process behaves when something goes wrong.

Important scenarios include:

A migration fails because existing data violates a new constraint.
The deployment is interrupted during a long-running operation.
An application instance continues running while the database schema changes.
A new application version starts before all old application instances have stopped.
A rollback is requested after the migration has already changed data.

The appropriate recovery strategy depends on the operation and database engine. Some schema changes can be rolled back cleanly; destructive data transformations may not be reversible without a backup or separately preserved data.

For applications deployed without downtime, consider an expand-and-contract approach. First introduce a backward-compatible schema change, then deploy application code that can work with it, migrate or backfill data, and remove obsolete schema elements only after the old code no longer depends on them.

This approach can reduce deployment risk, but it requires planning across multiple releases rather than treating a migration as an isolated database operation.

Common Mistakes to Avoid

Testing Only on an Empty Database

An empty database does not expose duplicate values, null records, historical data issues, or large-table performance problems. Always test the relevant upgrade path with representative existing data.

Using a Different Database Provider

A migration that works with SQLite may behave differently on SQL Server or PostgreSQL. Use the production database engine for migration-specific integration tests.

Checking Only Whether the Migration Succeeded

A migration can complete while producing incorrect business data. Validate transformed values, constraints, relationships, and important application queries after the upgrade.

Ignoring Application Compatibility

A schema change can break application instances that are still running an older version. Test the deployment sequence, not just the SQL operation.

Treating Rollback as Guaranteed Recovery

A migration's Down method does not automatically make every change reversible. Dropped columns, overwritten values, and destructive transformations can permanently remove information unless it has been preserved.

A Practical Migration Testing Checklist

Before deploying a database migration, verify the following:

The migration has been reviewed for schema and data effects.
Representative existing records have been prepared.
Edge cases, duplicates, and invalid values have been tested.
The migration has been applied using the production database provider.
Schema changes and transformed data have been verified.
Performance has been measured against an appropriate dataset.
Locking, transaction behavior, and deployment duration have been considered.
Application compatibility during rollout has been evaluated.
Recovery procedures and backups have been reviewed where necessary.

Summary

Testing database migrations against production-like data helps uncover failures that empty-database tests cannot reveal. Existing records, database-specific behavior, data constraints, and table size can all change the outcome of a migration.

For EF Core applications, create integration tests that start from the previous schema, insert representative records, apply the migration, and verify the resulting schema and data. Run these tests against the same database engine used in production, and measure performance when the change affects large tables or critical workloads.

Finally, treat deployment compatibility and recovery as part of migration testing. A migration is ready for production only when it has been validated against realistic data, its operational risks are understood, and the deployment process has a credible recovery plan.