/api-database-knex

name: api-database-knex
description: 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

**(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)**

</critical_requirements>

---

Examples

• [Core Patterns](examples/core.md) -- Initialization, query builder, insert/update/delete, raw queries, TypeScript integration
• [Schema & Migrations](examples/schema-migrations.md) -- Schema builder, createTable, alterTable, migrations, seeds
• [Transactions & Advanced](examples/transactions-advanced.md) -- Transactions, batch insert, subqueries, connection pooling, multi-tenancy

**Additional resources:**

• [reference.md](reference.md) -- Query method cheat sheet, column types, pool options, anti-patterns, production checklist

---

**Auto-detection:** Knex, knex, knexfile, knex.raw, knex.schema, knex.transaction, knex.migrate, knex.seed, batchInsert, query builder, schema builder, SQL query builder, knex.fn.now, knex.ref, knex.destroy, pg, mysql2, sqlite3, better-sqlite3

**When to use:**

• Building SQL queries programmatically with a fluent API
• Database schema creation and modification (createTable, alterTable)
• Running and managing database migrations (up/down)
• Seeding development/test databases
• Wrapping multi-step database operations in transactions
• Writing raw SQL with safe parameter binding
• Batch inserting large datasets with chunking

**Key patterns covered:**

• Knex initialization with connection pool configuration
• Fluent query builder (select, where, join, orderBy, groupBy, having)
• Insert, update, delete with `.returning()` for PostgreSQL/MSSQL
• Schema builder (createTable, alterTable, column types, indexes, foreign keys)
• Migrations (knex migrate:make, up/down, transaction control)
• Seeds (knex seed:make, seed:run)
• 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>

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:**

1. **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. 2. **Parameterize everything** -- Use `?` bindings for values and `??` for identifiers. Never interpolate strings into queries. 3. **Migrations are the source of truth** -- Schema changes happen through migrations, not ad-hoc `knex.schema` calls in application code. 4. **Transactions for consistency** -- Any operation touching multiple tables or needing atomicity must be wrapped in `knex.transaction()`. 5. **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.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Knex Initialization

Initialize once per application. The knex instance manages a connection pool internally. See [examples/core.md](examples/core.md) for full examples.

// Good Example -- Proper initialization with pool tuning
import knex from "knex";

const POOL_MIN = 0;
const POOL_MAX = 10;
const ACQUIRE_TIMEOUT_MS = 30_000;

function createDatabase() {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  return knex({
    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 config
import knex from "knex";

function getUsers() {
  const db = knex({ client: "pg", connection: "postgres://localhost/mydb" });
  return db("users").select("*");
  // Connection pool leaked -- db.destroy() never call