knexKnex.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.
Independent GitHub snapshot · Automated classification · Original documentation governs
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.
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.
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().
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.
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.
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.
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.
where({ email }), raw("... ?", [value])), never by string concatenation. Identifiers in raw need ??.knex instance per process; a new pool per request exhausts connections.pool.max to the database's connection limit divided by app instances.returning; read insertId from the result instead.