---

name: knex description: >- Knex.js is a SQL query builder for Node.js with a chainable API, schema migrations, seeds and transactions for PostgreSQL, MySQL, MariaDB, SQLite and MSSQL. Use when writing queries without a full ORM, setting up knexfile.js and migrations, running knex migrate:latest, building joins, subqueries and transactions, or typing results with TypeScript. license: Apache-2.0 compatibility: "Node.js 16+, knex 3.x plus one database driver (pg, mysql2, better-sqlite3, sqlite3, tedious)" metadata: author: terminal-skills version: 1.1.0 category: development repository: https://github.com/knex/knex tags: - query-builder - sql - database - migrations - node

Knex.js — SQL Query Builder for Node.js

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

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)

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

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

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

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

// 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"

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"

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.