Database Migrations with Knex.js + PostgreSQL

2. Write the up and down functions

Every migration exports two functions. Follow these rules:

Always write down as the exact inverse of up. If up creates a table, down drops it. If up adds a column, down drops that column. If up changes a column type, down reverts to the original type.

Use alterTable for column changes, createTable for new tables.

Use transactions for multi-step migrations:

exports.up = async function(knex) {
  await knex.transaction(async (trx) => {
    await trx.schema.createTable('posts', (table) => {
      table.increments('id').primary();
      table.integer('user_id').unsigned().notNullable()
        .references('id').inTable('users').onDelete('CASCADE');
      table.string('title', 255).notNullable();
      table.text('body');
      table.timestamps(true, true);
    });

    await trx.schema.alterTable('users', (table) => {
      table.integer('post_count').defaultTo(0);
    });
  });
};

exports.down = async function(knex) {
  await knex.transaction(async (trx) => {
    await trx.schema.alterTable('users', (table) => {
      table.dropColumn('post_count');
    });
    await trx.schema.dropTableIfExists('posts');
  });
};

For TypeScript projects:

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.string('title', 255).notNullable();
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('posts');
}

3. Handle common schema changes

Add a column

exports.up = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.string('phone', 20).nullable();
  });
};

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

Add a NOT NULL column to a table with existing data

Cannot add NOT NULL without a default or backfill. Use a three-step approach:

exports.up = async function(knex) {
  await knex.transaction(async (trx) => {
    // 1. Add as nullable
    await trx.schema.alterTable('users', (table) => {
      table.string('display_name', 100).nullable();
    });

    // 2. Backfill existing rows
    await trx.raw(`UPDATE users SET display_name = first_name || ' ' || last_name`);

    // 3. Set NOT NULL constraint
    await trx.raw(`ALTER TABLE users ALTER COLUMN display_name SET NOT NULL`);
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.dropColumn('display_name');
  });
};

Rename a column

exports.up = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.renameColumn('name', 'full_name');
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.renameColumn('full_name', 'name');
  });
};

Change a column type

exports.up = async function(knex) {
  await knex.schema.alterTable('products', (table) => {
    table.decimal('price', 10, 2).alter();
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('products', (table) => {
    table.integer('price').alter();
  });
};

Note: alter() requires the knex version 0.20+ and may need raw SQL for some PostgreSQL-specific type changes.

Add an index

exports.up = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.index('email', 'idx_users_email');
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.dropIndex('email', 'idx_users_email');
  });
};

Add a unique constraint

exports.up = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.unique('email');
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('users', (table) => {
    table.dropUnique('email');
  });
};

Create an enum column (PostgreSQL native enum)

exports.up = async function(knex) {
  await knex.raw(`CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled')`);
  await knex.schema.alterTable('orders', (table) => {
    table.specificType('status', 'order_status').defaultTo('pending');
  });
};

exports.down = async function(knex) {
  await knex.schema.alterTable('orders', (table) => {
    table.dropColumn('status');
  });
  await knex.raw(`DROP TYPE order_status`);
};

4. Run and manage migrations

5. Handle rollback safety

Before running any destructive migration in production:

1. Take a database backup -- pg_dump dbname > backup_before_migration.sql
2. Test on a staging copy first -- never run untested migrations in production
3. Verify the down function works -- run migrate:latest then migrate:rollback
4. Check for table locks -- adding indexes on large tables can lock them; use CREATE INDEX CONCURRENTLY via raw SQL instead

Concurrent index creation (avoids table lock)

exports.up = async function(knex) {
  // Cannot run inside a transaction -- CONCURRENTLY prevents that
  await knex.raw(`CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id ON orders (user_id)`);
};

exports.down = async function(knex) {
  await knex.raw(`DROP INDEX IF EXISTS idx_orders_user_id`);
};

Disable the automatic Knex transaction wrapper for this migration by setting exports.config = { transaction: false }; at the bottom of the file.

6. Knexfile configuration

Ensure knexfile.js (or knexfile.ts) is configured properly:

module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      port: 5432,
      user: 'devuser',
      password: 'devpass',
      database: 'myapp_dev',
    },
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
    seeds: {
      directory: './seeds',
    },
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations',
      tableName: 'knex_migrations',
    },
    pool: { min: 2, max: 10 },
  },
};

Troubleshooting

Decision guide: Knex schema builder vs raw SQL

Reference

For a quick reference of common PostgreSQL column types and their Knex equivalents, see references/pg-column-types.md.