---
name: drizzle
description: "Use when working with Drizzle ORM and PostgreSQL: table schemas, relations, derived types ($inferSelect, $inferInsert) and Zod schemas from drizzle-orm/zod, queries and transactions, tenant scoping and row-level security, migrations with drizzle-kit (generated by the agent, applied by the user), connections on Bun, Node and Workers (Hyperdrive), and testing against a database."
---

# Drizzle ORM + PostgreSQL

For Postgres design, indexes, locking and RLS details, also load `postgres`. Check the installed
version first (`drizzle-orm` 1.x changed several imports); read its types and docs before writing.

## Hard rules

- **The schema is the one source of truth.** Row types are `typeof table.$inferSelect` /
  `$inferInsert`; Zod schemas come from `drizzle-orm/zod` (`createSelectSchema`,
  `createInsertSchema`, `createUpdateSchema`), then `.pick()`/`.omit()`/`.extend()`. Never
  hand-write a row type or a parallel Zod object.
- **Migrations are generated and immutable.** Change the schema, generate a new migration, never
  edit, rename, reorder or squash an existing one (including the baseline and its snapshot).
- **Agents never apply migrations or connect to a database.** They change the schema and generate
  the migration file when the repo's script does that offline; anything that talks to a database
  (`migrate`, `push`, `studio`, introspection) is printed for the user.
- **Every query that touches tenant data is scoped** to the tenant from the server-side session,
  and the database enforces it too (RLS or composite keys).
- **No raw SQL strings built from input.** Use the query builder or `sql` template with bound
  parameters.

## Schema

```ts
// packages/db/src/schema/projects.ts
import { index, pgTable, text, timestamp, boolean } from 'drizzle-orm/pg-core'
import { organizations } from './organizations'

export const projects = pgTable(
	'project',
	{
		id: text().primaryKey(), // prefixed ULID from the app's id helper
		organizationId: text()
			.notNull()
			.references(() => organizations.id, { onDelete: 'cascade' }),
		name: text().notNull(),
		description: text(), // nullable: null is the one "empty"
		healthData: boolean().notNull().default(false),
		createdAt: timestamp({ withTimezone: true }).notNull().defaultNow()
	},
	(t) => [index().on(t.organizationId)]
)

export type Project = typeof projects.$inferSelect
export type NewProject = typeof projects.$inferInsert
```

- Snake_case columns through the repo's `casing` setting (or explicit names); camelCase in
  TypeScript; one convention for the whole schema.
- `timestamp({ withTimezone: true })`, `text` for strings, integers in minor units for money.
- An index on every foreign key and every column used in a frequent filter.
- `notNull()` by default; nullable only when "no value" is a real state.
- Enums as `pgEnum` or `text` with a check constraint, exported as a const array for types.

## Zod from the schema

```ts
import { createInsertSchema, createSelectSchema } from 'drizzle-orm/zod'
import { projects } from './schema/projects'

export const projectResponse = createSelectSchema(projects).pick({ id: true, name: true, description: true, createdAt: true })
export const createProjectInput = createInsertSchema(projects, {
	name: (s) => s.trim().min(1).max(80)
}).pick({ name: true, description: true })
```

- Server-generated fields (`id`, `organizationId`, timestamps) are never in input schemas.
- The API's response schema is derived here and imported by the client: one type end to end
  (`lean` section 3).

## Queries

```ts
const rows = await db
	.select()
	.from(projects)
	.where(and(eq(projects.organizationId, session.organizationId), eq(projects.id, projectId)))
	.limit(1)
```

- Tenant predicate in every query, from the session; never from the request body.
- Select the columns you need for large tables; paginate with keyset (`where(gt(id, cursor))`
  ordered by the key) and a maximum limit.
- Avoid N+1: one query with a join or `inArray`, or the relational query API with `with`.
- Transactions (`db.transaction(async (tx) => ...)`) for multi-row invariants; keep them short;
  never call external services inside one.
- Upserts with `onConflictDoUpdate` on a unique constraint, not read-then-write.
- Errors: catch unique and foreign-key violations at the boundary and map them to the API's
  error codes (`CONFLICT`), never leak SQL messages.

## Row-level security with Drizzle

- Define policies in the schema (`pgPolicy`) or in migrations; enable RLS on tenant tables.
- The app connects as a runtime role that does **not** own the tables and cannot bypass RLS; set
  the tenant per transaction (`set_config('app.organization_id', $1, true)`) and policies read it
  with `current_setting('app.organization_id', true)`.
- Migrations run as the owner role; the runtime role gets only the grants it needs.
- Test isolation with negative cases (another organization's IDs return nothing).

## Connections

- Bun and Node: a pooled `postgres` (postgres.js) or `pg` client, created once per process.
- Cloudflare Workers: through Hyperdrive, one client per request (`max: 1`), no module-level pool;
  prepared statements according to the pooler's mode.
- TLS to the database always, verifying the server certificate.

## Migrations workflow

1. Change the schema in TypeScript.
2. Generate the migration with the repo's script (offline diff of schema and snapshots).
3. Review the SQL: destructive changes (drop, rename, type change) need the expand-migrate-contract
   path from `postgres`.
4. Commit the migration and snapshot together; never edit them afterwards.
5. Print the apply command for the user (local and remote are theirs to run).

## Testing

- Integration tests run against a real Postgres (local container or a per-run schema) migrated from
  the files; each test creates its own organization.
- In-memory fakes only for code that does not depend on SQL semantics.