Node.js 47 🟢 Executing Database Migrations via Node CLI Tools - KandZ

A database schema is not static. The columns are added, the tables are renamed, the indexes are created, and the constraints are changed. Each change must be applied to every environment — the development, the staging, the production — in the same order and the same way. The migration is the versioned, the ordered, and the repeatable. The migration file describes the change, the migration tool tracks which files have been applied, and the CLI runs the pending ones. The Node.js ecosystem has the several migration tools: the Knex, the Prisma Migrate, the Drizzle Kit, the Umzug, and the db-migrate. Each has the different approach, and the choice depends on the ORM and the workflow.

The migration has two parts: the schema change and the data change. The schema change is the CREATE TABLE, the ALTER TABLE, and the DROP COLUMN. The data change is the UPDATE, the INSERT, and the DELETE that the schema change requires — the backfill, the seed, and the cleanup. The two are the separate migrations, and the data migration is the risky. The data migration must be the idempotent, the reversible, and the tested. The down function reverses the up, and the knex migrate:rollback runs it.

The CLI is the interface. The knex migrate:make creates the migration file, the knex migrate:latest applies the pending, and the knex migrate:rollback reverses the last batch. The prisma migrate dev and the prisma migrate deploy are the Prisma’s versions, and the drizzle-kit generate and the drizzle-kit migrate are the Drizzle’s. The migrate script is the package.json entry, and the CI/CD pipeline runs it before the application deploy.

This chapter covers three areas. First, why the migration tools exist — the schema-versioning problem and the environment-parity. Second, the migration anatomy — the up and the down, the timestamped filenames, the migration table, and the batching. Third, the CLI tools — the Knex, the Prisma, the Drizzle, and the Umzug, with the commands and the patterns. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the migration flow.

Key point: A database migration is the versioned, the ordered, and the repeatable schema change. The migration file has the up function that applies the change and the down function that reverses it. The migration tool tracks the applied migrations in the dedicated table, and the CLI runs the pending ones. The Knex, the Prisma, the Drizzle, and the Umzug are the common Node.js tools. The migration runs before the application deploy, and the production uses the migrate deploy (the Prisma) or the migrate:latest (the Knex), not the db push or the migrate dev.

Why the migration tools exist

The schema-versioning problem. The schema changes over time, and the change must be recorded. The ALTER TABLE users ADD COLUMN phone VARCHAR(20) is the change, and the migration file records it. The git history records the code, and the migration history records the schema. The two are the synchronized, and the developer can reproduce the schema at the any point .

The environment-parity problem. The development, the staging, and the production must have the same schema. The manual changes diverge, and the divergence causes the “it works on my machine” bug. The migration runs the same files in the same order in every environment, and the parity is the guaranteed .

The repeatability problem. The migration is the idempotent: the tool checks the migration table, and it applies only the pending. The running the migrate:latest twice does not apply the same migration twice. The repeatability makes the deploy the safe .

The rollback problem. The change is the wrong, and the rollback is required. The down function reverses the up, and the migrate:rollback runs it. The rollback is the not always possible — the dropped column cannot be restored — but the down is the best effort, and the review catches the irreversible .

The data-migration problem. The schema change often requires the data change. The new column is the non-null, and the existing rows need the default. The migration runs the UPDATE after the ALTER TABLE. The data migration is the separate, and it is the slow and the risky. The batching is the pattern for the large tables .

The trade-off. The migration adds the files, the tool, and the process. The small project with the single developer may use the db push, and the production with the many environments needs the versioned migrations. The trade-off is between the speed of the push and the safety of the versioned migrations.

a. The migration anatomy

A migration file has the up function and the down function. The up applies the change, and the down reverses it.

// migrations/20240115103000_create_users.js
exports.up = function (knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primaryKey();
    table.string('email').notNullable().unique();
    table.string('name').notNullable();
    table.timestamps(true, true);
  });
};

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

The filename is the timestamp plus the description. The timestamp orders the migrations, and the description identifies the change. The knex.schema is the schema builder, and the table.increments is the auto-incrementing primary key .

The migration table. The tool creates the knex_migrations table (the Knex) or the _prisma_migrations table (the Prisma) or the __drizzle_migrations table (the Drizzle). The table records the applied migration names and the batch numbers.

knex_migrations
┌────┬──────────────────────────────┬───────┬────────────────────┐
│ id │ name                         │ batch │ migration_time     │
├────┼──────────────────────────────┼───────┼────────────────────┤
│ 1  │ 20240115103000_create_users  │ 1     │ 2024-01-15 10:30   │
│ 2  │ 20240116120000_add_phone     │ 2     │ 2024-01-16 12:00   │
└────┴──────────────────────────────┴───────┴────────────────────┘

The batch groups the migrations applied in the same migrate:latest run. The migrate:rollback reverses the last batch .

The timestamped filename. The 20240115103000_create_users.js is the timestamp plus the description. The lexicographic order is the chronological order, and the migrations run in the filename order .

The transaction. The migration should run in the transaction, so the failure rolls back the whole migration. The Knex wraps the up in the transaction by default, and the Prisma does the same. The DDL in the PostgreSQL is the transactional, and the MySQL is the auto-commit .

The lock. The concurrent migration runs can corrupt the state. The migration tool takes the advisory lock, and the second run waits. The knex migrate:latest takes the lock, and the Prisma uses the _prisma_migrations row lock .

b. The Knex migrations

The setup. The knexfile.js specifies the connection and the migration directory:

module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
  },
};

The migrations.directory is the path, and the migrations.tableName is the tracking table .

The commands. The migrate:make creates the file, the migrate:latest applies the pending, and the migrate:rollback reverses the last batch:

npx knex migrate:make create_users
npx knex migrate:latest
npx knex migrate:rollback
npx knex migrate:list

The migrate:make creates the timestamped file, and the migrate:latest runs the pending. The migrate:list shows the applied and the pending .

The seed. The seed:make creates the seed file, and the seed:run runs it:

npx knex seed:make users
npx knex seed:run

The seed is the development data, and the migration is the schema change. The two are the separate .

The programmatic. The migration can run programmatically:

const knex = require('knex')(config);

async function migrate() {
  await knex.migrate.latest();
  await knex.destroy();
}

migrate().catch(console.error);

The knex.migrate.latest() runs the pending, and the knex.destroy() closes the connection .

c. The Prisma, the Drizzle, and the Umzug

The Prisma Migrate. The prisma migrate dev is the development, and the prisma migrate deploy is the production:

npx prisma migrate dev --name add_phone
npx prisma migrate deploy
npx prisma migrate status
npx prisma migrate reset

The migrate dev generates the migration from the schema change and applies it. The migrate deploy applies the pending migrations in the production and does not reset the database. The migrate reset drops the database, recreates it, and applies all the migrations .

The Drizzle Kit. The drizzle-kit generate creates the migration, and the drizzle-kit migrate applies it:

npx drizzle-kit generate
npx drizzle-kit migrate
npx drizzle-kit push
npx drizzle-kit check

The generate creates the SQL file, and the migrate applies it. The push is the development shortcut, and the check detects the schema drift .

The Umzug. The Umzug is the framework-agnostic migration library:

const { Umzug, SequelizeStorage } = require('umzug');

const umzug = new Umzug({
  migrations: { glob: 'migrations/*.js' },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({ sequelize }),
  logger: console,
});

await umzug.up();
await umzug.down();
await umzug.pending();

The Umzug works with the Sequelize, the TypeORM, the Prisma, the Knex, and the raw SQL. The storage tracks the applied migrations, and the up and the down run them .

The db-migrate. The db-migrate is the older and the standalone:

db-migrate create add-phone
db-migrate up
db-migrate down
db-migrate reset

The db-migrate supports the multiple databases and the plugins .

Complete Example Session

// ============================================
// PART 1: THE KNEXFILE
// ============================================

// knexfile.js
module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
  },
};


// ============================================
// PART 2: THE INITIAL MIGRATION
// ============================================

// migrations/20240115103000_create_users.js
exports.up = function (knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primaryKey();
    table.string('email').notNullable().unique();
    table.string('name').notNullable();
    table.timestamps(true, true);
  });
};

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


// ============================================
// PART 3: THE ALTER MIGRATION
// ============================================

// migrations/20240116120000_add_phone.js
exports.up = function (knex) {
  return knex.schema.alterTable('users', (table) => {
    table.string('phone', 20).nullable();
    table.index('phone');
  });
};

exports.down = function (knex) {
  return knex.schema.alterTable('users', (table) => {
    table.dropIndex('phone');
    table.dropColumn('phone');
  });
};


// ============================================
// PART 4: THE DATA MIGRATION
// ============================================

// migrations/20240117140000_backfill_status.js
exports.up = async function (knex) {
  await knex.schema.alterTable('users', (table) => {
    table.string('status').defaultTo('active');
  });

  await knex('users').whereNull('status').update({ status: 'active' });

  await knex.schema.alterTable('users', (table) => {
    table.string('status').notNullable().alter();
  });
};

exports.down = function (knex) {
  return knex.schema.alterTable('users', (table) => {
    table.dropColumn('status');
  });
};


// ============================================
// PART 5: THE COMMANDS
// ============================================

// The create
npx knex migrate:make create_users

// The apply
npx knex migrate:latest

// The rollback
npx knex migrate:rollback

// The list
npx knex migrate:list

// The status
npx knex migrate:status


// ============================================
// PART 6: THE PRISMA
// ============================================

// The schema change
// prisma/schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
  phone String?
}

// The migration
npx prisma migrate dev --name add_phone

// The production
npx prisma migrate deploy

// The status
npx prisma migrate status


// ============================================
// PART 7: THE DRIZZLE
// ============================================

// The schema change
// src/db/schema.ts
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 256 }).notNull().unique(),
  phone: varchar('phone', { length: 20 }),
});

// The generate
npx drizzle-kit generate

// The migrate
npx drizzle-kit migrate


// ============================================
// PART 8: THE UMZUG
// ============================================

const { Umzug, SequelizeStorage } = require('umzug');

const umzug = new Umzug({
  migrations: { glob: 'migrations/*.js' },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({ sequelize }),
  logger: console,
});

async function migrate() {
  const pending = await umzug.pending();
  console.log(`The pending: ${pending.length}`);
  await umzug.up();
}

migrate().catch(console.error);


// ============================================
// PART 9: THE PACKAGE.JSON SCRIPTS
// ============================================

{
  "scripts": {
    "migrate": "knex migrate:latest",
    "migrate:rollback": "knex migrate:rollback",
    "migrate:make": "knex migrate:make",
    "migrate:status": "knex migrate:status",
    "seed": "knex seed:run"
  }
}


// ============================================
// PART 10: THE LFCS PIPELINE
// ============================================

# The development
npx knex migrate:latest

# The production
NODE_ENV=production npx knex migrate:latest

# The status
npx knex migrate:status

# The rollback
npx knex migrate:rollback

# The seed
npx knex seed:run

# The CI/CD
# The migration runs before the application deploy.
# The failed migration stops the deploy.

The ten parts show the knexfile, the initial migration, the alter migration, the data migration, the commands, the Prisma, the Drizzle, the Umzug, the package.json scripts, and the LFCS pipeline.

Quick Reference

The Migration Tools

Tool	The ORM	The command
Knex	Knex	`knex migrate:latest`
Prisma	Prisma	`prisma migrate deploy`
Drizzle	Drizzle	`drizzle-kit migrate`
Umzug	Any	`umzug.up()`
db-migrate	Any	`db-migrate up`

The Knex Commands

Command	The purpose
`knex migrate:make name`	The create the migration
`knex migrate:latest`	The apply the pending
`knex migrate:rollback`	The reverse the last batch
`knex migrate:list`	The list the migrations
`knex migrate:status`	The show the status
`knex seed:make name`	The create the seed
`knex seed:run`	The run the seeds

The Prisma Commands

Command	The purpose
`prisma migrate dev`	The development
`prisma migrate deploy`	The production
`prisma migrate status`	The status
`prisma migrate reset`	The drop and recreate
`prisma db seed`	The seed

The Drizzle Commands

Command	The purpose
`drizzle-kit generate`	The generate the SQL
`drizzle-kit migrate`	The apply the SQL
`drizzle-kit push`	The development shortcut
`drizzle-kit check`	The schema drift
`drizzle-kit studio`	The visual browser

The Migration Anatomy

The part	The purpose
The `up`	The apply the change
The `down`	The reverse the change
The timestamp	The order
The description	The identifier
The migration table	The tracking
The batch	The group
The transaction	The atomicity
The lock	The concurrency

Best Practices

✅ Do This:

// Use the up and the down
exports.up = (knex) => { ... };
exports.down = (knex) => { ... };                                         // ✅
// Use the timestamped filenames
// 20240115103000_create_users.js                                         // ✅
// Use the data migration for the backfill
await knex('users').whereNull('status').update({ status: 'active' });     // ✅
// Use the transaction
exports.up = (knex) => knex.transaction(async (trx) => { ... });          // ✅
// Use the migrate:latest for the production
// The not the db push                                                   // ✅
// Use the migrate:status to verify
// The pending and the applied                                           // ✅
// Use the index for the large tables
table.index('phone');                                                     // ✅

❌ Don’t Do This:

// Don't edit the applied migration
// The create a new one instead                                           // ❌
// Don't drop the column without the down
exports.up = (knex) => knex.schema.dropColumn('phone');
// The no down                                                             // ❌
// Don't use the db push in the production
// The bypasses the history                                               // ❌
// Don't run the concurrent migrations
// The lock is the requirement                                            // ❌
// Don't use the non-transactional DDL
// The MySQL auto-commits the DDL                                        // ❌
// Don't skip the data migration for the non-null
// The existing rows fail the constraint                                 // ❌
// Don't run the migration during the peak
// The lock is the table-level                                            // ❌

Common Pitfalls

The pitfall	The why	The fix
The migration fails	The SQL error	Review the generated SQL
The partial application	The no transaction	Use the transaction
The concurrent conflict	The no lock	Use the advisory lock
The column not found	The wrong order	Check the filename order
The data loss	The drop without the backup	Back up first
The production drift	The manual change	Use the `migrate status`
The down fails	The irreversible	The review the migration

Real-World Examples

1. The Create Table

exports.up = (knex) => knex.schema.createTable('users', (t) => { ... });

2. The Alter Table

exports.up = (knex) => knex.schema.alterTable('users', (t) => { ... });

3. The Data Migration

await knex('users').whereNull('status').update({ status: 'active' });

4. The Knex Command

npx knex migrate:latest

5. The Prisma Command

npx prisma migrate deploy

6. The Drizzle Command

npx drizzle-kit migrate

7. The Umzug

await umzug.up();

8. The Seed

npx knex seed:run

9. The Status

npx knex migrate:status

10. The Complete

NODE_ENV=production npx knex migrate:latest

Visual

The Migration Flow

┌──────────────────────────────────────────────────────────────┐
│  THE MIGRATION FLOW                                          │
│                                                              │
│  The migration file:                                         │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  exports.up = (knex) => { ... };                     │    │
│  │  exports.down = (knex) => { ... };                   │    │
│  └──────────────────────────────────────────────────────┘    │
│    │                                                         │
│    │  knex migrate:latest                                   │
│    ▼                                                         │
│  The migration tool:                                         │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  1. Check the knex_migrations table.                 │    │
│  │  2. Find the pending migrations.                     │    │
│  │  3. Apply the up in the order.                       │    │
│  │  4. Record the names and the batch.                  │    │
│  └──────────────────────────────────────────────────────┘    │
│    │                                                         │
│    ▼                                                         │
│  The database:                                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  The schema is updated.                              │    │
│  │  The knex_migrations tracks the applied.             │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The Up and the Down

┌──────────────────────────────────────────────────────────────┐
│  THE UP AND THE DOWN                                         │
│                                                              │
│  THE UP (the apply):                                         │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  exports.up = (knex) => {                            │    │
│  │    return knex.schema.createTable('users', (t) => {  │    │
│  │      t.increments('id');                             │    │
│  │      t.string('email').unique();                     │    │
│  │    });                                               │    │
│  │  };                                                  │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  THE DOWN (the reverse):                                     │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  exports.down = (knex) => {                          │    │
│  │    return knex.schema.dropTable('users');            │    │
│  │  };                                                  │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The down reverses the up, and the rollback runs it.         │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The Migration Table

┌──────────────────────────────────────────────────────────────┐
│  THE MIGRATION TABLE                                         │
│                                                              │
│  knex_migrations                                             │
│  ┌────┬──────────────────────────────┬───────┐               │
│  │ id │ name                         │ batch │               │
│  ├────┼──────────────────────────────┼───────┤               │
│  │ 1  │ 20240115103000_create_users  │ 1     │               │
│  │ 2  │ 20240116120000_add_phone     │ 1     │               │
│  │ 3  │ 20240117140000_backfill      │ 2     │               │
│  └────┴──────────────────────────────┴───────┘               │
│                                                              │
│  The batch groups the migrations applied in the same run.    │
│  The migrate:rollback reverses the last batch.               │
│                                                              │
└──────────────────────────────────────────────────────────────┘

The Environment Parity

┌──────────────────────────────────────────────────────────────┐
│  THE ENVIRONMENT PARITY                                      │
│                                                              │
│  The development:                                            │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  npx knex migrate:latest                             │    │
│  │    → the schema v3                                   │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The staging:                                                │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  NODE_ENV=staging npx knex migrate:latest            │    │
│  │    → the schema v3                                   │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The production:                                             │
│  ┌──────────────────────────────────────────────────────┐    │
│  │  NODE_ENV=production npx knex migrate:latest         │    │
│  │    → the schema v3                                   │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
│  The same migrations, the same order, the same schema.       │
│                                                              │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
The migration	The versioned schema change
The `up`	The apply the change
The `down`	The reverse the change
The filename	The timestamp + the description
The migration table	The tracking
The batch	The group
The Knex	`knex migrate:latest`
The Prisma	`prisma migrate deploy`
The Drizzle	`drizzle-kit migrate`
The Umzug	`umzug.up()`
The transaction	The atomicity
The lock	The concurrency

Key takeaways:

A database migration is the versioned, the ordered, and the repeatable schema change. The migration file has the up function and the down function. The tool tracks the applied migrations in the dedicated table, and the CLI runs the pending ones .
The filename is the timestamp plus the description. The lexicographic order is the chronological order, and the migrations run in the filename order. The migration table records the names and the batch numbers .
The data migration is the separate from the schema migration. The ALTER TABLE adds the column, and the UPDATE backfills the existing rows. The data migration must be the idempotent and the tested .
The Knex uses the migrate:make, the migrate:latest, and the migrate:rollback. The migrate:make creates the file, the migrate:latest applies the pending, and the migrate:rollback reverses the last batch .
The Prisma uses the migrate dev and the migrate deploy. The migrate dev is the development and it generates the migration, and the migrate deploy is the production and it applies the pending .
The Drizzle uses the drizzle-kit generate and the drizzle-kit migrate. The generate creates the SQL file, and the migrate applies it. The push is the development shortcut .
The Umzug is the framework-agnostic. The umzug.up() and the umzug.down() run the migrations, and the storage tracks the applied. It works with the Sequelize, the TypeORM, the Prisma, and the Knex .
The migration runs before the application deploy. The CI/CD pipeline runs the migrate:latest or the migrate deploy, and the failed migration stops the deploy. The result is the synchronized schema, which is the foundation of the reliable deployment.

Remember: The database migration is the versioned, the ordered, and the repeatable schema change. The up applies the change, and the down reverses it. The migration table tracks the applied, and the CLI runs the pending. The Knex, the Prisma, the Drizzle, and the Umzug are the common Node.js tools. The result is the environment parity, which is the foundation of the reliable deployment.

database migrations, Knex, Prisma, Node.js

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!