0
# Database Operations
1

2
Core database operations for connecting, querying, and managing SQLite databases with promise-based async/await patterns.
3

4
## Capabilities
5

6
### Database Connection
7

8
Open and manage database connections with support for multiple drivers and configuration options.
9

10
```typescript { .api }
11
/**
12
 * Opens a database for manipulation. Most users will call this to get started.
13
 * @param config - Database configuration including filename, mode, and driver
14
 * @returns Promise resolving to Database instance
15
 */
16
function open<
17
  Driver extends sqlite3.Database = sqlite3.Database,
18
  Stmt extends sqlite3.Statement = sqlite3.Statement
19
>(config: ISqlite.Config): Promise<Database<Driver, Stmt>>;
20

21
interface Config {
22
  /** Database filename, ":memory:" for in-memory, or "" for anonymous disk-based */
23
  filename: string;
24
  /** One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE, sqlite3.OPEN_CREATE */
25
  mode?: number;
26
  /** Database driver (e.g., sqlite3.Database or sqlite3.cached.Database) */
27
  driver: any;
28
}
29
```
30

31
**Usage Examples:**
32

33
```typescript
34
import { open } from "sqlite";
35
import sqlite3 from "sqlite3";
36

37
// Basic connection
38
const db = await open({
39
  filename: "./mydb.sqlite",
40
  driver: sqlite3.Database
41
});
42

43
// In-memory database
44
const memDb = await open({
45
  filename: ":memory:",
46
  driver: sqlite3.Database
47
});
48

49
// With caching enabled
50
const cachedDb = await open({
51
  filename: "./mydb.sqlite",
52
  driver: sqlite3.cached.Database
53
});
54

55
// With explicit mode
56
const readOnlyDb = await open({
57
  filename: "./mydb.sqlite",
58
  mode: sqlite3.OPEN_READONLY,
59
  driver: sqlite3.Database
60
});
61
```
62

63
### Database Management
64

65
Core database lifecycle and configuration methods.
66

67
```typescript { .api }
68
class Database<
69
  Driver extends sqlite3.Database = sqlite3.Database,
70
  Stmt extends sqlite3.Statement = sqlite3.Statement
71
> {
72
  /** Opens the database connection */
73
  open(): Promise<void>;
74
  /** Closes the database connection */
75
  close(): Promise<void>;
76
  /** Returns the underlying sqlite3 Database instance */
77
  getDatabaseInstance(): Driver;
78
  /** Configure database options like busyTimeout */
79
  configure(option: ISqlite.ConfigureOption, value: any): any;
80
  /** Event handler when verbose mode is enabled */
81
  on(event: string, listener: (...args: any[]) => void): void;
82
}
83

84
type ConfigureOption = 'trace' | 'profile' | 'busyTimeout';
85
```
86

87
### Query Execution
88

89
Execute SQL queries with parameter binding and result processing.
90

91
```typescript { .api }
92
class Database<Driver, Stmt> {
93
  /**
94
   * Execute SQL query without retrieving results (INSERT, UPDATE, DELETE, DDL)
95
   * @param sql - SQL query string or sql-template-strings object
96
   * @param params - Parameters to bind to the query
97
   * @returns Promise with execution result including lastID and changes
98
   */
99
  run(sql: ISqlite.SqlType, ...params: any[]): Promise<ISqlite.RunResult<Stmt>>;
100

101
  /**
102
   * Execute query and return first result row
103
   * @param sql - SQL query string
104
   * @param params - Parameters to bind to the query
105
   * @returns Promise resolving to first row or undefined
106
   */
107
  get<T = any>(sql: ISqlite.SqlType, ...params: any[]): Promise<T | undefined>;
108

109
  /**
110
   * Execute query and return all result rows
111
   * @param sql - SQL query string
112
   * @param params - Parameters to bind to the query
113
   * @returns Promise resolving to array of all rows
114
   */
115
  all<T = any[]>(sql: ISqlite.SqlType, ...params: any[]): Promise<T>;
116

117
  /**
118
   * Execute query and call callback for each result row
119
   * @param sql - SQL query string
120
   * @param params - Parameters (last parameter must be callback)
121
   * @returns Promise resolving to number of rows processed
122
   */
123
  each<T = any>(sql: ISqlite.SqlType, ...params: any[]): Promise<number>;
124

125
  /**
126
   * Execute multiple SQL statements
127
   * @param sql - SQL statements separated by semicolons
128
   * @returns Promise that resolves when all statements complete
129
   */
130
  exec(sql: ISqlite.SqlType): Promise<void>;
131
}
132
```
133

134
**Usage Examples:**
135

136
```typescript
137
// Run INSERT/UPDATE/DELETE
138
const result = await db.run(
139
  "INSERT INTO users (name, email) VALUES (?, ?)",
140
  "Alice",
141
  "alice@example.com"
142
);
143
console.log(`Inserted row ID: ${result.lastID}`);
144

145
// Get single row
146
const user = await db.get<{id: number, name: string, email: string}>(
147
  "SELECT * FROM users WHERE id = ?",
148
  1
149
);
150

151
// Get all rows
152
const users = await db.all<{id: number, name: string}[]>(
153
  "SELECT id, name FROM users ORDER BY name"
154
);
155

156
// Process rows one by one
157
const count = await db.each(
158
  "SELECT * FROM large_table",
159
  (err, row) => {
160
    if (err) throw err;
161
    console.log(row);
162
  }
163
);
164

165
// Execute multiple statements
166
await db.exec(`
167
  CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT, content TEXT);
168
  CREATE INDEX idx_posts_title ON posts(title);
169
`);
170
```
171

172
### Prepared Statements
173

174
Create and manage prepared statements for efficient repeated queries.
175

176
```typescript { .api }
177
class Database<Driver, Stmt> {
178
  /**
179
   * Prepare a SQL statement for repeated execution
180
   * @param sql - SQL query string
181
   * @param params - Optional parameters to bind immediately
182
   * @returns Promise resolving to Statement instance
183
   */
184
  prepare(sql: ISqlite.SqlType, ...params: any[]): Promise<Statement<Stmt>>;
185
}
186
```
187

188
**Usage Examples:**
189

190
```typescript
191
// Prepare statement for repeated use
192
const stmt = await db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
193

194
// Execute multiple times
195
await stmt.run("Alice", "alice@example.com");
196
await stmt.run("Bob", "bob@example.com");
197
await stmt.run("Charlie", "charlie@example.com");
198

199
// Clean up
200
await stmt.finalize();
201
```
202

203
### Extension Loading
204

205
Load SQLite extensions to add custom functionality.
206

207
```typescript { .api }
208
class Database<Driver, Stmt> {
209
  /**
210
   * Load a compiled SQLite extension
211
   * @param path - Filename of the extension to load
212
   * @returns Promise that resolves when extension is loaded
213
   */
214
  loadExtension(path: string): Promise<void>;
215
}
216
```
217

218
### Transaction Control (Advanced)
219

220
Advanced transaction control methods that are currently not implemented in the promise-based wrapper. Use the underlying driver instance for these operations.
221

222
```typescript { .api }
223
class Database<Driver, Stmt> {
224
  /**
225
   * Serialize database operations (currently not implemented)
226
   * Use getDatabaseInstance().serialize() instead
227
   * @throws Error indicating method is not implemented
228
   */
229
  serialize(): never;
230

231
  /**
232
   * Parallelize database operations (currently not implemented)
233
   * Use getDatabaseInstance().parallelize() instead  
234
   * @throws Error indicating method is not implemented
235
   */
236
  parallelize(): never;
237
}
238
```
239

240
**Usage Examples:**
241

242
```typescript
243
// Use underlying driver for serialize/parallelize
244
const db = await open({ filename: ":memory:", driver: sqlite3.Database });
245
const driver = db.getDatabaseInstance();
246

247
// Serialize operations
248
driver.serialize(() => {
249
  driver.run("CREATE TABLE test (id INTEGER PRIMARY KEY)");
250
  driver.run("INSERT INTO test VALUES (1)");
251
  driver.run("INSERT INTO test VALUES (2)");
252
});
253

254
// Parallelize operations  
255
driver.parallelize(() => {
256
  for (let i = 0; i < 10; i++) {
257
    driver.run("INSERT INTO test VALUES (?)", i);
258
  }
259
});
260
```
261

262
### Parameter Binding
263

264
The library supports multiple parameter binding styles:
265

266
```typescript
267
// Positional parameters
268
await db.get("SELECT * FROM users WHERE id = ?", 1);
269
await db.get("SELECT * FROM users WHERE id = ? AND active = ?", [1, true]);
270

271
// Named parameters
272
await db.get("SELECT * FROM users WHERE id = :id", { ":id": 1 });
273
await db.get("SELECT * FROM users WHERE name = :name AND email = :email", {
274
  ":name": "Alice",
275
  ":email": "alice@example.com"
276
});
277

278
// sql-template-strings support
279
import SQL from "sql-template-strings";
280
const name = "Alice";
281
const user = await db.get(SQL`SELECT * FROM users WHERE name = ${name}`);
282
```
283

284
## Types
285

286
```typescript { .api }
287
interface RunResult<Stmt extends sqlite3.Statement = sqlite3.Statement> {
288
  /** Statement object (automatically finalized after run) */
289
  stmt: Statement<Stmt>;
290
  /** Row ID of inserted row (INSERT statements only) */
291
  lastID?: number;
292
  /** Number of rows changed (UPDATE/DELETE statements only) */
293
  changes?: number;
294
}
295

296
interface SqlObj {
297
  sql: string;
298
  params?: any[];
299
}
300

301
interface SQLStatement {
302
  sql: string;
303
  values?: any[];
304
}
305

306
type SqlType = SQLStatement | string;
307
```