Drizzle ORM

// Install: npm install drizzle-orm
//          npm install -D drizzle-kit
//          npm install postgres   (or mysql2, better-sqlite3)

// ===== Schema =====
// src/db/schema.ts
import { pgTable, serial, text, integer, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
  published: boolean('published').default(false),
});

// ===== Connect =====
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client);

// ===== Query =====
import { eq, and, desc } from 'drizzle-orm';

// Insert
const u = await db.insert(users).values({ email: 'a@x.io', name: 'Alex' }).returning();

// Select
const all = await db.select().from(users).where(eq(users.email, 'a@x.io'));

// Join
const result = await db
  .select({ id: posts.id, title: posts.title, author: users.name })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.id))
  .limit(20);

// Update
await db.update(users).set({ name: 'Sam' }).where(eq(users.id, 1));

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

// Transactions
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'b@x.io' });
  await tx.insert(posts).values({ title: 'Hi', authorId: 1 });
});

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

// query:
const withPosts = await db.query.users.findMany({
  with: { posts: true },
});

// ===== Migrations =====
// drizzle.config.ts
import type { Config } from 'drizzle-kit';
export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
} satisfies Config;

// Commands:
// npx drizzle-kit generate    create SQL migration files
// npx drizzle-kit migrate      apply migrations
// npx drizzle-kit studio       visual DB browser

// ===== Patterns =====
// - Schema in code; migrations in source control
// - SQL-first; ORM is thin
// - Relations for typed includes
// - Use studio for ad-hoc exploration

// ===== Pitfalls =====
// - Not generating after schema changes
// - Forgetting .returning() on insert (no PK back)
// - Drizzle is SQL-aware; understand what each query compiles to

import { drizzle } from 'drizzle-orm/node-postgres';
const db = drizzle(client);
const rows = await db.select().from(users).where(eq(users.active, true));