api-database-knex

Name: api-database-knex
Author: agents-inc

17stars5forks

How to install api-database-knex?

agents-inc/skills/api-database-knex

$npx -y skills add agents-inc/skills --skill api-database-knex

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# Knex.js Patterns
2
3	> 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.
4
5	---
6
7	<critical_requirements>
8
9	## CRITICAL: Before Using This Skill
10
11	> All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, `import type`, named constants)
12
13	(You MUST initialize the knex instance ONCE per application and reuse it -- creating multiple instances leaks connection pools)
14
15	(You MUST use parameterized bindings (`?` for values, `??` for identifiers) in ALL `knex.raw()` calls -- string interpolation causes SQL injection)
16
17	(You MUST return or await the promise inside `knex.transaction()` handlers -- failing to do so causes the transaction connection to hang indefinitely)
18
19	(You MUST call `knex.destroy()` on graceful shutdown -- orphaned pools prevent the Node.js process from exiting)
20
21	</critical_requirements>
22
23	---
24
25	## Examples
26
27	- [Core Patterns](examples/core.md) -- Initialization, query builder, insert/update/delete, raw queries, TypeScript integration
28	- [Schema & Migrations](examples/schema-migrations.md) -- Schema builder, createTable, alterTable, migrations, seeds
29	- [Transactions & Advanced](examples/transactions-advanced.md) -- Transactions, batch insert, subqueries, connection pooling, multi-tenancy
30
31	Additional resources:
32
33	- [reference.md](reference.md) -- Query method cheat sheet, column types, pool options, anti-patterns, production checklist
34
35	---
36
37	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
38
39	When to use:
40
41	- Building SQL queries programmatically with a fluent API
42	- Database schema creation and modification (createTable, alterTable)
43	- Running and managing database migrations (up/down)
44	- Seeding development/test databases
45	- Wrapping multi-step database operations in transactions
46	- Writing raw SQL with safe parameter binding
47	- Batch inserting large datasets with chunking
48
49	Key patterns covered:
50
51	- Knex initialization with connection pool configuration
52	- Fluent query builder (select, where, join, orderBy, groupBy, having)
53	- Insert, update, delete with `.returning()` for PostgreSQL/MSSQL
54	- Schema builder (createTable, alterTable, column types, indexes, foreign keys)
55	- Migrations (knex migrate:make, up/down, transaction control)
56	- Seeds (knex seed:make, seed:run)
57	- Transactions with async/await and isolation levels
58	- Raw queries with `?` value bindings and `??` identifier bindings
59	- Subqueries as callbacks or builder instances
60	- Batch insert with `batchInsert()` and chunking
61	- TypeScript table type augmentation
62	- Connection pool tuning (min, max, acquireTimeout, lifetime)
63
64	When NOT to use:
65
66	- You need a full ORM with model relationships, lifecycle hooks, and identity maps -- use your ORM solution instead
67	- You need database-specific features Knex doesn't abstract (e.g., PostgreSQL LISTEN/NOTIFY, MySQL fulltext indexes) -- use `knex.raw()` for those
68	- Your project already uses a different query layer or ORM and doesn't need a second one
69
70	---
71
72	<philosophy>
73
74	## Philosophy
75
76	Knex is a SQL query builder, not an ORM. The core principle: you write SQL, Knex just makes it safer and more portable.
77
78	Core principles:
79
80	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.
81	2. Parameterize everything -- Use `?` bindings for values and `??` for identifiers. Never interpolate strings into queries.
82	3. Migrations are the source of truth -- Schema changes happen through migrations, not ad-hoc `knex.schema` calls in application code.
83	4. Transactions for consistency -- Any operation touching multiple tables or needing atomicity must be wrapped in `knex.transaction()`.
84	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.
85
86	</philosophy>
87
88	---
89
90	<patterns>
91
92	## Core Patterns
93
94	### Pattern 1: Knex Ini

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

api-database-knex

17stars5forks

View on GitHub

How to install api-database-knex?

agents-inc/skills/api-database-knex

$npx -y skills add agents-inc/skills --skill api-database-knex

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# Knex.js Patterns
2
3	> 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.
4
5	---
6
7	<critical_requirements>
8
9	## CRITICAL: Before Using This Skill
10
11	> All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, `import type`, named constants)
12
13	(You MUST initialize the knex instance ONCE per application and reuse it -- creating multiple instances leaks connection pools)
14
15	(You MUST use parameterized bindings (`?` for values, `??` for identifiers) in ALL `knex.raw()` calls -- string interpolation causes SQL injection)
16
17	(You MUST return or await the promise inside `knex.transaction()` handlers -- failing to do so causes the transaction connection to hang indefinitely)
18
19	(You MUST call `knex.destroy()` on graceful shutdown -- orphaned pools prevent the Node.js process from exiting)
20
21	</critical_requirements>
22
23	---
24
25	## Examples
26
27	- [Core Patterns](examples/core.md) -- Initialization, query builder, insert/update/delete, raw queries, TypeScript integration
28	- [Schema & Migrations](examples/schema-migrations.md) -- Schema builder, createTable, alterTable, migrations, seeds
29	- [Transactions & Advanced](examples/transactions-advanced.md) -- Transactions, batch insert, subqueries, connection pooling, multi-tenancy
30
31	Additional resources:
32
33	- [reference.md](reference.md) -- Query method cheat sheet, column types, pool options, anti-patterns, production checklist
34
35	---
36
37	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
38
39	When to use:
40
41	- Building SQL queries programmatically with a fluent API
42	- Database schema creation and modification (createTable, alterTable)
43	- Running and managing database migrations (up/down)
44	- Seeding development/test databases
45	- Wrapping multi-step database operations in transactions
46	- Writing raw SQL with safe parameter binding
47	- Batch inserting large datasets with chunking
48
49	Key patterns covered:
50
51	- Knex initialization with connection pool configuration
52	- Fluent query builder (select, where, join, orderBy, groupBy, having)
53	- Insert, update, delete with `.returning()` for PostgreSQL/MSSQL
54	- Schema builder (createTable, alterTable, column types, indexes, foreign keys)
55	- Migrations (knex migrate:make, up/down, transaction control)
56	- Seeds (knex seed:make, seed:run)
57	- Transactions with async/await and isolation levels
58	- Raw queries with `?` value bindings and `??` identifier bindings
59	- Subqueries as callbacks or builder instances
60	- Batch insert with `batchInsert()` and chunking
61	- TypeScript table type augmentation
62	- Connection pool tuning (min, max, acquireTimeout, lifetime)
63
64	When NOT to use:
65
66	- You need a full ORM with model relationships, lifecycle hooks, and identity maps -- use your ORM solution instead
67	- You need database-specific features Knex doesn't abstract (e.g., PostgreSQL LISTEN/NOTIFY, MySQL fulltext indexes) -- use `knex.raw()` for those
68	- Your project already uses a different query layer or ORM and doesn't need a second one
69
70	---
71
72	<philosophy>
73
74	## Philosophy
75
76	Knex is a SQL query builder, not an ORM. The core principle: you write SQL, Knex just makes it safer and more portable.
77
78	Core principles:
79
80	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.
81	2. Parameterize everything -- Use `?` bindings for values and `??` for identifiers. Never interpolate strings into queries.
82	3. Migrations are the source of truth -- Schema changes happen through migrations, not ad-hoc `knex.schema` calls in application code.
83	4. Transactions for consistency -- Any operation touching multiple tables or needing atomicity must be wrapped in `knex.transaction()`.
84	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.
85
86	</philosophy>
87
88	---
89
90	<patterns>
91
92	## Core Patterns
93
94	### Pattern 1: Knex Ini

People who liked this also viewed

6 picks

Type