Skip to main content

Express Data Migration

Introduction​

Database migration is a critical aspect of modern web application development. It refers to the process of managing changes to your database schema over time, allowing your application to evolve while maintaining data integrity. In Express.js applications, data migration helps track, version, apply, and revert changes to your database structures methodically.

Think of migrations as version control for your database, similar to how Git works for your code. Each migration represents a specific change to your database schema (like creating a table, adding a column, or modifying data) that can be applied or rolled back as needed.

In this guide, we'll explore:

  • Why database migrations are essential
  • Common migration tools for Express.js applications
  • How to implement migrations for different database systems
  • Best practices for maintaining migrations in production applications

Why Use Database Migrations?​

Before diving into implementation details, let's understand the benefits of using database migrations:

  1. Version Control: Track all changes made to your database schema over time
  2. Collaboration: Enable multiple developers to work on the same database without conflicts
  3. Consistency: Ensure all environments (development, testing, production) have the same database structure
  4. Rollbacks: Easily revert database changes if something goes wrong
  5. Documentation: Self-document database schema evolution through migration files

Common Migration Tools for Express.js​

Several tools are available for implementing migrations in Express applications. We'll explore the most popular ones:

1. Sequelize (SQL databases)​

2. Knex.js (SQL databases)​

3. Mongoose/MongoDB migrations​

Let's examine each approach in detail.

Implementing Migrations with Sequelize​

Sequelize is a popular ORM for Node.js that supports PostgreSQL, MySQL, SQLite, and more. It includes a powerful migration system.

Setting Up Sequelize Migrations​

First, install the necessary packages:

bash
npm install sequelize sequelize-cli pg pg-hstore
# Or for MySQL
# npm install sequelize sequelize-cli mysql2

Initialize Sequelize in your project:

bash
npx sequelize-cli init

This creates the following folder structure:

├── config
│   └── config.json
├── models
│   └── index.js
├── migrations
└── seeders

Creating Your First Migration​

To create a migration file:

bash
npx sequelize-cli migration:generate --name create-users-table

This generates a timestamped file in your migrations folder:

javascript
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING,
        unique: true
      },
      password: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

Running Migrations​

To apply migrations:

bash
npx sequelize-cli db:migrate

To undo the most recent migration:

bash
npx sequelize-cli db:migrate:undo

To undo all migrations:

bash
npx sequelize-cli db:migrate:undo:all

Example: Adding a Column in a New Migration​

Let's create another migration to add a new column to our Users table:

bash
npx sequelize-cli migration:generate --name add-phone-to-users

Edit the generated file:

javascript
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'phone', {
      type: Sequelize.STRING,
      allowNull: true
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'phone');
  }
};

Implementing Migrations with Knex.js​

Knex.js is a flexible SQL query builder that also provides migration capabilities.

Setting Up Knex Migrations​

Install the necessary packages:

bash
npm install knex pg
# Or for MySQL
# npm install knex mysql

Initialize Knex with a configuration file:

bash
npx knex init

This creates a knexfile.js in your project root:

javascript
// knexfile.js
module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: 'localhost',
      user: 'postgres',
      password: 'password',
      database: 'my_db'
    },
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds'
    }
  },
  
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './db/migrations'
    },
    seeds: {
      directory: './db/seeds'
    }
  }
};

Creating a Migration​

Create your first migration:

bash
npx knex migrate:make create_users_table

This generates a timestamped file in your migrations directory:

javascript
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('first_name');
    table.string('last_name');
    table.string('email').unique();
    table.string('password');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

Running Knex Migrations​

To run migrations:

bash
npx knex migrate:latest

To rollback the last batch:

bash
npx knex migrate:rollback

To rollback all migrations:

bash
npx knex migrate:rollback --all

Let's create a posts table that references our users:

bash
npx knex migrate:make create_posts_table

Edit the generated file:

javascript
exports.up = function(knex) {
  return knex.schema.createTable('posts', table => {
    table.increments('id').primary();
    table.string('title').notNullable();
    table.text('content');
    table.integer('user_id').unsigned().notNullable();
    table.foreign('user_id').references('users.id').onDelete('CASCADE');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('posts');
};

MongoDB Migrations​

MongoDB is schema-less, but migrations are still useful for data transformations, creating indexes, or adding default values.

Using migrate-mongo​

Install the package:

bash
npm install -g migrate-mongo

Initialize in your project:

bash
migrate-mongo init

This creates a migrate-mongo-config.js file:

javascript
// migrate-mongo-config.js
const config = {
  mongodb: {
    url: "mongodb://localhost:27017",
    databaseName: "my_database",
    options: {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    }
  },
  migrationsDir: "migrations",
  changelogCollectionName: "changelog",
};

module.exports = config;

Creating a Migration​

Create your first migration:

bash
migrate-mongo create add-user-roles

Edit the generated file:

javascript
module.exports = {
  async up(db) {
    // Add a role field to all existing users
    await db.collection('users').updateMany(
      {}, 
      { $set: { role: 'user' } }
    );
    
    // Create an index on the role field
    await db.collection('users').createIndex({ role: 1 });
  },

  async down(db) {
    // Remove the role field from all users
    await db.collection('users').updateMany(
      {}, 
      { $unset: { role: '' } }
    );
    
    // Drop the index
    await db.collection('users').dropIndex({ role: 1 });
  }
};

Running MongoDB Migrations​

To run all pending migrations:

bash
migrate-mongo up

To roll back the last migration:

bash
migrate-mongo down

To see the status of migrations:

bash
migrate-mongo status

Integrating Migrations with Express​

Now let's see how to integrate migrations into an Express application workflow:

Example: Express App with Sequelize Migrations​

javascript
// app.js
const express = require('express');
const { Sequelize } = require('sequelize');
const app = express();
const port = 3000;

// Database configuration
const sequelize = new Sequelize({
  dialect: 'postgres',
  host: 'localhost',
  username: 'postgres',
  password: 'password',
  database: 'my_database',
  logging: false
});

// Define models based on your migrated schema
const User = require('./models/user')(sequelize);

// Middleware
app.use(express.json());

// Routes
app.get('/users', async (req, res) => {
  try {
    const users = await User.findAll();
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.post('/users', async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Start server
app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

Best Practices for Database Migrations​

  1. Keep Migrations Small: Each migration should perform a single logical change
  2. Make Migrations Reversible: Always implement both up and down functions
  3. Test Before Deploying: Run migrations in a test environment before production
  4. Version Control: Keep migration files in your repository
  5. CI/CD Integration: Automate migrations as part of your deployment process
  6. Data Backups: Always back up data before running migrations in production

Real-World Application: E-commerce Platform Migration​

Let's consider a real-world scenario where we need to modify an e-commerce database schema to add product variants.

Initial Schema​

We have the following tables:

  • Users
  • Products
  • Orders

Migration Plan​

  1. Create a product_variants table
  2. Add variant reference to products
  3. Update existing data

Here's how we would implement this with Sequelize:

bash
npx sequelize-cli migration:generate --name add-product-variants
javascript
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // 1. Create variants table
    await queryInterface.createTable('ProductVariants', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      sku: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      price: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false
      },
      stock: {
        type: Sequelize.INTEGER,
        defaultValue: 0
      },
      productId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'Products',
          key: 'id'
        },
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
    
    // 2. Add hasVariants column to products
    await queryInterface.addColumn('Products', 'hasVariants', {
      type: Sequelize.BOOLEAN,
      defaultValue: false
    });
    
    // 3. Create an index for faster lookups
    await queryInterface.addIndex('ProductVariants', ['productId']);
  },

  down: async (queryInterface, Sequelize) => {
    // Revert all changes in reverse order
    await queryInterface.removeIndex('ProductVariants', ['productId']);
    await queryInterface.removeColumn('Products', 'hasVariants');
    await queryInterface.dropTable('ProductVariants');
  }
};

Handling Production Migrations Safely​

Migrating a production database requires extra caution:

Safe Migration Steps​

  1. Schedule Maintenance Window: For significant schema changes
  2. Create Backups: Before running any migration
  3. Implement in Stages: Break complex migrations into smaller steps
  4. Have a Rollback Plan: Test the down migrations thoroughly
  5. Monitoring: Watch for errors or performance issues after migration

Zero-Downtime Migrations​

For high-availability applications:

  1. Use Database Features: Like PostgreSQL's transactional DDL
  2. Additive Changes First: Add columns and tables before modifying existing ones
  3. Code Compatibility: Ensure your application works with both old and new schemas during transition
  4. Background Data Migration: Migrate data in small batches if large tables are involved

Summary​

Database migrations are a crucial part of maintaining and evolving your Express.js applications. They provide a systematic, version-controlled approach to manage database schema changes throughout your project lifecycle.

In this guide, we've covered:

  • The importance of database migrations
  • How to implement migrations with Sequelize, Knex.js, and MongoDB
  • Best practices for creating and running migrations
  • Safely handling migrations in production environments
  • A real-world application example

By incorporating database migrations into your Express.js applications, you can ensure that your database schema evolves alongside your codebase while maintaining data integrity and enabling team collaboration.

Additional Resources​

Exercises​

  1. Create a migration to add a "role" column to a users table with a default value of "user"
  2. Write a migration that creates a many-to-many relationship between two existing tables
  3. Create a migration that transforms data (e.g., splitting a "name" field into "firstName" and "lastName")
  4. Design a migration plan for converting a simple blog database to support multi-language content
  5. Implement a migration to add necessary fields for supporting OAuth login providers to a users table

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)