LaoHandDeveloper Cheatsheet

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
knexsqlQuery Buildernodejs

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

ParameterEffectExample
.where(col, op, val)条件查询，三参形式可写比较符knex('users').where('age', '>', 18)
.whereNull(col)生成 IS NULLknex('users').whereNull('deleted_at')
.onConflict(col).merge()upsert：冲突时更新已有行.onConflict('email').merge()
.returning(id)插入后返回主键（仅 PG / SQLite）.insert(data).returning("id")
.del()删除匹配行knex('users').where('id', 1).del()
.truncate()清空整表并重置自增（不可回滚）knex('users').truncate()
knex.raw(sql, bindings)原生 SQL，? 为参数占位符knex.raw("SELECT * FROM users WHERE id = ?", [1])
.transaction(async (trx) => {})自动提交/回滚的事务包装await knex.transaction(async (trx) => { ... })
trx.savepoint(cb)保存点：内层失败只回滚该段const sp = await trx.savepoint(async (s) => { ... })
.having(knex.raw(...))分组后按聚合值过滤.having(knex.raw("count(*) > ?", [5]))
.debug(true)把生成的 SQL 打到控制台knex('users').debug(true)
.toSQL().toNative()查看 SQL 与绑定值，不执行knex("users").where("id",1).toSQL().toNative()

Common pitfalls

Symptom把用户输入拼进 knex.raw 后站点出现 SQL 注入风险。

Cause字符串拼接绕过了 Knex 的参数绑定机制。

Fix改用 knex.raw("... WHERE id = ?", [id])，或直接用链式 API。

Symptom事务执行了一部分后报错，之前的插入也没了。

Cause事务回调内的查询忘记 await，事务在操作完成前就提交了。

Fix确保回调内每个查询都 await；否则部分操作会跑在事务之外。

Symptom误用 truncate 清空了生产表且无法回滚。

Causetruncate 是不可回滚的 DDL，且通常不接受 where。

Fix生产环境改用 .where(...).del() 并先在小批量上验证。

Symptomupsert 后原有记录的其他字段被清空了。

Cause.merge() 不指定列时会覆盖整行。

Fix.merge(['name', 'updated_at']) 显式列出要更新的列。

Symptom同一段查询在 PostgreSQL 正常、在 MySQL 报错找不到 returning。

Causereturning 是 PG / SQLite 特性，MySQL 不支持。

Fix按方言分支处理，或改用 insert 后单独查询。

Symptom长连接闲置后首次查询报连接错误。

Causeknex 实例被跨进程共享，连接已在别处失效。

Fix每个进程各自初始化 knex 实例，不共享连接池对象。

Troubleshooting

  1. 1先看生成的 SQL 对不对

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

    不执行只打印 SQL 与绑定值，排查方言差异最快。

  2. 2确认字段名与表结构

    await knex.schema("users").columnInfo()

    字段拼错时 SQL 会在运行时报数据库错误，先核对结构。

  3. 3排查依赖版本冲突

    npm ls knex --depth=0

    确认装的是哪一版 knex 与哪一版数据库驱动。

  4. 4事务失败时定位是哪一步

    await knex.transaction(async (trx) => { await trx("a").insert(x); await trx("b").insert(y) })

    逐步注释找出失败的那条；配合保存点可只回滚其中一段。

  5. 5查看整棵依赖树

    npm why knex

    确认是否被某个上层依赖带入，避免多版本共存。

Command Examples

Use ? placeholders to avoid SQL injection

knex('users').where('name', '?', name).toString() // 反例：knex.raw('SELECT * FROM users WHERE name = ' + name)

链式 API 自动绑定参数。确需原生 SQL 时用 knex.raw("... WHERE id = ?", [1])，绝不要把变量直接拼进语句。

Output

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

Debug the generated SQL

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

需要看控制台输出可加 .debug(true)。生成结果与预期不符时，先看 SQL 再看数据，往往能立刻定位是方言差异还是逻辑问题。

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("列名") 指定冲突键，后跟 .merge() 更新已有记录或 .ignore() 跳过。注意 .merge() 不加列表会覆盖整行，重要字段建议显式列出。

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 }) }) })

保存点让内层失败只回滚该段而保留外层成果；没有它，一次失败会撤销整个事务的改动。回滚时抛出的错误即触发点。

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.

Related Cheatsheets

6 related cheatsheets

MySQL Cheatsheet - Queries, Joins, Users & IndexesA MySQL command handbook for DBAs, backends, and data analysts, covering database & table management, CRUD, index optimization and slow-query localization with EXPLAIN, user privileges, logical backup & recovery, and replication — highlighting operations that can lock tables or stall replication.PostgreSQL Cheatsheet - PostgreSQL Command ReferenceA PostgreSQL command handbook for backend engineers, DBAs, and data analysts, covering connection & role management, database & table operations, EXPLAIN analysis, lock & transaction troubleshooting, pg_dump/pg_restore backup & recovery, and streaming replication, with paths for locating lock waits and table bloat.Redis Cheatsheet - Keys, Strings, Hash & Expire CommandsA Redis command handbook for the cache/backend and its operators, covering string and per-data-structure operations, key space and TTL management, RDB/AOF persistence trade-offs, memory and big-key inspection, slow query monitoring, cluster & replication, and practical debugging of hot keys, big keys, and cache puncturing/cascading.MongoDB Cheatsheet - MongoDB Command ReferenceA MongoDB reference for backend engineers, covering database & collection management, document CRUD, query operators, index strategies that keep queries off full collection scans, the aggregation pipeline, and replica-set vs sharded-cluster responsibilities.Neo4j Cypher Cheatsheet - Neo4j Graph Database Command ReferenceA Neo4j Cypher reference for working with graph data where relationships matter more than rows, covering node & relationship creation, pattern matching, updates & deletes, aggregation and ordering, constraints & indexes, and traversal-style graph queries.SQL JOIN Cheatsheet - SQL JOIN Query ReferenceA SQL JOIN reference for writing multi-table queries correctly, covering INNER, LEFT/RIGHT/FULL OUTER, CROSS, self-joins, USING/natural joins, and when a correlated subquery with EXISTS beats a JOIN — plus the row-duplication pitfall of joins on one-to-many tables.

Official References

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

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.

Contact Us