drizzle-orm

// db/schema.ts
import { pgTable, text, integer, boolean, timestamp, serial, uuid, varchar, jsonb, index, uniqueIndex } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull(),
  role: varchar("role", { length: 20 }).notNull().default("user"),
  metadata: jsonb("metadata").$type<{ plan: string; seats: number }>(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("email_idx").on(table.email),
}));

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 500 }).notNull(),
  content: text("content"),
  published: boolean("published").default(false).notNull(),
  authorId: uuid("author_id").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  authorIdx: index("author_idx").on(table.authorId),
  publishedIdx: index("published_idx").on(table.published, table.createdAt),
}));

// Relations (for query builder)
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

import { drizzle } from "drizzle-orm/node-postgres";
import { eq, and, gte, desc, sql, like, count } from "drizzle-orm";
import * as schema from "./schema";

const db = drizzle(pool, { schema });

// Select — reads like SQL
const publishedPosts = await db.select()
  .from(posts)
  .where(and(
    eq(posts.published, true),
    gte(posts.createdAt, new Date("2026-01-01")),
  ))
  .orderBy(desc(posts.createdAt))
  .limit(20);

// Join
const postsWithAuthors = await db.select({
  title: posts.title,
  authorName: users.name,
  authorEmail: users.email,
})
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true));

// Relational queries (Prisma-like)
const usersWithPosts = await db.query.users.findMany({
  with: { posts: { where: eq(posts.published, true), limit: 5 } },
  where: eq(users.role, "admin"),
});

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

// Upsert
await db.insert(users)
  .values({ id: userId, name: "Alice", email: "alice@example.com" })
  .onConflictDoUpdate({ target: users.email, set: { name: "Alice Updated" } });

// Aggregate
const [stats] = await db.select({
  total: count(),
  published: count(sql`CASE WHEN ${posts.published} THEN 1 END`),
}).from(posts);

// Transaction
await db.transaction(async (tx) => {
  const [post] = await tx.insert(posts).values({ title: "New", authorId: userId }).returning();
  await tx.insert(notifications).values({ userId, message: `Post ${post.id} created` });
});

npx drizzle-kit generate                   # Generate migration from schema diff
npx drizzle-kit push                       # Push schema directly (prototyping)
npx drizzle-kit migrate                    # Apply migrations
npx drizzle-kit studio                     # Visual data browser

npm install drizzle-orm
npm install -D drizzle-kit
# + driver: pg | mysql2 | better-sqlite3 | @libsql/client | @neondatabase/serverless