Node.js Database Migrations with Sequelize and Prisma

Managing database schema changes is a critical part of backend development. This article explains how to effectively handle database migrations in a Node.js application using two of the most popular Object-Relational Mapping (ORM) tools: Sequelize and Prisma. You will learn the core concepts of migrations, how to set them up, and the step-by-step process of running and rolling back schema updates for both tools.

Understanding Database Migrations

Database migrations act as version control for your database schema. Instead of manually executing SQL commands to create, alter, or drop tables, migrations allow you to write schema changes in code files. This ensures that every developer on your team, as well as your production environment, runs the exact same database structure.

Migrations in Sequelize

Sequelize uses a programmatic approach to migrations. You write migration tasks in JavaScript files using the Sequelize Command Line Interface (CLI).

1. Setup and Initialization

First, install the Sequelize CLI and initialization packages in your Node.js project:

npm install sequelize-cli pg pg-hstore # (Using PostgreSQL as an example)
npx sequelize-cli init

This command generates several folders, including migrations, models, and seeders.

2. Creating a Migration

To generate a new migration file, run:

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

This creates a skeleton file in the migrations directory with up and down functions.

3. Writing the Migration

The up function describes the changes to apply, while the down function describes how to revert them.

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

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

4. Running and Reverting Migrations

To apply the migration and update your database schema:

npx sequelize-cli db:migrate

To roll back the most recent migration:

npx sequelize-cli db:migrate:undo

Migrations in Prisma

Prisma uses a declarative approach to migrations. Instead of writing separate migration files manually, you define your desired database state in a single schema.prisma file, and Prisma automatically generates the SQL migrations for you.

1. Setup and Initialization

Install the Prisma CLI and initialize your project:

npm install prisma --save-dev
npx prisma init

This creates a prisma folder containing a schema.prisma file.

2. Defining the Model

Open the schema.prisma file and define your database connection and data models:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  createdAt DateTime @default(now())
}

3. Creating and Applying Migrations

To generate and run the migration based on your schema definition, execute:

npx prisma migrate dev --name init-users

Prisma will: 1. Compare your schema.prisma file with your current database state. 2. Generate a highly optimized .sql migration file in a new prisma/migrations directory. 3. Execute the SQL against your database. 4. Generate the Prisma Client to match the new schema.

4. Deploying to Production

For production environments, you should not use the migrate dev command, as it is designed for local development. Instead, run the following command to apply pending migrations safely:

npx prisma migrate deploy

Sequelize vs. Prisma Migrations: Key Differences