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