0
# Transactions & Raw Queries
1

2
Transaction management with ACID compliance and raw SQL query execution for complex operations and database-specific functionality that requires precise control over SQL generation.
3

4
## Capabilities
5

6
### Transaction Management
7

8
ACID-compliant transaction support with nested transactions, savepoints, and comprehensive rollback capabilities.
9

10
```typescript { .api }
11
/**
12
 * Execute operations within a transaction
13
 * @param callback - Function containing transaction operations
14
 * @param config - Optional transaction configuration
15
 * @returns Promise resolving to callback result
16
 */
17
function transaction<T>(callback: (trx: Knex.Transaction) => Promise<T>, config?: TransactionConfig): Promise<T>;
18

19
/**
20
 * Create a transaction provider for reusable transaction configuration
21
 * @param config - Transaction configuration
22
 * @returns Function that creates transactions with the given config
23
 */
24
function transactionProvider(config?: TransactionConfig): <T>(callback: (trx: Knex.Transaction) => Promise<T>) => Promise<T>;
25

26
interface Transaction extends Knex.QueryBuilder {
27
  /**
28
   * Commit the transaction
29
   * @param value - Optional value to resolve the transaction with
30
   * @returns Promise that resolves when transaction is committed
31
   */
32
  commit(value?: any): Promise<any>;
33

34
  /**
35
   * Rollback the transaction
36
   * @param error - Optional error to reject the transaction with
37
   * @returns Promise that rejects when transaction is rolled back
38
   */
39
  rollback(error?: any): Promise<any>;
40

41
  /**
42
   * Create a savepoint within the transaction
43
   * @param callback - Function to execute within the savepoint
44
   * @returns Promise resolving to callback result
45
   */
46
  savepoint<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;
47

48
  /**
49
   * Check if the transaction has been completed
50
   * @returns true if transaction has been committed or rolled back
51
   */
52
  isCompleted(): boolean;
53

54
  /**
55
   * Promise that resolves when transaction execution completes
56
   */
57
  executionPromise: Promise<any>;
58

59
  /**
60
   * Reference to parent transaction (for nested transactions)
61
   */
62
  parentTransaction?: Transaction;
63

64
  /**
65
   * Transaction-specific user parameters
66
   */
67
  userParams: Record<string, any>;
68
}
69

70
interface TransactionConfig {
71
  /**
72
   * Transaction isolation level
73
   */
74
  isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable';
75

76
  /**
77
   * Whether the transaction is read-only
78
   */
79
  readOnly?: boolean;
80

81
  /**
82
   * Custom connection to use for the transaction
83
   */
84
  connection?: any;
85

86
  /**
87
   * Whether to disable acquiring connection from pool
88
   */
89
  doNotRejectOnRollback?: boolean;
90
}
91
```
92

93
### Raw Query Execution
94

95
Direct SQL execution with parameter binding and result processing for database-specific operations.
96

97
```typescript { .api }
98
/**
99
 * Create a raw SQL query
100
 * @param sql - Raw SQL string with optional parameter placeholders
101
 * @param bindings - Values to bind to SQL parameters
102
 * @returns Raw query instance
103
 */
104
function raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Knex.Raw;
105

106
/**
107
 * Create a column reference
108
 * @param columnName - Name of the column to reference
109
 * @returns Reference object for use in queries
110
 */
111
function ref<TMapping extends Record<string, string>>(columnName: string): Knex.Ref<string, TMapping>;
112

113
interface Raw<TResult = any> extends Promise<TResult> {
114
  /**
115
   * Wrap the raw query with prefix and suffix strings
116
   * @param before - String to prepend
117
   * @param after - String to append
118
   * @returns New Raw instance with wrapped SQL
119
   */
120
  wrap<TResult2 = TResult>(before: string, after: string): Raw<TResult2>;
121

122
  /**
123
   * Set timeout for the raw query
124
   * @param ms - Timeout in milliseconds
125
   * @param options - Timeout options
126
   * @returns Raw query with timeout
127
   */
128
  timeout(ms: number, options?: { cancel?: boolean }): Raw<TResult>;
129

130
  /**
131
   * Get SQL representation of the raw query
132
   * @returns SQL object with query string and bindings
133
   */
134
  toSQL(): Sql;
135

136
  /**
137
   * Convert raw query to string representation
138
   * @returns SQL string
139
   */
140
  toString(): string;
141

142
  /**
143
   * Set or get query context
144
   * @param context - Context object to set
145
   * @returns Raw query with context or current context
146
   */
147
  queryContext(context?: any): Raw<TResult>;
148

149
  /**
150
   * Convert to callback-style interface
151
   * @param callback - Node.js style callback
152
   * @returns void
153
   */
154
  asCallback(callback: (err: any, result: TResult) => void): void;
155

156
  /**
157
   * Return results as readable stream
158
   * @param options - Stream options
159
   * @returns Readable stream of results
160
   */
161
  stream(options?: Readonly<{ objectMode?: boolean; highWaterMark?: number }>): NodeJS.ReadableStream;
162

163
  /**
164
   * Pipe results to writable stream
165
   * @param writable - Destination stream
166
   * @param options - Pipe options
167
   * @returns Destination stream
168
   */
169
  pipe<T extends NodeJS.WritableStream>(writable: T, options?: { end?: boolean }): T;
170
}
171

172
interface Ref<TSrc extends string, TMapping extends Record<string, any>> {
173
  /**
174
   * Create an aliased reference
175
   * @param alias - Alias name for the reference
176
   * @returns Aliased reference
177
   */
178
  as<TAlias extends string>(alias: TAlias): Ref<TSrc, { [K in TAlias]: any }>;
179

180
  /**
181
   * Use the reference with a specific schema
182
   * @param schema - Schema name
183
   * @returns Reference with schema context
184
   */
185
  withSchema(schema: string): Ref<TSrc, TMapping>;
186

187
  /**
188
   * Convert reference to SQL representation
189
   * @returns SQL object
190
   */
191
  toSQL(): Sql;
192

193
  /**
194
   * Convert reference to string
195
   * @returns SQL string representation
196
   */
197
  toString(): string;
198
}
199
```
200

201
### Batch Operations
202

203
Efficient bulk operations for inserting large datasets with automatic chunking.
204

205
```typescript { .api }
206
/**
207
 * Insert data in batches to handle large datasets efficiently
208
 * @param tableName - Target table name
209
 * @param data - Array of records to insert
210
 * @param chunkSize - Number of records per batch (default: 1000)
211
 * @returns Promise that resolves when all batches are inserted
212
 */
213
function batchInsert(tableName: string, data: readonly any[], chunkSize?: number): Promise<any>;
214

215
/**
216
 * Batch insert with custom options
217
 * @param tableName - Target table name
218
 * @param data - Array of records to insert
219
 * @param chunkSize - Number of records per batch
220
 * @param options - Batch insert options
221
 * @returns Promise that resolves when all batches are inserted
222
 */
223
function batchInsert(tableName: string, data: readonly any[], chunkSize: number, options: BatchInsertOptions): Promise<any>;
224

225
interface BatchInsertOptions {
226
  /**
227
   * Whether to use transactions for each batch
228
   */
229
  useTransaction?: boolean;
230

231
  /**
232
   * Custom transaction to use for all batches
233
   */
234
  transaction?: Transaction;
235

236
  /**
237
   * Columns to return after insert
238
   */
239
  returning?: string | readonly string[];
240
}
241
```
242

243
### Connection Management
244

245
Direct connection management for advanced use cases requiring connection pooling control.
246

247
```typescript { .api }
248
/**
249
 * Initialize the connection pool
250
 * @param config - Optional initialization configuration
251
 * @returns Promise that resolves when pool is initialized
252
 */
253
function initialize(config?: any): Promise<void>;
254

255
/**
256
 * Destroy the connection pool and close all connections
257
 * @param callback - Optional callback for completion
258
 * @returns Promise that resolves when pool is destroyed
259
 */
260
function destroy(callback?: (err?: any) => void): Promise<void>;
261

262
/**
263
 * Get a connection from the pool
264
 * @returns Promise resolving to database connection
265
 */
266
function acquireConnection(): Promise<any>;
267

268
/**
269
 * Release a connection back to the pool
270
 * @param connection - Connection to release
271
 * @returns Promise that resolves when connection is released
272
 */
273
function releaseConnection(connection: any): Promise<void>;
274

275
/**
276
 * Create a new knex instance with user parameters
277
 * @param params - User parameters to attach
278
 * @returns New knex instance with parameters
279
 */
280
function withUserParams(params: Record<string, any>): Knex;
281
```
282

283
### Function Helpers
284

285
Database function helpers for common operations that vary across database systems.
286

287
```typescript { .api }
288
/**
289
 * Access function helpers for database-specific functions
290
 */
291
fn: Knex.FunctionHelper;
292

293
interface FunctionHelper {
294
  /**
295
   * Current timestamp function with optional precision
296
   * @param precision - Optional precision for timestamp
297
   * @returns Raw SQL for current timestamp
298
   */
299
  now(precision?: number): Raw;
300

301
  /**
302
   * Generate UUID using database-specific function
303
   * @returns Raw SQL for UUID generation
304
   */
305
  uuid(): Raw;
306

307
  /**
308
   * Convert UUID to binary format (MySQL)
309
   * @param uuid - UUID string or column reference
310
   * @param ordered - Whether to use ordered binary format
311
   * @returns Raw SQL for UUID to binary conversion
312
   */
313
  uuidToBin(uuid: string | Raw, ordered?: boolean): Raw;
314

315
  /**
316
   * Convert binary to UUID format (MySQL)
317
   * @param binary - Binary data or column reference
318
   * @param ordered - Whether binary is in ordered format
319
   * @returns Raw SQL for binary to UUID conversion
320
   */
321
  binToUuid(binary: string | Raw, ordered?: boolean): Raw;
322
}
323
```
324

325
### Event System
326

327
Event emission and handling for query lifecycle monitoring and debugging.
328

329
```typescript { .api }
330
/**
331
 * Add event listener
332
 * @param event - Event name
333
 * @param callback - Event handler function
334
 * @returns Knex instance for chaining
335
 */
336
on(event: string, callback: Function): Knex;
337

338
/**
339
 * Remove event listener
340
 * @param event - Event name
341
 * @param callback - Event handler function to remove
342
 * @returns Knex instance for chaining
343
 */
344
off(event: string, callback: Function): Knex;
345

346
/**
347
 * Add one-time event listener
348
 * @param event - Event name
349
 * @param callback - Event handler function
350
 * @returns Knex instance for chaining
351
 */
352
once(event: string, callback: Function): Knex;
353

354
/**
355
 * Remove all listeners for an event
356
 * @param event - Event name
357
 * @returns Knex instance for chaining
358
 */
359
removeAllListeners(event?: string): Knex;
360

361
// Event types
362
interface QueryEvent {
363
  __knexUid: string;
364
  __knexTxId?: string;
365
  sql: string;
366
  bindings: readonly RawBinding[];
367
  options: any;
368
  queryContext?: any;
369
}
370

371
interface QueryResponseEvent extends QueryEvent {
372
  response: any;
373
  duration: [number, number]; // [seconds, nanoseconds]
374
}
375

376
interface QueryErrorEvent extends QueryEvent {
377
  error: Error;
378
  duration: [number, number]; // [seconds, nanoseconds]
379
}
380
```
381

382
## Types
383

384
```typescript { .api }
385
type RawBinding = Value | QueryBuilder | Raw;
386
type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
387
type ValueDict = Record<string, RawBinding>;
388

389
interface Sql {
390
  method: string;
391
  sql: string;
392
  bindings: readonly RawBinding[];
393
  options: any;
394
  toNative(): SqlNative;
395
}
396

397
interface SqlNative {
398
  sql: string;
399
  bindings: readonly RawBinding[];
400
}
401

402
interface QueryContext {
403
  [key: string]: any;
404
}
405

406
interface ConnectionConfig {
407
  host?: string;
408
  port?: number;
409
  user?: string;
410
  password?: string;
411
  database?: string;
412
  ssl?: boolean | object;
413
  connection?: {
414
    timezone?: string;
415
    charset?: string;
416
    typeCast?: boolean | ((field: any, next: () => void) => any);
417
  };
418
}
419

420
interface PoolConfig {
421
  min?: number;
422
  max?: number;
423
  createTimeoutMillis?: number;
424
  acquireTimeoutMillis?: number;
425
  idleTimeoutMillis?: number;
426
  reapIntervalMillis?: number;
427
  createRetryIntervalMillis?: number;
428
  propagateCreateError?: boolean;
429
}
430
```
431

432
**Usage Examples:**
433

434
```javascript
435
const knex = require('knex')({ client: 'postgresql', connection: process.env.DATABASE_URL });
436

437
// Basic transaction
438
await knex.transaction(async trx => {
439
  const user = await trx('users').insert({
440
    email: 'john@example.com',
441
    name: 'John Doe'
442
  }).returning('*');
443

444
  await trx('user_profiles').insert({
445
    user_id: user[0].id,
446
    bio: 'Software developer'
447
  });
448

449
  // Transaction will automatically commit if no errors
450
});
451

452
// Transaction with explicit commit/rollback control
453
const trx = await knex.transaction();
454
try {
455
  const result = await trx('users').insert(userData).returning('*');
456
  await trx('logs').insert({ action: 'user_created', user_id: result[0].id });
457
  
458
  await trx.commit();
459
  console.log('Transaction committed');
460
} catch (error) {
461
  await trx.rollback();
462
  console.error('Transaction rolled back:', error);
463
}
464

465
// Savepoints for nested transactions
466
await knex.transaction(async trx => {
467
  await trx('users').insert({ name: 'User 1' });
468
  
469
  await trx.savepoint(async sp => {
470
    await sp('users').insert({ name: 'User 2' });
471
    // This will rollback to the savepoint if it fails
472
    throw new Error('Rollback to savepoint');
473
  });
474
  
475
  // This insert will still happen
476
  await trx('users').insert({ name: 'User 3' });
477
});
478

479
// Raw queries with parameter binding
480
const users = await knex.raw('SELECT * FROM users WHERE age > ? AND city = ?', [18, 'New York']);
481

482
// Raw query with named parameters
483
const result = await knex.raw('SELECT * FROM users WHERE name = :name', { name: 'John' });
484

485
// Complex raw query with joins
486
const stats = await knex.raw(`
487
  SELECT 
488
    u.id,
489
    u.name,
490
    COUNT(p.id) as post_count,
491
    AVG(p.view_count) as avg_views
492
  FROM users u
493
  LEFT JOIN posts p ON u.id = p.user_id
494
  WHERE u.created_at > ?
495
  GROUP BY u.id, u.name
496
  HAVING COUNT(p.id) > ?
497
  ORDER BY post_count DESC
498
`, [new Date('2023-01-01'), 5]);
499

500
// Raw query with transaction
501
await knex.transaction(async trx => {
502
  await trx.raw('LOCK TABLE users IN EXCLUSIVE MODE');
503
  
504
  const maxId = await trx.raw('SELECT MAX(id) as max_id FROM users');
505
  const nextId = maxId.rows[0].max_id + 1;
506
  
507
  await trx.raw('INSERT INTO users (id, name) VALUES (?, ?)', [nextId, 'New User']);
508
});
509

510
// Column references
511
const query = knex('posts')
512
  .select('title', knex.ref('users.name').as('author_name'))
513
  .join('users', 'posts.user_id', 'users.id')
514
  .where(knex.ref('posts.published_at'), '>', knex.fn.now());
515

516
// Function helpers
517
await knex('events').insert({
518
  name: 'User signup',
519
  event_id: knex.fn.uuid(),
520
  created_at: knex.fn.now()
521
});
522

523
// MySQL UUID functions
524
await knex('sessions').insert({
525
  id: knex.fn.uuidToBin(knex.fn.uuid(), true),
526
  user_id: userId,
527
  expires_at: knex.fn.now()
528
});
529

530
// Batch insert for large datasets
531
const users = [];
532
for (let i = 0; i < 10000; i++) {
533
  users.push({
534
    name: `User ${i}`,
535
    email: `user${i}@example.com`,
536
    created_at: new Date()
537
  });
538
}
539

540
await knex.batchInsert('users', users, 500); // Insert in chunks of 500
541

542
// Event listeners for monitoring
543
knex.on('query', (query) => {
544
  console.log('Executing:', query.sql);
545
  console.log('Bindings:', query.bindings);
546
});
547

548
knex.on('query-response', (response, query) => {
549
  console.log('Query completed in', response.duration, 'ms');
550
});
551

552
knex.on('query-error', (error, query) => {
553
  console.error('Query failed:', error.message);
554
  console.error('SQL:', query.sql);
555
});
556

557
// Connection management
558
await knex.initialize(); // Initialize connection pool
559

560
// Custom connection for specific operations
561
const connection = await knex.acquireConnection();
562
try {
563
  await knex.raw('SELECT pg_advisory_lock(12345)').connection(connection);
564
  // Perform operations with locked resource
565
  await knex('critical_table').insert(data).connection(connection);
566
} finally {
567
  await knex.raw('SELECT pg_advisory_unlock(12345)').connection(connection);
568
  await knex.releaseConnection(connection);
569
}
570

571
// Graceful shutdown
572
process.on('SIGINT', async () => {
573
  await knex.destroy();
574
  process.exit(0);
575
});
576

577
// Database-specific raw operations
578
// PostgreSQL array operations
579
await knex.raw("UPDATE users SET tags = tags || ? WHERE id = ?", [['new-tag'], userId]);
580

581
// MySQL JSON operations
582
await knex.raw("UPDATE users SET preferences = JSON_SET(preferences, '$.theme', ?) WHERE id = ?", ['dark', userId]);
583

584
// SQLite pragma settings
585
await knex.raw("PRAGMA foreign_keys = ON");
586

587
// Transaction isolation levels
588
await knex.transaction(async trx => {
589
  await trx.raw('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
590
  // Perform serializable operations
591
}, { isolationLevel: 'serializable' });
592
```