Prisma Database Access Patterns

# Prisma Database Access Patterns for Google Antigravity

Prisma ORM provides the best developer experience for database access in modern TypeScript applications. When combined with Google Antigravity's intelligent code generation powered by Gemini 3, you can build robust, type-safe database layers with minimal effort.

## Prisma Schema Design

Start with a well-structured schema that leverages Prisma's powerful modeling capabilities:

```prisma
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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 Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

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

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

model Category {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]

  @@map("categories")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
```

## Prisma Client Singleton

Create a singleton instance to prevent connection exhaustion in serverless environments:

```typescript
// lib/prisma.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === "development" 
    ? ["query", "error", "warn"] 
    : ["error"],
});

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

export default prisma;
```

## Type-Safe Query Patterns

Implement repository patterns with full type safety:

```typescript
// lib/repositories/user-repository.ts
import prisma from "@/lib/prisma";
import { Prisma, User } from "@prisma/client";

export type UserWithProfile = Prisma.UserGetPayload<{
  include: { profile: true };
}>;

export type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true };
}>;

export const userRepository = {
  async findById(id: string): Promise<User | null> {
    return prisma.user.findUnique({
      where: { id },
    });
  },

  async findByIdWithProfile(id: string): Promise<UserWithProfile | null> {
    return prisma.user.findUnique({
      where: { id },
      include: { profile: true },
    });
  },

  async findByEmail(email: string): Promise<User | null> {
    return prisma.user.findUnique({
      where: { email },
    });
  },

  async create(data: Prisma.UserCreateInput): Promise<User> {
    return prisma.user.create({ data });
  },

  async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
    return prisma.user.update({
      where: { id },
      data,
    });
  },

  async delete(id: string): Promise<User> {
    return prisma.user.delete({
      where: { id },
    });
  },

  async findMany(params: {
    skip?: number;
    take?: number;
    where?: Prisma.UserWhereInput;
    orderBy?: Prisma.UserOrderByWithRelationInput;
  }): Promise<User[]> {
    const { skip, take, where, orderBy } = params;
    return prisma.user.findMany({
      skip,
      take,
      where,
      orderBy,
    });
  },
};
```

## Transaction Patterns

Handle complex operations with transactions:

```typescript
// lib/services/user-service.ts
import prisma from "@/lib/prisma";
import { User, Profile } from "@prisma/client";

export async function createUserWithProfile(
  userData: { email: string; name: string },
  profileData: { bio?: string; avatar?: string }
): Promise<User & { profile: Profile }> {
  return prisma.$transaction(async (tx) => {
    const user = await tx.user.create({
      data: {
        email: userData.email,
        name: userData.name,
        profile: {
          create: profileData,
        },
      },
      include: { profile: true },
    });

    await tx.post.create({
      data: {
        title: "Welcome Post",
        content: "Welcome to the platform!",
        authorId: user.id,
        published: true,
      },
    });

    return user as User & { profile: Profile };
  });
}

export async function transferPosts(
  fromUserId: string,
  toUserId: string
): Promise<void> {
  await prisma.$transaction([
    prisma.post.updateMany({
      where: { authorId: fromUserId },
      data: { authorId: toUserId },
    }),
    prisma.user.delete({
      where: { id: fromUserId },
    }),
  ]);
}
```

## Pagination with Cursor

Implement efficient cursor-based pagination:

```typescript
// lib/utils/pagination.ts
import prisma from "@/lib/prisma";
import { Post } from "@prisma/client";

interface PaginatedResult<T> {
  items: T[];
  nextCursor: string | null;
  hasMore: boolean;
}

export async function getPaginatedPosts(
  cursor?: string,
  limit: number = 10
): Promise<PaginatedResult<Post>> {
  const posts = await prisma.post.findMany({
    take: limit + 1,
    cursor: cursor ? { id: cursor } : undefined,
    where: { published: true },
    orderBy: { createdAt: "desc" },
    skip: cursor ? 1 : 0,
  });

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

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

## Best Practices

1. **Use select for partial queries** - Only fetch fields you need
2. **Index frequently queried fields** - Add @@index annotations
3. **Handle soft deletes** - Use deletedAt instead of hard deletes
4. **Use transactions for related operations** - Ensure data consistency
5. **Implement connection pooling** - Use PgBouncer for serverless

Prisma with Google Antigravity enables rapid, type-safe database development with intelligent autocompletion and error prevention.