prisma-expert

Prisma Expert

Critical rules (non-obvious)

• Never use migrate dev in production — use migrate deploy; migrate dev can reset data
• Singleton client in serverless — new PrismaClient() per request exhausts connections; use global singleton
• include vs select: include: { posts: true } fetches ALL post fields; use select to limit
• $queryRaw returns unknown[] — you must cast with as or validate; Prisma can't infer raw query types
• Missing @relation on both sides causes "The relation is not defined on both sides" runtime error

Schema: canonical model structure

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  role      Role     @default(USER)
  posts     Post[]   @relation("UserPosts")
  profile   Profile? @relation("UserProfile")
  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("UserPosts", fields: [authorId], references: [id], onDelete: Cascade)
  authorId String

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

enum Role { USER ADMIN MODERATOR }

Query optimization: N+1 fix

// ❌ N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// ✅ Include (fetches all post fields)
const users = await prisma.user.findMany({ include: { posts: true } });

// ✅✅ Select (fetch only needed fields — best for performance)
const users = await prisma.user.findMany({
  select: {
    id: true, email: true,
    posts: { select: { id: true, title: true } },
  },
});

// ✅ Complex aggregations → use raw
const result = await prisma.$queryRaw<{ id: string; count: number }[]>`
  SELECT u.id, COUNT(p.id)::int as count
  FROM users u LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id
`;