🛠️ Using Migration Tools

The Tool Families

Every migration tool solves the same problem — an ordered, tracked set of schema changes — but they split into two philosophies. Imperative tools have you write the steps ("create this table, add that column"). Declarative tools have you describe the desired end state and generate the steps for you.

Setting Up Knex

Knex is a SQL query builder for Node that ships with a full migration system. It supports PostgreSQL, MySQL, SQLite, and MSSQL. Setup is three commands.

# 1. Install Knex and a database driver (pg for PostgreSQL)
npm install knex pg

# 2. Generate a knexfile.js in your project root
npx knex init

# 3. Create your first migration
npx knex migrate:make create_users_table

The knexfile.js

The knexfile.js is the control center. It defines a connection and a migrations folder per environment, so the same commands behave correctly in development, staging, and production. Notice how production reads secrets from environment variables rather than hard-coding them.

// knexfile.js
require('dotenv').config();

/** @type {import('knex').Knex.Config} */
module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: 'localhost',
      database: 'blog_dev',
      user: 'postgres',
      password: 'password',
    },
    migrations: { directory: './db/migrations', tableName: 'knex_migrations' },
    seeds: { directory: './db/seeds' },
  },

  production: {
    client: 'pg',
    connection: {
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      ssl: { rejectUnauthorized: false },   // most hosted Postgres requires SSL
    },
    pool: { min: 2, max: 10 },
    migrations: { directory: './db/migrations', tableName: 'knex_migrations' },
  },
};

The Knex CLI Workflow

Day-to-day, you'll live in a handful of commands. Here they are with what each one does.

The batch concept

When migrate:latest runs several pending migrations at once, it groups them into a single batch (see the batch column in the tracking table). A single migrate:rollback reverses the whole most-recent batch together — handy when a deploy's worth of changes needs to come back out as a unit.

A Real Multi-Table Migration

Let's build the core of a blog: users, posts, and comments, with foreign keys tying them together. First generate the files:

npx knex migrate:make create_users_table
npx knex migrate:make create_posts_table
npx knex migrate:make create_comments_table

Then fill them in. Each is a standalone, reversible change:

// db/migrations/20260731100000_create_users_table.js
exports.up = function (knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('username', 50).notNullable().unique();
    table.string('email', 100).notNullable().unique();
    table.string('password_hash', 255).notNullable();
    table.boolean('is_admin').defaultTo(false);
    table.timestamps(true, true); // created_at & updated_at, defaulting to now()
  });
};
exports.down = function (knex) {
  return knex.schema.dropTable('users');
};

// db/migrations/20260731100100_create_posts_table.js
exports.up = function (knex) {
  return knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.string('title', 255).notNullable();
    table.text('content').notNullable();
    table.string('slug', 255).notNullable().unique();
    // Foreign key: a post belongs to a user. CASCADE removes posts if the user is deleted.
    table.integer('author_id').unsigned().notNullable()
      .references('id').inTable('users').onDelete('CASCADE');
    table.string('status', 20).defaultTo('draft'); // draft | published | archived
    table.timestamp('published_at');
    table.timestamps(true, true);
  });
};
exports.down = function (knex) {
  return knex.schema.dropTable('posts');
};

// db/migrations/20260731100200_create_comments_table.js
exports.up = function (knex) {
  return knex.schema.createTable('comments', (table) => {
    table.increments('id').primary();
    table.text('content').notNullable();
    table.integer('user_id').unsigned().notNullable()
      .references('id').inTable('users').onDelete('CASCADE');
    table.integer('post_id').unsigned().notNullable()
      .references('id').inTable('posts').onDelete('CASCADE');
    table.boolean('is_approved').defaultTo(true);
    table.timestamps(true, true);
  });
};
exports.down = function (knex) {
  return knex.schema.dropTable('comments');
};

Run them and check the result:

npx knex migrate:latest
npx knex migrate:status

Batch 1 run: 3 migrations
Found 3 Completed Migration file/files.
20260731100000_create_users_table.js
20260731100100_create_posts_table.js
20260731100200_create_comments_table.js
No Pending Migration files Found.

Sequelize Migrations

Sequelize is a full ORM whose CLI generates migrations alongside models. The style is imperative like Knex, but the API is queryInterface and it can scaffold a model and its migration together.

# Install Sequelize, the CLI, and the driver
npm install sequelize sequelize-cli pg pg-hstore

# Scaffold config/, models/, migrations/, seeders/
npx sequelize-cli init

# Generate a model AND its migration in one step
npx sequelize-cli model:generate --name User \
  --attributes username:string,email:string,password:string

The generated migration uses the same up/down pair — just with Sequelize's own methods:

// migrations/20260731-create-user.js
'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Users', {
      id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER },
      username: { type: Sequelize.STRING, allowNull: false, unique: true },
      email: { type: Sequelize.STRING, allowNull: false, unique: true },
      password: { type: Sequelize.STRING, allowNull: false },
      createdAt: { allowNull: false, type: Sequelize.DATE },
      updatedAt: { allowNull: false, type: Sequelize.DATE },
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Users');
  },
};

Same concepts, different words. If Knex made sense, Sequelize will feel familiar.

Prisma Migrate

Prisma flips the model. Instead of writing migration steps, you edit one declarative schema.prisma file that describes the schema you want, and Prisma diffs it against the database to generate the SQL migration automatically.

npm install prisma --save-dev
npx prisma init

// prisma/schema.prisma
generator client { provider = "prisma-client-js" }
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Create and apply a migration in development with one command:

# Diffs the schema, writes an SQL migration, applies it, regenerates the client
npx prisma migrate dev --name init

# Later, after editing schema.prisma (e.g. adding a bio field):
npx prisma migrate dev --name add_user_bio

# In production, apply already-created migrations without generating new ones:
npx prisma migrate deploy

Prisma still produces real, reviewable SQL migration files under prisma/migrations/ and tracks them in a _prisma_migrations table — the same underlying mechanism, generated for you:

-- prisma/migrations/20260731_init/migration.sql
CREATE TABLE "User" (
  "id"        SERIAL      NOT NULL,
  "email"     TEXT        NOT NULL,
  "name"      TEXT,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Running Migrations in Your App

The CLI is great for local work, but real projects also run migrations programmatically — in npm scripts and inside CI/CD.

npm scripts

// package.json
{
  "scripts": {
    "migrate": "knex migrate:latest",
    "migrate:prod": "knex migrate:latest --env production",
    "rollback": "knex migrate:rollback",
    "reset-db": "knex migrate:rollback --all && knex migrate:latest",
    "make-migration": "knex migrate:make"
  }
}

Programmatic execution

Knex exposes its migration API in code, which lets you run migrations as part of a deploy script and react to the result:

// db/run-migrations.js
const knex = require('knex');
const config = require('../knexfile');

const env = process.env.NODE_ENV || 'development';
const db = knex(config[env]);

async function migrateToLatest() {
  try {
    const [batch, log] = await db.migrate.latest();
    if (log.length === 0) {
      console.log('Database already up to date.');
    } else {
      console.log(`Batch ${batch} ran ${log.length} migration(s): ${log.join(', ')}`);
    }
  } catch (err) {
    console.error('Migration failed:', err);
    process.exitCode = 1;   // signal failure to the pipeline
  } finally {
    await db.destroy();     // always close the pool
  }
}

migrateToLatest();

In CI/CD

A typical pipeline runs migrations as an explicit, ordered step. Note it uses migrate deploy / migrate:latest — never a command that could generate new migrations against a live database.

Practice & Quiz

exports.up = async function (knex) {
  await knex.schema.createTable('categories', (t) => {
    t.increments('id').primary();
    t.string('name', 50).notNullable().unique();
    t.string('slug', 50).notNullable().unique();
  });
  await knex.schema.createTable('post_categories', (t) => {
    t.integer('post_id').unsigned().notNullable()
      .references('id').inTable('posts').onDelete('CASCADE');
    t.integer('category_id').unsigned().notNullable()
      .references('id').inTable('categories').onDelete('CASCADE');
    t.unique(['post_id', 'category_id']); // no duplicate pairings
  });
};

exports.down = async function (knex) {
  await knex.schema.dropTable('post_categories'); // join table first
  await knex.schema.dropTable('categories');
};

// db/migrations/20260801_add_user_bio.js
exports.up = (knex) =>
  knex.schema.table('users', (t) => t.text('bio'));

exports.down = (knex) =>
  knex.schema.table('users', (t) => t.dropColumn('bio'));

npx knex migrate:latest     # applies add_user_bio
npx knex migrate:rollback   # reverses the last batch (drops bio)

Best Practices & Pitfalls

✅ Do

• Keep one knexfile.js (or equivalent config) with a block per environment
• Load production secrets from environment variables, never from committed files
• Run migrations as a dedicated, one-time step in your deploy pipeline
• Use migrate:status before and after to confirm what changed
• Prefer prisma migrate deploy (apply-only) on production
• Always close the connection pool (db.destroy()) in programmatic scripts

❌ Don't

• Don't run prisma migrate dev or rollback --all against production
• Don't auto-migrate from every booting server instance
• Don't forget the database driver (pg, mysql2) — Knex needs it separately
• Don't generate migrations in an order where a foreign key precedes its target table
• Don't skip a backup before a production migration

Summary

📚 Additional Resources

• Knex.js — Migrations guide
• Sequelize — Migrations documentation
• Prisma Migrate — getting started
• node-pg-migrate — documentation

🚀 What's Next?

Your schema is now built and versioned — but an empty schema isn't much use. The next lesson fills it with data the right way: reference data, realistic development data, and repeatable, environment-aware seeds: Data Seeding Strategies.