Drizzle ORM

Schema Definition

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

export const roleEnum = pgEnum('role', ['admin', 'user']);

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  clerkId: text('clerk_id').unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  role: roleEnum('role').default('user').notNull(),
  settings: jsonb('settings').$type<{ theme: string }>(),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: index('users_email_idx').on(table.email),
  createdAtIdx: index('users_created_at_idx').on(table.createdAt.desc()),
}));

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  slug: text('slug').unique().notNull(),
  body: text('body').notNull(),
  authorId: uuid('author_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

// Relations (for joins/queries — not FK constraints)
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// Type exports
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;

Queries

import { db } from '@/db';
import { users, posts } from '@/db/schema';
import { eq, and, or, ilike, gt, gte, lt, lte, isNull, inArray, desc, asc, count, sql } from 'drizzle-orm';

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

// Filtered
const user = await db.select()
  .from(users)
  .where(eq(users.email, 'alice@example.com'))
  .limit(1);

// Multiple conditions
const results = await db.select()
  .from(users)
  .where(
    and(
      eq(users.role, 'user'),
      gte(users.createdAt, new Date('2024-01-01')),
      ilike(users.name, '%alice%')
    )
  )
  .orderBy(desc(users.createdAt))
  .limit(20)
  .offset(0);

// JOIN (relational query — uses relations)
const usersWithPosts = await db.query.users.findMany({
  with: { posts: { limit: 5, orderBy: [desc(posts.createdAt)] } },
  where: eq(users.isActive, true),
  limit: 20,
});

// JOIN (SQL style)
const result = await db
  .select({ user: users, postCount: count(posts.id) })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id)
  .orderBy(desc(count(posts.id)));

// Aggregates
const [{ total }] = await db.select({ total: count() }).from(users);

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

// INSERT multiple
await db.insert(posts).values([
  { title: 'Post 1', slug: 'post-1', body: '...', authorId: userId },
  { title: 'Post 2', slug: 'post-2', body: '...', authorId: userId },
]);

// Upsert
await db.insert(users)
  .values({ clerkId: 'clerk_123', email: 'a@b.com', name: 'Alice' })
  .onConflictDoUpdate({ target: users.clerkId, set: { name: 'Alice Updated' } });

// UPDATE
const [updated] = await db.update(users)
  .set({ name: 'Bob', updatedAt: new Date() })
  .where(eq(users.id, userId))
  .returning();

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

// Transactions
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();
  await tx.insert(posts).values({ authorId: user.id, ... });
});

Migrations

# 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! },
  verbose: true,
  strict: true,
});

# Commands
npx drizzle-kit generate    # generate migration SQL from schema diff
npx drizzle-kit migrate     # apply pending migrations
npx drizzle-kit push        # push schema directly (dev only, no migration files)
npx drizzle-kit studio      # open Drizzle Studio (GUI)
npx drizzle-kit check       # check for schema issues