0
# Statement Operations
1

2
Prepared statement functionality for efficient reusable queries with parameter binding, result processing, and statement lifecycle management.
3

4
## Capabilities
5

6
### Statement Creation
7

8
Prepared statements are created through the Database class and provide an efficient way to execute the same query multiple times with different parameters.
9

10
```typescript { .api }
11
// Available on Database instance
12
prepare(sql: ISqlite.SqlType, ...params: any[]): Promise<Statement<Stmt>>;
13
```
14

15
### Statement Management
16

17
Core statement lifecycle and instance management methods.
18

19
```typescript { .api }
20
class Statement<S extends sqlite3.Statement = sqlite3.Statement> {
21
  /** Returns the underlying sqlite3 Statement instance */
22
  getStatementInstance(): S;
23

24
  /**
25
   * Bind parameters to the prepared statement
26
   * Completely resets the statement object and removes all previously bound parameters
27
   * @param params - Parameters to bind to the statement
28
   * @returns Promise that resolves when binding is complete
29
   */
30
  bind(...params: any[]): Promise<void>;
31

32
  /**
33
   * Reset the row cursor and preserve parameter bindings
34
   * Use this to re-execute the same query with the same bindings
35
   * @returns Promise that resolves when reset is complete
36
   */
37
  reset(): Promise<void>;
38

39
  /**
40
   * Finalize the statement to free resources
41
   * After finalization, all further function calls will throw errors
42
   * @returns Promise that resolves when finalization is complete
43
   */
44
  finalize(): Promise<void>;
45
}
46
```
47

48
### Statement Execution
49

50
Execute prepared statements with optional parameter binding and various result processing options.
51

52
```typescript { .api }
53
class Statement<S extends sqlite3.Statement = sqlite3.Statement> {
54
  /**
55
   * Execute the statement (INSERT, UPDATE, DELETE, DDL)
56
   * @param params - Optional parameters to bind before execution
57
   * @returns Promise with execution result including lastID and changes
58
   */
59
  run(...params: any[]): Promise<ISqlite.RunResult>;
60

61
  /**
62
   * Execute statement and retrieve the first result row
63
   * Can leave database locked - call finalize() or reset() when done
64
   * @param params - Optional parameters to bind before execution
65
   * @returns Promise resolving to first row or undefined
66
   */
67
  get<T = any>(...params: any[]): Promise<T | undefined>;
68

69
  /**
70
   * Execute statement and retrieve all result rows
71
   * @param params - Optional parameters to bind before execution
72
   * @returns Promise resolving to array of all rows
73
   */
74
  all<T = any[]>(...params: any[]): Promise<T>;
75

76
  /**
77
   * Execute statement and call callback for each result row
78
   * @param params - Parameters followed by callback function
79
   * @returns Promise resolving to number of rows processed
80
   */
81
  each<T = any>(...params: any[]): Promise<number>;
82
}
83
```
84

85
**Usage Examples:**
86

87
```typescript
88
import { open } from "sqlite";
89
import sqlite3 from "sqlite3";
90

91
const db = await open({
92
  filename: ":memory:",
93
  driver: sqlite3.Database
94
});
95

96
// Create table
97
await db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, active BOOLEAN)");
98

99
// Prepare INSERT statement
100
const insertStmt = await db.prepare("INSERT INTO users (name, email, active) VALUES (?, ?, ?)");
101

102
// Execute multiple times
103
const result1 = await insertStmt.run("Alice", "alice@example.com", true);
104
console.log(`Inserted Alice with ID: ${result1.lastID}`);
105

106
const result2 = await insertStmt.run("Bob", "bob@example.com", false);
107
console.log(`Inserted Bob with ID: ${result2.lastID}`);
108

109
// Finalize when done
110
await insertStmt.finalize();
111

112
// Prepare SELECT statement
113
const selectStmt = await db.prepare("SELECT * FROM users WHERE active = ?");
114

115
// Use with different parameters
116
const activeUsers = await selectStmt.all<{id: number, name: string, email: string, active: boolean}[]>(true);
117
console.log("Active users:", activeUsers);
118

119
const inactiveUsers = await selectStmt.all<{id: number, name: string, email: string, active: boolean}[]>(false);
120
console.log("Inactive users:", inactiveUsers);
121

122
// Reset and reuse with same parameters
123
await selectStmt.reset();
124
const firstActiveUser = await selectStmt.get<{id: number, name: string, email: string, active: boolean}>(true);
125

126
await selectStmt.finalize();
127
```
128

129
### Parameter Binding Patterns
130

131
Prepared statements support multiple parameter binding approaches:
132

133
```typescript
134
// Bind parameters at execution time
135
const stmt = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
136
const user = await stmt.get("Alice", true);
137

138
// Pre-bind parameters, then execute
139
const stmt2 = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
140
await stmt2.bind("Alice", true);
141
const user2 = await stmt2.get();
142

143
// Named parameters
144
const stmt3 = await db.prepare("SELECT * FROM users WHERE name = :name AND active = :active");
145
const user3 = await stmt3.get({ ":name": "Alice", ":active": true });
146

147
// Array parameters
148
const stmt4 = await db.prepare("SELECT * FROM users WHERE name = ? AND active = ?");
149
const user4 = await stmt4.get(["Alice", true]);
150
```
151

152
### Iteration with Each
153

154
Process large result sets efficiently with the each method:
155

156
```typescript
157
const stmt = await db.prepare("SELECT * FROM large_table WHERE category = ?");
158

159
// Process rows one by one
160
const rowCount = await stmt.each("electronics", (err, row) => {
161
  if (err) {
162
    console.error("Row processing error:", err);
163
    return;
164
  }
165
  
166
  // Process individual row
167
  console.log(`Processing row:`, row);
168
});
169

170
console.log(`Processed ${rowCount} rows`);
171
await stmt.finalize();
172
```
173

174
### Statement Reuse and Performance
175

176
Prepared statements provide performance benefits for repeated queries:
177

178
```typescript
179
// Efficient batch operations
180
const insertStmt = await db.prepare("INSERT INTO products (name, price, category) VALUES (?, ?, ?)");
181

182
const products = [
183
  ["Laptop", 999.99, "electronics"],
184
  ["Book", 29.99, "books"],
185
  ["Headphones", 199.99, "electronics"]
186
];
187

188
// Reuse the same prepared statement
189
for (const [name, price, category] of products) {
190
  await insertStmt.run(name, price, category);
191
}
192

193
await insertStmt.finalize();
194

195
// Query optimization with parameter reuse
196
const categoryStmt = await db.prepare("SELECT COUNT(*) as count FROM products WHERE category = ?");
197

198
const categories = ["electronics", "books", "clothing"];
199
for (const category of categories) {
200
  const result = await categoryStmt.get<{count: number}>(category);
201
  console.log(`${category}: ${result.count} products`);
202
}
203

204
await categoryStmt.finalize();
205
```
206

207
### Error Handling
208

209
Proper error handling patterns for prepared statements:
210

211
```typescript
212
let stmt;
213
try {
214
  stmt = await db.prepare("SELECT * FROM users WHERE id = ?");
215
  
216
  const user = await stmt.get(1);
217
  if (!user) {
218
    console.log("User not found");
219
  } else {
220
    console.log("Found user:", user);
221
  }
222
} catch (error) {
223
  console.error("Statement error:", error);
224
} finally {
225
  // Always finalize to free resources
226
  if (stmt) {
227
    await stmt.finalize();
228
  }
229
}
230
```
231

232
## Types
233

234
```typescript { .api }
235
interface RunResult {
236
  /** Statement object */
237
  stmt: Statement;
238
  /** Row ID of inserted row (INSERT statements only) */
239
  lastID?: number;
240
  /** Number of rows changed (UPDATE/DELETE statements only) */
241
  changes?: number;
242
}
243
```