Prisma Advanced Query Patterns

# Prisma Advanced Query Patterns for Google Antigravity

Efficient database queries are fundamental to application performance. This guide establishes advanced Prisma patterns for Google Antigravity projects, enabling Gemini 3 to generate optimized database access layers with complex queries, relations, and aggregations.

## Relation Queries

Handle complex relationships efficiently:

```typescript
// lib/db/queries/posts.ts
import { prisma } from "@/lib/prisma";
import { Prisma } from "@prisma/client";

// Type-safe include pattern
const postWithDetails = Prisma.validator<Prisma.PostDefaultArgs>()({
  include: {
    author: {
      select: {
        id: true,
        name: true,
        avatarUrl: true,
      },
    },
    categories: {
      select: {
        id: true,
        name: true,
        slug: true,
      },
    },
    _count: {
      select: {
        comments: true,
        likes: true,
      },
    },
  },
});

export type PostWithDetails = Prisma.PostGetPayload<typeof postWithDetails>;

export async function getPostBySlug(slug: string): Promise<PostWithDetails | null> {
  return prisma.post.findUnique({
    where: { slug },
    ...postWithDetails,
  });
}

export async function getPostsWithFilters({
  categorySlug,
  authorId,
  search,
  page = 1,
  limit = 10,
}: {
  categorySlug?: string;
  authorId?: string;
  search?: string;
  page?: number;
  limit?: number;
}) {
  const where: Prisma.PostWhereInput = {
    published: true,
    ...(categorySlug && {
      categories: { some: { slug: categorySlug } },
    }),
    ...(authorId && { authorId }),
    ...(search && {
      OR: [
        { title: { contains: search, mode: "insensitive" } },
        { content: { contains: search, mode: "insensitive" } },
      ],
    }),
  };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      ...postWithDetails,
      orderBy: { createdAt: "desc" },
      skip: (page - 1) * limit,
      take: limit,
    }),
    prisma.post.count({ where }),
  ]);

  return {
    posts,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  };
}
```

## Aggregation Queries

Perform analytics and statistics:

```typescript
// lib/db/queries/analytics.ts
import { prisma } from "@/lib/prisma";

export async function getDashboardStats(userId: string) {
  const [postStats, revenueStats, topCategories] = await prisma.$transaction([
    // Post statistics
    prisma.post.aggregate({
      where: { authorId: userId },
      _count: true,
      _sum: { viewCount: true },
    }),

    // Revenue by month
    prisma.order.groupBy({
      by: ["createdAt"],
      where: {
        sellerId: userId,
        status: "COMPLETED",
        createdAt: { gte: new Date(Date.now() - 365 * 24 * 60 * 60 * 1000) },
      },
      _sum: { total: true },
      _count: true,
    }),

    // Top performing categories
    prisma.category.findMany({
      where: {
        posts: { some: { authorId: userId } },
      },
      select: {
        id: true,
        name: true,
        _count: { select: { posts: true } },
      },
      orderBy: { posts: { _count: "desc" } },
      take: 5,
    }),
  ]);

  return { postStats, revenueStats, topCategories };
}

export async function getLeaderboard() {
  const topAuthors = await prisma.user.findMany({
    select: {
      id: true,
      name: true,
      avatarUrl: true,
      _count: {
        select: {
          posts: { where: { published: true } },
          followers: true,
        },
      },
    },
    orderBy: {
      posts: { _count: "desc" },
    },
    take: 10,
  });

  return topAuthors;
}
```

## Transaction Patterns

Ensure data consistency with transactions:

```typescript
// lib/db/queries/orders.ts
import { prisma } from "@/lib/prisma";
import { Prisma } from "@prisma/client";

export async function createOrderWithItems(
  userId: string,
  items: Array<{ productId: string; quantity: number }>
) {
  return prisma.$transaction(async (tx) => {
    // Fetch products and verify stock
    const products = await tx.product.findMany({
      where: { id: { in: items.map((i) => i.productId) } },
    });

    // Validate stock availability
    for (const item of items) {
      const product = products.find((p) => p.id === 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}`);
      }
    }

    // Calculate total
    const total = items.reduce((sum, item) => {
      const product = products.find((p) => p.id === item.productId)!;
      return sum + product.price * item.quantity;
    }, 0);

    // Create order
    const order = await tx.order.create({
      data: {
        userId,
        total,
        status: "PENDING",
        items: {
          create: items.map((item) => {
            const product = products.find((p) => p.id === item.productId)!;
            return {
              productId: item.productId,
              quantity: item.quantity,
              price: product.price,
            };
          }),
        },
      },
      include: { items: { include: { product: true } } },
    });

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

    return order;
  });
}
```

## Raw SQL for Complex Queries

Use raw SQL when Prisma DSL is insufficient:

```typescript
// lib/db/queries/search.ts
import { prisma } from "@/lib/prisma";
import { Prisma } from "@prisma/client";

export async function fullTextSearch(query: string, limit = 20) {
  const results = await prisma.$queryRaw<
    Array<{ id: string; title: string; excerpt: string; rank: number }>
  >`
    SELECT 
      id,
      title,
      LEFT(content, 200) as excerpt,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) as rank
    FROM posts
    WHERE 
      published = true
      AND search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit}
  `;

  return results;
}
```

## Best Practices

1. **Type-safe queries**: Use Prisma.validator for reusable includes
2. **Transactions**: Wrap related operations for consistency
3. **Pagination**: Always paginate large result sets
4. **Selective loading**: Only fetch needed fields with select
5. **Query batching**: Use $transaction for parallel queries