Prisma ORM Advanced Techniques

# Prisma ORM Advanced Techniques for Google Antigravity

Master database management with Prisma ORM in Google Antigravity IDE. This comprehensive guide covers schema design, relations, transactions, raw queries, middleware patterns, and performance optimization techniques for building robust database layers.

## Configuration

Configure your Antigravity environment for Prisma:

```typescript
// .antigravity/prisma.ts
export const prismaConfig = {
  features: {
    transactions: true,
    middleware: true,
    rawQueries: true,
    extensions: true
  },
  optimization: {
    connectionPooling: true,
    queryLogging: "development"
  }
};
```

## Schema Design

Define robust database schemas:

```prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "relationJoins"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id          String     @id @default(cuid())
  title       String
  content     String?
  published   Boolean    @default(false)
  author      User       @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId    String
  categories  Category[]
  viewCount   Int        @default(0)
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([authorId])
  @@index([published, createdAt])
  @@map("posts")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
```

## Transaction Patterns

Handle complex operations atomically:

```typescript
import { PrismaClient, Prisma } from "@prisma/client";

const prisma = new PrismaClient();

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

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

    const recipient = await tx.user.update({
      where: { id: toUserId },
      data: { credits: { increment: amount } }
    });

    await tx.transaction.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        type: "TRANSFER"
      }
    });

    return { sender, recipient };
  }, {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
    maxWait: 5000,
    timeout: 10000
  });
}
```

## Middleware Extensions

Add cross-cutting concerns:

```typescript
const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        const start = performance.now();
        const result = await query(args);
        const duration = performance.now() - start;

        if (duration > 100) {
          console.warn(`Slow query: ${model}.${operation} took ${duration}ms`);
        }

        return result;
      }
    }
  }
});

const prismaWithSoftDelete = prisma.$extends({
  query: {
    post: {
      async delete({ args, query }) {
        return prisma.post.update({
          where: args.where,
          data: { deletedAt: new Date() }
        });
      },
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      }
    }
  }
});
```

## Query Optimization

Write efficient queries:

```typescript
async function getPostsWithCounts() {
  return prisma.post.findMany({
    where: { published: true },
    select: {
      id: true,
      title: true,
      createdAt: true,
      author: {
        select: { name: true, email: true }
      },
      _count: {
        select: { comments: true, likes: true }
      }
    },
    orderBy: { createdAt: "desc" },
    take: 20
  });
}

async function getPaginatedPosts(cursor?: string, take = 10) {
  const posts = await prisma.post.findMany({
    take: take + 1,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: "desc" },
    include: { author: true }
  });

  const hasMore = posts.length > take;
  const items = hasMore ? posts.slice(0, -1) : posts;
  const nextCursor = hasMore ? items[items.length - 1].id : null;

  return { items, nextCursor, hasMore };
}
```

## Best Practices

Follow these guidelines for Prisma:

1. **Use transactions** - Atomic operations for consistency
2. **Index strategically** - Add indexes for common queries
3. **Select only needed fields** - Reduce data transfer
4. **Use cursor pagination** - Better performance than offset
5. **Extend with middleware** - Cross-cutting concerns
6. **Monitor slow queries** - Log and optimize

Google Antigravity IDE provides intelligent Prisma schema suggestions and query optimization tips.