Knex.js

What is Knex.js?

Knex.js is a SQL query builder for Node.js. It is deliberately not an ORM. It does not map rows to classes, does not track object state and does not load relations on its own. What it does is let you build SQL statements as composable JavaScript objects, bind every value safely, and run them against Postgres, MySQL, SQLite, MSSQL or Oracle.

That restraint is the reason it has lasted. Knex appeared in 2013 and became the foundation that Objection.js and several other libraries build on. If you want the ergonomics of an ORM, you can add one on top; if you want to stay close to SQL, Knex is already the right level.

The mental model is small: start with a table name, chain methods to describe the statement, then await it to execute. Every chain eventually becomes one parameterised query.

Why use a query builder at all?

Writing SQL as template strings seems fine until a query needs to change shape. A search endpoint with optional filters, a sort that depends on user input and a pagination clause is painful to assemble by concatenation, and dangerous if any value is interpolated directly.

A builder solves both problems. Conditions append cleanly, values become bound parameters and identifiers are quoted for the target dialect.

const query = db("users").select("id", "email");

if (search) {
  query.where("email", "ilike", `%${search}%`);
}
if (verifiedOnly) {
  query.whereNotNull("email_verified_at");
}

const users = await query.orderBy("created_at", "desc").limit(20);

Notice that search is passed as an argument to where, not spliced into a string. Knex sends it to the driver as a placeholder, so it can never change the structure of the statement.

The other benefit is portability. The same builder chain runs on Postgres and MySQL with only a configuration change, which is useful for local SQLite development and tests.

Installing and the knexfile

Install Knex and the driver for your database. The driver is a separate package, because Knex does not bundle database clients.

pnpm add knex pg
pnpm add -D @types/pg

Configuration lives in a knexfile, one entry per environment. The CLI reads it automatically, and your application imports the same object.

import type { Knex } from "knex";

const config: { [key: string]: Knex.Config } = {
  development: {
    client: "pg",
    connection: process.env.DATABASE_URL,
    migrations: { directory: "./migrations" },
    seeds: { directory: "./seeds" },
    pool: { min: 2, max: 10 },
  },
  production: {
    client: "pg",
    connection: process.env.DATABASE_URL,
    pool: { min: 2, max: 10 },
  },
};

export default config;

Create a single shared instance and import it everywhere, rather than calling knex() in each module. One instance owns one connection pool, and extra instances multiply connections against the database.

import knex from "knex";
import config from "../knexfile";

export const db = knex(config.development);

Use TypeScript’s satisfies or a typed config object so the editor catches a misspelled option, and keep secrets in the environment rather than the file.

Migrations with the schema builder

Migrations are how the schema changes over time. Each file has an up that applies the change and a down that reverses it.

npx knex migrate:make create_users
npx knex migrate:latest
npx knex migrate:rollback
npx knex migrate:list

The generated file is ordinary TypeScript. The schema builder describes tables with a callback that receives a table object.

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (table) => {
    table.increments("id").primary();
    table.string("email", 255).notNullable().unique();
    table.string("display_name", 120).notNullable();
    table.timestamp("created_at").notNullable().defaultTo(knex.fn.now());
  });
}

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

Knex records applied migrations in a knex_migrations table, so each environment knows exactly which files it has run. Always write a real down; even if you rarely roll back, it documents how to undo the change and makes testing migrations possible.

alterTable changes an existing table. Some changes, such as adding a notNullable column to a table with rows, need a default or a backfill step. Split those into two migrations: one to add the column and backfill, another to add the constraint.

Seeds for local data

Seeds populate a database with known rows for development and tests. They are separate from migrations because they represent data, not structure.

import type { Knex } from "knex";

export async function seed(knex: Knex): Promise<void> {
  await knex("users").del();
  await knex("users").insert([
    { email: "[email protected]", display_name: "Ada Lovelace" },
    { email: "[email protected]", display_name: "Linus Torvalds" },
  ]);
}

Run them with npx knex seed:run. Seeds should be idempotent where possible, which usually means clearing the table or using an upsert before inserting, so running them twice does not fail or duplicate data.

Building queries

A query starts with a table name. From there, methods chain until the statement is awaited.

const rows = await db("users")
  .select("id", "email", "display_name")
  .where("created_at", ">", since)
  .whereIn("role", ["admin", "editor"])
  .orderBy("created_at", "desc")
  .limit(50)
  .offset(0);

where accepts several forms: a column and value, a column, operator and value, or an object of equalities. whereIn, whereNot, whereNull, whereBetween and whereExists cover the rest of the common predicates. Grouped conditions use a callback so the parentheses land in the right place.

db("posts")
  .where("published", true)
  .andWhere((qb) => {
    qb.where("title", "ilike", `%${term}%`).orWhere("body", "ilike", `%${term}%`);
  });

Use .first() when you expect a single row, and pluck when you want a flat array of one column.

const user = await db("users").where({ email }).first();
const emails = await db("users").pluck("email");

Joins, aggregates and group by

Joins read the same way they do in SQL: a table, then the column pair that links it.

const rows = await db("users as u")
  .join("posts as p", "p.author_id", "u.id")
  .whereNot("p.status", "draft")
  .groupBy("u.id", "u.email")
  .select("u.email")
  .count("p.id as post_count")
  .max("p.created_at as last_post_at")
  .orderBy("post_count", "desc")
  .limit(10);

join is an inner join, leftJoin keeps unmatched rows from the first table, and rightJoin and fullOuterJoin are available on dialects that support them. The join condition can also be a callback when it needs more than one clause.

Aggregates use .count(), .sum(), .avg(), .min() and .max(). Two details catch people out. First, any non-aggregated column in the select must appear in groupBy. Second, on Postgres the count and sum values come back as strings to avoid integer overflow, so cast or parse them when you need numbers.

const { count } = await db("users").count("* as count").first();
const total = Number(count);

The returning clause

Inserting a row usually means wanting the generated primary key or the server-side defaults. On Postgres and MSSQL, .returning() asks the database to send the row back.

const [user] = await db("users")
  .insert({ email, display_name: displayName })
  .returning(["id", "email", "created_at"]);

Updates and deletes can return rows too:

const updated = await db("posts")
  .where({ id })
  .update({ title, updated_at: db.fn.now() })
  .returning("*");

MySQL and older SQLite do not support RETURNING. There, the insert call resolves to the new id, and you issue a follow-up select. Knowing which dialect you target matters, because this is one of the places where the portability promise has limits.

Upserts and conflict handling

A common pattern is “insert this row, but update it if it already exists”. Knex expresses it with onConflict, which maps to ON CONFLICT on Postgres and SQLite and to ON DUPLICATE KEY UPDATE on MySQL.

await db("users")
  .insert({ email, display_name: displayName, last_seen_at: db.fn.now() })
  .onConflict("email")
  .merge({
    display_name: displayName,
    last_seen_at: db.fn.now(),
  });

onConflict takes the unique column or columns that define the conflict. merge updates the listed columns from the new row, while ignore skips the insert entirely. This is the safe way to make a sync or webhook handler idempotent, because the database resolves the race between two concurrent writers rather than your application code.

Transactions

A transaction groups statements so they commit or roll back together. db.transaction passes a transaction object to the callback, and every statement inside must use it.

await db.transaction(async (trx) => {
  const account = await trx("accounts").where({ id: fromId }).first();

  if (!account || account.balance_cents < 5000) {
    throw new Error("insufficient_funds");
  }

  await trx("accounts").where({ id: fromId }).decrement("balance_cents", 5000);
  await trx("accounts").where({ id: toId }).increment("balance_cents", 5000);
});

Throwing rolls the transaction back and rejects the promise. Returning commits it. The most common mistake is mixing trx and db inside the same block: queries issued on db use a different pooled connection and run outside the transaction, so they are not rolled back.

For manual control, db.transaction() without a callback returns a transaction object with commit and rollback methods. Prefer the callback form; it is harder to leak a connection by forgetting to commit.

Raw queries when you need them

The builder cannot express everything, and it does not try to. knex.raw runs a SQL string with bound values.

const result = await db.raw(
  `select date_trunc('day', created_at) as day, count(*) as signups
   from users
   where created_at >= ?
   group by 1
   order by 1`,
  [since],
);

const rows = result.rows;

Always use ? placeholders and pass the values in the array. Interpolating them into the string reintroduces exactly the injection risk the builder exists to remove. You can embed a raw fragment inside a builder chain with whereRaw or select(db.raw(...)) when only part of the query needs hand-written SQL.

Window functions, recursive CTEs, COPY and dialect-specific operators are all good reasons to drop to raw. A ranking query, for example, is clearer written out than assembled from builder fragments:

const { rows } = await db.raw(
  `select email, score,
          row_number() over (order by score desc) as rank
   from leaderboard
   where season = ?`,
  [season],
);

Keep such fragments small and commented, and prefer the builder for everything around them.

Connection pooling

Every query runs through a pool of connections managed by tarn.js. The default is a minimum of two and a maximum of ten, which is reasonable for a single process but needs tuning in production.

const db = knex({
  client: "pg",
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 10, acquireTimeoutMillis: 30_000 },
});

Size the pool from the database’s max_connections, divided across all application instances. A pool that is too large is as harmful as one that is too small: too many connections exhaust the server’s memory and process table. Put a pooler such as PgBouncer in front when you run many instances or serverless functions.

Call await db.destroy() when the process shuts down. Without it, open connections keep the event loop alive and a graceful shutdown hangs.

Pairing Knex with Objection.js

Knex returns plain rows, so if you want models, relations and lifecycle hooks, add Objection.js. It is built directly on Knex and reuses the same connection.

import { Model } from "objection";
import { db } from "./db";

Model.knex(db);

class User extends Model {
  static tableName = "users";

  static relationMappings = {
    posts: {
      relation: Model.HasManyRelation,
      modelClass: Post,
      join: { from: "users.id", to: "posts.author_id" },
    },
  };
}

const user = await User.query()
  .withGraphFetched("posts")
  .findOne({ email });

This layering is the pragmatic answer for teams that want an ORM but dislike heavy abstraction: Knex handles SQL and migrations, Objection adds the object model, and you can drop back to Knex at any point for a query that the model layer does not fit.

You still write SQL

The most important thing to internalise about Knex is that it does not hide SQL. It reorders it, parameterises it and quotes it, but the statement it produces is exactly what you would have written.

That has two consequences. The first is good: reading a Knex chain tells you the query, and debugging means printing .toSQL() and reading the plan, not guessing at generated SQL.

The second is a responsibility: a builder will not save you from a missing index, a SELECT * over a wide table or an accidental cross join. You still design the schema, add the indexes and check EXPLAIN ANALYZE. Knex removes string plumbing, not the need to understand the database.

Best practices

• Create one Knex instance and import it; never call knex() per module.
• Bind every value as a parameter and use ? placeholders in raw queries.
• Version every schema change as a migration with a real down method.
• Split risky changes into separate migrations: add and backfill first, constrain later.
• Use .returning() where the dialect supports it, and fall back to a follow-up select otherwise.
• Always use the transaction object inside db.transaction, never the top-level instance.
• Size the connection pool from the database limit and call db.destroy() on shutdown.
• Cast Postgres count and sum results to numbers before doing arithmetic.
• Print .toSQL() when a query surprises you, and confirm performance with EXPLAIN ANALYZE.

Common mistakes

• Mixing trx and db in one transaction, so part of the work escapes the rollback.
• Interpolating values into knex.raw strings instead of using placeholders.
• Forgetting groupBy for non-aggregated columns and getting a SQL error.
• Treating the string returned by count as a number and producing "10" + 1.
• Assuming .returning() works identically on MySQL and SQLite.
• Leaving a pool unbounded or larger than the database can handle.
• Creating a new Knex instance per request and exhausting connections.
• Editing a production schema by hand and letting environments drift.
• Ignoring the down method until a rollback is actually needed.

Where to go next

Knex is a good place to stop if you like working close to SQL, and a good foundation if you later want an ORM on top. Read the SQL guide to sharpen the statements the builder produces, and the PostgreSQL guide for indexes, transactions and query plans. If you would rather have a typed schema-first client, Prisma generates one for you, while Drizzle ORM stays closer to the builder style with full TypeScript inference.