Database Migrations Workflow

In this lesson, you will learn how to automate the evolution of your database schema alongside your codebase so your data structure always stays in sync with your application logic.

The Problem: Out-of-Sync Schemas

When you build an application, your database schema (the structure of your tables and columns) changes over time. You might add a lastLogin column or rename a user_name field. If you manually run SQL commands on your production server to update the database, you are prone to human error, and your team won't know exactly what state the database is in.

The Solution: Version-Controlled Migrations

A migration is essentially a version-controlled script that defines a change to your database schema. Instead of running raw SQL, you create a migration file—usually a timestamped JavaScript or SQL file—that contains two main instructions: up (to apply the change) and down (to revert the change).

For example, a migration to add an email column might look like this:

// migrations/202310271200_add_email_to_users.js
exports.up = async (db) => {
  await db.query('ALTER TABLE users ADD COLUMN email VARCHAR(255);');
};

exports.down = async (db) => {
  await db.query('ALTER TABLE users DROP COLUMN email;');
};

The Workflow

The workflow works by maintaining a special table in your database, often called migrations or schema_migrations. When you deploy your Node.js application, your migration tool checks this table to see which scripts have already run.

1. Create: You generate a new migration file when you need a schema change.
2. Apply: When the app starts (or during a CI/CD step), the migration tool runs all "pending" migration files in chronological order.
3. Record: Once a migration runs successfully, the tool inserts a record into the migrations table, ensuring it never runs twice.

By committing these migration files to your repository, you ensure that every developer, test environment, and production server is running the exact same schema evolution. This eliminates "it works on my machine" issues related to database structure.

Key Takeaway: Automating your database schema updates through version-controlled migration files ensures that your database structure evolves safely and predictably alongside your application code.