0
# Query Building
1

2
Comprehensive SQL query construction with a fluent chainable interface for SELECT, INSERT, UPDATE, DELETE operations and advanced query features including joins, aggregations, and window functions.
3

4
## Capabilities
5

6
### Core Query Builder
7

8
Creates a query builder instance for a specific table or starts a new query.
9

10
```typescript { .api }
11
/**
12
 * Create a query builder for a table
13
 * @param tableName - Name of the table to query
14
 * @returns QueryBuilder instance for chaining operations
15
 */
16
function knex<TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;
17

18
/**
19
 * Create a standalone query builder
20
 * @returns QueryBuilder instance
21
 */
22
function queryBuilder(): Knex.QueryBuilder;
23

24
interface QueryBuilder<TRecord = any, TResult = any> extends Promise<TResult> {
25
  // Core table methods
26
  from<TTable extends TableNames>(tableName: TTable): QueryBuilder<ResolveTableType<TTable>, TResult>;
27
  as(alias: string): QueryBuilder<TRecord, TResult>;
28
  table(tableName: string): QueryBuilder<TRecord, TResult>;
29
  withSchema(schemaName: string): QueryBuilder<TRecord, TResult>;
30
}
31
```
32

33
### Selection Methods
34

35
Build SELECT statements with column specification, aliasing, and distinct operations.
36

37
```typescript { .api }
38
/**
39
 * Select specific columns from the query
40
 * @param columns - Column names to select
41
 * @returns QueryBuilder with selected columns
42
 */
43
select<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;
44

45
/**
46
 * Select columns with aliases and expressions
47
 * @param columns - Object mapping aliases to column expressions
48
 * @returns QueryBuilder with aliased columns
49
 */
50
select<AliasUT extends string>(columns: Record<AliasUT, string | Raw | QueryBuilder>): QueryBuilder<TRecord, { [K in AliasUT]: any }[]>;
51

52
/**
53
 * Add columns to existing selection
54
 * @param columns - Additional columns to select
55
 * @returns QueryBuilder with added columns
56
 */
57
columns<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, TResult>;
58

59
/**
60
 * Select distinct values
61
 * @param columns - Columns for distinct operation
62
 * @returns QueryBuilder with distinct selection
63
 */
64
distinct<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;
65

66
/**
67
 * Select distinct on specific columns (PostgreSQL)
68
 * @param columns - Columns for distinct on
69
 * @returns QueryBuilder with distinct on
70
 */
71
distinctOn<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, TResult>;
72
```
73

74
### Where Clauses
75

76
Comprehensive WHERE clause construction with support for all SQL comparison operators and logical combinations.
77

78
```typescript { .api }
79
/**
80
 * Basic where clause
81
 * @param columnName - Column to filter on
82
 * @param value - Value to compare against
83
 * @returns QueryBuilder with where condition
84
 */
85
where<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
86

87
/**
88
 * Where clause with operator
89
 * @param columnName - Column to filter on
90
 * @param operator - Comparison operator
91
 * @param value - Value to compare against
92
 * @returns QueryBuilder with where condition
93
 */
94
where<K extends keyof TRecord>(columnName: K, operator: ComparisionOperator, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
95

96
/**
97
 * Where clause with object conditions
98
 * @param conditions - Object with column-value pairs
99
 * @returns QueryBuilder with multiple where conditions
100
 */
101
where(conditions: Partial<TRecord>): QueryBuilder<TRecord, TResult>;
102

103
/**
104
 * Where clause with raw SQL
105
 * @param raw - Raw SQL condition
106
 * @returns QueryBuilder with raw where condition
107
 */
108
where(raw: Raw): QueryBuilder<TRecord, TResult>;
109

110
/**
111
 * AND where clause
112
 * @param columnName - Column to filter on
113
 * @param value - Value to compare against
114
 * @returns QueryBuilder with AND where condition
115
 */
116
andWhere<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
117

118
/**
119
 * OR where clause
120
 * @param columnName - Column to filter on
121
 * @param value - Value to compare against
122
 * @returns QueryBuilder with OR where condition
123
 */
124
orWhere<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
125

126
/**
127
 * WHERE NOT clause
128
 * @param columnName - Column to filter on
129
 * @param value - Value to compare against
130
 * @returns QueryBuilder with WHERE NOT condition
131
 */
132
whereNot<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
133

134
/**
135
 * WHERE IN clause
136
 * @param columnName - Column to check
137
 * @param values - Array of values to match
138
 * @returns QueryBuilder with WHERE IN condition
139
 */
140
whereIn<K extends keyof TRecord>(columnName: K, values: readonly DbColumn<TRecord[K]>[]): QueryBuilder<TRecord, TResult>;
141

142
/**
143
 * WHERE NOT IN clause
144
 * @param columnName - Column to check
145
 * @param values - Array of values to exclude
146
 * @returns QueryBuilder with WHERE NOT IN condition
147
 */
148
whereNotIn<K extends keyof TRecord>(columnName: K, values: readonly DbColumn<TRecord[K]>[]): QueryBuilder<TRecord, TResult>;
149

150
/**
151
 * WHERE NULL clause
152
 * @param columnName - Column to check for null
153
 * @returns QueryBuilder with WHERE NULL condition
154
 */
155
whereNull<K extends keyof TRecord>(columnName: K): QueryBuilder<TRecord, TResult>;
156

157
/**
158
 * WHERE NOT NULL clause
159
 * @param columnName - Column to check for not null
160
 * @returns QueryBuilder with WHERE NOT NULL condition
161
 */
162
whereNotNull<K extends keyof TRecord>(columnName: K): QueryBuilder<TRecord, TResult>;
163

164
/**
165
 * WHERE BETWEEN clause
166
 * @param columnName - Column to check
167
 * @param range - Array with min and max values
168
 * @returns QueryBuilder with WHERE BETWEEN condition
169
 */
170
whereBetween<K extends keyof TRecord>(columnName: K, range: readonly [DbColumn<TRecord[K]>, DbColumn<TRecord[K]>]): QueryBuilder<TRecord, TResult>;
171

172
/**
173
 * WHERE NOT BETWEEN clause
174
 * @param columnName - Column to check
175
 * @param range - Array with min and max values
176
 * @returns QueryBuilder with WHERE NOT BETWEEN condition
177
 */
178
whereNotBetween<K extends keyof TRecord>(columnName: K, range: readonly [DbColumn<TRecord[K]>, DbColumn<TRecord[K]>]): QueryBuilder<TRecord, TResult>;
179

180
/**
181
 * WHERE EXISTS clause
182
 * @param callback - Subquery callback
183
 * @returns QueryBuilder with WHERE EXISTS condition
184
 */
185
whereExists(callback: QueryCallback<any, any>): QueryBuilder<TRecord, TResult>;
186

187
/**
188
 * WHERE NOT EXISTS clause
189
 * @param callback - Subquery callback
190
 * @returns QueryBuilder with WHERE NOT EXISTS condition
191
 */
192
whereNotExists(callback: QueryCallback<any, any>): QueryBuilder<TRecord, TResult>;
193

194
/**
195
 * WHERE LIKE clause
196
 * @param columnName - Column to check
197
 * @param pattern - LIKE pattern
198
 * @returns QueryBuilder with WHERE LIKE condition
199
 */
200
whereLike<K extends keyof TRecord>(columnName: K, pattern: string): QueryBuilder<TRecord, TResult>;
201

202
/**
203
 * WHERE ILIKE clause (case-insensitive)
204
 * @param columnName - Column to check
205
 * @param pattern - ILIKE pattern
206
 * @returns QueryBuilder with WHERE ILIKE condition
207
 */
208
whereILike<K extends keyof TRecord>(columnName: K, pattern: string): QueryBuilder<TRecord, TResult>;
209

210
/**
211
 * Raw WHERE clause
212
 * @param sql - Raw SQL condition
213
 * @param bindings - Parameter bindings
214
 * @returns QueryBuilder with raw WHERE condition
215
 */
216
whereRaw(sql: string, bindings?: RawBinding[]): QueryBuilder<TRecord, TResult>;
217
```
218

219
### Join Operations
220

221
Comprehensive JOIN support for combining tables with various join types and conditions.
222

223
```typescript { .api }
224
/**
225
 * Inner join with another table
226
 * @param tableName - Table to join
227
 * @param leftColumn - Left table column
228
 * @param rightColumn - Right table column
229
 * @returns QueryBuilder with inner join
230
 */
231
join(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;
232

233
/**
234
 * Inner join with callback for complex conditions
235
 * @param tableName - Table to join
236
 * @param callback - Join condition callback
237
 * @returns QueryBuilder with inner join
238
 */
239
join(tableName: string, callback: JoinCallback): QueryBuilder<TRecord, TResult>;
240

241
/**
242
 * Left outer join
243
 * @param tableName - Table to join
244
 * @param leftColumn - Left table column
245
 * @param rightColumn - Right table column
246
 * @returns QueryBuilder with left join
247
 */
248
leftJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;
249

250
/**
251
 * Right outer join
252
 * @param tableName - Table to join
253
 * @param leftColumn - Left table column
254
 * @param rightColumn - Right table column
255
 * @returns QueryBuilder with right join
256
 */
257
rightJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;
258

259
/**
260
 * Full outer join
261
 * @param tableName - Table to join
262
 * @param leftColumn - Left table column
263
 * @param rightColumn - Right table column
264
 * @returns QueryBuilder with full outer join
265
 */
266
fullOuterJoin(tableName: string, leftColumn: string, rightColumn: string): QueryBuilder<TRecord, TResult>;
267

268
/**
269
 * Cross join
270
 * @param tableName - Table to cross join
271
 * @returns QueryBuilder with cross join
272
 */
273
crossJoin(tableName: string): QueryBuilder<TRecord, TResult>;
274

275
/**
276
 * Raw join clause
277
 * @param sql - Raw SQL join statement
278
 * @param bindings - Parameter bindings
279
 * @returns QueryBuilder with raw join
280
 */
281
joinRaw(sql: string, bindings?: RawBinding[]): QueryBuilder<TRecord, TResult>;
282

283
interface JoinCallback {
284
  (this: JoinClause, join: JoinClause): void;
285
}
286

287
interface JoinClause {
288
  on(left: string, operator: string, right: string): JoinClause;
289
  on(left: string, right: string): JoinClause;
290
  onIn(left: string, values: any[]): JoinClause;
291
  onNotIn(left: string, values: any[]): JoinClause;
292
  onNull(column: string): JoinClause;
293
  onNotNull(column: string): JoinClause;
294
  onExists(callback: QueryCallback<any, any>): JoinClause;
295
  onNotExists(callback: QueryCallback<any, any>): JoinClause;
296
  onBetween(column: string, range: [any, any]): JoinClause;
297
  onNotBetween(column: string, range: [any, any]): JoinClause;
298
  using(columns: string | readonly string[]): JoinClause;
299
}
300
```
301

302
### Aggregation Functions
303

304
SQL aggregation functions for data analysis and reporting.
305

306
```typescript { .api }
307
/**
308
 * Count rows or specific column values
309
 * @param column - Column to count (optional, defaults to *)
310
 * @returns QueryBuilder with count aggregation
311
 */
312
count<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column?: string): QueryBuilder<TRecord, TResult2>;
313

314
/**
315
 * Count distinct values
316
 * @param column - Column to count distinct values
317
 * @returns QueryBuilder with count distinct aggregation
318
 */
319
countDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;
320

321
/**
322
 * Sum numeric values
323
 * @param column - Column to sum
324
 * @returns QueryBuilder with sum aggregation
325
 */
326
sum<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;
327

328
/**
329
 * Sum distinct numeric values
330
 * @param column - Column to sum distinct values
331
 * @returns QueryBuilder with sum distinct aggregation
332
 */
333
sumDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;
334

335
/**
336
 * Calculate average of numeric values
337
 * @param column - Column to average
338
 * @returns QueryBuilder with average aggregation
339
 */
340
avg<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;
341

342
/**
343
 * Calculate average of distinct numeric values
344
 * @param column - Column to average distinct values
345
 * @returns QueryBuilder with average distinct aggregation
346
 */
347
avgDistinct<TResult2 = AggregationQueryResult<TResult, Dict<string | number>>>(column: string): QueryBuilder<TRecord, TResult2>;
348

349
/**
350
 * Find minimum value
351
 * @param column - Column to find minimum
352
 * @returns QueryBuilder with min aggregation
353
 */
354
min<TResult2 = AggregationQueryResult<TResult, Dict<any>>>(column: string): QueryBuilder<TRecord, TResult2>;
355

356
/**
357
 * Find maximum value
358
 * @param column - Column to find maximum
359
 * @returns QueryBuilder with max aggregation
360
 */
361
max<TResult2 = AggregationQueryResult<TResult, Dict<any>>>(column: string): QueryBuilder<TRecord, TResult2>;
362
```
363

364
### Window Functions
365

366
Advanced SQL window functions for analytical queries.
367

368
```typescript { .api }
369
/**
370
 * ROW_NUMBER window function
371
 * @returns Window function builder
372
 */
373
rowNumber(): Knex.Ref<string, { [x: string]: string }>;
374

375
/**
376
 * RANK window function
377
 * @returns Window function builder
378
 */
379
rank(): Knex.Ref<string, { [x: string]: string }>;
380

381
/**
382
 * DENSE_RANK window function
383
 * @returns Window function builder
384
 */
385
denseRank(): Knex.Ref<string, { [x: string]: string }>;
386
```
387

388
### Data Manipulation
389

390
INSERT, UPDATE, DELETE operations with comprehensive options and return value handling.
391

392
```typescript { .api }
393
/**
394
 * Insert data into table
395
 * @param data - Data to insert (single record or array)
396
 * @param returning - Columns to return after insert
397
 * @returns QueryBuilder for insert operation
398
 */
399
insert<TResult2 = number[]>(data: TRecord | readonly TRecord[], returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;
400

401
/**
402
 * Update existing records
403
 * @param data - Data to update
404
 * @param returning - Columns to return after update
405
 * @returns QueryBuilder for update operation
406
 */
407
update<TResult2 = number>(data: DbRecordArr<TRecord>, returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;
408

409
/**
410
 * Delete records
411
 * @param returning - Columns to return after delete
412
 * @returns QueryBuilder for delete operation
413
 */
414
del<TResult2 = number>(returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;
415

416
/**
417
 * Delete records (alias for del)
418
 * @param returning - Columns to return after delete
419
 * @returns QueryBuilder for delete operation
420
 */
421
delete<TResult2 = number>(returning?: string | readonly string[]): QueryBuilder<TRecord, TResult2>;
422

423
/**
424
 * Truncate table
425
 * @returns QueryBuilder for truncate operation
426
 */
427
truncate(): QueryBuilder<TRecord, void>;
428

429
/**
430
 * Specify returning columns for DML operations
431
 * @param columns - Columns to return
432
 * @returns QueryBuilder with returning clause
433
 */
434
returning<T extends string | readonly string[]>(columns: T): QueryBuilder<TRecord, DeferredKeySelection.Resolve<TRecord, T>[]>;
435

436
/**
437
 * Upsert operation (insert or update)
438
 * @param data - Data to upsert
439
 * @param conflictColumns - Columns that define conflicts
440
 * @param updateColumns - Columns to update on conflict
441
 * @returns QueryBuilder for upsert operation
442
 */
443
upsert(data: TRecord | readonly TRecord[], conflictColumns?: string | readonly string[], updateColumns?: string | readonly string[]): QueryBuilder<TRecord, number>;
444

445
/**
446
 * Handle insert conflicts with ON CONFLICT clause
447
 * @param columns - Conflict columns
448
 * @returns OnConflictQueryBuilder for conflict handling
449
 */
450
onConflict<TResult2 = number>(columns?: string | readonly string[]): OnConflictQueryBuilder<TRecord, TResult2>;
451
```
452

453
### Set Operations
454

455
Combine queries using UNION, INTERSECT, and EXCEPT operations.
456

457
```typescript { .api }
458
/**
459
 * Union with another query
460
 * @param callback - Query callback or array of callbacks
461
 * @param wrap - Whether to wrap in parentheses
462
 * @returns QueryBuilder with union
463
 */
464
union(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;
465

466
/**
467
 * Union all with another query
468
 * @param callback - Query callback or array of callbacks
469
 * @param wrap - Whether to wrap in parentheses
470
 * @returns QueryBuilder with union all
471
 */
472
unionAll(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;
473

474
/**
475
 * Intersect with another query
476
 * @param callback - Query callback or array of callbacks
477
 * @param wrap - Whether to wrap in parentheses
478
 * @returns QueryBuilder with intersect
479
 */
480
intersect(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;
481

482
/**
483
 * Except (difference) with another query
484
 * @param callback - Query callback or array of callbacks
485
 * @param wrap - Whether to wrap in parentheses
486
 * @returns QueryBuilder with except
487
 */
488
except(callback: MaybeArray<QueryCallback<any, any> | QueryBuilder | Raw>, wrap?: boolean): QueryBuilder<TRecord, TResult>;
489
```
490

491
### Grouping and Ordering
492

493
GROUP BY, ORDER BY, and HAVING clauses for organizing query results.
494

495
```typescript { .api }
496
/**
497
 * Group by columns
498
 * @param columns - Columns to group by
499
 * @returns QueryBuilder with group by
500
 */
501
groupBy<K extends keyof TRecord>(...columns: readonly K[]): QueryBuilder<TRecord, TResult>;
502

503
/**
504
 * Group by with raw SQL
505
 * @param sql - Raw SQL for group by
506
 * @param bindings - Parameter bindings
507
 * @returns QueryBuilder with raw group by
508
 */
509
groupByRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;
510

511
/**
512
 * Order by columns
513
 * @param column - Column to order by
514
 * @param order - Sort order ('asc' or 'desc')
515
 * @returns QueryBuilder with order by
516
 */
517
orderBy<K extends keyof TRecord>(column: K, order?: 'asc' | 'desc'): QueryBuilder<TRecord, TResult>;
518

519
/**
520
 * Order by with raw SQL
521
 * @param sql - Raw SQL for order by
522
 * @param bindings - Parameter bindings
523
 * @returns QueryBuilder with raw order by
524
 */
525
orderByRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;
526

527
/**
528
 * Having clause
529
 * @param column - Column for having condition
530
 * @param operator - Comparison operator
531
 * @param value - Value to compare against
532
 * @returns QueryBuilder with having condition
533
 */
534
having(column: string, operator: string, value: any): QueryBuilder<TRecord, TResult>;
535

536
/**
537
 * Having clause with raw SQL
538
 * @param sql - Raw SQL for having
539
 * @param bindings - Parameter bindings
540
 * @returns QueryBuilder with raw having
541
 */
542
havingRaw(sql: string, bindings?: readonly RawBinding[]): QueryBuilder<TRecord, TResult>;
543

544
/**
545
 * OR having clause
546
 * @param column - Column for having condition
547
 * @param operator - Comparison operator
548
 * @param value - Value to compare against
549
 * @returns QueryBuilder with OR having condition
550
 */
551
orHaving(column: string, operator: string, value: any): QueryBuilder<TRecord, TResult>;
552
```
553

554
### Limit and Offset
555

556
Control result set size and pagination.
557

558
```typescript { .api }
559
/**
560
 * Limit number of results
561
 * @param limit - Maximum number of rows to return
562
 * @returns QueryBuilder with limit
563
 */
564
limit(limit: number): QueryBuilder<TRecord, TResult>;
565

566
/**
567
 * Offset results for pagination
568
 * @param offset - Number of rows to skip
569
 * @returns QueryBuilder with offset
570
 */
571
offset(offset: number): QueryBuilder<TRecord, TResult>;
572
```
573

574
### Utility Methods
575

576
Helper methods for query execution and result processing.
577

578
```typescript { .api }
579
/**
580
 * Get first row from results
581
 * @returns QueryBuilder that returns single record
582
 */
583
first<TResult2 = TRecord extends any[] ? TRecord[0] | undefined : TRecord>(): QueryBuilder<TRecord, TResult2>;
584

585
/**
586
 * Pluck values from a specific column
587
 * @param column - Column to extract values from
588
 * @returns QueryBuilder that returns array of column values
589
 */
590
pluck<K extends keyof TRecord>(column: K): QueryBuilder<TRecord, TRecord[K][]>;
591

592
/**
593
 * Increment a numeric column
594
 * @param column - Column to increment
595
 * @param amount - Amount to increment by (default 1)
596
 * @returns QueryBuilder for increment operation
597
 */
598
increment<K extends keyof TRecord>(column: K, amount?: number): QueryBuilder<TRecord, number>;
599

600
/**
601
 * Decrement a numeric column
602
 * @param column - Column to decrement
603
 * @param amount - Amount to decrement by (default 1)
604
 * @returns QueryBuilder for decrement operation
605
 */
606
decrement<K extends keyof TRecord>(column: K, amount?: number): QueryBuilder<TRecord, number>;
607

608
/**
609
 * Modify query with callback function
610
 * @param callback - Function to modify query
611
 * @param args - Additional arguments for callback
612
 * @returns Modified QueryBuilder
613
 */
614
modify<TRecord2 = TRecord, TResult2 = TResult>(callback: QueryCallbackWithArgs<TRecord, TResult>, ...args: any[]): QueryBuilder<TRecord2, TResult2>;
615

616
/**
617
 * Clone the query builder
618
 * @returns Cloned QueryBuilder instance
619
 */
620
clone(): QueryBuilder<TRecord, TResult>;
621
```
622

623
### Execution and Control
624

625
Methods for query execution, debugging, and performance optimization.
626

627
```typescript { .api }
628
/**
629
 * Use specific database connection
630
 * @param connection - Database connection to use
631
 * @returns QueryBuilder with specific connection
632
 */
633
connection(connection: any): QueryBuilder<TRecord, TResult>;
634

635
/**
636
 * Use transaction for query
637
 * @param transaction - Transaction instance
638
 * @returns QueryBuilder within transaction
639
 */
640
transacting(transaction: Transaction): QueryBuilder<TRecord, TResult>;
641

642
/**
643
 * Set query timeout
644
 * @param ms - Timeout in milliseconds
645
 * @param options - Timeout options
646
 * @returns QueryBuilder with timeout
647
 */
648
timeout(ms: number, options?: { cancel?: boolean }): QueryBuilder<TRecord, TResult>;
649

650
/**
651
 * Enable/disable debug mode
652
 * @param enabled - Whether to enable debugging
653
 * @returns QueryBuilder with debug setting
654
 */
655
debug(enabled?: boolean): QueryBuilder<TRecord, TResult>;
656

657
/**
658
 * Get SQL query object
659
 * @returns SQL object with query and bindings
660
 */
661
toSQL(): Sql;
662

663
/**
664
 * Get SQL query string
665
 * @returns SQL query as string
666
 */
667
toString(): string;
668

669
/**
670
 * Get column information for table
671
 * @param column - Specific column (optional)
672
 * @returns Promise with column information
673
 */
674
columnInfo(column?: string): Promise<ColumnInfo>;
675

676
/**
677
 * Convert to callback-style interface
678
 * @param callback - Node.js style callback
679
 * @returns void
680
 */
681
asCallback(callback: Function): void;
682

683
/**
684
 * Return results as readable stream
685
 * @param options - Stream options
686
 * @returns Readable stream of results
687
 */
688
stream(options?: Readonly<{ objectMode?: boolean; highWaterMark?: number }>): NodeJS.ReadableStream;
689

690
/**
691
 * Pipe results to writable stream
692
 * @param writable - Destination stream
693
 * @param options - Pipe options
694
 * @returns Destination stream
695
 */
696
pipe<T extends NodeJS.WritableStream>(writable: T, options?: { end?: boolean }): T;
697
```
698

699
## Types
700

701
```typescript { .api }
702
type ComparisionOperator = '=' | '>' | '>=' | '<' | '<=' | '<>' | '!=' | 'like' | 'ilike';
703
type QueryCallback<TRecord, TResult> = (this: QueryBuilder<TRecord, TResult>, builder: QueryBuilder<TRecord, TResult>) => void;
704
type QueryCallbackWithArgs<TRecord, TResult> = (this: QueryBuilder<TRecord, TResult>, builder: QueryBuilder<TRecord, TResult>, ...args: any[]) => void;
705
type JoinCallback = (this: JoinClause, join: JoinClause) => void;
706
type DbColumn<T> = T extends string | number | boolean | Date | null | undefined ? T : never;
707
type DbRecordArr<T> = Partial<T>;
708
type Dict<T = any> = { [k: string]: T };
709
type MaybeArray<T> = T | T[];
710

711
interface OnConflictQueryBuilder<TRecord = any, TResult = any> {
712
  ignore(): QueryBuilder<TRecord, TResult>;
713
  merge(updates?: Partial<TRecord>): QueryBuilder<TRecord, TResult>;
714
  update(updates: Partial<TRecord>): QueryBuilder<TRecord, TResult>;
715
}
716

717
interface ColumnInfo {
718
  defaultValue: Value;
719
  type: string;
720
  maxLength: number;
721
  nullable: boolean;
722
}
723

724
interface AggregationQueryResult<TResult, TAggregationResult> {
725
  [key: string]: TAggregationResult;
726
}
727
```
728

729
**Usage Examples:**
730

731
```javascript
732
const knex = require('knex')({ client: 'sqlite3', connection: ':memory:' });
733

734
// Basic queries
735
const users = await knex('users').select('*').where('active', true);
736
const user = await knex('users').where('id', 1).first();
737

738
// Complex joins
739
const postsWithAuthors = await knex('posts')
740
  .join('users', 'posts.user_id', 'users.id')
741
  .select('posts.title', 'posts.content', 'users.name as author')
742
  .where('posts.published', true);
743

744
// Aggregations
745
const stats = await knex('orders')
746
  .select('user_id')
747
  .count('* as order_count')
748
  .sum('total as revenue')
749
  .groupBy('user_id')
750
  .having('order_count', '>', 5);
751

752
// Subqueries
753
const recentOrders = await knex('orders')
754
  .where('created_at', '>', 
755
    knex('orders').max('created_at').where('user_id', 1)
756
  );
757

758
// Window functions
759
const rankedProducts = await knex('products')
760
  .select('*', knex.raw('ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank'))
761
  .where(knex.raw('ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC)'), '<=', 3);
762
```