Verwendung
- Installationsbefehl kopieren und das Quellpaket vor der Installation in der eigenen Agentenumgebung prüfen.
- Benötigte Werkzeuge, Zugangsdaten und Hosts im Original prüfen.
- Eingabe und gewünschtes Ergebnis beschreiben und die Ausgabe mit den Quellen abgleichen.
npx skills add https://github.com/TerminalSkills/skills --skill knexVor dem Start
- Die Installation umfasst weder Modellabo noch API-Kontingent oder Dateizugriffsrechte.
- Sterne und Installationen zeigen Aktivität, keine unabhängige Qualitäts- oder Sicherheitsprüfung.
Aus dem Quell-Repository gerendert; Überschriften, Beispiele, Code, Tabellen, Links und Bilder bleiben erhalten.
Knex.js — SQL Query Builder for Node.js
Overview
Knex (knexjs.org, knex/knex) builds parameterized SQL through a chainable API and ships a migration and seed CLI. It is the layer under Objection.js and Bookshelf. Checked against knex 3.3.0 (26 June 2026; 3.0 dropped Node < 16, 3.2 added migration lifecycle hooks, 3.3 added MariaDB driver support). Queries below were run against SQLite with the same builder calls.
Instructions
Install and connect
npm install knex pg # PostgreSQL; other drivers: mysql2, better-sqlite3, sqlite3, tedious (MSSQL)
npx knex init -x ts # creates knexfile.ts (use `npx knex init` for knexfile.js)import knex from "knex";
export const db = knex({
client: "pg",
connection: process.env.DATABASE_URL,
pool: { min: 2, max: 20 },
});Use client: "better-sqlite3" with useNullAsDefault: true for SQLite. Call await db.destroy() when a script finishes, or the process hangs on the open pool.
Queries
interface User { id: number; name: string; email: string; role: string }
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);
const [user] = await db<User>("users")
.insert({ name: "Alice Morgan", email: "alice.morgan@northwind.io" })
.returning("*"); // PostgreSQL, SQLite 3.35+, MSSQL; not MySQL
await db("users").where({ id: user.id }).update({ name: "Alice M." });
await db("users").where({ id: user.id }).del();
const recent = await db("users")
.whereIn("id", db("posts").select("author_id").where("created_at", ">", thirtyDaysAgo));
const monthly = await db("orders")
.select(db.raw("DATE_TRUNC('month', created_at) as month")) // PostgreSQL function
.sum("amount as total").count("* as orders")
.groupByRaw("DATE_TRUNC('month', created_at)")
.orderBy("month", "desc");db<User>("users") types the result rows. On PostgreSQL count and sum come back as strings (bigint/numeric); cast in the query or in code. db.raw(sql, [values]) returns the driver's native result (for pg, read result.rows). Inspect SQL with .toQuery() or .toSQL().
Transactions
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);
}); // commits when the callback resolves, rolls back when it throwsEvery statement inside must use trx, not db, or it runs outside the transaction.
Migrations and seeds
npx knex migrate:make create_users_table -x ts # timestamped file in ./migrations
npx knex migrate:latest # run all pending
npx knex migrate:status # list applied / pending
npx knex migrate:rollback # undo the last batch
npx knex seed:make seed_users && npx knex seed:run
npx knex migrate:latest --env production # pick a knexfile environment// migrations/20261002120000_create_users_table.ts
import type { Knex } from "knex";
export async function up(knex: Knex): Promise<void> {
await knex.schema.createTable("users", (t) => {
t.increments("id");
t.string("name", 100).notNullable();
t.string("email").notNullable().unique();
t.enu("role", ["user", "admin"]).defaultTo("user");
t.timestamps(true, true); // created_at / updated_at defaulting to now
});
await knex.schema.createTable("posts", (t) => {
t.increments("id");
t.string("title").notNullable();
t.boolean("published").defaultTo(false);
t.integer("author_id").unsigned().references("id").inTable("users").onDelete("CASCADE");
t.index(["author_id", "published"]);
});
}
export async function down(knex: Knex): Promise<void> {
await knex.schema.dropTable("posts");
await knex.schema.dropTable("users");
}TypeScript migrations need a loader the CLI can find: install tsx and run NODE_OPTIONS="--import tsx" npx knex migrate:latest (ts-node also works), and set migrations: { extension: "ts" } in the knexfile. Confirmed with tsx on Node 24.
Examples
Example 1: "Add a users table and apply it"
npm install knex pg
npx knex init -x ts
npx knex migrate:make create_users_table -x ts
# paste the up/down functions above, then
NODE_OPTIONS="--import tsx" npx knex migrate:latestOutput: Batch 1 run: 1 migrations. migrate:status then lists 20261002120000_create_users_table.ts as completed.
Example 2: "Move money between two accounts atomically"
await db.transaction(async (trx) => {
const { balance } = await trx("accounts").where({ id: 7 }).forUpdate().first();
if (balance < 250) throw new Error("insufficient funds"); // triggers rollback
await trx("accounts").where({ id: 7 }).decrement("balance", 250);
await trx("accounts").where({ id: 12 }).increment("balance", 250);
});Either both updates persist or neither does. forUpdate() locks the row on PostgreSQL and MySQL.
Guidelines
- Pass user input as bindings (
where({ email }),raw("... ?", [value])), never by string concatenation. Identifiers inrawneed??. - Change schema only through migrations; never edit a migration that has already run in shared environments, add a new one.
- Keep one
knexinstance per process; a new pool per request exhausts connections. - Match
pool.maxto the database's connection limit divided by app instances. - Knex does not map rows to classes or manage relations; use Objection.js or an ORM if you need that.
- MySQL has no
returning; readinsertIdfrom the result instead.
