api-database-knex

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