Advanced Prisma ORM Patterns

# Advanced Prisma ORM Patterns for Google Antigravity

Master Prisma ORM with Google Antigravity's Gemini 3 engine. This guide covers transactions, middleware, soft deletes, multi-tenancy, and performance optimization patterns.

## Prisma Client Extensions

```typescript
// lib/prisma.ts
import { PrismaClient, Prisma } from '@prisma/client';

const prismaClientSingleton = () => {
  return new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  }).$extends({
    model: {
      $allModels: {
        async softDelete<T>(
          this: T,
          where: Prisma.Args<T, 'update'>['where']
        ): Promise<Prisma.Result<T, any, 'update'>> {
          const context = Prisma.getExtensionContext(this);
          return (context as any).update({
            where,
            data: { deletedAt: new Date() },
          });
        },

        async findManyActive<T>(
          this: T,
          args?: Prisma.Args<T, 'findMany'>
        ): Promise<Prisma.Result<T, any, 'findMany'>> {
          const context = Prisma.getExtensionContext(this);
          return (context as any).findMany({
            ...args,
            where: {
              ...args?.where,
              deletedAt: null,
            },
          });
        },
      },
    },
    query: {
      $allModels: {
        async findMany({ model, operation, args, query }) {
          // Auto-exclude soft-deleted records
          args.where = { ...args.where, deletedAt: null };
          return query(args);
        },
      },
    },
  });
};

declare global {
  var prisma: ReturnType<typeof prismaClientSingleton> | undefined;
}

export const prisma = globalThis.prisma ?? prismaClientSingleton();

if (process.env.NODE_ENV !== 'production') {
  globalThis.prisma = prisma;
}
```

## Transaction Patterns

```typescript
// services/order.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';

interface CreateOrderInput {
  userId: string;
  items: Array<{ productId: string; quantity: number }>;
  shippingAddress: string;
}

export async function createOrder(input: CreateOrderInput) {
  return prisma.$transaction(async (tx) => {
    // 1. Verify stock and calculate totals
    const products = await tx.product.findMany({
      where: { id: { in: input.items.map(i => i.productId) } },
    });

    const productMap = new Map(products.map(p => [p.id, p]));
    let totalAmount = 0;

    for (const item of input.items) {
      const product = productMap.get(item.productId);
      if (!product) {
        throw new Error(`Product ${item.productId} not found`);
      }
      if (product.stock < item.quantity) {
        throw new Error(`Insufficient stock for ${product.name}`);
      }
      totalAmount += product.price * item.quantity;
    }

    // 2. Create order
    const order = await tx.order.create({
      data: {
        userId: input.userId,
        totalAmount,
        shippingAddress: input.shippingAddress,
        status: 'PENDING',
        items: {
          create: input.items.map(item => ({
            productId: item.productId,
            quantity: item.quantity,
            price: productMap.get(item.productId)!.price,
          })),
        },
      },
      include: { items: true },
    });

    // 3. Update stock levels
    await Promise.all(
      input.items.map(item =>
        tx.product.update({
          where: { id: item.productId },
          data: { stock: { decrement: item.quantity } },
        })
      )
    );

    // 4. Create audit log
    await tx.auditLog.create({
      data: {
        action: 'ORDER_CREATED',
        entityType: 'Order',
        entityId: order.id,
        userId: input.userId,
        metadata: { itemCount: input.items.length, totalAmount },
      },
    });

    return order;
  }, {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    timeout: 10000,
  });
}
```

## Multi-Tenancy Pattern

```typescript
// lib/prisma-tenant.ts
import { PrismaClient, Prisma } from '@prisma/client';
import { AsyncLocalStorage } from 'async_hooks';

const tenantContext = new AsyncLocalStorage<{ tenantId: string }>();

export function withTenant<T>(tenantId: string, fn: () => T): T {
  return tenantContext.run({ tenantId }, fn);
}

export function getTenantId(): string | undefined {
  return tenantContext.getStore()?.tenantId;
}

export const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        const tenantId = getTenantId();

        if (!tenantId) {
          return query(args);
        }

        // Skip tenant filtering for tenant-agnostic models
        const tenantAgnosticModels = ['Tenant', 'SystemConfig'];
        if (tenantAgnosticModels.includes(model)) {
          return query(args);
        }

        // Inject tenant filter for read operations
        if (['findMany', 'findFirst', 'findUnique', 'count'].includes(operation)) {
          args.where = { ...args.where, tenantId };
        }

        // Inject tenant for create operations
        if (['create', 'createMany'].includes(operation)) {
          if (operation === 'createMany') {
            args.data = args.data.map((d: any) => ({ ...d, tenantId }));
          } else {
            args.data = { ...args.data, tenantId };
          }
        }

        return query(args);
      },
    },
  },
});

// Usage in API route
export async function GET(request: Request) {
  const tenantId = request.headers.get('x-tenant-id')!;

  return withTenant(tenantId, async () => {
    const users = await prisma.user.findMany();
    return Response.json(users);
  });
}
```

## Query Optimization

```typescript
// services/analytics.service.ts
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';

export async function getOrderAnalytics(startDate: Date, endDate: Date) {
  // Use raw query for complex aggregations
  const dailyStats = await prisma.$queryRaw<Array<{
    date: Date;
    orderCount: bigint;
    totalRevenue: Prisma.Decimal;
    avgOrderValue: Prisma.Decimal;
  }>>`
    SELECT 
      DATE_TRUNC('day', "createdAt") as date,
      COUNT(*) as "orderCount",
      SUM("totalAmount") as "totalRevenue",
      AVG("totalAmount") as "avgOrderValue"
    FROM "Order"
    WHERE "createdAt" BETWEEN ${startDate} AND ${endDate}
      AND "status" != 'CANCELLED'
    GROUP BY DATE_TRUNC('day', "createdAt")
    ORDER BY date DESC
  `;

  return dailyStats.map(stat => ({
    date: stat.date,
    orderCount: Number(stat.orderCount),
    totalRevenue: Number(stat.totalRevenue),
    avgOrderValue: Number(stat.avgOrderValue),
  }));
}

// Optimized relation loading
export async function getUserWithOrders(userId: string) {
  return prisma.user.findUnique({
    where: { id: userId },
    select: {
      id: true,
      email: true,
      name: true,
      orders: {
        select: {
          id: true,
          totalAmount: true,
          status: true,
          createdAt: true,
          _count: { select: { items: true } },
        },
        orderBy: { createdAt: 'desc' },
        take: 10,
      },
      _count: { select: { orders: true } },
    },
  });
}
```

## Best Practices

Google Antigravity's Gemini 3 engine recommends these Prisma patterns: Use transactions for operations requiring atomicity. Implement soft deletes with middleware for data recovery. Leverage Prisma extensions for reusable functionality. Use select to fetch only required fields. Implement connection pooling for serverless environments.