Knex Migration Generator from Table

Knex.js migrations: versioned schema for Node.js

Knex.js is the SQL query builder for Node.js created by Tim Griesser in 2013. Beyond fluent SELECT/INSERT/UPDATE chains it ships a first-class migration system: a folder of timestamped JavaScript files that describe each schema change as code, version-controlled alongside the application. The migration runner records applied versions in a knex_migrations table so the same series produces identical schemas across dev, CI, staging, and production.

Knex is database-agnostic and supports PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, Oracle, and Amazon Redshift. It is not a full ORM — there are no models, validations, or lifecycle hooks. For full ORM features pair it with Objection.js, which sits on top of Knex.

Creating and running migrations

The Knex CLI scaffolds, runs, and rolls back migrations:

• npx knex migrate:make create_users_table — generates a timestamped template under ./migrations.
• npx knex migrate:latest — applies every pending migration in order.
• npx knex migrate:rollback — reverts the most recent batch.
• npx knex migrate:status — lists applied vs pending.
• npx knex seed:run — populates lookup tables (separate from migrations).

Anatomy of a migration file

Each file exports an up (apply) and down (revert) function. The schema builder offers a fluent API over column types and constraints:

exports.up = (knex) => knex.schema.createTable('users', (t) => {
  t.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
  t.string('email').notNullable().unique();
  t.string('name', 100);
  t.boolean('active').notNullable().defaultTo(true);
  t.timestamps(true, true); // created_at + updated_at
  t.index(['email']);
});

exports.down = (knex) => knex.schema.dropTable('users');

Common column helpers: t.string, t.integer, t.bigInteger, t.boolean, t.date, t.timestamp, t.json, t.jsonb (PostgreSQL), t.uuid, t.text, t.decimal. Foreign keys: t.foreign('user_id').references('users.id').onDelete('CASCADE'). Composite indexes: t.index(['col1', 'col2']); uniques: t.unique(['email', 'tenant_id']).

Best practices and anti-patterns

• Always write down — even if you never run it in prod, it documents intent and lets contributors reset locally.
• Never edit a committed migration — running it again on a fresh DB must yield the same state. Add a new migration instead.
• Use knex.transaction for migrations that mix DDL and data backfills, but remember that MySQL implicitly commits DDL.
• For test suites, point Knex at an in-memory SQLite (:memory:) for sub-second startup.
• In NestJS or large apps, expose Knex via a custom provider so the same instance is shared across modules.

FAQ

Is Knex an ORM? No — it is a query builder. There are no models or relations. Pair with Objection.js if you want an ORM on top.

Can I use Knex alongside Prisma? Technically yes, but the philosophies clash: Prisma owns the schema via schema.prisma and runs its own migrations. Pick one or the other for schema management.

Do migrations support transactions? Yes. By default each migration runs in a transaction (where the engine supports it). Disable per-file with config.transaction = false for engines like MySQL.

How does Knex compare to Drizzle or Kysely? Drizzle and Kysely are newer TypeScript-first builders with stronger type inference. Knex remains the most battle-tested choice and works in plain JavaScript without a build step.

Where are credentials stored? In knexfile.js at the project root, typically reading from process.env. Use one block per environment (development, test, production).