You are an expert in Knex.js for database access and query building. You help developers write queries, manage migrations, seed data, and configure connection pooling.

## Key Points

- **Query Builder** — chainable, dialect-agnostic SQL builder
- **Schema Builder** — DDL API for creating/altering tables
- **Migrations** — timestamped, reversible schema changes
- **Seeds** — repeatable data population scripts
- **Always use parameterized queries** — Knex handles this automatically through its API, but when using `db.raw()`, always use `?` placeholders or named bindings.
- **Use `returning('*')`** on insert/update/delete to get affected rows (PostgreSQL only; MySQL and SQLite do not support this).
- **Destroy the pool on shutdown** — call `await db.destroy()` in graceful shutdown handlers to release all connections.
- **Keep migrations idempotent** and always implement both `up` and `down` functions.
- **Use `.first()`** instead of `.limit(1)[0]` for single-record lookups — it returns `undefined` instead of an empty array.
- **Type your results** with generics: `db<User>('users').select('*')` for basic type hints (though Knex typing is limited compared to Kysely).
- **No `.returning()` on MySQL/SQLite**: Calling `.returning()` on dialects that don't support it silently fails or throws. Guard with dialect checks.
- **Forgetting to await**: Knex queries return thenables. Forgetting `await` means the query fires but results are ignored, leading to subtle bugs.

## Quick Example

```bash
npm install knex pg  # or mysql2, better-sqlite3
```

```typescript
import knex from 'knex';
import config from '../knexfile';

const environment = process.env.NODE_ENV ?? 'development';
export const db = knex(config[environment]);
```

Knex.js — Database Toolkit

You are an expert in Knex.js for database access and query building. You help developers write queries, manage migrations, seed data, and configure connection pooling.

Overview

Knex.js is a flexible SQL query builder for Node.js that supports PostgreSQL, MySQL, MariaDB, SQLite3, and MSSQL. It provides a fluent API for building queries, a robust migration system, and seed file support. Knex is often used directly or as the query layer underlying higher-level ORMs like Objection.js and Bookshelf.

Key components:

• Query Builder — chainable, dialect-agnostic SQL builder
• Schema Builder — DDL API for creating/altering tables
• Migrations — timestamped, reversible schema changes
• Seeds — repeatable data population scripts

Setup & Configuration

Installation

npm install knex pg  # or mysql2, better-sqlite3

Configuration (knexfile.ts)

import type { Knex } from 'knex';

const config: Record<string, Knex.Config> = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    pool: { min: 2, max: 10 },
    migrations: {
      directory: './migrations',
      extension: 'ts',
    },
    seeds: {
      directory: './seeds',
    },
  },
  production: {
    client: 'pg',
    connection: {
      connectionString: process.env.DATABASE_URL,
      ssl: { rejectUnauthorized: false },
    },
    pool: { min: 2, max: 20 },
    migrations: {
      directory: './migrations',
      extension: 'ts',
    },
  },
};

export default config;

Instance (src/db.ts)

import knex from 'knex';
import config from '../knexfile';

const environment = process.env.NODE_ENV ?? 'development';
export const db = knex(config[environment]);

Migrations

npx knex migrate:make create_users --knexfile knexfile.ts
npx knex migrate:latest
npx knex migrate:rollback
npx knex seed:make seed_users
npx knex seed:run

// migrations/20240101000000_create_users.ts
import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('email', 255).notNullable().unique();
    table.string('name', 100).nullable();
    table.timestamps(true, true); // created_at, updated_at
  });

  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.string('title', 255).notNullable();
    table.text('content').nullable();
    table.boolean('published').notNullable().defaultTo(false);
    table.integer('author_id').unsigned().notNullable()
      .references('id').inTable('users').onDelete('CASCADE');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.index('author_id');
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('posts');
  await knex.schema.dropTableIfExists('users');
}

Seed File

// seeds/seed_users.ts
import { Knex } from 'knex';

export async function seed(knex: Knex): Promise<void> {
  await knex('users').del();
  await knex('users').insert([
    { email: 'alice@example.com', name: 'Alice' },
    { email: 'bob@example.com', name: 'Bob' },
  ]);
}

Core Patterns

CRUD Operations

import { db } from './db';

// Insert
const [user] = await db('users')
  .insert({ email: 'alice@example.com', name: 'Alice' })
  .returning('*');

// Batch insert
await db('posts').insert([
  { title: 'Post 1', author_id: user.id },
  { title: 'Post 2', author_id: user.id, published: true },
]);

// Select
const users = await db('users')
  .where('email', 'like', '%@example.com')
  .orderBy('created_at', 'desc')
  .limit(10)
  .offset(0);

// Select specific columns
const emails = await db('users').select('id', 'email');

// First (single record)
const found = await db('users').where({ email: 'alice@example.com' }).first();

// Join
const postsWithAuthors = await db('posts')
  .join('users', 'users.id', 'posts.author_id')
  .select('posts.title', 'users.name as author_name')
  .where('posts.published', true);

// Update
await db('users').where({ id: 1 }).update({ name: 'Alice Updated' });

// Upsert (PostgreSQL)
await db('users')
  .insert({ email: 'alice@example.com', name: 'Alice' })
  .onConflict('email')
  .merge();

// Delete
await db('posts').where({ id: 1 }).del();

Complex Queries

// Subquery
const activeAuthors = await db('users')
  .whereIn('id',
    db('posts').select('author_id').where('published', true).distinct()
  );

// Aggregation
const stats = await db('posts')
  .select('author_id')
  .count('id as post_count')
  .max('created_at as latest_post')
  .groupBy('author_id')
  .having(db.raw('COUNT(id) > ?', [5]));

// Raw expressions
const result = await db('posts')
  .select(db.raw('DATE(created_at) as day'), db.raw('COUNT(*) as count'))
  .groupByRaw('DATE(created_at)')
  .orderByRaw('DATE(created_at) DESC');

// CTE
const result = await db.with('active_users', (qb) => {
  qb.select('id', 'name').from('users')
    .whereIn('id', db('posts').select('author_id').where('published', true));
}).select('*').from('active_users');

Transactions

const result = await db.transaction(async (trx) => {
  const [user] = await trx('users')
    .insert({ email: 'bob@example.com', name: 'Bob' })
    .returning('*');

  const [post] = await trx('posts')
    .insert({ title: 'Hello', author_id: user.id, published: true })
    .returning('*');

  return { user, post };
});

Core Philosophy

Knex occupies a deliberate middle ground in the database access spectrum: it provides more structure than raw SQL strings but less abstraction than a full ORM. There are no models, no identity maps, no unit of work — just a fluent, chainable query builder that produces parameterized SQL for whatever dialect you target. This positioning is Knex's greatest strength: you get the safety of parameterized queries, the readability of a builder pattern, and the predictability of knowing exactly what SQL will execute, without the complexity of an entity management system.

The migration and seed system is where Knex provides the most value beyond query building. Migrations are timestamped JavaScript or TypeScript files with up and down functions that evolve the schema reversibly. Seeds populate development and test databases with consistent data. Together, they create a repeatable, version-controlled database lifecycle that keeps every environment — development, CI, staging, production — in sync. Treating migrations as first-class code (reviewed, tested, never edited after deployment) is the foundation of reliable database management.

Knex shines as a foundation layer. It is commonly used underneath higher-level ORMs like Objection.js and Bookshelf, providing the query building and connection management while those libraries add model definitions and relationship handling. Even when used standalone, Knex works best when wrapped in a thin repository or data access layer that encapsulates queries behind domain-specific methods. The query builder is an implementation detail; the repository interface is the API your application code should depend on.

Anti-Patterns

• String interpolation inside db.raw() — using template literals or concatenation to inject values into raw SQL bypasses Knex's parameterization and opens a direct SQL injection vulnerability. Always use db.raw('SELECT * FROM users WHERE id = ?', [id]) with placeholder binding.

• Creating multiple Knex instances — each Knex instance creates its own connection pool. Multiple instances exhaust database connections, and there is no coordination between their pools. Use a single Knex instance (singleton pattern) for the entire application.

• Forgetting to await query results — Knex queries return thenables, not promises. Omitting await causes the query to fire without capturing the result, leading to silent data inconsistencies and race conditions.

• Relying on .returning() across all dialects — .returning() is PostgreSQL-specific. Calling it on MySQL or SQLite either silently returns nothing or throws, depending on the version. Guard with dialect checks or abstract behind a data access layer.

• Editing migration files after they have been applied — Knex tracks applied migrations by filename. Editing or renaming a deployed migration breaks the migration state, causing subsequent migrations to fail or apply out of order.

Best Practices

• Always use parameterized queries — Knex handles this automatically through its API, but when using db.raw(), always use ? placeholders or named bindings.
• Use returning('*') on insert/update/delete to get affected rows (PostgreSQL only; MySQL and SQLite do not support this).
• Destroy the pool on shutdown — call await db.destroy() in graceful shutdown handlers to release all connections.
• Keep migrations idempotent and always implement both up and down functions.
• Use .first() instead of .limit(1)[0] for single-record lookups — it returns undefined instead of an empty array.
• Type your results with generics: db<User>('users').select('*') for basic type hints (though Knex typing is limited compared to Kysely).

Common Pitfalls

• No .returning() on MySQL/SQLite: Calling .returning() on dialects that don't support it silently fails or throws. Guard with dialect checks.
• Forgetting to await: Knex queries return thenables. Forgetting await means the query fires but results are ignored, leading to subtle bugs.
• Pool exhaustion: Not destroying connections or creating multiple Knex instances leaks connections. Use a singleton pattern.
• Migration ordering: Migrations run in alphabetical/timestamp order. Renaming migration files after they've been applied breaks the migration state.
• db.raw() SQL injection: Using string interpolation inside db.raw() bypasses parameterization. Always use db.raw('SELECT * FROM users WHERE id = ?', [id]).
• Mixing query builder and raw: Mixing .where() with .whereRaw() can produce unexpected SQL. Be consistent and double-check generated queries with .toSQL().