0
# Database Operations
1

2
Core database functionality for connecting, querying, and managing SQLite databases. The Database class extends EventEmitter and provides both callback-based and event-driven patterns for database operations.
3

4
## Capabilities
5

6
### Database Constructor
7

8
Creates a new database connection instance.
9

10
```javascript { .api }
11
/**
12
 * Creates a new database connection
13
 * @param filename - Path to database file or ':memory:' for in-memory database
14
 * @param mode - Optional database mode flags (OPEN_READONLY, OPEN_READWRITE, OPEN_CREATE)
15
 * @param callback - Optional callback called when database is opened
16
 */
17
constructor(filename: string, mode?: number, callback?: (err: Error | null) => void);
18
```
19

20
**Usage Examples:**
21

22
```javascript
23
const sqlite3 = require('sqlite3').verbose();
24

25
// In-memory database
26
const memDb = new sqlite3.Database(':memory:');
27

28
// File database with callback
29
const db = new sqlite3.Database('example.db', (err) => {
30
    if (err) {
31
        console.error('Error opening database:', err.message);
32
    } else {
33
        console.log('Connected to SQLite database');
34
    }
35
});
36

37
// Read-only mode
38
const readOnlyDb = new sqlite3.Database('data.db', sqlite3.OPEN_READONLY);
39
```
40

41
### Execute SQL Statement
42

43
Executes SQL statements that don't return data (INSERT, UPDATE, DELETE, CREATE, etc.).
44

45
```javascript { .api }
46
/**
47
 * Executes a SQL statement
48
 * @param sql - SQL statement to execute
49
 * @param params - Optional parameters for parameter binding
50
 * @param callback - Optional callback with RunResult context
51
 * @returns Database instance for chaining
52
 */
53
run(sql: string, params?: any, callback?: (this: RunResult, err: Error | null) => void): this;
54
```
55

56
**Usage Examples:**
57

58
```javascript
59
// Simple statement
60
db.run("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
61

62
// With parameters
63
db.run("INSERT INTO users (name) VALUES (?)", ["Alice"], function(err) {
64
    if (err) {
65
        console.error(err.message);
66
    } else {
67
        console.log(`Row inserted with ID: ${this.lastID}`);
68
        console.log(`Rows changed: ${this.changes}`);
69
    }
70
});
71

72
// Multiple parameters
73
db.run("INSERT INTO users (id, name) VALUES (?, ?)", [1, "Bob"]);
74
```
75

76
### Get Single Row
77

78
Retrieves a single row from a SELECT query.
79

80
```javascript { .api }
81
/**
82
 * Gets a single row from a SELECT query
83
 * @param sql - SELECT SQL statement
84
 * @param params - Optional parameters for parameter binding
85
 * @param callback - Callback receiving the result row
86
 * @returns Database instance for chaining
87
 */
88
get<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, row: T) => void): this;
89
```
90

91
**Usage Examples:**
92

93
```javascript
94
// Simple query
95
db.get("SELECT name FROM users WHERE id = 1", (err, row) => {
96
    if (err) {
97
        console.error(err.message);
98
    } else {
99
        console.log(row ? row.name : 'No user found');
100
    }
101
});
102

103
// With parameters
104
db.get("SELECT * FROM users WHERE name = ?", ["Alice"], (err, row) => {
105
    if (row) {
106
        console.log(`User: ${row.name}, ID: ${row.id}`);
107
    }
108
});
109
```
110

111
### Get All Rows
112

113
Retrieves all rows from a SELECT query.
114

115
```javascript { .api }
116
/**
117
 * Gets all rows from a SELECT query
118
 * @param sql - SELECT SQL statement
119
 * @param params - Optional parameters for parameter binding
120
 * @param callback - Callback receiving array of result rows
121
 * @returns Database instance for chaining
122
 */
123
all<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, rows: T[]) => void): this;
124
```
125

126
**Usage Examples:**
127

128
```javascript
129
// Get all users
130
db.all("SELECT * FROM users", (err, rows) => {
131
    if (err) {
132
        console.error(err.message);
133
    } else {
134
        rows.forEach(row => {
135
            console.log(`${row.id}: ${row.name}`);
136
        });
137
    }
138
});
139

140
// With parameters
141
db.all("SELECT * FROM users WHERE name LIKE ?", ["%A%"], (err, rows) => {
142
    console.log(`Found ${rows.length} users`);
143
});
144
```
145

146
### Iterate Through Rows
147

148
Processes rows one by one from a SELECT query.
149

150
```javascript { .api }
151
/**
152
 * Iterates through rows from a SELECT query
153
 * @param sql - SELECT SQL statement
154
 * @param params - Optional parameters for parameter binding
155
 * @param callback - Callback called for each row
156
 * @param complete - Optional callback called when iteration completes
157
 * @returns Database instance for chaining
158
 */
159
each<T>(
160
    sql: string, 
161
    params?: any, 
162
    callback?: (this: Statement, err: Error | null, row: T) => void, 
163
    complete?: (err: Error | null, count: number) => void
164
): this;
165
```
166

167
**Usage Examples:**
168

169
```javascript
170
// Process each row
171
db.each("SELECT * FROM users", (err, row) => {
172
    if (err) {
173
        console.error(err.message);
174
    } else {
175
        console.log(`Processing user: ${row.name}`);
176
    }
177
}, (err, count) => {
178
    console.log(`Processed ${count} rows`);
179
});
180

181
// With parameters
182
db.each("SELECT * FROM users WHERE id > ?", [10], (err, row) => {
183
    // Process each row
184
}, (err, count) => {
185
    console.log(`Found ${count} users with ID > 10`);
186
});
187
```
188

189
### Map Query Results
190

191
Maps query results to key-value pairs.
192

193
```javascript { .api }
194
/**
195
 * Maps query results to key-value pairs
196
 * @param sql - SELECT SQL statement  
197
 * @param params - Optional parameters for parameter binding
198
 * @param callback - Callback receiving mapped results
199
 * @returns Database instance for chaining
200
 */
201
map<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, result: {[key: string]: T}) => void): this;
202
```
203

204
**Usage Examples:**
205

206
```javascript
207
// Map users by ID
208
db.map("SELECT id, name FROM users", (err, result) => {
209
    if (err) {
210
        console.error(err.message);
211
    } else {
212
        // result = { "1": "Alice", "2": "Bob", ... }
213
        Object.keys(result).forEach(id => {
214
            console.log(`User ${id}: ${result[id]}`);
215
        });
216
    }
217
});
218
```
219

220
### Execute Raw SQL
221

222
Executes multiple SQL statements separated by semicolons.
223

224
```javascript { .api }
225
/**
226
 * Executes raw SQL statements (multiple statements allowed)
227
 * @param sql - SQL statements separated by semicolons
228
 * @param callback - Optional callback called when execution completes
229
 * @returns Database instance for chaining
230
 */
231
exec(sql: string, callback?: (this: Statement, err: Error | null) => void): this;
232
```
233

234
**Usage Examples:**
235

236
```javascript
237
const schema = `
238
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
239
    CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT);
240
    INSERT INTO users (name) VALUES ('Alice'), ('Bob');
241
`;
242

243
db.exec(schema, (err) => {
244
    if (err) {
245
        console.error('Schema creation failed:', err.message);
246
    } else {
247
        console.log('Schema created successfully');
248
    }
249
});
250
```
251

252
### Prepare Statement
253

254
Creates a prepared statement for efficient repeated execution.
255

256
```javascript { .api }
257
/**
258
 * Prepares a SQL statement for repeated execution
259
 * @param sql - SQL statement with parameter placeholders
260
 * @param params - Optional initial parameters
261
 * @param callback - Optional callback called when statement is prepared
262
 * @returns Statement instance
263
 */
264
prepare(sql: string, params?: any, callback?: (this: Statement, err: Error | null) => void): Statement;
265
```
266

267
**Usage Examples:**
268

269
```javascript
270
// Basic prepared statement
271
const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");
272
stmt.run("Alice");
273
stmt.run("Bob");
274
stmt.finalize();
275

276
// With callback
277
const selectStmt = db.prepare("SELECT * FROM users WHERE name = ?", (err) => {
278
    if (err) {
279
        console.error('Prepare failed:', err.message);
280
    }
281
});
282
```
283

284
### Serialize Operations
285

286
Serializes database calls to run sequentially.
287

288
```javascript { .api }
289
/**
290
 * Serializes database operations to run sequentially
291
 * @param callback - Optional callback function to execute in serial mode
292
 */
293
serialize(callback?: () => void): void;
294
```
295

296
**Usage Examples:**
297

298
```javascript
299
db.serialize(() => {
300
    db.run("CREATE TABLE test (id INTEGER, value TEXT)");
301
    db.run("INSERT INTO test VALUES (1, 'first')");
302
    db.run("INSERT INTO test VALUES (2, 'second')");
303
    
304
    db.get("SELECT COUNT(*) as count FROM test", (err, row) => {
305
        console.log(`Total rows: ${row.count}`);
306
    });
307
});
308
```
309

310
### Parallelize Operations
311

312
Parallelizes database calls to run concurrently.
313

314
```javascript { .api }
315
/**
316
 * Parallelizes database operations to run concurrently
317
 * @param callback - Optional callback function to execute in parallel mode
318
 */
319
parallelize(callback?: () => void): void;
320
```
321

322
**Usage Examples:**
323

324
```javascript
325
db.parallelize(() => {
326
    // These operations will run in parallel
327
    db.run("INSERT INTO logs VALUES (?, ?)", [1, "Log entry 1"]);
328
    db.run("INSERT INTO logs VALUES (?, ?)", [2, "Log entry 2"]);
329
    db.run("INSERT INTO logs VALUES (?, ?)", [3, "Log entry 3"]);
330
});
331
```
332

333
### Configure Database
334

335
Configures database options and limits.
336

337
```javascript { .api }
338
/**
339
 * Configures database options
340
 * @param option - Configuration option name
341
 * @param value - Configuration value (for busyTimeout)
342
 */
343
configure(option: "busyTimeout", value: number): void;
344

345
/**
346
 * Configures database limits
347
 * @param option - Must be "limit"
348
 * @param id - Limit ID constant
349
 * @param value - Limit value
350
 */
351
configure(option: "limit", id: number, value: number): void;
352
```
353

354
**Usage Examples:**
355

356
```javascript
357
// Set busy timeout to 30 seconds
358
db.configure("busyTimeout", 30000);
359

360
// Set SQL length limit
361
db.configure("limit", sqlite3.LIMIT_SQL_LENGTH, 1000000);
362
```
363

364
### Load Extension
365

366
Loads a SQLite extension.
367

368
```javascript { .api }
369
/**
370
 * Loads a SQLite extension
371
 * @param filename - Path to extension file
372
 * @param callback - Optional callback called when extension is loaded
373
 * @returns Database instance for chaining
374
 */
375
loadExtension(filename: string, callback?: (err: Error | null) => void): this;
376
```
377

378
**Usage Examples:**
379

380
```javascript
381
// Load an extension
382
db.loadExtension("./my-extension.so", (err) => {
383
    if (err) {
384
        console.error('Extension loading failed:', err.message);
385
    } else {
386
        console.log('Extension loaded successfully');
387
    }
388
});
389
```
390

391
### Wait for Operations
392

393
Waits for all pending operations to complete.
394

395
```javascript { .api }
396
/**
397
 * Waits for all pending database operations to complete
398
 * @param callback - Optional callback called when all operations complete
399
 * @returns Database instance for chaining
400
 */
401
wait(callback?: (param: null) => void): this;
402
```
403

404
### Interrupt Operations
405

406
Interrupts currently running database operations.
407

408
```javascript { .api }
409
/**
410
 * Interrupts currently running database operations
411
 */
412
interrupt(): void;
413
```
414

415
### Close Database
416

417
Closes the database connection.
418

419
```javascript { .api }
420
/**
421
 * Closes the database connection
422
 * @param callback - Optional callback called when database is closed
423
 */
424
close(callback?: (err: Error | null) => void): void;
425
```
426

427
**Usage Examples:**
428

429
```javascript
430
// Simple close
431
db.close();
432

433
// With callback
434
db.close((err) => {
435
    if (err) {
436
        console.error('Error closing database:', err.message);
437
    } else {
438
        console.log('Database closed successfully');
439
    }
440
});
441
```
442

443
### Create Backup
444

445
Creates a database backup.
446

447
```javascript { .api }
448
/**
449
 * Creates a database backup (simple form)
450
 * @param filename - Destination filename
451
 * @param callback - Optional callback called when backup is initialized
452
 * @returns Backup instance
453
 */
454
backup(filename: string, callback?: (err: Error | null) => void): Backup;
455

456
/**
457
 * Creates a database backup (advanced form)
458
 * @param filename - Source or destination filename
459
 * @param destName - Destination database name
460
 * @param sourceName - Source database name  
461
 * @param filenameIsDest - Whether filename parameter is destination
462
 * @param callback - Optional callback called when backup is initialized
463
 * @returns Backup instance
464
 */
465
backup(
466
    filename: string, 
467
    destName: string, 
468
    sourceName: string, 
469
    filenameIsDest: boolean, 
470
    callback?: (err: Error | null) => void
471
): Backup;
472
```
473

474
## Events
475

476
The Database class emits the following events:
477

478
```javascript { .api }
479
/**
480
 * Emitted when a SQL statement is traced
481
 * @param sql - The SQL statement being executed
482
 */
483
on(event: "trace", listener: (sql: string) => void): this;
484

485
/**
486
 * Emitted when a SQL statement is profiled
487
 * @param sql - The SQL statement that was executed
488
 * @param time - Execution time in milliseconds
489
 */
490
on(event: "profile", listener: (sql: string, time: number) => void): this;
491

492
/**
493
 * Emitted when the database is changed
494
 * @param type - Type of change (insert, update, delete)
495
 * @param database - Database name
496
 * @param table - Table name
497
 * @param rowid - Row ID that was changed
498
 */
499
on(event: "change", listener: (type: string, database: string, table: string, rowid: number) => void): this;
500

501
/**
502
 * Emitted when an error occurs
503
 * @param err - The error object
504
 */
505
on(event: "error", listener: (err: Error) => void): this;
506

507
/**
508
 * Emitted when the database is opened
509
 */
510
on(event: "open", listener: () => void): this;
511

512
/**
513
 * Emitted when the database is closed
514
 */
515
on(event: "close", listener: () => void): this;
516
```
517

518
**Usage Examples:**
519

520
```javascript
521
// Enable tracing
522
db.on('trace', (sql) => {
523
    console.log('Executing SQL:', sql);
524
});
525

526
// Enable profiling
527
db.on('profile', (sql, time) => {
528
    console.log(`SQL executed in ${time}ms:`, sql);
529
});
530

531
// Monitor changes
532
db.on('change', (type, database, table, rowid) => {
533
    console.log(`${type} in ${database}.${table}, row ${rowid}`);
534
});
535

536
// Handle errors
537
db.on('error', (err) => {
538
    console.error('Database error:', err.message);
539
});
540
```
541

542
## Event Management
543

544
The Database class provides enhanced event management methods that automatically configure SQLite event handling:
545

546
### Add Event Listener
547

548
Adds an event listener with automatic configuration for supported events.
549

550
```javascript { .api }
551
/**
552
 * Adds an event listener (overrides EventEmitter.addListener)
553
 * Automatically configures SQLite for trace, profile, and change events
554
 * @param event - Event name
555
 * @param listener - Event listener function
556
 * @returns Database instance for chaining
557
 */
558
addListener(event: string, listener: (...args: any[]) => void): this;
559

560
/**
561
 * Alias for addListener
562
 */
563
on(event: string, listener: (...args: any[]) => void): this;
564
```
565

566
### Remove Event Listener
567

568
Removes an event listener with automatic configuration cleanup.
569

570
```javascript { .api }
571
/**
572
 * Removes an event listener (overrides EventEmitter.removeListener)
573
 * Automatically disables SQLite configuration when no listeners remain
574
 * @param event - Event name
575
 * @param listener - Event listener function to remove
576
 * @returns Database instance for chaining
577
 */
578
removeListener(event: string, listener: (...args: any[]) => void): this;
579
```
580

581
### Remove All Event Listeners
582

583
Removes all event listeners with automatic configuration cleanup.
584

585
```javascript { .api }
586
/**
587
 * Removes all event listeners (overrides EventEmitter.removeAllListeners)
588
 * Automatically disables SQLite configuration for supported events
589
 * @param event - Optional event name (if omitted, removes all listeners)
590
 * @returns Database instance for chaining
591
 */
592
removeAllListeners(event?: string): this;
593
```
594

595
**Usage Examples:**
596

597
```javascript
598
// Adding listeners automatically enables SQLite events
599
db.addListener('trace', (sql) => {
600
    console.log('SQL trace:', sql);
601
});
602

603
// The above is equivalent to:
604
db.on('trace', (sql) => {
605
    console.log('SQL trace:', sql);
606
});
607

608
// Removing listeners automatically disables events when no listeners remain
609
db.removeListener('trace', traceHandler);
610

611
// Remove all listeners for an event
612
db.removeAllListeners('trace');
613

614
// Remove all listeners for all events
615
db.removeAllListeners();
616
```
617

618
**Note:** These methods automatically call `database.configure()` to enable or disable SQLite's native event support for `trace`, `profile`, and `change` events when listeners are added or removed.