Drizzle ORM — CLAUDE.md

# Drizzle ORM — CLAUDE.md

## Philosophy

Drizzle is "SQL for TypeScript" — if you know SQL, you know Drizzle. The API mirrors SQL syntax rather than abstracting it away.

## Schema Definition

```typescript
// db/schema.ts
import { pgTable, uuid, text, timestamp, integer } from 'drizzle-orm/pg-core';

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

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  authorId: uuid('author_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  content: text('content'),
  views: integer('views').default(0),
});
```

## Queries

```typescript
// Select with join
const result = await db
  .select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .where(gt(posts.views, 100))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({ email: '[email protected]', name: 'Test' })
  .returning();

// Update
await db
  .update(posts)
  .set({ views: sql`${posts.views} + 1` })
  .where(eq(posts.id, postId));
```

## Migrations

```bash
# Generate migration
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Drizzle Studio (GUI)
npx drizzle-kit studio
```

## Relations

```typescript
import { relations } from 'drizzle-orm';

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

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

## Best Practices

- Use `db.transaction()` for atomic operations.
- Prefer `returning()` to avoid extra selects after insert/update.
- Use `sql` template tag for raw SQL expressions.
- Never use `varchar` — use `text` in PostgreSQL.