Claude Skill

api-database-knex

SQL query builder for PostgreSQL, MySQL, SQLite, and MSSQL -- fluent queries, schema builder, migrations, seeds, transactions, raw queries

LLM Mart · 0 points · 0 views 0 listing impressions 0 install-command copies
Virus-scanned Reviewed automatically before listing.

Full trust report

Download agents-inc-skills-dist_plugins_api-database-knex_skills_api-database-knex-3a51ef5.zip · 22 KB
agents-inc/skills 24 8 forks MIT Updated 22d ago
Part of agents-inc/skills — 130 skills

Install

skills CLI npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/api-database-knex/skills/api-database-knex
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart
Git git clone https://github.com/agents-inc/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

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 knex.destroy() on graceful shutdown -- orphaned pools prevent the Node.js process from exiting)

</critical_requirements>

Examples

  • Core Patterns -- Initialization, query builder, insert/update/delete, raw queries, TypeScript integration
  • Schema & Migrations -- Schema builder, createTable, alterTable, migrations, seeds
  • Transactions & Advanced -- Transactions, batch insert, subqueries, connection pooling, multi-tenancy

Additional resources:

  • 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

<decision_framework>

Decision Framework

Knex Method Selection

What kind of database operation?
-- SELECT query -> db("table").select().where()
-- INSERT -> db("table").insert(data).returning()
-- UPDATE -> db("table").where().update(data).returning()
-- DELETE -> db("table").where().del()
-- Schema change -> db.schema.createTable() / .alterTable() (in migrations only)
-- Complex SQL -> db.raw("SQL", bindings)
-- Batch insert -> db.batchInsert("table", rows, chunkSize)
-- Multi-table atomic write -> db.transaction(async (trx) => { ... })

When to Use Raw Queries

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 empty array [] 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.

</critical_reminders>

Files (skills)
  • examples
    • core.md 11.3 KB 
      # Knex.js -- Core Pattern Examples

> Initialization, query builder, insert/update/delete, raw queries, TypeScript integration. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [schema-migrations.md](schema-migrations.md) -- Schema builder, createTable, alterTable, migrations, seeds
- [transactions-advanced.md](transactions-advanced.md) -- Transactions, batch insert, subqueries, pooling, multi-tenancy

---

## Knex Initialization

```typescript
import knex from "knex";
import type { Knex } from "knex";

const POOL_MIN = 0;
const POOL_MAX = 10;
const ACQUIRE_TIMEOUT_MS = 30_000;
const CONNECTION_LIFETIME_MS = 5 * 60_000;
const LIFETIME_JITTER_MS = 60_000;

function createDatabase(): Knex {
  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,
      // Force periodic connection churn to avoid stale connections
      maxConnectionLifetimeMillis: CONNECTION_LIFETIME_MS,
      maxConnectionLifetimeJitterMillis: LIFETIME_JITTER_MS,
    },
    acquireConnectionTimeout: ACQUIRE_TIMEOUT_MS,
  });
}

export { createDatabase };
```

**Why good:** Single instance, pool min: 0 releases idle connections, connection lifetime prevents stale connections, jitter avoids thundering herd on reconnection, named constants

```typescript
// ❌ Bad Example -- Pool leak with multiple instances
import knex from "knex";

// Called on every request -- each call creates a new connection pool
async function getUser(id: number) {
  const db = knex({ client: "pg", connection: "postgres://localhost/mydb" });
  const user = await db("users").where("id", id).first();
  // db.destroy() never called -- pool leaked
  return user;
}
```

**Why bad:** New pool per call exhausts database connections, hardcoded connection string, pool never destroyed

---

## Graceful Shutdown

```typescript
import type { Knex } from "knex";

function setupGracefulShutdown(db: Knex): void {
  const shutdown = async () => {
    await db.destroy();
    process.exit(0);
  };

  process.on("SIGTERM", shutdown);
  process.on("SIGINT", shutdown);
}

export { setupGracefulShutdown };
```

**Why good:** Drains connection pool before exit, handles both SIGTERM and SIGINT

---

## Query Builder -- SELECT

```typescript
import type { Knex } from "knex";

interface User {
  id: number;
  name: string;
  email: string;
  status: string;
  created_at: Date;
}

const ACTIVE_STATUS = "active";
const PAGE_SIZE = 25;

// Basic select with typed result
async function getActiveUsers(db: Knex, page: number): Promise<User[]> {
  const offset = (page - 1) * PAGE_SIZE;

  return db<User>("users")
    .select("id", "name", "email", "created_at")
    .where("status", ACTIVE_STATUS)
    .orderBy("created_at", "desc")
    .limit(PAGE_SIZE)
    .offset(offset);
}

// Join with aliased columns
async function getUsersWithOrderCount(
  db: Knex,
): Promise<Array<{ id: number; name: string; order_count: string }>> {
  return db("users")
    .select("users.id", "users.name")
    .count("orders.id as order_count")
    .leftJoin("orders", "users.id", "orders.user_id")
    .groupBy("users.id", "users.name")
    .orderBy("order_count", "desc");
}

// Complex where with OR groups
async function searchUsers(db: Knex, query: string): Promise<User[]> {
  return db<User>("users")
    .select("id", "name", "email")
    .where(function () {
      this.where("name", "ilike", `%${query}%`).orWhere(
        "email",
        "ilike",
        `%${query}%`,
      );
    })
    .andWhere("status", ACTIVE_STATUS)
    .limit(PAGE_SIZE);
}

// First row (returns undefined if not found)
async function getUserById(
  db: Knex,
  userId: number,
): Promise<User | undefined> {
  return db<User>("users")
    .select("id", "name", "email", "status")
    .where("id", userId)
    .first();
}

export { getActiveUsers, getUsersWithOrderCount, searchUsers, getUserById };
```

**Why good:** Explicit column selection, typed results, pagination with limit/offset, `.first()` for single-row queries, grouped OR conditions with callback syntax

```typescript
// ❌ Bad Example -- Overly broad query
async function getUsers(db: Knex) {
  return db("users").select("*"); // Fetches ALL columns, ALL rows -- no limit
}
```

**Why bad:** `select("*")` fetches unnecessary columns, no limit loads entire table into memory

---

## Insert / Update / Delete

```typescript
import type { Knex } from "knex";

interface NewUser {
  name: string;
  email: string;
}

// Insert with returning (PostgreSQL/MSSQL)
async function createUser(
  db: Knex,
  data: NewUser,
): Promise<{ id: number; created_at: Date }> {
  const [result] = await db("users")
    .insert({
      ...data,
      status: "active",
      created_at: db.fn.now(),
      updated_at: db.fn.now(),
    })
    .returning(["id", "created_at"]);

  return result;
}

// Update with returning
async function updateUserEmail(
  db: Knex,
  userId: number,
  newEmail: string,
): Promise<{ id: number; email: string; updated_at: Date }> {
  const [result] = await db("users")
    .where("id", userId)
    .update({
      email: newEmail,
      updated_at: db.fn.now(),
    })
    .returning(["id", "email", "updated_at"]);

  return result;
}

// Soft delete
async function softDeleteUser(db: Knex, userId: number): Promise<void> {
  const rowsAffected = await db("users")
    .where("id", userId)
    .update({ deleted_at: db.fn.now(), status: "deleted" });

  if (rowsAffected === 0) {
    throw new Error(`User ${userId} not found`);
  }
}

// Hard delete with validation
async function deleteUser(db: Knex, userId: number): Promise<void> {
  const rowsDeleted = await db("users").where("id", userId).del();

  if (rowsDeleted === 0) {
    throw new Error(`User ${userId} not found`);
  }
}

// Upsert (PostgreSQL, MySQL, SQLite)
async function upsertUser(
  db: Knex,
  data: NewUser & { id: number },
): Promise<void> {
  await db("users").insert(data).onConflict("id").merge(["name", "email"]); // Only update these columns on conflict
}

export { createUser, updateUserEmail, softDeleteUser, deleteUser, upsertUser };
```

**Why good:** `.returning()` avoids extra SELECT, `db.fn.now()` uses database-native timestamps, row count check catches not-found cases, `.onConflict().merge()` for upsert

```typescript
// ❌ Bad Example -- Missing WHERE on update
async function makeAdmin(db: Knex) {
  await db("users").update({ role: "admin" });
  // ALL users are now admin -- WHERE clause missing
}
```

**Why bad:** No `.where()` clause updates every row in the table

---

## Raw Queries with Safe Bindings

```typescript
import type { Knex } from "knex";

const MIN_ORDER_AMOUNT = 100;

// Value bindings with ?
async function getHighValueOrders(db: Knex, sinceDate: Date) {
  const { rows } = await db.raw(
    `SELECT o.id, o.total, u.name as customer_name
     FROM orders o
     JOIN users u ON u.id = o.user_id
     WHERE o.total > ? AND o.created_at > ?
     ORDER BY o.total DESC`,
    [MIN_ORDER_AMOUNT, sinceDate],
  );
  return rows;
}

// Identifier bindings with ??
async function getDynamicColumn(
  db: Knex,
  tableName: string,
  columnName: string,
  filterValue: string,
) {
  const { rows } = await db.raw("SELECT ?? FROM ?? WHERE ?? = ?", [
    columnName,
    tableName,
    columnName,
    filterValue,
  ]);
  return rows;
}

// Raw in WHERE clause
async function getRecentActiveUsers(db: Knex) {
  return db("users")
    .select("id", "name")
    .whereRaw("created_at > NOW() - INTERVAL '30 days'")
    .andWhereRaw("login_count > ?", [0]);
}

// Raw in SELECT (computed column)
async function getUsersWithAge(db: Knex) {
  return db("users").select(
    "id",
    "name",
    db.raw("EXTRACT(YEAR FROM AGE(birth_date)) as age"),
  );
}

export {
  getHighValueOrders,
  getDynamicColumn,
  getRecentActiveUsers,
  getUsersWithAge,
};
```

**Why good:** `?` for values, `??` for identifiers, all user input parameterized, raw SQL only where query builder can't express the query

```typescript
// ❌ Bad Example -- SQL injection via interpolation
async function findUser(db: Knex, email: string) {
  return db.raw(`SELECT * FROM users WHERE email = '${email}'`);
  // email = "'; DROP TABLE users; --" => disaster
}
```

**Why bad:** String interpolation allows SQL injection, attacker can execute arbitrary SQL

---

## TypeScript Integration

```typescript
import type { Knex } from "knex";

// Augment Knex's table type system
declare module "knex/types/tables" {
  interface User {
    id: number;
    name: string;
    email: string;
    status: "active" | "inactive" | "deleted";
    created_at: Date;
    updated_at: Date;
  }

  // CompositeTableType: separate types for select, insert, update
  interface Tables {
    users: Knex.CompositeTableType<
      // Select type (what you get back)
      User,
      // Insert type (what you provide on insert)
      Pick<User, "name" | "email"> & Partial<Pick<User, "status">>,
      // Update type (what you can update)
      Partial<Omit<User, "id" | "created_at">>
    >;
  }
}

// Usage: TypeScript infers correct types based on table name
async function typedQueries(db: Knex) {
  // Select: returns User[]
  const users = await db("users").select("id", "name");

  // Insert: requires name + email, optional status
  const [inserted] = await db("users")
    .insert({ name: "Alice", email: "alice@example.com" })
    .returning("*");

  // Update: accepts partial fields (except id, created_at)
  await db("users")
    .where("id", 1)
    .update({ name: "Bob", updated_at: new Date() });

  return { users, inserted };
}

export { typedQueries };
```

**Why good:** `CompositeTableType` gives different types for select/insert/update, type inference works with `.select()` and `.returning()`, prevents inserting read-only fields

**Important caveat:** Knex TypeScript support is best-effort. Not all query patterns can be fully type-checked. Complex joins, raw queries, and dynamic column selection may require explicit type annotations.

---

## postProcessResponse for snake_case to camelCase

```typescript
import knex from "knex";

function snakeToCamel(str: string): string {
  return str.replace(/_([a-z])/g, (_, letter: string) => letter.toUpperCase());
}

function camelToSnake(str: string): string {
  return str.replace(/[A-Z]/g, (letter) => `_${letter.toLowerCase()}`);
}

const db = knex({
  client: "pg",
  connection: process.env.DATABASE_URL,
  // Convert database snake_case columns to camelCase in results
  postProcessResponse: (result) => {
    if (Array.isArray(result)) {
      return result.map((row) =>
        Object.fromEntries(
          Object.entries(row).map(([key, val]) => [snakeToCamel(key), val]),
        ),
      );
    }
    return result;
  },
  // Convert camelCase identifiers to snake_case in queries
  wrapIdentifier: (value, origImpl) => {
    return origImpl(camelToSnake(value));
  },
});

export { db };
```

**Why good:** Automatic conversion between JS camelCase and DB snake_case, applied globally so every query benefits, both directions handled

**When to use:** Projects that use camelCase in TypeScript but snake_case in the database. Avoids manual conversion in every query.

**When NOT to use:** Projects that use snake_case everywhere, or projects using a separate mapping layer.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_
    • schema-migrations.md 8.9 KB 
      # Knex.js -- Schema & Migration Examples

> Schema builder, createTable, alterTable, migrations, seeds. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [core.md](core.md) -- Initialization, query builder, insert/update/delete, raw queries
- [transactions-advanced.md](transactions-advanced.md) -- Transactions, batch insert, subqueries, pooling

---

## Creating Tables

```typescript
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  // Users table
  await knex.schema.createTable("users", (table) => {
    table.increments("id").primary();
    table.string("name", 100).notNullable();
    table.string("email", 255).notNullable().unique();
    table.string("password_hash", 255).notNullable();
    table
      .enum("role", ["user", "admin", "moderator"], {
        useNative: true,
        enumName: "user_role",
      })
      .notNullable()
      .defaultTo("user");
    table.jsonb("preferences").defaultTo("{}");
    table.timestamp("email_verified_at").nullable();
    table.timestamps(true, true); // created_at, updated_at with defaults
  });

  // Orders table with foreign key
  await knex.schema.createTable("orders", (table) => {
    table.increments("id").primary();
    table
      .integer("user_id")
      .unsigned()
      .notNullable()
      .references("id")
      .inTable("users")
      .onDelete("CASCADE");
    table.decimal("total", 10, 2).notNullable();
    table
      .enum("status", ["pending", "paid", "shipped", "cancelled"])
      .notNullable()
      .defaultTo("pending");
    table.text("notes").nullable();
    table.timestamps(true, true);

    // Composite index for common query pattern
    table.index(["user_id", "status"], "idx_orders_user_status");
  });
}

export async function down(knex: Knex): Promise<void> {
  // Drop in reverse order to respect foreign keys
  await knex.schema.dropTable("orders");
  await knex.schema.dropTable("users");
  // Drop native enum type (PostgreSQL)
  await knex.raw("DROP TYPE IF EXISTS user_role");
}
```

**Why good:** Foreign key with cascade, composite index named explicitly, native enum with explicit type name, timestamps with defaults, down migration drops in reverse order, enum type cleaned up in down

---

## Altering Tables

```typescript
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.string("phone", 20).nullable();
    table.string("avatar_url", 500).nullable();
    table.timestamp("last_login_at").nullable();
    table.index("email"); // Add index on existing column
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropIndex("email");
    table.dropColumn("last_login_at");
    table.dropColumn("avatar_url");
    table.dropColumn("phone");
  });
}
```

**Why good:** Reversible migration, index added for frequently queried column, down drops in reverse order of creation

---

## PostgreSQL-Specific: Concurrent Index and Enum Extension

Some DDL operations don't work inside transactions. Disable per-migration transaction for these.

```typescript
import type { Knex } from "knex";

// Disable transaction for this migration -- CREATE INDEX CONCURRENTLY
// cannot run inside a transaction
export const config = { transaction: false };

export async function up(knex: Knex): Promise<void> {
  // Non-blocking index creation (PostgreSQL only)
  await knex.raw(
    "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at ON orders (created_at)",
  );

  // Add value to existing enum type (PostgreSQL)
  await knex.raw("ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'super_admin'");
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw("DROP INDEX CONCURRENTLY IF EXISTS idx_orders_created_at");
  // Note: PostgreSQL does not support removing values from an enum type
  // Dropping requires recreating the type -- omit unless truly needed
}
```

**Why good:** `config.transaction = false` required for CONCURRENTLY, IF NOT EXISTS/IF EXISTS for idempotency, explains PostgreSQL enum limitation in down migration

---

## Migration with Data Backfill

When a migration needs to modify data alongside schema changes:

```typescript
import type { Knex } from "knex";

const BATCH_SIZE = 1000;

export async function up(knex: Knex): Promise<void> {
  // Step 1: Add new column (nullable first)
  await knex.schema.alterTable("users", (table) => {
    table.string("display_name", 100).nullable();
  });

  // Step 2: Backfill data in batches
  let updated = 0;
  do {
    updated = await knex("users")
      .whereNull("display_name")
      .update({ display_name: knex.ref("name") })
      .limit(BATCH_SIZE);
  } while (updated === BATCH_SIZE);

  // Step 3: Make column NOT NULL after backfill
  await knex.schema.alterTable("users", (table) => {
    table.string("display_name", 100).notNullable().alter();
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumn("display_name");
  });
}
```

**Why good:** Three-step pattern (add nullable, backfill, make NOT NULL) prevents failures on existing data, batch processing avoids locking entire table, `knex.ref()` copies column value in-database without fetching to Node.js

---

## Knexfile Configuration (TypeScript)

```typescript
// knexfile.ts
import type { Knex } from "knex";

const POOL_MIN = 0;
const POOL_MAX_DEV = 5;
const POOL_MAX_PROD = 20;

const baseConfig: Partial<Knex.Config> = {
  migrations: {
    directory: "./migrations",
    extension: "ts",
    tableName: "knex_migrations",
  },
  seeds: {
    directory: "./seeds",
    extension: "ts",
  },
};

const config: Record<string, Knex.Config> = {
  development: {
    ...baseConfig,
    client: "pg",
    connection:
      process.env.DATABASE_URL ?? "postgres://localhost:5432/myapp_dev",
    pool: { min: POOL_MIN, max: POOL_MAX_DEV },
  },
  test: {
    ...baseConfig,
    client: "pg",
    connection:
      process.env.TEST_DATABASE_URL ?? "postgres://localhost:5432/myapp_test",
    pool: { min: POOL_MIN, max: POOL_MAX_DEV },
  },
  production: {
    ...baseConfig,
    client: "pg",
    connection: process.env.DATABASE_URL,
    pool: { min: POOL_MIN, max: POOL_MAX_PROD },
  },
};

export { config };
```

**Why good:** Shared base config, per-environment pool sizing, environment variables for connection strings, TypeScript migrations and seeds

---

## Migration CLI Commands

```bash
# Create a new migration
npx knex migrate:make create_users_table --knexfile knexfile.ts

# Run all pending migrations
npx knex migrate:latest --knexfile knexfile.ts

# Rollback the last batch
npx knex migrate:rollback --knexfile knexfile.ts

# Rollback ALL migrations
npx knex migrate:rollback --all --knexfile knexfile.ts

# Run the next single migration
npx knex migrate:up --knexfile knexfile.ts

# Rollback the last single migration
npx knex migrate:down --knexfile knexfile.ts

# List migration status
npx knex migrate:list --knexfile knexfile.ts
```

---

## Seeds

Seeds populate tables with initial or test data. Unlike migrations, seeds are not versioned -- they run independently.

```typescript
// seeds/01_users.ts
import type { Knex } from "knex";

export async function seed(knex: Knex): Promise<void> {
  // Truncate in correct order (child tables first)
  await knex("order_items").truncate();
  await knex("orders").truncate();
  await knex("users").truncate();

  // Insert seed data
  await knex("users").insert([
    { name: "Alice Admin", email: "alice@example.com", role: "admin" },
    { name: "Bob User", email: "bob@example.com", role: "user" },
    { name: "Carol Mod", email: "carol@example.com", role: "moderator" },
  ]);
}
```

**Why good:** Truncates in reverse dependency order, then inserts fresh data, idempotent (safe to re-run)

```bash
# Create a new seed file
npx knex seed:make 01_users --knexfile knexfile.ts

# Run all seed files
npx knex seed:run --knexfile knexfile.ts

# Run a specific seed file
npx knex seed:run --specific=01_users.ts --knexfile knexfile.ts
```

---

## View Creation

```typescript
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createView("active_users_summary", (view) => {
    view.as(
      knex("users")
        .select(
          "users.id",
          "users.name",
          "users.email",
          knex.raw("COUNT(orders.id) as order_count"),
          knex.raw("COALESCE(SUM(orders.total), 0) as total_spent"),
        )
        .leftJoin("orders", "users.id", "orders.user_id")
        .where("users.status", "active")
        .groupBy("users.id", "users.name", "users.email"),
    );
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropView("active_users_summary");
}
```

**Why good:** View encapsulates complex query, used from migrations (not ad-hoc), COALESCE handles null sums

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_
    • transactions-advanced.md 12.1 KB 
      # Knex.js -- Transactions & Advanced Pattern Examples

> Transactions, batch insert, subqueries, connection pooling, multi-tenancy. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [core.md](core.md) -- Initialization, query builder, insert/update/delete, raw queries
- [schema-migrations.md](schema-migrations.md) -- Schema builder, createTable, alterTable, migrations, seeds

---

## Transaction with Async/Await

```typescript
import type { Knex } from "knex";

interface OrderItem {
  product_id: number;
  quantity: number;
  price: number;
}

async function createOrder(
  db: Knex,
  userId: number,
  items: OrderItem[],
): Promise<{ orderId: number }> {
  return db.transaction(async (trx) => {
    // Create the order
    const [order] = await trx("orders")
      .insert({
        user_id: userId,
        total: items.reduce((sum, item) => sum + item.price * item.quantity, 0),
        status: "pending",
        created_at: db.fn.now(),
      })
      .returning(["id"]);

    // Insert all line items
    await trx("order_items").insert(
      items.map((item) => ({
        order_id: order.id,
        product_id: item.product_id,
        quantity: item.quantity,
        unit_price: item.price,
      })),
    );

    // Decrement inventory for each product
    for (const item of items) {
      const updated = await trx("inventory")
        .where("product_id", item.product_id)
        .andWhere("quantity", ">=", item.quantity)
        .decrement("quantity", item.quantity);

      if (updated === 0) {
        // Throwing rolls back the entire transaction
        throw new Error(
          `Insufficient inventory for product ${item.product_id}`,
        );
      }
    }

    return { orderId: order.id };
    // Auto-commits when handler completes without error
  });
}

export { createOrder };
```

**Why good:** All-or-nothing: order, items, and inventory updated atomically. Throwing inside the handler auto-rolls-back. Inventory check inside transaction prevents overselling.

```typescript
// ❌ Bad Example -- Transaction that hangs
await db.transaction((trx) => {
  // Handler does not return a promise
  trx("orders").insert({ user_id: 1 });
  trx("items").insert({ order_id: 1 });
  // Knex waits for a returned promise to commit/rollback
  // Neither trx.commit() nor a returned promise -- hangs forever
});
```

**Why bad:** Without returning a promise, Knex cannot detect when the transaction is done. The connection hangs until the acquire timeout fires, potentially exhausting the pool.

---

## Manual Transaction (Without Handler)

Useful when transaction lifecycle spans multiple function calls.

```typescript
import type { Knex } from "knex";

async function manualTransaction(db: Knex): Promise<void> {
  const trx = await db.transaction();

  try {
    await trx("accounts").where("id", 1).decrement("balance", 100);

    await trx("accounts").where("id", 2).increment("balance", 100);

    await trx.commit();
  } catch (error) {
    await trx.rollback();
    throw error;
  }
}

export { manualTransaction };
```

**Why good:** Manual control when transaction spans multiple functions, explicit commit/rollback in try/catch

**When to use:** When you need to pass the transaction object across module boundaries. Prefer the callback pattern for simpler cases.

---

## Transaction with Isolation Level

```typescript
import type { Knex } from "knex";

async function readConsistentReport(
  db: Knex,
  userId: number,
): Promise<{ balance: number; orderTotal: number }> {
  return db.transaction(
    async (trx) => {
      const account = await trx("accounts")
        .where("user_id", userId)
        .select("balance")
        .first();

      const orders = await trx("orders")
        .where("user_id", userId)
        .where("status", "pending")
        .sum("total as order_total")
        .first();

      return {
        balance: account?.balance ?? 0,
        orderTotal: parseFloat(orders?.order_total ?? "0"),
      };
    },
    { isolationLevel: "repeatable read" },
  );
}

export { readConsistentReport };
```

**Why good:** `repeatable read` ensures both queries see the same snapshot, prevents phantom reads between the two queries

---

## Batch Insert with Chunking

```typescript
import type { Knex } from "knex";

const DEFAULT_CHUNK_SIZE = 500;

// Using built-in batchInsert
async function bulkCreateUsers(
  db: Knex,
  users: Array<{ name: string; email: string }>,
): Promise<number[]> {
  const ids = await db
    .batchInsert("users", users, DEFAULT_CHUNK_SIZE)
    .returning("id");

  return ids.map((row: { id: number }) => row.id);
}

// Manual chunking with progress callback (for very large datasets)
async function bulkImportWithProgress(
  db: Knex,
  rows: Array<Record<string, unknown>>,
  tableName: string,
  onProgress?: (inserted: number, total: number) => void,
): Promise<void> {
  let inserted = 0;

  await db.transaction(async (trx) => {
    for (let i = 0; i < rows.length; i += DEFAULT_CHUNK_SIZE) {
      const chunk = rows.slice(i, i + DEFAULT_CHUNK_SIZE);
      await trx(tableName).insert(chunk);
      inserted += chunk.length;
      onProgress?.(inserted, rows.length);
    }
  });
}

export { bulkCreateUsers, bulkImportWithProgress };
```

**Why good:** `batchInsert` handles chunking and wraps in transaction automatically, manual version adds progress tracking, named constant for chunk size

**Gotcha:** `batchInsert` wraps ALL chunks in a single transaction. If one chunk fails, all previous chunks are rolled back. For partial-success behavior, insert each chunk in its own transaction.

---

## Subqueries

```typescript
import type { Knex } from "knex";

// Subquery in WHERE (correlated)
async function getUsersWithRecentOrders(
  db: Knex,
): Promise<Array<{ id: number; name: string }>> {
  return db("users")
    .select("id", "name")
    .whereExists(function () {
      this.select(db.raw("1"))
        .from("orders")
        .whereRaw("orders.user_id = users.id")
        .andWhere(
          "orders.created_at",
          ">",
          db.raw("NOW() - INTERVAL '30 days'"),
        );
    });
}

// Subquery in FROM (derived table)
async function getTopSpenders(db: Knex, limit: number) {
  const subquery = db("orders")
    .select("user_id")
    .sum("total as total_spent")
    .groupBy("user_id")
    .as("order_totals"); // Required: derived tables must have an alias

  return db("users")
    .select("users.name", "order_totals.total_spent")
    .join(subquery, "users.id", "order_totals.user_id")
    .orderBy("order_totals.total_spent", "desc")
    .limit(limit);
}

// Subquery in WHERE IN
async function getUsersInActiveTeams(db: Knex) {
  return db("users")
    .select("id", "name")
    .whereIn("team_id", function () {
      this.select("id").from("teams").where("status", "active");
    });
}

export { getUsersWithRecentOrders, getTopSpenders, getUsersInActiveTeams };
```

**Why good:** Correlated subquery with `whereExists` is efficient, derived table uses `.as()` alias (required by SQL), `whereIn` with subquery avoids fetching IDs to Node.js

**Gotcha:** Subqueries in FROM (derived tables) MUST have an `.as()` alias, otherwise the query fails.

---

## Connection Pool Monitoring

```typescript
import type { Knex } from "knex";

interface PoolStats {
  used: number;
  free: number;
  pendingAcquires: number;
  pendingCreates: number;
}

function getPoolStats(db: Knex): PoolStats {
  const pool = db.client.pool;
  return {
    used: pool.numUsed(),
    free: pool.numFree(),
    pendingAcquires: pool.numPendingAcquires(),
    pendingCreates: pool.numPendingCreates(),
  };
}

// Health check endpoint
async function healthCheck(
  db: Knex,
): Promise<{ healthy: boolean; pool: PoolStats }> {
  const pool = getPoolStats(db);

  try {
    await db.raw("SELECT 1");
    return { healthy: true, pool };
  } catch {
    return { healthy: false, pool };
  }
}

export { getPoolStats, healthCheck };
```

**Why good:** Exposes pool stats for monitoring/alerting, health check verifies connectivity, useful for load balancer probes

---

## Query Timeout

```typescript
import type { Knex } from "knex";

const QUERY_TIMEOUT_MS = 5000;

async function getReportData(db: Knex, startDate: Date, endDate: Date) {
  return db("orders")
    .select("status")
    .count("id as count")
    .sum("total as revenue")
    .whereBetween("created_at", [startDate, endDate])
    .groupBy("status")
    .timeout(QUERY_TIMEOUT_MS, { cancel: true });
}

export { getReportData };
```

**Why good:** `.timeout()` with `{ cancel: true }` sends a cancel signal to the database, preventing runaway queries from consuming server resources

**Gotcha:** Without `{ cancel: true }`, Knex only times out on the Node.js side. The query continues running on the database server, consuming resources.

---

## Multi-Tenancy with Schema

PostgreSQL schemas provide logical isolation for multi-tenant applications.

```typescript
import type { Knex } from "knex";

async function createTenantSchema(db: Knex, tenantId: string): Promise<void> {
  const schemaName = `tenant_${tenantId}`;

  // Create isolated schema
  await db.raw("CREATE SCHEMA IF NOT EXISTS ??", [schemaName]);

  // Run migrations in tenant schema
  await db.migrate.latest({
    directory: "./migrations/tenant",
    schemaName,
  });
}

function tenantQuery(db: Knex, tenantId: string): Knex {
  return db.withUserParams({ tenantId });
}

// Alternative: Use searchPath per query
async function getTenantUsers(
  db: Knex,
  tenantId: string,
): Promise<Array<{ id: number; name: string }>> {
  const schemaName = `tenant_${tenantId}`;
  return db.withSchema(schemaName).select("id", "name").from("users");
}

export { createTenantSchema, tenantQuery, getTenantUsers };
```

**Why good:** Schema-per-tenant provides logical isolation, `.withSchema()` targets the correct tenant, `IF NOT EXISTS` for idempotency

---

## Query Event Listeners

```typescript
import type { Knex } from "knex";

const SLOW_QUERY_THRESHOLD_MS = 1000;

function setupQueryLogging(db: Knex): void {
  const queryStartTimes = new Map<string, number>();

  // Track query start times (all environments)
  db.on("query", (queryData) => {
    queryStartTimes.set(queryData.__knexQueryUid, Date.now());
  });

  // Log all queries (development only)
  if (process.env.NODE_ENV === "development") {
    db.on("query", (queryData) => {
      console.log("SQL:", queryData.sql);
      console.log("Bindings:", queryData.bindings);
    });
  }

  // Log slow queries (all environments)
  db.on("query-response", (_response, queryData) => {
    const startTime = queryStartTimes.get(queryData.__knexQueryUid);
    queryStartTimes.delete(queryData.__knexQueryUid);
    if (startTime !== undefined) {
      const duration = Date.now() - startTime;
      if (duration > SLOW_QUERY_THRESHOLD_MS) {
        console.warn(`Slow query (${duration}ms):`, queryData.sql);
      }
    }
  });

  // Log query errors
  db.on("query-error", (error, queryData) => {
    queryStartTimes.delete(queryData.__knexQueryUid);
    console.error("Query error:", error.message);
    console.error("SQL:", queryData.sql);
  });
}

export { setupQueryLogging };
```

**Why good:** Map tracks start times per query via `__knexQueryUid`, cleans up entries on response/error to prevent memory leak, development-only verbose logging, slow query detection in all environments

---

## Using knex.ref() for Column References

```typescript
import type { Knex } from "knex";

// Copy value from one column to another (in-database, no round-trip)
async function copyDisplayName(db: Knex): Promise<void> {
  await db("users")
    .whereNull("display_name")
    .update({ display_name: db.ref("name") });
}

// Use ref in join conditions with aliased columns
async function getOrdersWithUserEmail(db: Knex) {
  return db("orders")
    .select(
      "orders.id",
      "orders.total",
      db.ref("users.email").as("customer_email"),
    )
    .join("users", "users.id", "orders.user_id");
}

export { copyDisplayName, getOrdersWithUserEmail };
```

**Why good:** `db.ref()` references a column without quoting it as a string value, enables in-database column copy without fetching data to Node.js

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_
  • reference.md 18.6 KB 
    # Knex.js Quick Reference

> Query method cheat sheet, column types, pool options, anti-patterns, and production checklist. See [SKILL.md](SKILL.md) for core concepts and [examples/](examples/) for code examples.

---

## Query Builder Methods

### SELECT Methods

| Method                          | Description                        | Example                                |
| ------------------------------- | ---------------------------------- | -------------------------------------- |
| `.select(columns...)`           | Select columns                     | `db("users").select("id", "name")`     |
| `.distinct(columns...)`         | Select distinct                    | `db("users").distinct("email")`        |
| `.first()`                      | Return first row (or `undefined`)  | `db("users").where("id", 1).first()`   |
| `.pluck(column)`                | Return flat array of single column | `db("users").pluck("id")`              |
| `.count(column?)`               | Count rows                         | `db("users").count("id as total")`     |
| `.sum(column)`                  | Sum values                         | `db("orders").sum("total as revenue")` |
| `.avg(column)`                  | Average values                     | `db("orders").avg("total")`            |
| `.min(column)` / `.max(column)` | Min/max value                      | `db("orders").max("total as highest")` |

### WHERE Methods

| Method                      | Description     | Example                                                                             |
| --------------------------- | --------------- | ----------------------------------------------------------------------------------- |
| `.where(col, val)`          | Equal           | `db("users").where("status", "active")`                                             |
| `.where(col, op, val)`      | Operator        | `db("users").where("age", ">", 18)`                                                 |
| `.where(obj)`               | Multiple AND    | `db("users").where({ status: "active", role: "admin" })`                            |
| `.whereNot(col, val)`       | Not equal       | `db("users").whereNot("status", "banned")`                                          |
| `.whereIn(col, arr)`        | In array        | `db("users").whereIn("id", [1, 2, 3])`                                              |
| `.whereNotIn(col, arr)`     | Not in array    | `db("users").whereNotIn("role", ["guest"])`                                         |
| `.whereNull(col)`           | Is null         | `db("users").whereNull("deleted_at")`                                               |
| `.whereNotNull(col)`        | Is not null     | `db("users").whereNotNull("email")`                                                 |
| `.whereBetween(col, range)` | Between         | `db("users").whereBetween("age", [18, 65])`                                         |
| `.whereExists(builder)`     | Subquery exists | `db("users").whereExists(db("orders").where("orders.user_id", db.ref("users.id")))` |
| `.whereRaw(sql, bindings)`  | Raw WHERE       | `db("users").whereRaw("age > ?", [18])`                                             |
| `.orWhere(col, val)`        | OR condition    | `db("users").where("role", "admin").orWhere("role", "super")`                       |

### JOIN Methods

| Method                         | Description      | Example                                                         |
| ------------------------------ | ---------------- | --------------------------------------------------------------- |
| `.join(table, col1, op, col2)` | Inner join       | `db("users").join("orders", "users.id", "=", "orders.user_id")` |
| `.leftJoin(...)`               | Left outer join  | `db("users").leftJoin("orders", "users.id", "orders.user_id")`  |
| `.rightJoin(...)`              | Right outer join | `db("users").rightJoin("orders", ...)`                          |
| `.fullOuterJoin(...)`          | Full outer join  | `db("users").fullOuterJoin("orders", ...)`                      |
| `.crossJoin(table)`            | Cross join       | `db("users").crossJoin("roles")`                                |
| `.joinRaw(sql)`                | Raw join         | `db("users").joinRaw("NATURAL JOIN orders")`                    |

### ORDER / GROUP / LIMIT

| Method                      | Description   | Example                                                        |
| --------------------------- | ------------- | -------------------------------------------------------------- |
| `.orderBy(col, dir?)`       | Sort results  | `db("users").orderBy("created_at", "desc")`                    |
| `.orderByRaw(sql)`          | Raw order     | `db("users").orderByRaw("FIELD(status, 'active', 'pending')")` |
| `.groupBy(cols...)`         | Group results | `db("orders").groupBy("user_id")`                              |
| `.having(col, op, val)`     | Filter groups | `db("orders").groupBy("user_id").having("total", ">", 100)`    |
| `.havingRaw(sql, bindings)` | Raw having    | `db("orders").havingRaw("COUNT(*) > ?", [5])`                  |
| `.limit(n)`                 | Limit results | `db("users").limit(25)`                                        |
| `.offset(n)`                | Skip results  | `db("users").offset(50)`                                       |

### MUTATION Methods

| Method                          | Description              | Example                                                  |
| ------------------------------- | ------------------------ | -------------------------------------------------------- |
| `.insert(data)`                 | Insert row(s)            | `db("users").insert({ name: "Alice" })`                  |
| `.insert(data).returning(cols)` | Insert + return          | `db("users").insert({...}).returning(["id"])`            |
| `.update(data)`                 | Update rows              | `db("users").where("id", 1).update({ name: "Bob" })`     |
| `.increment(col, amount?)`      | Increment                | `db("users").where("id", 1).increment("login_count")`    |
| `.decrement(col, amount?)`      | Decrement                | `db("inventory").where("id", 1).decrement("stock", 5)`   |
| `.del()` / `.delete()`          | Delete rows              | `db("users").where("id", 1).del()`                       |
| `.onConflict(col).merge()`      | Upsert (PG/MySQL/SQLite) | `db("users").insert({...}).onConflict("email").merge()`  |
| `.onConflict(col).ignore()`     | Insert or skip           | `db("users").insert({...}).onConflict("email").ignore()` |

### UTILITY Methods

| Method                             | Description           | Example                                       |
| ---------------------------------- | --------------------- | --------------------------------------------- |
| `.raw(sql, bindings)`              | Raw SQL               | `db.raw("SELECT NOW()")`                      |
| `.ref(column)`                     | Column reference      | `db.ref("users.id")`                          |
| `.fn.now(precision?)`              | Current timestamp     | `db.fn.now(6)`                                |
| `.fn.uuid()`                       | Generate UUID         | `db.fn.uuid()`                                |
| `.batchInsert(table, rows, chunk)` | Chunked insert        | `db.batchInsert("users", rows, 500)`          |
| `.timeout(ms, opts?)`              | Query timeout         | `db("users").timeout(5000, { cancel: true })` |
| `.toSQL()`                         | Inspect generated SQL | `db("users").where("id", 1).toSQL()`          |
| `.toString()`                      | SQL as string         | `db("users").where("id", 1).toString()`       |

---

## Schema Column Types

| Method                                     | SQL Type                    | Notes                                        |
| ------------------------------------------ | --------------------------- | -------------------------------------------- |
| `table.increments("id")`                   | `SERIAL` / `AUTO_INCREMENT` | Primary key by default                       |
| `table.bigIncrements("id")`                | `BIGSERIAL`                 | For large tables                             |
| `table.integer("col")`                     | `INTEGER`                   |                                              |
| `table.bigInteger("col")`                  | `BIGINT`                    |                                              |
| `table.float("col", precision?, scale?)`   | `FLOAT`                     |                                              |
| `table.decimal("col", precision?, scale?)` | `DECIMAL`                   | Use for money (e.g., 10, 2)                  |
| `table.string("col", length?)`             | `VARCHAR`                   | Default length: 255                          |
| `table.text("col", textType?)`             | `TEXT`                      | textType: "mediumtext", "longtext"           |
| `table.boolean("col")`                     | `BOOLEAN`                   |                                              |
| `table.date("col")`                        | `DATE`                      |                                              |
| `table.datetime("col", opts?)`             | `DATETIME`                  | opts: `{ precision: 6 }`                     |
| `table.timestamp("col", opts?)`            | `TIMESTAMP`                 | opts: `{ precision: 6, useTz: true }`        |
| `table.time("col", precision?)`            | `TIME`                      |                                              |
| `table.json("col")`                        | `JSON`                      |                                              |
| `table.jsonb("col")`                       | `JSONB`                     | PostgreSQL only                              |
| `table.binary("col", length?)`             | `BLOB` / `BYTEA`            |                                              |
| `table.enum("col", values, opts?)`         | `ENUM`                      | opts: `{ useNative: true, enumName: "..." }` |
| `table.uuid("col")`                        | `UUID` / `CHAR(36)`         |                                              |
| `table.specificType("col", type)`          | Custom type                 | `table.specificType("col", "CITEXT")`        |

### Column Modifiers

| Modifier                            | Description       | Example                                                      |
| ----------------------------------- | ----------------- | ------------------------------------------------------------ |
| `.primary()`                        | Primary key       | `table.uuid("id").primary()`                                 |
| `.notNullable()`                    | NOT NULL          | `table.string("name").notNullable()`                         |
| `.nullable()`                       | Allow NULL        | `table.string("bio").nullable()`                             |
| `.defaultTo(value)`                 | Default value     | `table.boolean("active").defaultTo(true)`                    |
| `.unsigned()`                       | Unsigned integer  | `table.integer("age").unsigned()`                            |
| `.unique()`                         | Unique constraint | `table.string("email").unique()`                             |
| `.index()`                          | Create index      | `table.string("slug").index()`                               |
| `.references("col").inTable("tbl")` | Foreign key       | `table.integer("user_id").references("id").inTable("users")` |
| `.onDelete("CASCADE")`              | FK delete action  | Chain after `.references()`                                  |
| `.onUpdate("CASCADE")`              | FK update action  | Chain after `.references()`                                  |
| `.comment("text")`                  | Column comment    | `table.string("code").comment("ISO country code")`           |

### Table-Level Operations

| Method                                 | Description                 | Example                                 |
| -------------------------------------- | --------------------------- | --------------------------------------- |
| `table.timestamps(true, true)`         | Add created_at + updated_at | Both with defaultTo(now)                |
| `table.index(columns, name?)`          | Composite index             | `table.index(["user_id", "status"])`    |
| `table.unique(columns, name?)`         | Composite unique            | `table.unique(["email", "tenant_id"])`  |
| `table.primary(columns)`               | Composite PK                | `table.primary(["user_id", "role_id"])` |
| `table.foreign("col").references(...)` | Named FK                    | More control than column-level          |
| `table.dropColumn("col")`              | Remove column               |                                         |
| `table.renameColumn("old", "new")`     | Rename column               |                                         |

---

## Connection Pool Options (tarn.js)

| Option                                   | Default        | Description                                                 |
| ---------------------------------------- | -------------- | ----------------------------------------------------------- |
| `pool.min`                               | `2`            | Minimum connections (set to `0` for serverless/low-traffic) |
| `pool.max`                               | `10`           | Maximum connections                                         |
| `pool.idleTimeoutMillis`                 | `30000`        | Close idle connections after this duration                  |
| `pool.reapIntervalMillis`                | `1000`         | How often to check for idle connections                     |
| `pool.createTimeoutMillis`               | `30000`        | Timeout for creating a new connection                       |
| `pool.acquireTimeoutMillis`              | `30000`        | Timeout for acquiring a connection from pool                |
| `pool.destroyTimeoutMillis`              | `5000`         | Timeout for destroying a connection                         |
| `pool.maxConnectionLifetimeMillis`       | `0` (disabled) | Force connection churn after this duration                  |
| `pool.maxConnectionLifetimeJitterMillis` | `0`            | Spread out reconnections to avoid thundering herd           |
| `pool.validate`                          | `undefined`    | Function to validate connection before reuse                |

---

## Anti-Patterns

### String Interpolation in Raw Queries

```typescript
// ANTI-PATTERN: SQL injection
const results = await db.raw(`SELECT * FROM users WHERE email = '${email}'`);
```

**Why it's wrong:** User input is directly interpolated into SQL, allowing injection attacks.

**What to do instead:** Use parameterized bindings:

```typescript
const results = await db.raw("SELECT * FROM users WHERE email = ?", [email]);
```

---

### Creating Multiple Knex Instances

```typescript
// ANTI-PATTERN: Pool leak
function queryUsers() {
  const db = require("knex")({
    client: "pg",
    connection: process.env.DATABASE_URL,
  });
  return db("users").select("*"); // Pool never destroyed
}
```

**Why it's wrong:** Each call creates a new connection pool. Pools accumulate and exhaust database connections.

**What to do instead:** Create one instance at startup, share via module export or dependency injection.

---

### Missing WHERE on Update/Delete

```typescript
// ANTI-PATTERN: Updates ALL rows
await db("users").update({ role: "admin" });
// Every single user is now an admin
```

**Why it's wrong:** Without `.where()`, the operation affects every row in the table.

**What to do instead:** Always chain `.where()` before `.update()` or `.del()`.

---

### Not Destroying Pool on Shutdown

```typescript
// ANTI-PATTERN: Process hangs
process.on("SIGTERM", () => {
  // db.destroy() never called
  // Process hangs because pool connections are still open
  process.exit(0); // Force exit -- connections not cleanly closed
});
```

**Why it's wrong:** Open pool connections keep the event loop alive. `process.exit(0)` forces termination without cleanly closing connections.

**What to do instead:**

```typescript
process.on("SIGTERM", async () => {
  await db.destroy();
  process.exit(0);
});
```

---

## Production Checklist

### Connection Management

- [ ] Single knex instance per application
- [ ] Connection string from environment variable (DATABASE_URL)
- [ ] Pool `min: 0` for serverless/low-traffic (avoids stale connections)
- [ ] Pool `max` tuned for your database's connection limit (leave headroom)
- [ ] `acquireConnectionTimeout` set (default 60s may be too long)
- [ ] `knex.destroy()` called on SIGTERM/SIGINT
- [ ] TLS/SSL configured for production databases

### Query Safety

- [ ] All `knex.raw()` calls use `?` / `??` bindings (never string interpolation)
- [ ] All `.update()` and `.del()` calls have a `.where()` clause
- [ ] `.timeout()` set on long-running queries with `{ cancel: true }`
- [ ] `.returning()` used on PostgreSQL/MSSQL inserts and updates
- [ ] No `select("*")` in production queries

### Migrations

- [ ] All schema changes in migrations (not ad-hoc `knex.schema` calls)
- [ ] Every `up()` has a corresponding `down()` for rollback
- [ ] Migration filenames use consistent timestamp format
- [ ] Migrations tested: run `migrate:latest` then `migrate:rollback` then `migrate:latest` again
- [ ] `disableTransactions` only used when necessary (e.g., PostgreSQL `CREATE INDEX CONCURRENTLY`)

### Transactions

- [ ] All multi-table writes wrapped in `knex.transaction()`
- [ ] Transaction handlers always return or await promises
- [ ] No manual `trx.commit()`/`trx.rollback()` AND returned promise (pick one)
- [ ] Isolation level set when needed (default is `read committed` on PostgreSQL)

### Monitoring

- [ ] Connection pool stats monitored: `db.client.pool.numUsed()`, `numFree()`, `numPendingAcquires()`
- [ ] Slow query logging enabled via `knex.on("query", ...)` or database-level logging
- [ ] Pool exhaustion alerts configured
- [ ] Migration state tracked (`knex_migrations` table)

---

## .returning() Behavior by Database

| Database     | `.returning()`          | Insert default return | Notes                         |
| ------------ | ----------------------- | --------------------- | ----------------------------- |
| PostgreSQL   | Returns `[{ id, ... }]` | `[]` (empty array)    | Full support                  |
| MSSQL        | Returns `[{ id, ... }]` | `[]` (empty array)    | Full support                  |
| SQLite 3.35+ | Returns `[{ id, ... }]` | `[rowid]`             | Requires SQLite 3.35+         |
| MySQL        | **Silently ignored**    | `[insertId]`          | Use `result[0]` for insert ID |
| Oracle       | Returns `[{ id, ... }]` | `[sequence]`          | Requires explicit sequence    |

---

_Full skill documentation: [SKILL.md](SKILL.md) | Examples: [examples/](examples/)_
  • SKILL.md 16.9 KB 
    ---
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

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

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

```typescript
// 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

```typescript
// 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 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](examples/core.md) for joins, groupBy, having.

```typescript
// Good Example -- Typed query with explicit columns
const ACTIVE_STATUS = "active";
const PAGE_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

```typescript
// Bad Example -- select(*) with string interpolation
const users = await db("users").select("*").whereRaw(`status = '${status}'`); // SQL INJECTION
```

**Why bad:** `select("*")` fetches unnecessary data, string interpolation in whereRaw creates SQL injection vulnerability

---

### 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](examples/core.md).

```typescript
// Good Example -- Insert with returning (PostgreSQL)
const [inserted] = await db("users")
  .insert({ name: "Alice", email: "alice@example.com" })
  .returning(["id", "created_at"]);

// Good Example -- Update with returning
const [updated] = await db("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

```typescript
// Bad Example -- Forgetting returning() on PostgreSQL
await db("users").insert({ name: "Alice" });
// Returns [] (empty array) on PostgreSQL, 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](examples/core.md).

```typescript
// Good Example -- Parameterized raw query
const MIN_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

```typescript
// Bad Example -- String concatenation in raw query
const results = await db.raw(`SELECT * FROM users WHERE name = '${name}'`);
// SQL INJECTION: name = "'; DROP TABLE users; --"
```

**Why bad:** String interpolation allows SQL injection, attacker can execute arbitrary SQL

---

### Pattern 5: Transactions

Wrap multi-step operations in transactions. Return or await the promise -- otherwise the connection hangs. See [examples/transactions-advanced.md](examples/transactions-advanced.md).

```typescript
// Good Example -- Async/await transaction
const result = await db.transaction(async (trx) => {
  const [order] = await trx("orders")
    .insert({ user_id: userId, total: amount })
    .returning("id");

  await trx("order_items").insert(
    items.map((item) => ({ order_id: order.id, ...item })),
  );

  await trx("inventory")
    .whereIn(
      "product_id",
      items.map((i) => i.product_id),
    )
    .decrement("quantity", 1);

  return order;
});
// Transaction auto-commits on success, auto-rolls-back on thrown error
```

**Why good:** All operations atomic, auto-commit on success, auto-rollback on error, returns value from transaction

```typescript
// Bad Example -- Forgetting to return/await inside transaction
await db.transaction((trx) => {
  trx("orders").insert({ user_id: userId }); // NOT returned/awaited
  trx("items").insert({ order_id: 1 }); // NOT returned/awaited
  // Transaction handler returns undefined -- trx NEVER commits or rolls back
  // Connection hangs until acquireConnectionTimeout fires
});
```

**Why bad:** Without returning a promise, Knex cannot detect completion, transaction hangs indefinitely consuming a pool connection

---

### Pattern 6: Schema Builder

Create and modify tables. Use in migrations, not application code. See [examples/schema-migrations.md](examples/schema-migrations.md).

```typescript
// Good Example -- Migration creating a table
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("orders", (table) => {
    table.increments("id").primary();
    table
      .integer("user_id")
      .unsigned()
      .notNullable()
      .references("id")
      .inTable("users")
      .onDelete("CASCADE");
    table.decimal("total", 10, 2).notNullable();
    table
      .enum("status", ["pending", "paid", "shipped", "cancelled"])
      .notNullable()
      .defaultTo("pending");
    table.timestamps(true, true); // created_at, updated_at with defaults
    table.index(["user_id", "status"]);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTable("orders");
}
```

**Why good:** Foreign key with cascade, composite index, enum constraint, timestamps with defaults, reversible down migration

</patterns>

---

<decision_framework>

## Decision Framework

### Knex Method Selection

```
What kind of database operation?
-- SELECT query -> db("table").select().where()
-- INSERT -> db("table").insert(data).returning()
-- UPDATE -> db("table").where().update(data).returning()
-- DELETE -> db("table").where().del()
-- Schema change -> db.schema.createTable() / .alterTable() (in migrations only)
-- Complex SQL -> db.raw("SQL", bindings)
-- Batch insert -> db.batchInsert("table", rows, chunkSize)
-- Multi-table atomic write -> db.transaction(async (trx) => { ... })
```

### When to Use Raw Queries

```
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 empty array `[]` 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.**

</critical_reminders>

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.

Related

Claude Prompt
Tighten-my-prose editing prompt
Claude Skill
Adversarial code-review skill for Claude
Claude Workflow
Deep-research workflow (fan-out + verify)
Claude Skill
Conventional-commit writer for Claude