Database Migration

Database Migration

Mastering the process of database migration is essential for modern application development, especially as projects evolve and data models change. The Database Migration skill enables you to execute migrations across Object-Relational Mappers (ORMs) such as Sequelize, TypeORM, and Prisma, covering schema changes, data transformations, rollback procedures, and zero-downtime deployment techniques. This article provides a comprehensive overview of the skill, practical code examples, and key considerations for reliable and maintainable database migrations.

What Is Database Migration?

Database migration refers to the controlled process of evolving a database schema and its data from one state to another. This could involve creating new tables, altering columns, transforming data, or even moving data between different database platforms. With growing application complexity and evolving business requirements, database migrations ensure that the underlying data structure remains consistent with application logic without manual intervention.

Modern projects often use ORMs like Sequelize (Node.js), TypeORM (TypeScript), and Prisma to abstract database operations. Each ORM provides tools for writing, applying, and rolling back migrations, making it easier for developers to manage changes over time.

Why Use Database Migration?

Implementing database migrations delivers several critical benefits:

• Consistency: Migrations enforce a systematic approach to database changes, reducing the risk of human error.
• Collaboration: Teams can track, review, and apply schema changes collaboratively thanks to migration scripts stored in version control.
• Rollback Capability: If a migration introduces issues, rollback procedures allow you to revert changes safely.
• Zero-downtime Deployments: Well-structured migrations enable you to modify databases while applications remain online, minimizing service interruptions.
• Data Transformation: Migrations can also handle data-level changes, such as migrating field formats or populating new columns with computed values.

These benefits are especially important during continuous integration and delivery, database upgrades, and when transitioning between different data platforms or ORMs.

How to Use Database Migration

The Database Migration skill leverages migration systems provided by major ORMs. Here are practical examples for Sequelize, TypeORM, and Prisma.

Sequelize Migrations

Sequelize uses migration scripts written in JavaScript. Each migration defines up (apply) and down (rollback) methods.

// migrations/20231201-create-users.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      email: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    });
  },

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

// Apply migration: npx sequelize-cli db:migrate
// Rollback: npx sequelize-cli db:migrate:undo

TypeORM Migrations

TypeORM uses migration classes written in TypeScript, with up and down methods.

// migrations/1701234567-CreateUsers.ts
import { MigrationInterface, QueryRunner, Table } from "typeorm";

export class CreateUsers1701234567 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: "users",
        columns: [
          {
            name: "id",
            type: "int",
            isPrimary: true,
            isGenerated: true,
            generationStrategy: "increment",
          },
          {
            name: "email",
            type: "varchar",
            isUnique: true,
            isNullable: false,
          },
          {
            name: "createdAt",
            type: "timestamp",
          },
          {
            name: "updatedAt",
            type: "timestamp",
          },
        ],
      })
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable("users");
  }
}

// Apply migration: npx typeorm migration:run
// Rollback: npx typeorm migration:revert

Prisma Migrations

Prisma uses declarative schema files and migration commands.

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// Apply migration: npx prisma migrate dev --name create_users
// Rollback (development only): npx prisma migrate reset

When to Use This Skill

The Database Migration skill is applicable in several scenarios:

• Migrating between ORMs: When switching from one ORM to another, such as from Sequelize to Prisma.
• Schema transformations: Adding, removing, or modifying tables and columns.
• Data migrations: Moving or transforming data between tables or databases.
• Implementing rollback procedures: Ensuring every migration has a corresponding rollback script.
• Zero-downtime deployments: Updating schemas while keeping your application online.
• Database version upgrades: Applying schema changes required by new database engine versions.
• Data model refactoring: Changing the structure of models to improve performance or maintainability.

Important Notes

• Version Control: Always store migration scripts in your project's version control system. This practice ensures team members apply changes consistently.
• Rollback Strategies: Each migration must include a reliable rollback method to reverse changes in case of failure.
• Zero-downtime Best Practices: For online systems, avoid destructive changes (such as dropping columns immediately). Use phased rollouts: add new columns, backfill data, switch application logic, then remove old columns.
• Testing: Test migrations in a staging environment before running them in production to detect issues early.
• Data Integrity: When transforming data, ensure that no records are lost or corrupted. Use transactions where supported to provide atomicity.
• ORM-Specific Features: Each ORM provides unique features and limitations. Consult your ORM's documentation for advanced scenarios like seed data, partial rollbacks, or custom migration ordering.

By mastering the Database Migration skill, you can confidently manage database changes across various platforms and ORMs, ensuring robust, maintainable, and production-ready schema evolution.