Knex.js Cheatsheet - Knex SQL Query Builder Reference

All essential Knex.js commands organized by use case, with 50+ entries you can copy and run directly. Find the right command fast when you need it.

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

Back to Databases

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

Tips

Knex 支持 PostgreSQL、MySQL、MariaDB、SQLite、MSSQL，连接时指定 client。
查询构建器返回 Promise，需要 await 或 .then() 才会执行。
knex.raw("SQL", bindings) 执行原生 SQL，参数绑定可防 SQL 注入。
Migration 用 knex.migrate.make() 创建，knex.migrate.latest() 执行迁移。
连接池默认启用，配置 pool: { min: 2, max: 10 } 调整大小。

Official References

Commands are compiled from the official docs below. Click to verify the latest usage.

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.