Database Transactions | Bootspring Docs

Patterns for handling database transactions with Prisma.

Overview#

Transactions ensure data integrity:

Atomic operations (all or nothing)
Consistent state changes
Isolated concurrent access
Durable commits

Prerequisites:

Prisma ORM setup
PostgreSQL, MySQL, or compatible database

Implementation#

Basic Transaction#

// lib/db.ts
import { prisma } from '@/lib/db'

export async function transferCredits(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  return prisma.$transaction(async (tx) => {
    // Deduct from sender
    const sender = await tx.user.update({
      where: { id: fromUserId },
      data: { credits: { decrement: amount } }
    })

    if (sender.credits < 0) {
      throw new Error('Insufficient credits')
    }

    // Add to recipient
    await tx.user.update({
      where: { id: toUserId },
      data: { credits: { increment: amount } }
    })

    // Record transaction
    return tx.transaction.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        type: 'TRANSFER'
      }
    })
  })
}

Sequential Operations#

// lib/db.ts
export async function createOrder(
  userId: string,
  items: { productId: string; quantity: number }[]
) {
  return prisma.$transaction(async (tx) => {
    // 1. Check inventory
    for (const item of items) {
      const product = await tx.product.findUnique({
        where: { id: item.productId }
      })

      if (!product || product.stock < item.quantity) {
        throw new Error(`Insufficient stock for ${item.productId}`)
      }
    }

    // 2. Create order
    const order = await tx.order.create({
      data: {
        userId,
        status: 'PENDING',
        items: {
          create: items.map((item) => ({
            productId: item.productId,
            quantity: item.quantity
          }))
        }
      }
    })

    // 3. Decrement inventory
    for (const item of items) {
      await tx.product.update({
        where: { id: item.productId },
        data: { stock: { decrement: item.quantity } }
      })
    }

    return order
  })
}

Transaction Options#

// lib/db.ts
export async function riskyOperation() {
  return prisma.$transaction(
    async (tx) => {
      // Long-running operation
      await tx.heavyComputation.create({ /* ... */ })
    },
    {
      maxWait: 5000, // Wait up to 5s for transaction slot
      timeout: 10000, // Allow up to 10s for transaction
      isolationLevel: 'Serializable' // Strictest isolation
    }
  )
}

Batch Operations#

// lib/db.ts
export async function batchUpdate(updates: { id: string; data: any }[]) {
  // Use $transaction for batching multiple operations
  return prisma.$transaction(
    updates.map((update) =>
      prisma.item.update({
        where: { id: update.id },
        data: update.data
      })
    )
  )
}

Optimistic Locking#

// lib/db.ts
export async function updateWithVersion(
  id: string,
  expectedVersion: number,
  data: any
) {
  const result = await prisma.document.updateMany({
    where: {
      id,
      version: expectedVersion
    },
    data: {
      ...data,
      version: { increment: 1 }
    }
  })

  if (result.count === 0) {
    throw new Error('Concurrent modification detected')
  }

  return prisma.document.findUnique({ where: { id } })
}

Nested Transactions#

// lib/db.ts
export async function complexOperation(userId: string) {
  return prisma.$transaction(async (tx) => {
    // Create main record
    const order = await tx.order.create({
      data: { userId, status: 'PENDING' }
    })

    // Call another function that uses the same transaction
    await processOrderItems(tx, order.id, items)

    // Update user stats
    await tx.user.update({
      where: { id: userId },
      data: { orderCount: { increment: 1 } }
    })

    return order
  })
}

// Helper that accepts transaction client
async function processOrderItems(
  tx: Prisma.TransactionClient,
  orderId: string,
  items: OrderItem[]
) {
  for (const item of items) {
    await tx.orderItem.create({
      data: {
        orderId,
        productId: item.productId,
        quantity: item.quantity
      }
    })
  }
}

Error Handling#

// lib/db.ts
import { Prisma } from '@prisma/client'

export async function safeTransaction<T>(
  operation: (tx: Prisma.TransactionClient) => Promise<T>
): Promise<{ success: true; data: T } | { success: false; error: string }> {
  try {
    const data = await prisma.$transaction(operation)
    return { success: true, data }
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      // Handle specific error codes
      switch (error.code) {
        case 'P2002':
          return { success: false, error: 'Duplicate entry' }
        case 'P2003':
          return { success: false, error: 'Foreign key constraint failed' }
        case 'P2025':
          return { success: false, error: 'Record not found' }
        default:
          return { success: false, error: 'Database error' }
      }
    }
    return { success: false, error: 'Transaction failed' }
  }
}

Usage Examples#

Server Action with Transaction#

// actions/order.ts
'use server'

import { createOrder } from '@/lib/db'
import { auth } from '@/auth'
import { revalidatePath } from 'next/cache'

export async function placeOrder(items: { productId: string; quantity: number }[]) {
  const session = await auth()
  if (!session?.user) throw new Error('Unauthorized')

  try {
    const order = await createOrder(session.user.id, items)
    revalidatePath('/orders')
    return { success: true, orderId: order.id }
  } catch (error) {
    return { success: false, error: error.message }
  }
}

API Route with Transaction#

// app/api/transfer/route.ts
import { NextResponse } from 'next/server'
import { transferCredits } from '@/lib/db'

export async function POST(request: Request) {
  const { fromUserId, toUserId, amount } = await request.json()

  try {
    const transaction = await transferCredits(fromUserId, toUserId, amount)
    return NextResponse.json({ success: true, transaction })
  } catch (error) {
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 400 }
    )
  }
}

Best Practices#

Keep transactions short - Long transactions block other operations
Handle errors properly - Always catch and handle transaction failures
Use appropriate isolation levels - Balance consistency vs. performance
Avoid external API calls - Keep transactions database-only
Test rollback scenarios - Ensure failed transactions clean up properly

Prisma - Prisma setup and basics
Soft Delete - Soft delete with transactions
Pooling - Connection management