Data Integrity in Migrations

In this lesson, you will learn how to implement automated verification steps within your migration workflow to ensure data remains consistent after a schema change.

When you run a database migration—such as splitting a name column into first_name and last_name—there is always a risk that data is lost or corrupted during the transformation. Relying on manual inspection is error-prone. Instead, you should treat your migrations as code that must be verified programmatically before the deployment is considered successful.

The Verification Pattern

An automated verification step typically involves a "Check-Transform-Verify" pattern. You perform the schema change, execute the data migration, and then run a script that asserts the integrity of the new records against the original source of truth.

If you are using a tool like knex or sequelize, you can include a verification block directly in your migration file:

exports.up = async (knex) => {
  // 1. Schema update
  await knex.schema.table('users', (table) => {
    table.string('first_name');
  });

  // 2. Data transformation
  await knex.raw("UPDATE users SET first_name = split_part(name, ' ', 1)");

  // 3. Automated Verification
  const mismatchCount = await knex('users')
    .whereNull('first_name')
    .andWhereNotNull('name')
    .count('* as total');

  if (parseInt(mismatchCount[0].total) > 0) {
    throw new Error('Data integrity violation: Null values detected after migration!');
  }
};

Why Automated Verification Matters

By throwing an error inside the migration, you prevent the database from finishing the transaction if the migration logic fails. Most modern migration tools will automatically rollback if an error occurs, effectively protecting your production data from partial updates.

When designing these checks, focus on "invariants"—rules that must hold true regardless of the state. Common examples include verifying that the total record count remains unchanged, that non-nullable constraints are satisfied, or that sample rows contain expected values. By embedding these checks directly into your deployment pipeline, you move from "hoping the migration worked" to "knowing the data is valid."

Key Takeaway: Always pair schema transformations with automated assertions that confirm data consistency within the migration transaction itself, ensuring that any failure triggers an immediate rollback.