Overview

Knex (knexjs.org, knex/knex) builds parameterized SQL through a chainable API and ships a migration and seed CLI. It is the layer under Objection.js and Bookshelf. Checked against knex 3.3.0 (26 June 2026; 3.0 dropped Node < 16, 3.2 added migration lifecycle hooks, 3.3 added MariaDB driver support). Queries below were run against SQLite with the same builder calls.

Instructions

Install and connect

bash

npm install knex pg            # PostgreSQL; other drivers: mysql2, better-sqlite3, sqlite3, tedious (MSSQL)
npx knex init -x ts            # creates knexfile.ts (use `npx knex init` for knexfile.js)

typescript

import knex from "knex";

export const db = knex({
  client: "pg",
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 20 },
});

Use client: "better-sqlite3" with useNullAsDefault: true for SQLite. Call await db.destroy() when a script finishes, or the process hangs on the open pool.

Queries

typescript

interface User { id: number; name: string; email: string; role: string }

const posts = await db("posts")
  .join("users", "posts.author_id", "users.id")
  .select("posts.*", "users.name as author_name")
  .where("posts.published", true)
  .orderBy("posts.created_at", "desc")
  .limit(10).offset(20);

const [user] = await db<User>("users")
  .insert({ name: "Alice Morgan", email: "alice.morgan@northwind.io" })
  .returning("*");                              // PostgreSQL, SQLite 3.35+, MSSQL; not MySQL

await db("users").where({ id: user.id }).update({ name: "Alice M." });
await db("users").where({ id: user.id }).del();

const recent = await db("users")
  .whereIn("id", db("posts").select("author_id").where("created_at", ">", thirtyDaysAgo));

const monthly = await db("orders")
  .select(db.raw("DATE_TRUNC('month', created_at) as month"))   // PostgreSQL function
  .sum("amount as total").count("* as orders")
  .groupByRaw("DATE_TRUNC('month', created_at)")
  .orderBy("month", "desc");

db<User>("users") types the result rows. On PostgreSQL count and sum come back as strings (bigint/numeric); cast in the query or in code. db.raw(sql, [values]) returns the driver's native result (for pg, read result.rows). Inspect SQL with .toQuery() or .toSQL().

Transactions

typescript

await db.transaction(async (trx) => {
  const [order] = await trx("orders").insert({ user_id: 1, total: 99.99 }).returning("*");
  await trx("order_items").insert(items.map((i) => ({ ...i, order_id: order.id })));
  await trx("users").where({ id: 1 }).decrement("balance", 99.99);
});   // commits when the callback resolves, rolls back when it throws

Every statement inside must use trx, not db, or it runs outside the transaction.

Migrations and seeds

bash

npx knex migrate:make create_users_table -x ts   # timestamped file in ./migrations
npx knex migrate:latest                          # run all pending
npx knex migrate:status                          # list applied / pending
npx knex migrate:rollback                        # undo the last batch
npx knex seed:make seed_users && npx knex seed:run
npx knex migrate:latest --env production         # pick a knexfile environment

typescript

// migrations/20261002120000_create_users_table.ts
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (t) => {
    t.increments("id");
    t.string("name", 100).notNullable();
    t.string("email").notNullable().unique();
    t.enu("role", ["user", "admin"]).defaultTo("user");
    t.timestamps(true, true);                    // created_at / updated_at defaulting to now
  });
  await knex.schema.createTable("posts", (t) => {
    t.increments("id");
    t.string("title").notNullable();
    t.boolean("published").defaultTo(false);
    t.integer("author_id").unsigned().references("id").inTable("users").onDelete("CASCADE");
    t.index(["author_id", "published"]);
  });
}

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

TypeScript migrations need a loader the CLI can find: install tsx and run NODE_OPTIONS="--import tsx" npx knex migrate:latest (ts-node also works), and set migrations: { extension: "ts" } in the knexfile. Confirmed with tsx on Node 24.

Examples

Example 1: "Add a users table and apply it"

bash

npm install knex pg
npx knex init -x ts
npx knex migrate:make create_users_table -x ts
# paste the up/down functions above, then
NODE_OPTIONS="--import tsx" npx knex migrate:latest

Output: Batch 1 run: 1 migrations. migrate:status then lists 20261002120000_create_users_table.ts as completed.

Example 2: "Move money between two accounts atomically"

typescript

await db.transaction(async (trx) => {
  const { balance } = await trx("accounts").where({ id: 7 }).forUpdate().first();
  if (balance < 250) throw new Error("insufficient funds");   // triggers rollback
  await trx("accounts").where({ id: 7 }).decrement("balance", 250);
  await trx("accounts").where({ id: 12 }).increment("balance", 250);
});

Either both updates persist or neither does. forUpdate() locks the row on PostgreSQL and MySQL.

Guidelines

Pass user input as bindings (where({ email }), raw("... ?", [value])), never by string concatenation. Identifiers in raw need ??.
Change schema only through migrations; never edit a migration that has already run in shared environments, add a new one.
Keep one knex instance per process; a new pool per request exhausts connections.
Match pool.max to the database's connection limit divided by app instances.
Knex does not map rows to classes or manage relations; use Objection.js or an ORM if you need that.
MySQL has no returning; read insertId from the result instead.

Knex.js

Validation

Getting Started

Example Prompts

Documentation

Overview

Instructions

Install and connect

Queries

Transactions

Migrations and seeds

Examples

Example 1: "Add a users table and apply it"

Example 2: "Move money between two accounts atomically"

Guidelines

Information

Related Skills