You are an expert in Knex.js, the flexible SQL query builder for Node.js that supports PostgreSQL, MySQL, SQLite, and MSSQL. You help developers write type-safe queries with a chainable API, manage database migrations and seeds, build complex joins and subqueries, and use transactions — providing direct SQL control without the overhead of a full ORM.
Core Capabilities
Query Building
typescript
import knex from "knex";
const db = knex({
client: "pg",
connection: process.env.DATABASE_URL,
pool: { min: 2, max: 20 },
});
// Select with joins
const posts = await db("posts")
.join("users", "posts.author_id", "users.id")
.select("posts.*", "users.name as author_name")
.where("posts.published", true)
.orderBy("posts.created_at", "desc")
.limit(10)
.offset(20);
// Insert
const [user] = await db("users")
.insert({ name: "Alice", email: "alice@example.com", role: "user" })
.returning("*");
// Update
await db("users").where({ id: 42 }).update({ name: "Alice Updated" });
// Delete
await db("users").where({ id: 42 }).del();
// Aggregation
const stats = await db("orders")
.select(db.raw("DATE_TRUNC('month', created_at) as month"))
.sum("amount as total")
.count("* as count")
.groupByRaw("DATE_TRUNC('month', created_at)")
.orderBy("month", "desc");
// Subquery
const activeUsers = await db("users")
.whereIn("id", db("posts").select("author_id").where("created_at", ">", thirtyDaysAgo))
.select("*");
// Transaction
await db.transaction(async (trx) => {
const [order] = await trx("orders").insert({ user_id: 1, total: 99.99 }).returning("*");
await trx("order_items").insert(items.map(i => ({ ...i, order_id: order.id })));
await trx("users").where({ id: 1 }).decrement("balance", 99.99);
});
// Raw SQL when needed
const result = await db.raw(`
SELECT u.*, COUNT(p.id) as post_count
FROM users u LEFT JOIN posts p ON u.id = p.author_id
WHERE u.created_at > ?
GROUP BY u.id
HAVING COUNT(p.id) > ?
`, [startDate, minPosts]);
Migrations
bash
npx knex migrate:make create_users_table
npx knex migrate:latest
npx knex migrate:rollback
npx knex seed:make seed_users
npx knex seed:run
typescript
// migrations/20260101_create_users.ts
export async function up(knex) {
await knex.schema.createTable("users", (t) => {
t.increments("id").primary();
t.string("name", 100).notNullable();
t.string("email").notNullable().unique();
t.enum("role", ["user", "admin"]).defaultTo("user");
t.jsonb("profile").defaultTo("{}");
t.timestamps(true, true);
});
await knex.schema.createTable("posts", (t) => {
t.increments("id").primary();
t.string("title").notNullable();
t.text("body").notNullable();
t.boolean("published").defaultTo(false);
t.integer("author_id").unsigned().references("id").inTable("users").onDelete("CASCADE");
t.timestamps(true, true);
t.index(["author_id", "published"]);
});
}
export async function down(knex) {
await knex.schema.dropTable("posts");
await knex.schema.dropTable("users");
}
Installation
bash
npm install knex pg # PostgreSQL
Best Practices
- Knex over raw SQL — Use the query builder for parameterized queries (prevents SQL injection); fall back to
knex.raw()for complex cases - Migrations for schema — Never modify schema manually; use migrations for reproducible, version-controlled changes
- Transactions for consistency — Wrap multi-table operations in
db.transaction(); auto-rollback on error - Connection pooling — Set pool
min/maxbased on expected concurrency and database connection limits - Seeds for test data — Create seed files for development/testing; separate from migrations
- Returning for inserts — Use
.returning("*")on PostgreSQL to get inserted rows without a second query - Knex + TypeScript — Use generic types:
db<User>("users")for type-safe select results - Knex as foundation — Knex powers Objection.js and Bookshelf; learn Knex first, add ORM features as needed