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 Post {
  id          String     @id @default(cuid())
  title       String
  content     String?
  published   Boolean    @default(false)
  author      User       @relation(fields: [authorId], references: [id])
  authorId    String
  categories  Category[]
  createdAt   DateTime   @default(now())
  
  @@index([authorId])
  @@index([published, createdAt])
}

model Profile {
  id     String @id @default(cuid())
  bio    String?
  avatar String?
  user   User   @relation(fields: [userId], references: [id])
  userId String @unique
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create with relations
const user = await prisma.user.create({
  data: {
    name: "Alice",
    email: "alice@example.com",
    profile: { create: { bio: "Developer" } },
    posts: {
      create: [
        { title: "First Post", content: "Hello world", published: true },
        { title: "Draft", content: "Work in progress" },
      ],
    },
  },
  include: { posts: true, profile: true },
});

// Complex queries — fully typed
const publishedPosts = await prisma.post.findMany({
  where: {
    published: true,
    author: { role: "ADMIN" },
    createdAt: { gte: new Date("2026-01-01") },
  },
  include: {
    author: { select: { name: true, email: true } },
    categories: true,
  },
  orderBy: { createdAt: "desc" },
  take: 20,
  skip: 0,
});

// Aggregate
const stats = await prisma.post.aggregate({
  _count: true,
  _avg: { createdAt: true },
  where: { published: true },
});

// Transaction
const [updatedPost, newNotification] = await prisma.$transaction([
  prisma.post.update({ where: { id: "..." }, data: { published: true } }),
  prisma.notification.create({ data: { userId: "...", message: "Post published!" } }),
]);

// Raw SQL when needed
const result = await prisma.$queryRaw`
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u LEFT JOIN posts p ON p."authorId" = u.id
  GROUP BY u.name ORDER BY post_count DESC LIMIT 10
`;

npx prisma migrate dev --name add-categories    # Create + apply migration
npx prisma migrate deploy                       # Apply in production
npx prisma db push                              # Push schema without migration (prototyping)
npx prisma studio                               # GUI for browsing data
npx prisma generate                             # Regenerate client after schema change

npm install prisma @prisma/client
npx prisma init                            # Creates schema.prisma + .env