Knex.js Cheatsheet - Knex SQL Query Builder Reference

For Node.js developers who want to write SQL fluently in JavaScript and switch databases without rewriting queries. The value of Knex-transpiled is a chainable API with automatic parameter binding, but you must call .then/await for it to execute and understand when a raw() escape hatch beats the builder. By the end you can build CRUD, joins, and groupBy chains deterministically, wrap multi-statement logic in a transaction with rollback, and confirm the generated SQL with debug/toString.

Databases·50 commands·Last updated 2026-07-21

Typical Use Case

For developers who want to write SQL in JavaScript and keep one codebase across PostgreSQL, MySQL and SQLite. Knex earns its keep with chainable queries, automatic parameter binding and cross-database migrations; it costs you in dialect gaps (returning exists only on PG/SQLite). This sheet covers queries, writes, joins, aggregation, transactions, schema building and raw SQL. Read the transaction and injection-prevention sections back to back.

Query Data 10

knex("users").select("*")

SELECT * FROM users

knex("users").select("name", "email")

Select specific columns

knex("users").where("age", ">", 18)

WHERE age > 18

knex("users").where({ status: "active" })

WHERE status = active

knex("users").whereNull("deleted_at")

WHERE deleted_at IS NULL

knex("users").orderBy("created_at", "desc")

ORDER BY created_at DESC

knex("users").limit(10).offset(20)

LIMIT 10 OFFSET 20 (pagination)

knex("users").first()

Fetch the first row

knex("users").pluck("id")

Array of one column [1, 2, 3]

knex("users").distinct("country")

SELECT DISTINCT country

Insert Data 5

knex("users").insert({ name: "John", email: "john@example.com" })

Insert one row

knex("users").insert([{ name: "A" }, { name: "B" }])

Batch insert

knex("users").insert(data).returning("id")

Insert and return id (PG/SQLite)

knex("users").insert(data).onConflict("email").merge()

Upsert on conflict

knex("users").insert(data).onConflict("email").ignore()

Ignore on conflict

Update & Delete 5

knex("users").where("id", 1).update({ name: "Jane" })

Update a row

knex("users").where("id", 1).update({ views: knex.raw("views + 1") })

Increment update

knex("users").where("id", 1).update({ deleted_at: knex.fn.now() })

Soft-delete pattern

knex("users").where("id", 1).del()

Delete a row

knex("users").truncate()

Empty table (reset autoincrement)

Join 6

knex("users").join("posts", "users.id", "posts.user_id")

INNER JOIN

knex("users").leftJoin("posts", "users.id", "posts.user_id")

LEFT JOIN

knex("users").rightJoin("posts", "users.id", "posts.user_id")

RIGHT JOIN

knex("users").fullOuterJoin("posts", "users.id", "posts.user_id")

FULL OUTER JOIN

knex("users").crossJoin("posts")

CROSS JOIN (cartesian)

knex("users").join("posts", function() { this.on("users.id", "=", "posts.user_id").andOn("users.active", "=", knex.raw("?", [1])) })

Multi-condition JOIN

Aggregate & Group 8

knex("users").count("id as total")

COUNT(id) AS total

knex("users").count("* as total").first()

Get total row count

knex("orders").sum("amount")

SUM(amount)

knex("users").max("age")

MAX(age)

knex("users").min("age")

MIN(age)

knex("orders").avg("amount")

AVG(amount)

knex("users").groupBy("status")

GROUP BY status

knex("users").groupBy("status").having(knex.raw("count(*) > ?", [5]))

HAVING count > 5

Transactions 4

knex.transaction(async (trx) => { await trx("users").insert(data) })

Auto commit/rollback

const trx = await knex.transaction(); await trx.commit() / trx.rollback()

Manual transaction control

const sp = await trx.savepoint(async (sp) => { /* ... */ })

Savepoint (partial rollback)

await knex.transaction(cb, { isolationLevel: "read committed" })

Set isolation level

Schema Builder 7

knex.schema.createTable("users", (t) => { t.increments("id"); t.string("name") })

Create a table

knex.schema.dropTableIfExists("users")

Drop table if exists

knex.schema.alterTable("users", (t) => { t.string("email") })

Alter table

t.integer("age").unsigned().notNullable().defaultTo(0)

Chained column constraints

t.timestamps(true, true)

Add created_at/updated_at

knex.schema.table("users", (t) => { t.index(["name", "status"]) })

Create composite index

knex.schema.table("users", (t) => { t.unique("email") })

Add unique constraint

Raw & Debug 5

knex.raw("SELECT * FROM users WHERE id = ?", [1])

Raw SQL (parameterized)

knex("users").where("active", true).debug(true)

Log SQL to console

knex("users").where("id", 1).toSQL().toNative()

Show generated SQL & bindings

knex("users").where("id", 1).toString()

Return SQL string

knex("users").columnInfo()

Get table schema info

Parameter matrix

Parameter	Effect	Example
`.where(col, op, val)`	Conditional query; the three-argument form writes a comparison operator.	`knex('users').where('age', '>', 18)`
`.whereNull(col)`	Generates IS NULL.	`knex('users').whereNull('deleted_at')`
`.onConflict(col).merge()`	upsert: update the existing row on conflict.	`.onConflict('email').merge()`
`.returning(id)`	Returns the primary key after insert (PG / SQLite only).	`.insert(data).returning("id")`
`.del()`	Deletes matching rows.	`knex('users').where('id', 1).del()`
`.truncate()`	Empties the whole table and resets the auto-increment (not rollbackable).	`knex('users').truncate()`
`knex.raw(sql, bindings)`	Raw SQL, with ? as the parameter placeholder.	`knex.raw("SELECT * FROM users WHERE id = ?", [1])`
`.transaction(async (trx) => {})`	A transaction wrapper that auto-commits/rolls back.	`await knex.transaction(async (trx) => { ... })`
`trx.savepoint(cb)`	Savepoint: an inner failure rolls back only that segment.	`const sp = await trx.savepoint(async (s) => { ... })`
`.having(knex.raw(...))`	Filter by aggregate value after grouping.	`.having(knex.raw("count(*) > ?", [5]))`
`.debug(true)`	Prints the generated SQL to the console.	`knex('users').debug(true)`
`.toSQL().toNative()`	Inspect the SQL and bindings without executing.	`knex("users").where("id",1).toSQL().toNative()`

Common pitfalls

SymptomInterpolating user input into knex.raw introduces SQL injection risk.

CauseString concatenation bypasses the parameter binding mechanism of Knex.

FixUse knex.raw("... WHERE id = ?", [id]) instead, or go straight to the chained API.

SymptomA transaction runs partway, errors, and earlier inserts are gone too.

CauseA query inside the transaction callback is missing await, so the transaction commits before the operation completes.

FixEnsure every query inside the callback is awaited; otherwise some operations run outside the transaction.

SymptomA mistaken truncate wiped a production table with no rollback.

Causetruncate is a non-rollbackable DDL and usually does not accept a where clause.

FixIn production use .where(...).del() instead, and validate on a small batch first.

SymptomAfter an upsert the other fields of the existing record are cleared.

Cause.merge() without columns overwrites the whole row.

FixList the columns to update explicitly: .merge(['name', 'updated_at']).

SymptomThe same query works on PostgreSQL but MySQL errors that returning cannot be found.

Causereturning is a PG / SQLite feature; MySQL does not support it.

FixBranch by dialect, or do a separate query after insert.

SymptomThe first query after a long idle connection reports a connection error.

CauseThe knex instance is shared across processes and the connection already went stale elsewhere.

FixInitialize a knex instance per process and never share the connection pool object.

Troubleshooting

1First check whether the generated SQL is right.
knex("users").where("id", 1).toSQL().toNative()
Prints only the SQL and bindings without executing — the fastest way to debug dialect differences.
2Confirm field names and table structure.
await knex.schema("users").columnInfo()
A misspelled field only surfaces as a database error at runtime; verify the structure first.
3Debug dependency version conflicts.
npm ls knex --depth=0
Confirm which knex version and which database driver are installed.
4Locate which step failed when a transaction errors.
await knex.transaction(async (trx) => { await trx("a").insert(x); await trx("b").insert(y) })
Comment out steps one by one to find the failing one; savepoints let you roll back only that segment.
5View the entire dependency tree.
npm why knex
Confirm whether it was pulled in by an upstream dependency, avoiding multiple versions coexisting.

Command Examples

Use ? placeholders to avoid SQL injection

knex('users').where('name', '?', name).toString()
// counter-example: knex.raw('SELECT * FROM users WHERE name = ' + name)

The chained API binds parameters automatically. When you truly need raw SQL use knex.raw("... WHERE id = ?", [1]) — never concatenate a variable directly into the statement.

Output

select * from `users` where `name` = 'tom'

Debug the generated SQL

knex('users').where('active', true).toSQL().toNative()

Add .debug(true) to see console output. When the generated result does not match expectations, look at the SQL first and then the data — this usually pinpoints immediately whether it is a dialect difference or a logic problem.

Output

{
  sql: 'select * from `users` where `active` = ?',
  bindings: [ true ]
}

Upsert: update or ignore on conflict

await knex('users')
  .insert({ id: 1, name: 'ali' })
  .onConflict('id')
  .merge()

onConflict("column") sets the conflict key, followed by .merge() to update existing records or .ignore() to skip. Note that .merge() without a list overwrites the whole row, so list important fields explicitly.

Transactions and savepoints

await knex.transaction(async (trx) => {
  await trx('users').insert({ id: 1 })
  const sp = await trx.savepoint(async (s) => {
    await s('orders').insert({ id: 1 })
  })
})

A savepoint lets an inner failure roll back only that segment while keeping the outer results; without it, one failure undoes the whole transaction. The error thrown during rollback marks the trigger point.

Common Pitfalls

Concatenating strings inside knex.raw is a SQL injection hole — use ? placeholders with a bindings array.
truncate empties the table and resets identity, is a non-rollbackable DDL, and normally takes no where clause; never reach for it on production tables.
.merge() without a column list overwrites the entire row and can blank fields you did not mention; name critical columns explicitly.
Every query inside a transaction callback must be awaited; forgetting causes the transaction to commit before the work finishes.
A knex instance cannot be shared across processes — its pooled connections die. Initialize per process.

Tips

Knex supports PostgreSQL, MySQL, MariaDB, SQLite, MSSQL — set client on connect.
The query builder returns a Promise; await or .then() to execute.
knex.raw("SQL", bindings) runs raw SQL; bound params prevent SQL injection.
Create migrations with knex.migrate.make() and run them with knex.migrate.latest().
Connection pooling is on by default; tune with pool: { min: 2, max: 10 }.

FAQ

How does Knex prevent SQL injection?

Do not concatenate SQL strings yourself — use the chainable API, which binds parameters automatically: knex("users").where("id", 1) rather than knex.raw("SELECT * FROM users WHERE id = " + id). When raw SQL is genuinely needed, use knex.raw("... WHERE id = ?", [1]) with ? placeholders and a values array, never interpolating variables. Add .debug(true) to any chain to print the generated SQL for review.

What is the difference between truncate and del?

del() removes only the matching rows, leaving the schema and auto-increment sequence intact. truncate() empties the whole table and resets the identity counter; in most databases it is implemented as a faster bulk operation, but it is a non-rollbackable DDL statement and normally takes no where clause. truncate is handy for test data, but it wipes everything — do not run it casually in production.

How do I debug a Knex query?

Add .debug(true) to the chain to print the generated SQL to the console — the quickest option. Use .toSQL() for the SQL object and .toSQL().toNative() for the driver-specific implementation and bound values, which is invaluable when diagnosing dialect differences. columnInfo() returns the table structure so you can verify column names.

What problem do savepoints solve?

They let you roll back part of a transaction while keeping the rest. Inside trx.savepoint(async (sp) => { ... }) a failure reverts only to that point and leaves earlier changes in the outer transaction intact. Without nested savepoints, one failure discards every change in the transaction, which is often not what you want.

How does Knex support multiple databases?

You pick the client at init time, such as knex({ client: "pg" }) or "mysql2" or "sqlite3", and the same query code runs on PostgreSQL, MySQL or SQLite. The trade-off is that dialects differ in capability: returning works only on PostgreSQL and SQLite, and fullOuterJoin is not universally supported, which matters when writing portable code.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Official Docs

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.