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

Mistake	Correct Pattern
Missing .returning() on mutate	Always use .returning() to get results
Using any for query results	Use $inferSelect / $inferInsert types
Not using transactions	Wrap related mutations in transaction
Forgetting casing: 'snake_case'	Set in drizzle config for auto-conversion
Hardcoding IDs	Use crypto.randomUUID() for UUIDs
Missing indexes on foreign keys	Add indexes for frequently queried FKs
Not handling null from findFirst	Check for undefined before using result
Using raw SQL for simple queries	Prefer query builder for type safety

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