0
# Prepared Statements
1

2
Statement preparation and execution with parameter binding. The Statement class extends EventEmitter and provides efficient query execution for repeated operations with different parameters.
3

4
## Capabilities
5

6
### Bind Parameters
7

8
Binds parameters to the prepared statement.
9

10
```javascript { .api }
11
/**
12
 * Binds parameters to the prepared statement
13
 * @param params - Parameters to bind (array, object, or individual values)
14
 * @param callback - Optional callback called when binding completes
15
 * @returns Statement instance for chaining
16
 */
17
bind(params?: any, callback?: (err: Error | null) => void): this;
18

19
/**
20
 * Binds multiple parameters as separate arguments
21
 */
22
bind(...params: any[]): this;
23
```
24

25
**Usage Examples:**
26

27
```javascript
28
const stmt = db.prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
29

30
// Bind with array
31
stmt.bind(["Alice", "alice@example.com", 25], (err) => {
32
    if (err) console.error('Bind error:', err.message);
33
});
34

35
// Bind with individual parameters
36
stmt.bind("Bob", "bob@example.com", 30);
37

38
// Named parameters (using object)
39
const namedStmt = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)");
40
namedStmt.bind({
41
    $name: "Charlie",
42
    $email: "charlie@example.com"
43
});
44
```
45

46
### Reset Statement
47

48
Resets the statement to its initial state.
49

50
```javascript { .api }
51
/**
52
 * Resets the statement to its initial state
53
 * @param callback - Optional callback called when reset completes
54
 * @returns Statement instance for chaining
55
 */
56
reset(callback?: (err: null) => void): this;
57
```
58

59
**Usage Examples:**
60

61
```javascript
62
const stmt = db.prepare("SELECT * FROM users WHERE age > ?");
63

64
// Use statement
65
stmt.bind([18]).get((err, row) => {
66
    console.log('First query result:', row);
67
    
68
    // Reset and reuse
69
    stmt.reset().bind([25]).get((err, row) => {
70
        console.log('Second query result:', row);
71
    });
72
});
73
```
74

75
### Finalize Statement
76

77
Finalizes the statement and releases its resources.
78

79
```javascript { .api }
80
/**
81
 * Finalizes the statement and releases resources
82
 * @param callback - Optional callback called when finalization completes
83
 * @returns Database instance that created this statement
84
 */
85
finalize(callback?: (err: Error) => void): Database;
86
```
87

88
**Usage Examples:**
89

90
```javascript
91
const stmt = db.prepare("INSERT INTO logs (message) VALUES (?)");
92

93
// Use statement multiple times
94
stmt.run("Log entry 1");
95
stmt.run("Log entry 2");
96
stmt.run("Log entry 3");
97

98
// Always finalize when done
99
stmt.finalize((err) => {
100
    if (err) {
101
        console.error('Finalization error:', err.message);
102
    } else {
103
        console.log('Statement finalized successfully');
104
    }
105
});
106
```
107

108
### Execute Statement
109

110
Executes the prepared statement (non-query operations).
111

112
```javascript { .api }
113
/**
114
 * Executes the prepared statement
115
 * @param params - Optional parameters to bind before execution
116
 * @param callback - Optional callback with RunResult context
117
 * @returns Statement instance for chaining
118
 */
119
run(params?: any, callback?: (this: RunResult, err: Error | null) => void): this;
120

121
/**
122
 * Executes with multiple parameters as separate arguments
123
 */
124
run(...params: any[]): this;
125
```
126

127
**Usage Examples:**
128

129
```javascript
130
const insertStmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
131

132
// Execute with parameters
133
insertStmt.run(["Alice", "alice@example.com"], function(err) {
134
    if (err) {
135
        console.error('Insert error:', err.message);
136
    } else {
137
        console.log(`Inserted user with ID: ${this.lastID}`);
138
        console.log(`Changes made: ${this.changes}`);
139
    }
140
});
141

142
// Execute with individual parameters
143
insertStmt.run("Bob", "bob@example.com");
144

145
// Chaining operations
146
insertStmt
147
    .run("Charlie", "charlie@example.com")
148
    .run("David", "david@example.com")
149
    .finalize();
150
```
151

152
### Get Single Row
153

154
Retrieves a single row using the prepared statement.
155

156
```javascript { .api }
157
/**
158
 * Gets a single row using the prepared statement
159
 * @param params - Optional parameters to bind before execution
160
 * @param callback - Callback receiving the result row
161
 * @returns Statement instance for chaining
162
 */
163
get<T>(params?: any, callback?: (this: RunResult, err: Error | null, row?: T) => void): this;
164

165
/**
166
 * Gets with multiple parameters as separate arguments
167
 */
168
get(...params: any[]): this;
169
```
170

171
**Usage Examples:**
172

173
```javascript
174
const selectStmt = db.prepare("SELECT * FROM users WHERE age > ? AND city = ?");
175

176
// Get with array parameters
177
selectStmt.get([25, "New York"], (err, row) => {
178
    if (err) {
179
        console.error('Query error:', err.message);
180
    } else if (row) {
181
        console.log(`Found user: ${row.name}, age ${row.age}`);
182
    } else {
183
        console.log('No user found matching criteria');
184
    }
185
});
186

187
// Get with individual parameters
188
selectStmt.get(30, "London", (err, row) => {
189
    if (row) {
190
        console.log(`User: ${row.name}`);
191
    }
192
});
193

194
// With named parameters
195
const namedSelectStmt = db.prepare("SELECT * FROM users WHERE name = $name");
196
namedSelectStmt.get({ $name: "Alice" }, (err, row) => {
197
    console.log('Found user:', row);
198
});
199
```
200

201
### Get All Rows
202

203
Retrieves all rows using the prepared statement.
204

205
```javascript { .api }
206
/**
207
 * Gets all rows using the prepared statement
208
 * @param params - Optional parameters to bind before execution
209
 * @param callback - Callback receiving array of result rows
210
 * @returns Statement instance for chaining
211
 */
212
all<T>(params?: any, callback?: (this: RunResult, err: Error | null, rows: T[]) => void): this;
213

214
/**
215
 * Gets all with multiple parameters as separate arguments
216
 */
217
all(...params: any[]): this;
218
```
219

220
**Usage Examples:**
221

222
```javascript
223
const selectAllStmt = db.prepare("SELECT * FROM users WHERE age BETWEEN ? AND ?");
224

225
// Get all with parameters
226
selectAllStmt.all([18, 65], (err, rows) => {
227
    if (err) {
228
        console.error('Query error:', err.message);
229
    } else {
230
        console.log(`Found ${rows.length} users`);
231
        rows.forEach(row => {
232
            console.log(`- ${row.name}, age ${row.age}`);
233
        });
234
    }
235
});
236

237
// Reuse with different parameters
238
selectAllStmt.all([25, 35], (err, rows) => {
239
    console.log(`Users aged 25-35: ${rows.length}`);
240
});
241
```
242

243
### Iterate Through Rows
244

245
Processes rows one by one using the prepared statement.
246

247
```javascript { .api }
248
/**
249
 * Iterates through rows using the prepared statement
250
 * @param params - Optional parameters to bind before execution
251
 * @param callback - Callback called for each row
252
 * @param complete - Optional callback called when iteration completes
253
 * @returns Statement instance for chaining
254
 */
255
each<T>(
256
    params?: any, 
257
    callback?: (this: RunResult, err: Error | null, row: T) => void, 
258
    complete?: (err: Error | null, count: number) => void
259
): this;
260

261
/**
262
 * Iterates with multiple parameters as separate arguments
263
 */
264
each(...params: any[]): this;
265
```
266

267
**Usage Examples:**
268

269
```javascript
270
const processStmt = db.prepare("SELECT id, name, email FROM users WHERE active = ?");
271

272
// Process each active user
273
processStmt.each([1], (err, row) => {
274
    if (err) {
275
        console.error('Row processing error:', err.message);
276
    } else {
277
        console.log(`Processing user ${row.id}: ${row.name}`);
278
        // Perform processing logic here
279
    }
280
}, (err, count) => {
281
    if (err) {
282
        console.error('Iteration error:', err.message);
283
    } else {
284
        console.log(`Processed ${count} active users`);
285
    }
286
});
287

288
// Process with different parameters
289
processStmt.each([0], (err, row) => {
290
    console.log(`Inactive user: ${row.name}`);
291
}, (err, count) => {
292
    console.log(`Found ${count} inactive users`);
293
});
294
```
295

296
### Map Query Results
297

298
Maps query results to key-value pairs using the prepared statement.
299

300
```javascript { .api }
301
/**
302
 * Maps query results to key-value pairs using the prepared statement
303
 * @param params - Optional parameters to bind before execution
304
 * @param callback - Callback receiving mapped results
305
 * @returns Statement instance for chaining
306
 */
307
map<T>(params?: any, callback?: (this: RunResult, err: Error | null, result: {[key: string]: T}) => void): this;
308

309
/**
310
 * Maps with multiple parameters as separate arguments
311
 */
312
map(...params: any[]): this;
313
```
314

315
**Usage Examples:**
316

317
```javascript
318
const mapStmt = db.prepare("SELECT id, name FROM users WHERE department = ?");
319

320
// Map users by ID
321
mapStmt.map(["Engineering"], (err, result) => {
322
    if (err) {
323
        console.error('Map error:', err.message);
324
    } else {
325
        // result = { "1": "Alice", "2": "Bob", ... }
326
        console.log('Engineering users:');
327
        Object.entries(result).forEach(([id, name]) => {
328
            console.log(`ID ${id}: ${name}`);
329
        });
330
    }
331
});
332

333
// Map users from different department
334
mapStmt.map(["Marketing"], (err, result) => {
335
    console.log(`Marketing has ${Object.keys(result).length} users`);
336
});
337
```
338

339
## Advanced Usage Patterns
340

341
### Transaction-like Operations
342

343
```javascript
344
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
345

346
db.serialize(() => {
347
    db.run("BEGIN TRANSACTION");
348
    
349
    try {
350
        stmt.run("User1", "user1@example.com");
351
        stmt.run("User2", "user2@example.com");
352
        stmt.run("User3", "user3@example.com");
353
        
354
        db.run("COMMIT", (err) => {
355
            if (err) {
356
                console.error('Commit failed:', err.message);
357
                db.run("ROLLBACK");
358
            } else {
359
                console.log('Transaction completed successfully');
360
            }
361
            stmt.finalize();
362
        });
363
    } catch (error) {
364
        console.error('Transaction error:', error.message);
365
        db.run("ROLLBACK");
366
        stmt.finalize();
367
    }
368
});
369
```
370

371
### Batch Processing
372

373
```javascript
374
const batchStmt = db.prepare("INSERT INTO logs (timestamp, level, message) VALUES (?, ?, ?)");
375

376
const logEntries = [
377
    [Date.now(), "INFO", "Application started"],
378
    [Date.now(), "DEBUG", "Configuration loaded"],
379
    [Date.now(), "WARNING", "High memory usage detected"],
380
    [Date.now(), "ERROR", "Database connection failed"]
381
];
382

383
db.serialize(() => {
384
    logEntries.forEach(([timestamp, level, message]) => {
385
        batchStmt.run(timestamp, level, message);
386
    });
387
    
388
    batchStmt.finalize((err) => {
389
        if (err) {
390
            console.error('Batch processing failed:', err.message);
391
        } else {
392
            console.log(`Processed ${logEntries.length} log entries`);
393
        }
394
    });
395
});
396
```
397

398
### Parameter Binding Patterns
399

400
```javascript
401
// Positional parameters (?)
402
const positionalStmt = db.prepare("SELECT * FROM users WHERE age > ? AND city = ?");
403
positionalStmt.get([25, "New York"], callback);
404

405
// Named parameters ($name, :name, @name)
406
const namedStmt = db.prepare("SELECT * FROM users WHERE name = $name AND email = $email");
407
namedStmt.get({
408
    $name: "Alice",
409
    $email: "alice@example.com"
410
}, callback);
411

412
// Mixed parameters (not recommended, but supported)
413
const mixedStmt = db.prepare("SELECT * FROM users WHERE id = ? AND name = $name");
414
mixedStmt.bind([123]).bind({ $name: "Bob" }).get(callback);
415
```
416

417
## Statement Lifecycle
418

419
```javascript
420
// 1. Prepare - creates the statement
421
const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");
422

423
// 2. Bind (optional) - can be done separately or with run/get/all/each
424
stmt.bind(["Alice"]);
425

426
// 3. Execute - run the statement (can be done multiple times)
427
stmt.run(); // Uses previously bound parameters
428
stmt.run(["Bob"]); // Binds new parameters and executes
429

430
// 4. Reset (optional) - clears bindings and state
431
stmt.reset();
432

433
// 5. Finalize - must be called to free resources
434
stmt.finalize((err) => {
435
    if (err) console.error('Finalization error:', err);
436
});
437
```