Introduction to
Knex

Foundations

• What Knex is — origins, scope, query builder vs ORM
• Why Knex — abstraction, dialect portability, trade-offs
• Installation, clients, the first query
• knexfile.js, environments, pool configuration

What it owns

• Query building — chainable, composable, parameterised
• Schema builder — DDL across dialects in JS
• Migrations — versioned, ordered, reversible
• Seeds — repeatable data population
• Connection pool — via tarn
• Transactions — including savepoints
• Streams & batching for large result sets

Composition example

function listUsers({ role, search, page = 1 }) {
  let q = knex('users').select('id', 'name', 'email');
  if (role)   q = q.where('role', role);
  if (search) q = q.whereILike('name', `%${search}%`);
  return q.orderBy('created_at', 'desc')
          .limit(20).offset((page - 1) * 20);
}

Install

npm install knex

# pick the driver(s) you need
npm install pg                  # PostgreSQL / CockroachDB
npm install mysql2              # MySQL / MariaDB
npm install better-sqlite3      # SQLite (sync)
npm install sqlite3             # SQLite (async, legacy)
npm install tedious             # MSSQL
npm install oracledb            # Oracle

# CLI for migrations / seeds
npm install --save-dev knex
npx knex --version

knexfile.js

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

module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL ||
                'postgres://dev:dev@localhost:5432/myapp',
    pool: { min: 2, max: 10 },
    migrations: { directory: './db/migrations' },
    seeds:      { directory: './db/seeds/development' }
  },

  test: {
    client: 'better-sqlite3',
    connection: { filename: ':memory:' },
    useNullAsDefault: true,
    migrations: { directory: './db/migrations' },
    seeds:      { directory: './db/seeds/test' }
  },

  production: {
    client: 'pg',
    connection: {
      connectionString: process.env.DATABASE_URL,
      ssl: { rejectUnauthorized: false }
    },
    pool: { min: 2, max: 20, idleTimeoutMillis: 30_000 },
    migrations: { directory: './db/migrations',
                  tableName: 'knex_migrations' }
  }
};

Pool config

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: {
    min: 2,                       // keep this many warm
    max: 20,                      // hard cap on live conns
    acquireTimeoutMillis: 30_000, // queued, then throws
    createTimeoutMillis: 5_000,   // TCP handshake budget
    idleTimeoutMillis: 30_000,    // close idle conns
    reapIntervalMillis: 1_000,    // sweep frequency
    afterCreate: (conn, done) => {
      // run on every new connection
      conn.query("SET TIME ZONE 'UTC'", err => done(err, conn));
    }
  }
});

Basic SELECT

// columns
await knex('users').select('id', 'name');
await knex('users').select(); // all columns (== '*')

// where
await knex('users').where({ active: true, role: 'admin' });
await knex('users').where('created_at', '>', '2025-01-01');

// chained AND / OR
await knex('users')
  .where('role', 'admin')
  .orWhere(b => b.where('role', 'editor').andWhere('verified', true));

// IN / NOT IN
await knex('users').whereIn('id', [1, 2, 3]);
await knex('users').whereNotIn('role', ['guest']);

// NULL handling
await knex('users').whereNull('deleted_at');

// LIKE / ILIKE (PG)
await knex('users').whereILike('email', '%@dev.io');

Inner / left / right / cross

// inner join
await knex('posts as p')
  .join('users as u', 'u.id', 'p.author_id')
  .select('p.id', 'p.title', 'u.name as author');

// left join — users with optional posts
await knex('users as u')
  .leftJoin('posts as p', 'p.author_id', 'u.id')
  .select('u.id', 'u.name', 'p.title');

// compound ON
await knex('orders as o')
  .join('payments as pm', function () {
    this.on('pm.order_id', '=', 'o.id')
        .andOn('pm.status', '=', knex.raw('?', ['captured']));
  });

// self-join — reports-to manager
await knex('employees as e')
  .leftJoin('employees as m', 'e.manager_id', 'm.id')
  .select('e.name', 'm.name as manager');

Counts & sums

// count rows
await knex('orders').count('* as total').first();
// COUNT(DISTINCT user_id)
await knex('orders').countDistinct('user_id as buyers');

// sum / min / max / avg
await knex('orders')
  .select('user_id')
  .sum('total as revenue')
  .min('created_at as first_order')
  .max('created_at as last_order')
  .groupBy('user_id');

// having + havingRaw
await knex('orders')
  .select('user_id')
  .sum('total as revenue')
  .groupBy('user_id')
  .havingRaw('SUM(total) > ?', [1000]);

INSERT

// single row, return generated columns (PG, SQLite, MSSQL)
const [user] = await knex('users')
  .insert({ name: 'Alice', email: 'a@dev.io' })
  .returning(['id', 'created_at']);

// bulk — one INSERT statement
await knex('users').insert([
  { name: 'Bob',   email: 'b@dev.io' },
  { name: 'Carol', email: 'c@dev.io' }
]);

// onConflict — upsert
await knex('users')
  .insert({ email: 'a@dev.io', name: 'Alice 2' })
  .onConflict('email').merge();

// ignore on conflict
await knex('audit_log')
  .insert(rows).onConflict('event_id').ignore();

Sub-queries inline

// scalar sub-query in WHERE
await knex('users').where('id', '=',
  knex('orders').max('user_id').where('total', '>', 1000)
);

// EXISTS / NOT EXISTS
await knex('users').whereExists(
  knex('orders').whereRaw('orders.user_id = users.id')
);

// IN with sub-query
await knex('users').whereIn('id',
  knex('orders').select('user_id').where('total', '>', 100)
);

// sub-query as a derived table
const top = knex('orders')
  .select('user_id').sum('total as revenue')
  .groupBy('user_id').as('t');

await knex('users as u')
  .innerJoin(top, 'u.id', 't.user_id')
  .select('u.name', 't.revenue')
  .orderBy('t.revenue', 'desc').limit(10);

Three binding styles

// ?  — positional value binding
knex.raw('select * from users where id = ?', [42]);

// :name  — named binding
knex.raw('select * from users where role = :role',
         { role: 'admin' });

// ??  — identifier binding (table / column)
knex.raw('select ?? from ??', ['email', 'users']);

// mixed
knex.raw('update ?? set ?? = ? where ?? = :id',
         ['users', 'name', 'Alice', 'id', { id: 42 }]);

Creating a table

await knex.schema.createTable('users', (t) => {
  t.increments('id').primary();
  t.string('email', 320).notNullable().unique();
  t.string('name').notNullable();
  t.specificType('role', 'user_role').notNullable()
   .defaultTo('user');
  t.boolean('active').notNullable().defaultTo(true);
  t.jsonb('metadata').defaultTo('{}');
  t.timestamp('last_login_at');
  t.timestamps(true, true);            // created_at, updated_at

  t.index(['active', 'role']);
  t.index('email', 'users_email_lower_idx',
          { storageEngineIndexType: 'btree' });
});

// posts — with FK
await knex.schema.createTable('posts', (t) => {
  t.uuid('id').primary().defaultTo(knex.fn.uuid());
  t.text('title').notNullable();
  t.text('body');
  t.integer('author_id').unsigned().notNullable()
   .references('id').inTable('users').onDelete('CASCADE');
  t.timestamps(true, true);
});

Workflow

# scaffold a new migration
npx knex migrate:make add_posts_table

# → db/migrations/20260506T120000_add_posts_table.js

# apply pending migrations
npx knex migrate:latest

# undo the last batch
npx knex migrate:rollback

# show what is applied / pending
npx knex migrate:status
npx knex migrate:list

# move forward / back one at a time
npx knex migrate:up
npx knex migrate:down

# unlock if a migration crashed mid-run
npx knex migrate:unlock

Workflow

npx knex seed:make 01_users
npx knex seed:make 02_posts

npx knex seed:run                  # all files in seeds dir
npx knex seed:run --specific=01_users.js

Callback-style (auto-commit)

await knex.transaction(async (trx) => {
  const [order] = await trx('orders')
    .insert({ user_id: 1, total: 99.99 })
    .returning('*');

  await trx('inventory')
    .where('product_id', 42)
    .decrement('stock', 1);

  await trx('payments').insert({
    order_id: order.id, amount: 99.99
  });
});  // throws => ROLLBACK · resolves => COMMIT

Streaming reads

// pg: server-side cursor; mysql2: row-by-row
const stream = knex('events')
  .select()
  .where('created_at', '>', cutoff)
  .stream();

for await (const row of stream) {
  await processOne(row);     // back-pressure honoured
}

// pipe to a CSV stream
const fastCsv = require('fast-csv');
knex('events').stream()
  .pipe(fastCsv.format({ headers: true }))
  .pipe(fs.createWriteStream('events.csv'));

Declare your tables

// db/types.ts
import { Knex } from 'knex';

interface User {
  id: number;
  email: string;
  name: string;
  role: 'user' | 'admin';
  active: boolean;
  created_at: Date;
}

declare module 'knex/types/tables' {
  interface Tables {
    users: User;
    users_composite: Knex.CompositeTableType<
      User,                                 // result
      Pick<User, 'email' | 'name'>,         // insert
      Partial<Pick<User, 'name' | 'role'>>  // update
    >;
  }
}

Strategy A — in-memory SQLite

const knex = require('knex')({
  client: 'better-sqlite3',
  connection: { filename: ':memory:' },
  useNullAsDefault: true
});

beforeAll(async () => {
  await knex.migrate.latest();
  await knex.seed.run();
});
afterAll(() => knex.destroy());

Fast (millisecond per test). Lower fidelity — SQLite differs from PG on JSON, regex, locking and FK enforcement.

Inspect what's running

const { sql, bindings } = q.toSQL().toNative();
console.log(sql, bindings);

knex.on('query', e =>
  console.log(e.__knexUid, e.sql, e.bindings));

knex.on('query-response', (rows, e, b) =>
  console.log(e.__knexUid, 'rows:', rows.length));

knex.on('query-error', (err, e) =>
  console.error(e.sql, '\n  ', err));

Repository pattern

// db/repositories/users.js
module.exports = (db) => ({
  byEmail: (email)  => db('users').where({ email }).first(),
  create:  (input)  => db('users').insert(input).returning('*'),
  update:  (id, p)  => db('users').where({ id }).update(p),
  destroy: (id)     => db('users').where({ id }).del(),
});

// app.js
const knex = require('./db');
const users = require('./db/repositories/users')(knex);
app.get('/users/:email',
  async (req, res) => res.json(await users.byEmail(req.params.email)));

Where Knex earns its keep

• SQL stays SQL — you compose it, the DB plans it
• One toolbox for queries, schema, migrations, seeds, transactions, streams
• Multi-dialect — SQLite for tests, PG/MySQL in prod, MSSQL/Oracle when forced
• Battle-tested for over a decade; stable surface; small surface area
• Excellent escape hatch via knex.raw — rest of the chain still composes