0
# Transactions
1

2
Complete transaction support including nested transactions via savepoints and two-phase commit preparation.
3

4
## Capabilities
5

6
### Basic Transactions
7

8
Execute multiple queries within a single database transaction with automatic rollback on errors.
9

10
```javascript { .api }
11
/**
12
 * Execute function within a transaction
13
 * @param fn - Function to execute within transaction scope
14
 * @returns Promise resolving to function result
15
 */
16
begin<T>(
17
  fn: (sql: TransactionSql) => T | Promise<T>
18
): Promise<T>;
19

20
/**
21
 * Execute function within a transaction with options
22
 * @param options - Transaction options (isolation level, etc.)
23
 * @param fn - Function to execute within transaction scope
24
 * @returns Promise resolving to function result
25
 */
26
begin<T>(
27
  options: string,
28
  fn: (sql: TransactionSql) => T | Promise<T>
29
): Promise<T>;
30

31
interface TransactionSql extends Sql {
32
  savepoint<T>(fn: (sql: TransactionSql) => T | Promise<T>): Promise<T>;
33
  savepoint<T>(name: string, fn: (sql: TransactionSql) => T | Promise<T>): Promise<T>;
34
  prepare(name: string): void;
35
}
36
```
37

38
**Usage Examples:**
39

40
```javascript
41
// Basic transaction
42
const result = await sql.begin(async (sql) => {
43
  const user = await sql`
44
    INSERT INTO users (name, email) 
45
    VALUES (${name}, ${email}) 
46
    RETURNING id
47
  `;
48
  
49
  await sql`
50
    INSERT INTO user_profiles (user_id, bio) 
51
    VALUES (${user[0].id}, ${bio})
52
  `;
53
  
54
  return user[0];
55
});
56

57
// Transaction with options
58
const result = await sql.begin("ISOLATION LEVEL SERIALIZABLE", async (sql) => {
59
  const balance = await sql`
60
    SELECT balance FROM accounts WHERE id = ${accountId}
61
  `;
62
  
63
  if (balance[0].balance < amount) {
64
    throw new Error("Insufficient funds");
65
  }
66
  
67
  await sql`
68
    UPDATE accounts SET balance = balance - ${amount} WHERE id = ${accountId}
69
  `;
70
  
71
  return balance[0].balance - amount;
72
});
73

74
// Multiple operations
75
const result = await sql.begin(async (sql) => {
76
  // All these operations will be in the same transaction
77
  const order = await sql`
78
    INSERT INTO orders (user_id, total) 
79
    VALUES (${userId}, ${total}) 
80
    RETURNING id
81
  `;
82
  
83
  for (const item of items) {
84
    await sql`
85
      INSERT INTO order_items (order_id, product_id, quantity, price)
86
      VALUES (${order[0].id}, ${item.productId}, ${item.quantity}, ${item.price})
87
    `;
88
  }
89
  
90
  await sql`
91
    UPDATE products 
92
    SET stock = stock - ${item.quantity} 
93
    WHERE id = ${item.productId}
94
  `;
95
  
96
  return order[0];
97
});
98
```
99

100
### Savepoints (Nested Transactions)
101

102
Create nested transaction scopes with savepoints for granular rollback control.
103

104
```javascript { .api }
105
/**
106
 * Create an unnamed savepoint within transaction
107
 * @param fn - Function to execute within savepoint scope
108
 * @returns Promise resolving to function result
109
 */
110
savepoint<T>(
111
  fn: (sql: TransactionSql) => T | Promise<T>
112
): Promise<T>;
113

114
/**
115
 * Create a named savepoint within transaction
116
 * @param name - Savepoint name for debugging
117
 * @param fn - Function to execute within savepoint scope
118
 * @returns Promise resolving to function result
119
 */
120
savepoint<T>(
121
  name: string,
122
  fn: (sql: TransactionSql) => T | Promise<T>
123
): Promise<T>;
124
```
125

126
**Usage Examples:**
127

128
```javascript
129
const result = await sql.begin(async (sql) => {
130
  // Main transaction operations
131
  const user = await sql`
132
    INSERT INTO users (name, email) 
133
    VALUES (${name}, ${email}) 
134
    RETURNING id
135
  `;
136
  
137
  // Nested transaction with savepoint
138
  try {
139
    await sql.savepoint("user_profile", async (sql) => {
140
      await sql`
141
        INSERT INTO user_profiles (user_id, bio) 
142
        VALUES (${user[0].id}, ${bio})
143
      `;
144
      
145
      // This might fail, but won't rollback the user insert
146
      await sql`
147
        INSERT INTO user_settings (user_id, theme) 
148
        VALUES (${user[0].id}, ${theme})
149
      `;
150
    });
151
  } catch (error) {
152
    console.log("Profile creation failed, but user was created");
153
  }
154
  
155
  return user[0];
156
});
157

158
// Multiple savepoints
159
const result = await sql.begin(async (sql) => {
160
  const order = await sql`
161
    INSERT INTO orders (user_id) VALUES (${userId}) RETURNING id
162
  `;
163
  
164
  for (const item of items) {
165
    try {
166
      await sql.savepoint(async (sql) => {
167
        // Try to add item to order
168
        await sql`
169
          INSERT INTO order_items (order_id, product_id, quantity)
170
          VALUES (${order[0].id}, ${item.id}, ${item.quantity})
171
        `;
172
        
173
        // Update stock
174
        await sql`
175
          UPDATE products 
176
          SET stock = stock - ${item.quantity} 
177
          WHERE id = ${item.id} AND stock >= ${item.quantity}
178
        `;
179
      });
180
    } catch (error) {
181
      console.log(`Failed to add item ${item.id}, continuing with other items`);
182
    }
183
  }
184
  
185
  return order[0];
186
});
187
```
188

189
### Two-Phase Commit
190

191
Prepare transactions for two-phase commit scenarios across multiple databases.
192

193
```javascript { .api }
194
/**
195
 * Prepare transaction for two-phase commit
196
 * @param name - Transaction identifier for prepare
197
 */
198
prepare(name: string): void;
199
```
200

201
**Usage Examples:**
202

203
```javascript
204
// Prepare transaction for two-phase commit
205
const result = await sql.begin(async (sql) => {
206
  await sql`
207
    INSERT INTO orders (user_id, total) 
208
    VALUES (${userId}, ${total})
209
  `;
210
  
211
  await sql`
212
    UPDATE inventory 
213
    SET quantity = quantity - ${orderQuantity} 
214
    WHERE product_id = ${productId}
215
  `;
216
  
217
  // Prepare for two-phase commit instead of committing
218
  sql.prepare("order_transaction_123");
219
  
220
  return "prepared";
221
});
222

223
// Later, commit or rollback the prepared transaction
224
await sql`COMMIT PREPARED 'order_transaction_123'`;
225
// or
226
await sql`ROLLBACK PREPARED 'order_transaction_123'`;
227
```
228

229
### Error Handling in Transactions
230

231
Understanding how errors are handled within transactions and savepoints.
232

233
**Usage Examples:**
234

235
```javascript
236
try {
237
  const result = await sql.begin(async (sql) => {
238
    const user = await sql`
239
      INSERT INTO users (name, email) 
240
      VALUES (${name}, ${email}) 
241
      RETURNING id
242
    `;
243
    
244
    // This will cause transaction to rollback if it fails
245
    await sql`
246
      INSERT INTO user_profiles (user_id, bio) 
247
      VALUES (${user[0].id}, ${bio})
248
    `;
249
    
250
    return user[0];
251
  });
252
} catch (error) {
253
  // Both user and profile inserts will be rolled back
254
  console.error("Transaction failed:", error);
255
}
256

257
// Handling errors in savepoints
258
const result = await sql.begin(async (sql) => {
259
  const user = await sql`
260
    INSERT INTO users (name, email) 
261
    VALUES (${name}, ${email}) 
262
    RETURNING id
263
  `;
264
  
265
  // Try optional operations in savepoint
266
  try {
267
    await sql.savepoint(async (sql) => {
268
      await sql`
269
        INSERT INTO user_profiles (user_id, bio) 
270
        VALUES (${user[0].id}, ${bio})
271
      `;
272
      
273
      // If this fails, only the savepoint is rolled back
274
      await sql`
275
        INSERT INTO user_preferences (user_id, theme)
276
        VALUES (${user[0].id}, ${theme})
277
      `;
278
    });
279
  } catch (error) {
280
    console.log("Optional operations failed, but user was created");
281
  }
282
  
283
  return user[0]; // User will still be created
284
});
285
```
286

287
### Transaction Isolation Levels
288

289
Configure transaction isolation levels for concurrent access control.
290

291
**Usage Examples:**
292

293
```javascript
294
// Serializable isolation
295
await sql.begin("ISOLATION LEVEL SERIALIZABLE", async (sql) => {
296
  // Highest isolation level - prevents all phenomena
297
  const result = await sql`SELECT * FROM sensitive_data WHERE id = ${id}`;
298
  await sql`UPDATE sensitive_data SET value = ${newValue} WHERE id = ${id}`;
299
  return result;
300
});
301

302
// Repeatable read isolation  
303
await sql.begin("ISOLATION LEVEL REPEATABLE READ", async (sql) => {
304
  // Prevents dirty and non-repeatable reads
305
  const balance1 = await sql`SELECT balance FROM accounts WHERE id = ${accountId}`;
306
  // ... other operations ...
307
  const balance2 = await sql`SELECT balance FROM accounts WHERE id = ${accountId}`;
308
  // balance1 and balance2 will be the same
309
});
310

311
// Read committed (default)
312
await sql.begin("ISOLATION LEVEL READ COMMITTED", async (sql) => {
313
  // Default level - prevents dirty reads only
314
  const result = await sql`SELECT * FROM data WHERE updated_at > ${date}`;
315
  return result;
316
});
317

318
// Read uncommitted
319
await sql.begin("ISOLATION LEVEL READ UNCOMMITTED", async (sql) => {
320
  // Lowest level - allows all phenomena (rarely used)
321
  const result = await sql`SELECT COUNT(*) FROM large_table`;
322
  return result;
323
});
324
```
325

326
### Transaction Best Practices
327

328
**Performance Considerations:**
329

330
```javascript
331
// Keep transactions short
332
await sql.begin(async (sql) => {
333
  // Do database operations quickly
334
  const result = await sql`UPDATE users SET last_login = NOW() WHERE id = ${id}`;
335
  // Don't do heavy computation here
336
  return result;
337
});
338

339
// Batch operations when possible
340
await sql.begin(async (sql) => {
341
  // Insert multiple records in one operation
342
  await sql`
343
    INSERT INTO logs (user_id, action, timestamp)
344
    VALUES ${sql(logEntries.map(entry => [entry.userId, entry.action, entry.timestamp]))}
345
  `;
346
});
347

348
// Use savepoints for optional operations
349
await sql.begin(async (sql) => {
350
  // Critical operation
351
  const order = await sql`
352
    INSERT INTO orders (user_id, total) VALUES (${userId}, ${total}) 
353
    RETURNING id
354
  `;
355
  
356
  // Optional operations that shouldn't fail the transaction
357
  try {
358
    await sql.savepoint(async (sql) => {
359
      await sql`INSERT INTO audit_log (action, order_id) VALUES ('created', ${order[0].id})`;
360
      await sql`UPDATE user_stats SET orders_count = orders_count + 1 WHERE user_id = ${userId}`;
361
    });
362
  } catch (error) {
363
    console.warn("Audit operations failed, but order was created");
364
  }
365
  
366
  return order[0];
367
});
368
```