Database

For advanced patterns, migrations, and query examples, see reference.md.

Drizzle ORM

Package Structure

packages/database/
├── src/
│   ├── client.ts      # Drizzle client
│   ├── schema/        # Drizzle schemas
│   │   ├── users.ts
│   │   ├── sessions.ts
│   │   ├── accounts.ts
│   │   ├── verifications.ts
│   │   └── index.ts
│   └── index.ts       # Public exports
├── drizzle.config.ts  # Drizzle config
└── package.json

Database Client

// packages/database/src/client.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

export const db = drizzle(process.env.DATABASE_URL!, {
  casing: 'snake_case', // Auto-converts camelCase ↔ snake_case
  schema,
});

Schema Definition

// packages/database/src/schema/users.ts
import { pgTable, text, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: text().primaryKey(),
  name: text().notNull(),
  email: text().notNull().unique(),
  emailVerified: boolean().notNull().default(false),
  image: text(),
  createdAt: timestamp({ mode: 'date' }).notNull().defaultNow(),
  updatedAt: timestamp({ mode: 'date' })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
});

// Type inference
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Relations

import { relations } from 'drizzle-orm';

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

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

Queries

Relational Queries (Recommended)

// Find one with relations
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { sessions: true, posts: true },
});

// Find many with filters
const activePosts = await db.query.posts.findMany({
  where: eq(posts.published, true),
  orderBy: desc(posts.createdAt),
  limit: 10,
});

SQL-like API

// Select
const allUsers = await db.select().from(users);
const names = await db.select({ name: users.name }).from(users);

// With conditions
const admins = await db.select().from(users).where(eq(users.role, 'admin'));

Mutations

// Insert
const [newUser] = await db
  .insert(users)
  .values({ id: crypto.randomUUID(), name: 'John', email: 'john@example.com' })
  .returning();

// Update
const [updated] = await db
  .update(users)
  .set({ name: 'Jane' })
  .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(accounts).values({ userId: user.id, ... });
});

Filter Operators

import {
  eq, // Equal
  ne, // Not equal
  gt, // Greater than
  gte, // Greater than or equal
  lt, // Less than
  lte, // Less than or equal
  like, // LIKE pattern
  ilike, // Case-insensitive LIKE
  inArray, // IN (array)
  isNull, // IS NULL
  and, // AND
  or, // OR
} from 'drizzle-orm';

// Examples
const results = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.authorId, userId),
      or(eq(posts.status, 'published'), eq(posts.status, 'draft')),
    ),
  );

Pagination

const page = 1;
const pageSize = 10;

const posts = await db.query.posts.findMany({
  orderBy: desc(posts.createdAt),
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

Common Mistakes

Delegation

• Server functions: For using db in server functions, see tanstack-start skill
• Auth integration: For auth-related tables, see auth skill
• Code review: After implementing queries, delegate to code-reviewer agent

Topic References

• Database Reference - Complete query patterns, drizzle-zod, migrations