SQL query builder for PostgreSQL, MySQL, SQLite, and MSSQL -- fluent queries, schema builder, migrations, seeds, transactions, raw queries
Knex.js Patterns
Quick Guide: Use Knex.js (v3.x) as a SQL query builder for PostgreSQL, MySQL, SQLite, and MSSQL. Initialize the knex instance once per application (it creates a connection pool internally via tarn.js). Set pool min: 0 so idle connections are released. Always use parameterized bindings (? for values, ?? for identifiers) in knex.raw() -- never interpolate user input. Wrap multi-table writes in knex.transaction() and always return or await the promise (otherwise the transaction hangs). Use .returning() on PostgreSQL/MSSQL for inserted/updated rows -- it is a no-op on MySQL/SQLite. Call knex.destroy() on graceful shutdown to drain the pool.
<critical_requirements>
CRITICAL: Before Using This Skill
All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)
(You MUST initialize the knex instance ONCE per application and reuse it -- creating multiple instances leaks connection pools)
(You MUST use parameterized bindings (? for values, ?? for identifiers) in ALL knex.raw() calls -- string interpolation causes SQL injection)
(You MUST return or await the promise inside knex.transaction() handlers -- failing to do so causes the transaction connection to hang indefinitely)
(You MUST call on graceful shutdown -- orphaned pools prevent the Node.js process from exiting)
Transactions with async/await and isolation levels
Raw queries with ? value bindings and ?? identifier bindings
Subqueries as callbacks or builder instances
Batch insert with batchInsert() and chunking
TypeScript table type augmentation
Connection pool tuning (min, max, acquireTimeout, lifetime)
When NOT to use:
You need a full ORM with model relationships, lifecycle hooks, and identity maps -- use your ORM solution instead
You need database-specific features Knex doesn't abstract (e.g., PostgreSQL LISTEN/NOTIFY, MySQL fulltext indexes) -- use knex.raw() for those
Your project already uses a different query layer or ORM and doesn't need a second one
Philosophy
Knex is a SQL query builder, not an ORM. The core principle: you write SQL, Knex just makes it safer and more portable.
Core principles:
One instance, one pool -- Initialize knex once. The instance manages a connection pool (tarn.js). Never create multiple knex instances pointing at the same database.
Parameterize everything -- Use ? bindings for values and ?? for identifiers. Never interpolate strings into queries.
Migrations are the source of truth -- Schema changes happen through migrations, not ad-hoc knex.schema calls in application code.
Transactions for consistency -- Any operation touching multiple tables or needing atomicity must be wrapped in knex.transaction().
Knex is dialect-aware, not dialect-hiding -- Knex normalizes common SQL, but database-specific features (e.g., .returning() on PostgreSQL, ON DUPLICATE KEY on MySQL) must be handled per-dialect.
Core Patterns
Pattern 1: Knex Initialization
Initialize once per application. The knex instance manages a connection pool internally. See examples/core.md for full examples.
// Good Example -- Proper initialization with pool tuningimport knex from"knex";
constPOOL_MIN = 0;
constPOOL_MAX = 10;
constACQUIRE_TIMEOUT_MS = 30_000;
functioncreateDatabase() {
const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
thrownewError("DATABASE_URL environment variable is required");
}
returnknex({
client: "pg",
connection: connectionString,
pool: { min: POOL_MIN, max: POOL_MAX },
acquireConnectionTimeout: ACQUIRE_TIMEOUT_MS,
});
}
export { createDatabase };
Why good: Single instance, environment variable for connection string, pool min: 0 releases idle connections, named constants
// Bad Example -- Multiple instances, hardcoded configimport knex from"knex";
functiongetUsers() {
const db = knex({ client: "pg", connection: "postgres://localhost/mydb" });
returndb("users").select("*");
// Connection pool leaked -- db.destroy() never called
}
Why bad: Creates a new pool per call (leaks connections), hardcoded connection string, select("*") fetches unnecessary columns
Pattern 2: Query Builder Basics
Fluent API for building SELECT queries. See examples/core.md for joins, groupBy, having.
// Good Example -- Typed query with explicit columnsconstACTIVE_STATUS = "active";
constPAGE_SIZE = 25;
const users = await db<User>("users")
.select("id", "name", "email")
.where("status", ACTIVE_STATUS)
.orderBy("created_at", "desc")
.limit(PAGE_SIZE);
Why good: Explicit column selection, typed result, named constants for status and page size
// Bad Example -- select(*) with string interpolationconst users = awaitdb("users").select("*").whereRaw(`status = '${status}'`); // SQL INJECTION
Pattern 3: Insert / Update / Delete with Returning
.returning() works on PostgreSQL, MSSQL, CockroachDB, and SQLite 3.35+. MySQL ignores it silently. See examples/core.md.
// Good Example -- Insert with returning (PostgreSQL)const [inserted] = awaitdb("users")
.insert({ name: "Alice", email: "alice@example.com" })
.returning(["id", "created_at"]);
// Good Example -- Update with returningconst [updated] = awaitdb("users")
.where("id", userId)
.update({ name: newName, updated_at: db.fn.now() })
.returning(["id", "name", "updated_at"]);
Why good:.returning() avoids a separate SELECT, db.fn.now() uses database-native timestamp
// Bad Example -- Forgetting returning() on PostgreSQLawaitdb("users").insert({ name: "Alice" });
// Returns [0] on PostgreSQL -- the row count, not the inserted data// Developer expects the inserted row but gets a useless number
Why bad: Without .returning(), PostgreSQL insert returns row count (not data), forcing an extra SELECT query
Pattern 4: Raw Queries with Safe Bindings
Use ? for value bindings and ?? for identifier bindings. See examples/core.md.
// Good Example -- Parameterized raw queryconstMIN_ORDER_COUNT = 5;
const results = await db.raw(
`SELECT ??, COUNT(*) as order_count
FROM ??
WHERE ?? > ?
GROUP BY ??
HAVING COUNT(*) >= ?`,
[
"users.id",
"orders",
"orders.created_at",
cutoffDate,
"users.id",
MIN_ORDER_COUNT,
],
);
Why good:?? for identifiers, ? for values, all user input parameterized
// Bad Example -- String concatenation in raw queryconst results = await db.raw(`SELECT * FROM users WHERE name = '${name}'`);
// SQL INJECTION: name = "'; DROP TABLE users; --"
Can the query builder express this?
-- YES -> Use the query builder (portable, type-safe)
-- NO -> Does it use database-specific syntax?
-- YES -> Use db.raw() with parameterized bindings
-- NO -> Is it a performance-critical query needing exact SQL?
-- YES -> Use db.raw() with parameterized bindings
-- NO -> File an issue or use a subquery callback
Transaction vs No Transaction
Does this operation modify multiple tables?
-- YES -> Use db.transaction()
Does this read need snapshot isolation?
-- YES -> Use db.transaction({ isolationLevel: "repeatable read" })
Is this a single INSERT/UPDATE/DELETE?
-- YES -> No transaction needed (single statement is atomic)
.returning() Behavior by Database
Which database are you targeting?
-- PostgreSQL -> .returning() works, returns array of objects
-- MSSQL -> .returning() works, returns array of objects
-- SQLite 3.35+ -> .returning() works
-- MySQL -> .returning() is silently ignored, insert returns [insertId]
-- Oracle -> .returning() works
</decision_framework>
<red_flags>
RED FLAGS
High Priority Issues:
String interpolation in knex.raw() or .whereRaw() -- SQL injection vulnerability; always use ? / ?? bindings
Creating multiple knex instances pointing at the same database -- leaks connection pools, exhausts database connections
Not returning/awaiting the promise inside knex.transaction() handler -- transaction connection hangs indefinitely
Missing knex.destroy() on shutdown -- orphaned pool prevents process exit, connections leak
Running knex.schema calls in application code instead of migrations -- schema state becomes unpredictable across environments
Medium Priority Issues:
Using select("*") in production queries -- fetches unnecessary data, increases memory usage, breaks when columns are added
Forgetting .returning() on PostgreSQL inserts -- returns useless row count [0] instead of inserted data
Not setting pool min: 0 -- default min: 2 keeps stale connections alive during low-traffic periods
Missing WHERE clause on .update() or .del() -- updates/deletes ALL rows in the table
Using KEYS-style patterns without pagination -- db("table").select() with no limit loads entire table into memory
Common Mistakes:
Expecting .returning() to work on MySQL -- it is silently ignored; use insertId from the result instead
Using .timeout() on the query without { cancel: true } -- times out the Node.js side but the query keeps running on the database server
Running migrations with disableTransactions: true and assuming rollback works -- without a transaction, a failed migration leaves the database in a partial state
Assuming knex.schema.hasTable() and knex.schema.createTable() are atomic -- another process can create the table between the check and the create
Calling trx.commit() or trx.rollback() AND returning a promise -- double-completion causes unpredictable behavior
Gotchas & Edge Cases:
knex.raw() returns a { rows, fields } object on PostgreSQL but a flat array on MySQL -- access .rows for PostgreSQL or destructure accordingly
.timestamps(true, true) creates created_at and updated_at with defaultTo(knex.fn.now()) -- but updated_at is NOT automatically updated on row changes; you must set it yourself in UPDATE queries or use a database trigger
.first() returns undefined (not null) when no row matches -- check with if (!result) not if (result === null)
knex.batchInsert() wraps all chunks in a single transaction by default -- if one chunk fails, all previous chunks are rolled back
Column names in .returning() must match the database column names exactly (case-sensitive on PostgreSQL)
.whereIn("id", []) with an empty array generates WHERE 1 = 0 (always false) -- Knex handles it but it can be surprising in logs
Migrations run in filename-sorted order -- ensure timestamps are consistent (don't mix manual names with generated timestamps)
knex.fn.now() is evaluated by the database server, not Node.js -- useful for consistency but means you can't mock it in tests without stubbing the query
</red_flags>
<critical_reminders>
CRITICAL REMINDERS
All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)
(You MUST initialize the knex instance ONCE per application and reuse it -- creating multiple instances leaks connection pools)
(You MUST use parameterized bindings (? for values, ?? for identifiers) in ALL knex.raw() calls -- string interpolation causes SQL injection)
(You MUST return or await the promise inside knex.transaction() handlers -- failing to do so causes the transaction connection to hang indefinitely)
(You MUST call knex.destroy() on graceful shutdown -- orphaned pools prevent the Node.js process from exiting)
Failure to follow these rules will cause SQL injection vulnerabilities, connection pool exhaustion, hanging transactions, and zombie processes.
