0
# Database Operations
1

2
Core database interaction methods for executing SQL statements, managing results, and handling different query patterns with full type safety and performance optimization.
3

4
## Capabilities
5

6
### Single Statement Execution
7

8
Execute individual SQL statements with optional parameters for queries, inserts, updates, and deletes.
9

10
```typescript { .api }
11
/**
12
 * Execute a single SQL statement
13
 * @param stmt - Statement object with SQL and optional arguments
14
 * @returns Promise resolving to query results
15
 */
16
execute(stmt: InStatement): Promise<ResultSet>;
17

18
/**
19
 * Execute a single SQL statement with separate arguments
20
 * @param sql - SQL query string
21
 * @param args - Optional array or object of parameter values
22
 * @returns Promise resolving to query results
23
 */
24
execute(sql: string, args?: InArgs): Promise<ResultSet>;
25
```
26

27
**Usage Examples:**
28

29
```typescript
30
import { createClient } from "@libsql/client";
31

32
const client = createClient({ url: "file:database.db" });
33

34
// Simple query without parameters
35
const users = await client.execute("SELECT * FROM users");
36
console.log(users.rows);
37

38
// Query with positional parameters
39
const user = await client.execute({
40
  sql: "SELECT * FROM users WHERE id = ? AND status = ?",
41
  args: [1, "active"]
42
});
43

44
// Query with named parameters  
45
const userByEmail = await client.execute({
46
  sql: "SELECT * FROM users WHERE email = $email",
47
  args: { email: "alice@example.com" }
48
});
49

50
// Insert with parameters
51
const insertResult = await client.execute({
52
  sql: "INSERT INTO users (name, email) VALUES (?, ?)",
53
  args: ["Bob", "bob@example.com"]
54
});
55
console.log(insertResult.lastInsertRowid); // New row ID
56

57
// Update with parameters
58
const updateResult = await client.execute({
59
  sql: "UPDATE users SET name = ? WHERE id = ?", 
60
  args: ["Robert", 1]
61
});
62
console.log(updateResult.rowsAffected); // Number of updated rows
63
```
64

65
### Batch Operations
66

67
Execute multiple SQL statements atomically in a transaction for data consistency and performance.
68

69
```typescript { .api }
70
/**
71
 * Execute a batch of SQL statements in a transaction
72
 * @param stmts - Array of statements or [sql, args] tuples
73
 * @param mode - Transaction mode (default: "deferred")
74
 * @returns Promise resolving to array of results for each statement
75
 */
76
batch(
77
  stmts: Array<InStatement | [string, InArgs?]>, 
78
  mode?: TransactionMode
79
): Promise<Array<ResultSet>>;
80
```
81

82
**Usage Examples:**
83

84
```typescript
85
// Batch insert multiple records
86
const results = await client.batch([
87
  {
88
    sql: "INSERT INTO users (name, email) VALUES (?, ?)",
89
    args: ["Alice", "alice@example.com"]
90
  },
91
  {
92
    sql: "INSERT INTO users (name, email) VALUES (?, ?)", 
93
    args: ["Bob", "bob@example.com"]
94
  }
95
], "write");
96

97
// Mixed operations in a transaction
98
const mixedResults = await client.batch([
99
  "CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT)",
100
  ["INSERT INTO products (name) VALUES (?)", ["Laptop"]],
101
  ["UPDATE users SET last_login = ? WHERE id = ?", [new Date(), 1]]
102
], "write");
103

104
// Read-only batch for consistent snapshots
105
const reportData = await client.batch([
106
  "SELECT COUNT(*) as user_count FROM users",
107
  "SELECT COUNT(*) as product_count FROM products",
108
  "SELECT AVG(price) as avg_price FROM products"
109
], "read");
110
```
111

112
### Migration Operations
113

114
Execute database schema migrations with foreign key constraints temporarily disabled.
115

116
```typescript { .api }
117
/**
118
 * Execute a batch of statements with foreign key constraints disabled
119
 * Useful for database migrations and schema changes
120
 * @param stmts - Array of migration statements
121
 * @returns Promise resolving to array of results
122
 */
123
migrate(stmts: Array<InStatement>): Promise<Array<ResultSet>>;
124
```
125

126
**Usage Examples:**
127

128
```typescript
129
// Database schema migration
130
const migrationResults = await client.migrate([
131
  {
132
    sql: "CREATE TABLE new_users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)"
133
  },
134
  {
135
    sql: "INSERT INTO new_users (id, name, email) SELECT id, name, email FROM users"
136
  },
137
  {
138
    sql: "DROP TABLE users"
139
  },
140
  {
141
    sql: "ALTER TABLE new_users RENAME TO users"
142
  }
143
]);
144

145
// Add foreign key relationships
146
await client.migrate([
147
  {
148
    sql: "CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id))"
149
  }
150
]);
151
```
152

153
### Multiple Statement Execution
154

155
Execute multiple semicolon-separated SQL statements from scripts or migrations.
156

157
```typescript { .api }
158
/**
159
 * Execute multiple semicolon-separated SQL statements
160
 * @param sql - SQL script with multiple statements
161
 * @returns Promise resolving when all statements complete
162
 */
163
executeMultiple(sql: string): Promise<void>;
164
```
165

166
**Usage Examples:**
167

168
```typescript
169
// Execute SQL script
170
await client.executeMultiple(`
171
  CREATE TABLE IF NOT EXISTS categories (
172
    id INTEGER PRIMARY KEY,
173
    name TEXT NOT NULL
174
  );
175
  
176
  CREATE TABLE IF NOT EXISTS products (
177
    id INTEGER PRIMARY KEY,
178
    name TEXT NOT NULL,
179
    category_id INTEGER REFERENCES categories(id)
180
  );
181
  
182
  INSERT INTO categories (name) VALUES ('Electronics'), ('Books');
183
`);
184

185
// Execute migration script
186
const migrationScript = await fs.readFile('./migrations/001_initial.sql', 'utf8');
187
await client.executeMultiple(migrationScript);
188
```
189

190
### Database Synchronization
191

192
Synchronize embedded replica databases with remote servers.
193

194
```typescript { .api }
195
/**
196
 * Manually trigger synchronization with remote database
197
 * Only available for embedded replica configurations
198
 * @returns Promise resolving to sync statistics
199
 */
200
sync(): Promise<Replicated>;
201
```
202

203
**Usage Examples:**
204

205
```typescript
206
// Manual sync for embedded replica
207
const client = createClient({
208
  url: "file:local.db",
209
  syncUrl: "libsql://remote.turso.io",
210
  authToken: "token"
211
});
212

213
// Trigger manual sync
214
const syncResult = await client.sync();
215
if (syncResult) {
216
  console.log(`Synced ${syncResult.frames_synced} frames`);
217
  console.log(`Current frame: ${syncResult.frame_no}`);
218
}
219
```
220

221
### Connection Management
222

223
Manage client lifecycle and connection state.
224

225
```typescript { .api }
226
/**
227
 * Close the client and release all resources
228
 * Aborts any operations currently in progress
229
 */
230
close(): void;
231

232
/**
233
 * Reconnect after the client has been closed
234
 * Creates new underlying connections
235
 */
236
reconnect(): void;
237

238
/** Whether the client is closed */
239
readonly closed: boolean;
240

241
/** Protocol used by the client: "http", "ws", or "file" */
242
readonly protocol: string;
243
```
244

245
**Usage Examples:**
246

247
```typescript
248
const client = createClient({ url: "file:database.db" });
249

250
// Check connection state
251
console.log(client.protocol); // "file"
252
console.log(client.closed);   // false
253

254
// Proper cleanup
255
process.on('SIGINT', () => {
256
  client.close();
257
  process.exit(0);
258
});
259

260
// Reconnect after network issues
261
try {
262
  await client.execute("SELECT 1");
263
} catch (error) {
264
  if (client.closed) {
265
    await client.reconnect();
266
    await client.execute("SELECT 1"); // Retry operation
267
  }
268
}
269
```
270

271
## Result Set Interface
272

273
```typescript { .api }
274
interface ResultSet {
275
  /** Column names from the SQL query */
276
  columns: Array<string>;
277
  
278
  /** Column types (when available from schema) */
279
  columnTypes: Array<string>;
280
  
281
  /** Array of result rows */
282
  rows: Array<Row>;
283
  
284
  /** Number of rows affected by INSERT, UPDATE, or DELETE */
285
  rowsAffected: number;
286
  
287
  /** ROWID of the last inserted row (INSERT operations only) */
288
  lastInsertRowid: bigint | undefined;
289
  
290
  /** Convert result set to JSON representation */
291
  toJSON(): any;
292
}
293

294
interface Row {
295
  /** Number of columns in this row */
296
  length: number;
297
  
298
  /** Access column values by numeric index */
299
  [index: number]: Value;
300
  
301
  /** Access column values by column name */
302
  [name: string]: Value;
303
}
304
```
305

306
## Transaction Modes
307

308
```typescript { .api }
309
/**
310
 * Transaction isolation and locking modes
311
 * - "write": Immediate write lock, blocks other write transactions
312
 * - "read": Read-only transaction, allows concurrent reads
313
 * - "deferred": Starts as read, upgrades to write on first write operation
314
 */
315
type TransactionMode = "write" | "read" | "deferred";
316
```
317

318
## Input Types
319

320
```typescript { .api }
321
/** SQL statement with optional parameters */
322
type InStatement = { sql: string; args?: InArgs } | string;
323

324
/** Parameter values for SQL statements */
325
type InArgs = Array<InValue> | Record<string, InValue>;
326

327
/** JavaScript values that can be used as SQL parameters */
328
type InValue = Value | boolean | Uint8Array | Date;
329

330
/** SQL result values */
331
type Value = null | string | number | bigint | ArrayBuffer;
332

333
/** Sync operation result */
334
type Replicated = { frame_no: number; frames_synced: number } | undefined;
335
```