Transactions

// Automatic transaction with callback
await db.transaction(async (trx) => {
  // All queries use the transaction
  const user = await trx.query(
    cook`nocap:users drip:name,email fire:John,john@test.com flex:id`
  );

  await trx.query(
    cook`nocap:profiles drip:user_id,bio fire:${user.id},New user`
  );

  // If any query fails, all changes are rolled back
});

// Manual control over commit/rollback
const trx = await db.beginTransaction();

try {
  await trx.query(cook`glow:accounts rizz:balance=balance-100 sus:id=1`);
  await trx.query(cook`glow:accounts rizz:balance=balance+100 sus:id=2`);

  // Commit if all successful
  await trx.commit();
} catch (error) {
  // Rollback on any error
  await trx.rollback();
  throw error;
}

await db.transaction(async (trx) => {
  await trx.query(cook`nocap:orders drip:user_id,total fire:1,100`);

  // Create a savepoint
  await trx.transaction(async (nested) => {
    await nested.query(cook`nocap:order_items drip:order_id,product_id fire:1,42`);

    // This inner transaction can be rolled back independently
    if (outOfStock) {
      throw new Error('Out of stock');
      // Only rolls back to savepoint, outer transaction continues
    }
  }).catch(() => {
    // Handle nested transaction failure
    console.log('Order item failed, continuing without it');
  });

  // Outer transaction still commits
});

// Set isolation level
await db.transaction(
  async (trx) => {
    // Queries here see a consistent snapshot
    const balance = await trx.query(cook`main:accounts slay:balance sus:id=1`);
  },
  { isolation: 'serializable' }
);

// Available levels:
// - 'read uncommitted'
// - 'read committed' (default)
// - 'repeatable read'
// - 'serializable'

// Automatic retry on serialization failures
await db.transaction(
  async (trx) => {
    const account = await trx.query(
      cook`main:accounts slay:* sus:id=1`
    );

    await trx.query(
      cook`glow:accounts rizz:balance=${account.balance - 100} sus:id=1`
    );
  },
  {
    isolation: 'serializable',
    retry: {
      times: 3,
      delay: 100,  // ms between retries
      onRetry: (attempt, error) => {
        console.log(`Retry ${attempt}: ${error.message}`);
      }
    }
  }
);