Handling Schema Evolution without Downtime

In this lesson, you will learn how to evolve your database schema while your Node.js application remains fully operational for your users.

When your application grows, you often need to change your database structure—like renaming a column or splitting a table. If you simply run a DROP or ALTER command that locks the table, your Node.js application will stop responding to user requests, causing downtime. To avoid this, we use the Expand and Contract pattern.

The Expand and Contract Pattern

Instead of making destructive changes in one go, we break the migration into three non-blocking steps:

1. Expand: Add the new column (or table) while keeping the old one. Update your Node.js code to write to both the old and new columns simultaneously. Use a background process or a lazy-migration script to copy existing data from the old column to the new one.
2. Migrate/Sync: Your application reads from the old column but writes to both. Once all records are synced, your application can safely switch to reading from the new column.
3. Contract: Once the old column is no longer referenced by the application code, you can safely drop it.

Practical Example: Renaming a Column

Imagine renaming user_name to full_name. A single ALTER TABLE might lock the table for seconds or minutes. Instead:

• Step 1: Add the full_name column.
• Step 2: Update your Node.js model to write to both columns:

async function updateUser(id, data) {
  // Write to both, maintaining backward compatibility
  await db.query('UPDATE users SET user_name = $1, full_name = $1 WHERE id = $2', [data.name, id]);
}

• Step 3: Deploy the code change. Once the data is synced and the app reads only from full_name, remove the code references to user_name and drop the old column in a final, minor migration.

By splitting these changes into incremental steps, you ensure that your database never enters a state where the application code is incompatible with the schema, allowing for a seamless deployment.

Key Takeaway: Never perform destructive schema changes in a single step; use the Expand and Contract pattern to ensure your database remains available while you evolve it.