0
# Knex.js
1

2
Knex.js is a comprehensive SQL query builder library for Node.js that provides a flexible, portable, and intuitive API for building SQL queries across multiple database systems including PostgreSQL, MySQL, CockroachDB, MSSQL, SQLite3, and Oracle. It features a chainable interface for constructing complex queries, schema building capabilities, transaction support, connection pooling, streaming queries, and supports both promise-based and callback-based APIs.
3

4
## Package Information
5

6
- **Package Name**: knex
7
- **Package Type**: npm
8
- **Language**: JavaScript/TypeScript
9
- **Installation**: `npm install knex`
10

11
## Core Imports
12

13
```javascript
14
const knex = require('knex');
15
```
16

17
For TypeScript:
18

19
```typescript
20
import knex from 'knex';
21
import { Knex } from 'knex';
22
```
23

24
For ESM:
25

26
```javascript
27
import knex from 'knex';
28
```
29

30
## Basic Usage
31

32
```javascript
33
const knex = require('knex')({
34
  client: 'sqlite3',
35
  connection: {
36
    filename: './mydb.sqlite'
37
  }
38
});
39

40
// Basic query
41
const users = await knex('users')
42
  .select('*')
43
  .where('age', '>', 18);
44

45
// Insert data
46
await knex('users').insert({
47
  name: 'John Doe',
48
  email: 'john@example.com',
49
  age: 25
50
});
51

52
// Update data
53
await knex('users')
54
  .where('id', 1)
55
  .update({ age: 26 });
56

57
// Create table
58
await knex.schema.createTable('posts', table => {
59
  table.increments('id');
60
  table.string('title').notNullable();
61
  table.text('content');
62
  table.integer('user_id').references('id').inTable('users');
63
  table.timestamps(true, true);
64
});
65
```
66

67
## Architecture
68

69
Knex.js is built around several key components:
70

71
- **Main Knex Instance**: Central factory that creates database connections and query builders
72
- **Query Builder**: Fluent interface for constructing SQL queries with method chaining
73
- **Schema Builder**: Database schema management for creating, altering, and dropping tables/views
74
- **Migration System**: Version control for database schema changes
75
- **Transaction Manager**: ACID transaction support with savepoints and rollbacks
76
- **Connection Pooling**: Efficient database connection management
77
- **Multi-Dialect Support**: Unified API across different database systems
78

79
## Capabilities
80

81
### Core Knex Instance
82

83
Main knex factory function and instance management for database connections and configuration.
84

85
```typescript { .api }
86
interface KnexConfig {
87
  client: string;
88
  connection: string | ConnectionConfig;
89
  pool?: PoolConfig;
90
  migrations?: MigratorConfig;
91
  seeds?: SeederConfig;
92
  debug?: boolean;
93
  useNullAsDefault?: boolean;
94
}
95

96
interface ConnectionConfig {
97
  host?: string;
98
  port?: number;
99
  user?: string;
100
  password?: string;
101
  database?: string;
102
  filename?: string;
103
  ssl?: boolean | object;
104
}
105

106
function knex(config: KnexConfig): Knex;
107
function knex<TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;
108

109
interface Knex {
110
  <TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;
111
  raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Knex.Raw;
112
  ref(columnName: string): Knex.Ref<string, { [K in string]: string }>;
113
  transaction<T>(callback: (trx: Knex.Transaction) => Promise<T>): Promise<T>;
114
  schema: Knex.SchemaBuilder;
115
  migrate: Knex.Migrator;
116
  seed: Knex.Seeder;
117
  fn: Knex.FunctionHelper;
118
  client: Knex.Client;
119
  destroy(): Promise<void>;
120
}
121
```
122

123
### Query Building
124

125
Comprehensive SQL query construction with a fluent chainable interface for SELECT, INSERT, UPDATE, DELETE operations and advanced query features.
126

127
```typescript { .api }
128
interface QueryBuilder<TRecord = any, TResult = any> {
129
  select<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;
130
  from<TTable extends TableNames>(tableName: TTable): QueryBuilder<ResolveTableType<TTable>, TResult>;
131
  where<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
132
  insert(data: TRecord | readonly TRecord[]): QueryBuilder<TRecord, number[]>;
133
  update(data: DbRecordArr<TRecord>): QueryBuilder<TRecord, number>;
134
  del(): QueryBuilder<TRecord, number>;
135
}
136
```
137

138
[Query Building](./query-builder.md)
139

140
### Schema Management  
141

142
Database schema operations for creating, altering, and managing tables, columns, indexes, and constraints across different database systems.
143

144
```typescript { .api }
145
interface SchemaBuilder {
146
  createTable(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;
147
  alterTable(tableName: string, callback: (tableBuilder: AlterTableBuilder) => any): SchemaBuilder;
148
  dropTable(tableName: string): SchemaBuilder;
149
  hasTable(tableName: string): Promise<boolean>;
150
  createSchema(schemaName: string): SchemaBuilder;
151
  dropSchema(schemaName: string): SchemaBuilder;
152
}
153

154
interface CreateTableBuilder {
155
  increments(columnName?: string): ColumnBuilder;
156
  integer(columnName: string): ColumnBuilder;
157
  string(columnName: string, length?: number): ColumnBuilder;
158
  text(columnName: string): ColumnBuilder;
159
  boolean(columnName: string): ColumnBuilder;
160
  date(columnName: string): ColumnBuilder;
161
  timestamp(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;
162
  primary(columnNames: readonly string[]): TableBuilder;
163
  unique(columnNames: readonly string[]): TableBuilder;
164
  foreign(column: string): ReferencingColumnBuilder;
165
}
166
```
167

168
[Schema Management](./schema-builder.md)
169

170
### Migrations & Seeds
171

172
Database version control through migrations and data seeding functionality for managing schema changes and populating databases.
173

174
```typescript { .api }
175
interface Migrator {
176
  make(name: string, config?: MigratorConfig): Promise<string>;
177
  latest(config?: MigratorConfig): Promise<[number, string[]]>;
178
  rollback(config?: MigratorConfig, all?: boolean): Promise<[number, string[]]>;
179
  status(config?: MigratorConfig): Promise<number>;
180
  currentVersion(config?: MigratorConfig): Promise<string>;
181
  list(config?: MigratorConfig): Promise<[string[], string[]]>;
182
}
183

184
interface Seeder {
185
  make(name: string, config?: SeederConfig): Promise<string>;
186
  run(config?: SeederConfig): Promise<[string[]]>;
187
}
188
```
189

190
[Migrations & Seeds](./migrations-seeds.md)
191

192
### Transactions & Raw Queries
193

194
Transaction management with ACID compliance and raw SQL query execution for complex operations and database-specific functionality.
195

196
```typescript { .api }
197
interface Transaction extends QueryBuilder {
198
  commit(value?: any): Promise<any>;
199
  rollback(error?: any): Promise<any>;
200
  savepoint<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;
201
  isCompleted(): boolean;
202
}
203

204
interface Raw<TResult = any> extends Promise<TResult> {
205
  wrap<TResult2 = TResult>(before: string, after: string): Raw<TResult2>;
206
  toSQL(): Sql;
207
  timeout(ms: number, options?: { cancel?: boolean }): Raw<TResult>;
208
}
209

210
function raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Raw;
211
function transaction<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;
212
```
213

214
[Transactions & Raw Queries](./transactions-raw.md)
215

216
### CLI Tools
217

218
Command-line interface for managing migrations, seeds, and project initialization with comprehensive tooling support.
219

220
```bash { .api }
221
# Initialize project
222
knex init
223

224
# Migration commands
225
knex migrate:make <name>
226
knex migrate:latest
227
knex migrate:rollback
228
knex migrate:up
229
knex migrate:down
230
knex migrate:currentVersion
231
knex migrate:list
232

233
# Seed commands  
234
knex seed:make <name>
235
knex seed:run
236
```
237

238
[CLI Tools](./cli.md)
239

240
## Types
241

242
```typescript { .api }
243
type RawBinding = Value | QueryBuilder | Raw;
244
type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
245
type ColumnName<TRecord> = keyof TRecord | string;
246
type TableName = string;
247

248
interface Sql {
249
  method: string;
250
  sql: string;
251
  bindings: readonly RawBinding[];
252
  options: any;
253
  toNative(): SqlNative;
254
}
255

256
interface SqlNative {
257
  sql: string;
258
  bindings: readonly RawBinding[];
259
}
260

261
interface ColumnBuilder {
262
  index(indexName?: string): ColumnBuilder;
263
  primary(): ColumnBuilder;
264
  unique(): ColumnBuilder;
265
  references(columnName: string): ReferencingColumnBuilder;
266
  onDelete(command: string): ColumnBuilder;
267
  onUpdate(command: string): ColumnBuilder;
268
  defaultTo(value: Value): ColumnBuilder;
269
  unsigned(): ColumnBuilder;
270
  notNullable(): ColumnBuilder;
271
  nullable(): ColumnBuilder;
272
  comment(val: string): ColumnBuilder;
273
}
274

275
interface ReferencingColumnBuilder extends ColumnBuilder {
276
  inTable(tableName: string): ColumnBuilder;
277
  deferrable(type: deferrableType): ColumnBuilder;
278
}
279

280
type deferrableType = 'not deferrable' | 'immediate' | 'deferred';
281

282
interface PoolConfig {
283
  min?: number;
284
  max?: number;
285
  createTimeoutMillis?: number;
286
  acquireTimeoutMillis?: number;
287
  idleTimeoutMillis?: number;
288
  reapIntervalMillis?: number;
289
  createRetryIntervalMillis?: number;
290
  propagateCreateError?: boolean;
291
}
292

293
interface MigratorConfig {
294
  database?: string;
295
  directory?: string | readonly string[];
296
  extension?: string;
297
  tableName?: string;
298
  schemaName?: string;
299
  disableTransactions?: boolean;
300
  sortDirsSeparately?: boolean;
301
  loadExtensions?: readonly string[];
302
  migrationSource?: MigrationSource<any>;
303
}
304

305
interface SeederConfig {
306
  database?: string;
307
  directory?: string | readonly string[];
308
  loadExtensions?: readonly string[];
309
  timestampFilenamePrefix?: boolean;
310
  sortDirsSeparately?: boolean;
311
}
312

313
interface MigrationSource<TMigrationSpec> {
314
  getMigrations(loadExtensions: readonly string[]): Promise<string[]>;
315
  getMigrationName(migration: string): string;
316
  getMigration(migration: string): Promise<TMigrationSpec>;
317
}
318

319
interface FunctionHelper {
320
  now(precision?: number): Raw;
321
  uuid(): Raw;
322
}
323
```