Drizzle & Prisma ORM Cheatsheet

# Install (PostgreSQL example)
npm i drizzle-orm postgres
npm i -D drizzle-kit

# drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

// src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

import {
  pgTable, serial, text, varchar, integer,
  boolean, timestamp, uuid, jsonb, pgEnum
} from "drizzle-orm/pg-core";

// Enum
export const roleEnum = pgEnum("role", ["admin", "user", "moderator"]);

// Users table
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }).notNull(),
  email: text("email").notNull().unique(),
  role: roleEnum("role").default("user"),
  isActive: boolean("is_active").default(true),
  metadata: jsonb("metadata"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Posts table (with FK)
export const posts = pgTable("posts", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: varchar("title", { length: 512 }).notNull(),
  content: text("content"),
  authorId: integer("author_id").references(() => users.id),
  published: boolean("published").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

// Types (auto-inferred)
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

import { eq, and, or, gt, lt, like, inArray, sql, desc, asc, count } from "drizzle-orm";

// ── SELECT ──
const allUsers = await db.select().from(users);

const filtered = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(and(
    eq(users.role, "admin"),
    gt(users.createdAt, new Date("2024-01-01"))
  ))
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// ── INSERT ──
const [user] = await db
  .insert(users)
  .values({ name: "Alice", email: "alice@example.com" })
  .returning();

// Bulk insert
await db.insert(users).values([
  { name: "Bob", email: "bob@ex.com" },
  { name: "Charlie", email: "charlie@ex.com" },
]);

// ── UPDATE ──
await db
  .update(users)
  .set({ name: "Alice Updated", isActive: false })
  .where(eq(users.id, 1));

// ── DELETE ──
await db.delete(users).where(eq(users.id, 1));

// ── Aggregate ──
const [result] = await db
  .select({ total: count() })
  .from(users)
  .where(eq(users.isActive, true));

// ── Raw SQL ──
const raw = await db.execute(sql`SELECT NOW()`);

// ── Relational query (query API) ──
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
  where: eq(users.isActive, true),
  limit: 10,
});

import { relations } from "drizzle-orm";

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// Many-to-many (join table)
export const postTags = pgTable("post_tags", {
  postId: uuid("post_id").references(() => posts.id),
  tagId: integer("tag_id").references(() => tags.id),
});

export const postsToTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

# Generate migration from schema changes
npx drizzle-kit generate

# Push schema directly (dev only, no migration files)
npx drizzle-kit push

# Open Drizzle Studio (DB browser)
npx drizzle-kit studio

# Run migrations programmatically
import { migrate } from "drizzle-orm/postgres-js/migrator";
await migrate(db, { migrationsFolder: "./drizzle" });

# Install
npm i prisma @prisma/client
npx prisma init

# This creates:
#   prisma/schema.prisma
#   .env (DATABASE_URL)

// src/db.ts
import { PrismaClient } from "@prisma/client";

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

export const prisma =
  globalForPrisma.prisma ?? new PrismaClient();

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

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum Role {
  ADMIN
  USER
  MODERATOR
}

model User {
  id        Int      @id @default(autoincrement())
  name      String   @db.VarChar(256)
  email     String   @unique
  role      Role     @default(USER)
  isActive  Boolean  @default(true)  @map("is_active")
  metadata  Json?
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

model Post {
  id        String   @id @default(uuid())
  title     String   @db.VarChar(512)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int      @map("author_id")
  tags      Tag[]
  createdAt DateTime @default(now()) @map("created_at")

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]

  @@map("tags")
}

// ── CREATE ──
const user = await prisma.user.create({
  data: { name: "Alice", email: "alice@example.com" },
});

// Create with relation
const userWithPost = await prisma.user.create({
  data: {
    name: "Bob",
    email: "bob@example.com",
    posts: { create: { title: "First Post", content: "Hello!" } },
  },
  include: { posts: true },
});

// Bulk create
await prisma.user.createMany({
  data: [
    { name: "Charlie", email: "c@ex.com" },
    { name: "Diana", email: "d@ex.com" },
  ],
  skipDuplicates: true,
});

// ── READ ──
const all = await prisma.user.findMany();

const filtered = await prisma.user.findMany({
  where: {
    AND: [
      { role: "ADMIN" },
      { createdAt: { gte: new Date("2024-01-01") } },
    ],
  },
  select: { id: true, name: true, email: true },
  orderBy: { createdAt: "desc" },
  take: 10,
  skip: 0,
});

const unique = await prisma.user.findUnique({
  where: { email: "alice@example.com" },
});

const first = await prisma.user.findFirst({
  where: { name: { contains: "Ali", mode: "insensitive" } },
});

// ── UPDATE ──
await prisma.user.update({
  where: { id: 1 },
  data: { name: "Alice Updated" },
});

// Upsert
await prisma.user.upsert({
  where: { email: "alice@example.com" },
  create: { name: "Alice", email: "alice@example.com" },
  update: { name: "Alice Updated" },
});

// ── DELETE ──
await prisma.user.delete({ where: { id: 1 } });
await prisma.user.deleteMany({ where: { isActive: false } });

// ── AGGREGATE ──
const stats = await prisma.user.aggregate({
  _count: true,
  where: { isActive: true },
});

// Group by
const byRole = await prisma.user.groupBy({
  by: ["role"],
  _count: { _all: true },
});

// Include related data (eager loading)
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      take: 5,
      include: { tags: true },  // nested include
    },
  },
});

// Select specific fields (instead of include)
const lean = await prisma.user.findMany({
  select: {
    name: true,
    posts: { select: { title: true } },
  },
});

// Relation filters
const usersWithPublished = await prisma.user.findMany({
  where: {
    posts: { some: { published: true } },  // has at least one published post
  },
});

// Transactions
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { name: "Tx User", email: "tx@ex.com" } }),
  prisma.post.create({ data: { title: "Tx Post", authorId: 1 } }),
]);

# Generate migration from schema changes
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (dangerous!)
npx prisma migrate reset

# Push schema without migrations (prototyping)
npx prisma db push

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio (GUI)
npx prisma studio

# Introspect existing database
npx prisma db pull