database

---
name: database
description: Drizzle ORM + PostgreSQL database layer. Use for db, database, query, schema, table, migrate, sql, postgres, drizzle, model, relation
license: MIT
---

For advanced patterns, migrations, and query examples, see [reference.md](reference.md).

# Drizzle ORM

## Package Structure

```sh
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

```ts
// 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

```ts
// 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

```ts
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)

```ts
// 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

```ts
// 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

```ts
// 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

```ts
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();
  await tx.insert(accounts).values({ userId: user.id, ... });
});
```

## Filter Operators

```ts
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

```ts
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](../tanstack-start/SKILL.md) skill
- **Auth integration**: For auth-related tables, see [auth](../auth/SKILL.md) skill
- **Code review**: After implementing queries, delegate to `code-reviewer` agent

## Topic References

- [Database Reference](reference.md) - Complete query patterns, drizzle-zod, migrations