Drizzle CRUD/Advanced
Transactions
Use database transactions with Drizzle CRUD operations
Transactions
Drizzle CRUD supports database transactions by allowing you to pass a transaction instance through the operation context. This ensures data consistency across multiple operations.
Basic Transaction Usage
Pass a transaction instance to any CRUD operation:
import { drizzle } from 'drizzle-orm/postgres-js'
const db = drizzle(/* connection */)
const result = await db.transaction(async (tx) => {
// Create user
const user = await userCrud.create(
{
name: 'John Doe',
email: 'john@example.com',
},
{
db: tx, // Pass transaction instance
}
)
// Create user profile
const profile = await profileCrud.create(
{
userId: user.id,
bio: 'Software developer',
},
{
db: tx,
}
)
return { user, profile }
})Multiple Operations in Transaction
Perform multiple CRUD operations within a single transaction:
const transferFunds = async (fromUserId: string, toUserId: string, amount: number) => {
return await db.transaction(async (tx) => {
// Deduct from sender
const fromUser = await userCrud.findById(fromUserId, { db: tx })
if (!fromUser || fromUser.balance < amount) {
throw new Error('Insufficient funds')
}
await userCrud.update(
fromUserId,
{ balance: fromUser.balance - amount },
{ db: tx }
)
// Add to receiver
const toUser = await userCrud.findById(toUserId, { db: tx })
if (!toUser) {
throw new Error('Recipient not found')
}
await userCrud.update(
toUserId,
{ balance: toUser.balance + amount },
{ db: tx }
)
// Create transaction record
const transaction = await transactionCrud.create(
{
fromUserId,
toUserId,
amount,
type: 'transfer',
status: 'completed',
},
{ db: tx }
)
return transaction
})
}Error Handling in Transactions
Transactions automatically roll back on errors:
const createOrderWithItems = async (orderData: any, items: any[]) => {
try {
return await db.transaction(async (tx) => {
// Create order
const order = await orderCrud.create(orderData, { db: tx })
// Create order items
const orderItems = []
for (const item of items) {
const orderItem = await orderItemCrud.create(
{
orderId: order.id,
productId: item.productId,
quantity: item.quantity,
price: item.price,
},
{ db: tx }
)
orderItems.push(orderItem)
}
// Update inventory
for (const item of items) {
const product = await productCrud.findById(item.productId, { db: tx })
if (product.stock < item.quantity) {
throw new Error(`Insufficient stock for product ${item.productId}`)
}
await productCrud.update(
item.productId,
{ stock: product.stock - item.quantity },
{ db: tx }
)
}
return { order, orderItems }
})
} catch (error) {
console.error('Order creation failed:', error)
throw error
}
}Nested Transactions
Drizzle supports nested transactions (savepoints):
const complexOperation = async () => {
return await db.transaction(async (tx) => {
// Main transaction
const user = await userCrud.create(
{ name: 'John', email: 'john@example.com' },
{ db: tx }
)
try {
// Nested transaction (savepoint)
await tx.transaction(async (nestedTx) => {
await profileCrud.create(
{ userId: user.id, bio: 'Developer' },
{ db: nestedTx }
)
// This might fail
await riskyCrud.create(
{ userId: user.id, data: 'risky' },
{ db: nestedTx }
)
})
} catch (error) {
// Nested transaction rolls back, main transaction continues
console.log('Profile creation failed, continuing...')
}
// Create settings (always succeeds)
await settingsCrud.create(
{ userId: user.id, theme: 'dark' },
{ db: tx }
)
return user
})
}Transaction with Hooks
Hooks work within transactions:
const userCrud = createCrud(users, {
hooks: {
afterCreate: async (user, { context }) => {
// This runs within the same transaction
if (context?.db) {
await auditCrud.create(
{
operation: 'create',
tableName: 'users',
recordId: user.id,
timestamp: new Date(),
},
{ db: context.db }
)
}
return user
},
},
})
// Usage
await db.transaction(async (tx) => {
// Both user creation and audit log creation happen in same transaction
const user = await userCrud.create(
{ name: 'John', email: 'john@example.com' },
{ db: tx }
)
})Transaction Isolation Levels
Configure transaction isolation levels:
// PostgreSQL example
const result = await db.transaction(
async (tx) => {
const user = await userCrud.create(
{ name: 'John', email: 'john@example.com' },
{ db: tx }
)
return user
},
{
isolationLevel: 'serializable',
}
)Long-Running Transactions
For long-running transactions, consider chunking operations:
const bulkImport = async (records: any[]) => {
const chunkSize = 1000
const chunks = []
for (let i = 0; i < records.length; i += chunkSize) {
chunks.push(records.slice(i, i + chunkSize))
}
const results = []
for (const chunk of chunks) {
const chunkResult = await db.transaction(async (tx) => {
const created = []
for (const record of chunk) {
const created_record = await userCrud.create(record, { db: tx })
created.push(created_record)
}
return created
})
results.push(...chunkResult)
}
return results
}Transaction Best Practices
1. Keep Transactions Short
// Good: Short transaction
const quickTransaction = async () => {
return await db.transaction(async (tx) => {
const user = await userCrud.create(userData, { db: tx })
await auditCrud.create(auditData, { db: tx })
return user
})
}
// Avoid: Long-running transaction
const longTransaction = async () => {
return await db.transaction(async (tx) => {
const user = await userCrud.create(userData, { db: tx })
// Don't do heavy processing in transactions
await sendEmailsToAllUsers() // This takes too long
await generateReports() // This takes too long
return user
})
}2. Handle Deadlocks
const retryTransaction = async (operation: () => Promise<any>, maxRetries = 3) => {
let attempt = 0
while (attempt < maxRetries) {
try {
return await operation()
} catch (error) {
if (error.code === '40001' && attempt < maxRetries - 1) {
// Deadlock detected, retry
attempt++
await new Promise(resolve => setTimeout(resolve, 100 * attempt))
continue
}
throw error
}
}
}
// Usage
const result = await retryTransaction(async () => {
return await db.transaction(async (tx) => {
await userCrud.update(userId1, { balance: newBalance1 }, { db: tx })
await userCrud.update(userId2, { balance: newBalance2 }, { db: tx })
})
})3. Use Consistent Transaction Context
// Good: Consistent transaction passing
const createUserWithProfile = async (userData: any, profileData: any) => {
return await db.transaction(async (tx) => {
const context = { db: tx }
const user = await userCrud.create(userData, context)
const profile = await profileCrud.create(
{ ...profileData, userId: user.id },
context
)
return { user, profile }
})
}
// Avoid: Inconsistent transaction usage
const inconsistentTransaction = async () => {
return await db.transaction(async (tx) => {
const user = await userCrud.create(userData, { db: tx })
// This won't be in the transaction!
const profile = await profileCrud.create(profileData)
return { user, profile }
})
}Advanced Transaction Patterns
Saga Pattern
Handle complex business transactions across multiple services:
const processOrder = async (orderData: any) => {
const saga = {
actions: [],
compensations: [],
}
try {
// Step 1: Create order
const order = await db.transaction(async (tx) => {
const order = await orderCrud.create(orderData, { db: tx })
saga.actions.push({ type: 'order_created', orderId: order.id })
saga.compensations.unshift({
type: 'cancel_order',
orderId: order.id,
})
return order
})
// Step 2: Reserve inventory
await db.transaction(async (tx) => {
await inventoryCrud.update(
orderData.productId,
{ reserved: { increment: orderData.quantity } },
{ db: tx }
)
saga.actions.push({ type: 'inventory_reserved' })
saga.compensations.unshift({ type: 'release_inventory' })
})
// Step 3: Process payment
await db.transaction(async (tx) => {
await paymentCrud.create(
{
orderId: order.id,
amount: orderData.total,
status: 'processed',
},
{ db: tx }
)
saga.actions.push({ type: 'payment_processed' })
})
return order
} catch (error) {
// Compensate for completed actions
for (const compensation of saga.compensations) {
await executeCompensation(compensation)
}
throw error
}
}Next Steps
- Soft Deletes - Soft delete configuration
- Bulk Operations - Efficient bulk operations
- Performance - Optimization techniques