raheesahmed/sajicode/database

id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()

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])
  @@index([createdAt])
}
 
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
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  @@index([authorId])
  @@index([published, createdAt])
}

// Pagination with cursor (much faster than offset)
const posts = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastPostId },
  orderBy: { createdAt: "desc" },
  select: { id: true, title: true, createdAt: true, author: { select: { name: true } } },
});
// Transaction for multi-table writes
const [user, team] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.team.update({ where: { id: teamId }, data: { memberCount: { increment: 1 } } }),
]);
// Upsert pattern
await prisma.user.upsert({
  where: { email },
  update: { name, updatedAt: new Date() },
  create: { email, name },
});

import { pgTable, text, timestamp, boolean, uuid, index } from "drizzle-orm/pg-core";
export const users = pgTable("user", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: index("idx_user_email").on(table.email),
}));

1. Design schema changes
2. Generate: npx prisma migrate dev --name descriptive_name
3. Review generated SQL — NEVER skip this
4. Test with staging data
5. Deploy: npx prisma migrate deploy
6. NEVER edit committed migrations — create a new one

-- Composite index for multi-column WHERE clauses (order matters!)
CREATE INDEX idx_post_status_date ON post(published, created_at DESC);
 
-- Partial index for filtered queries
CREATE INDEX idx_active_users ON "user"(email) WHERE active = true;
 
-- Covering index (includes all queried columns)
CREATE INDEX idx_post_listing ON post(author_id, created_at DESC) INCLUDE (title);

// BAD: N+1 queries
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}
// GOOD: Eager loading
const users = await prisma.user.findMany({
  include: { posts: { take: 5, orderBy: { createdAt: "desc" } } },
});

// Use connection pooling in production
const prisma = new PrismaClient({
  datasources: { db: { url: process.env.DATABASE_URL } },
  log: process.env.NODE_ENV === "development" ? ["query", "warn", "error"] : ["error"],
});
// Graceful shutdown
process.on("beforeExit", async () => { await prisma.$disconnect(); });