# Pathrule Pattern: Drizzle ORM (1.0.0)
# ::pathrule:package:drizzle-orm

### [RULE] Schema is the single source of truth  (path: /src/db/schema)
<!-- scope: folder | priority: high | strict -->

Treat the Drizzle TypeScript schema as the only place a table shape is defined.

- Edit column and table definitions in `src/db/schema`, then run `drizzle-kit generate` to produce the SQL migration.
- Never alter columns directly in the database or hand-edit a generated migration's SQL after it is committed; both create a drift that silently breaks generated types.
- Export inferred types with `typeof users.$inferSelect` and `typeof users.$inferInsert` instead of redeclaring row shapes by hand. Hand-written interfaces fall out of sync whenever a column is added or removed.
- Define foreign keys, indexes, and constraints in the schema so `drizzle-kit` can diff and version them alongside column changes.

---

### [RULE] Generate then migrate; never push to a shared database  (path: /drizzle)
<!-- scope: folder | priority: high | advisory -->

Use the `generate` + `migrate` workflow for anything that touches a shared or production database.

- Run `drizzle-kit generate` and commit the resulting file in `drizzle/` alongside the schema change in the same PR.
- Apply migrations with `drizzle-kit migrate` (or the `migrate()` helper at deploy time). Reserve `drizzle-kit push` for local throwaway prototyping only; it overwrites the database schema without recording a migration file.
- Hand-write the reverse SQL for destructive changes (dropping or retyping a column) and test it on a copy of the staging schema first.
- Add a CI check that runs `drizzle-kit check` (or equivalent diff) and fails when the schema has a diff with no corresponding migration file, so drift cannot reach main.

---

### [MEMORY] Drizzle versions and config baseline (2026)  (path: /src/db)

Pin to the current Drizzle line and use the dialect-based config introduced in the 0.40+ releases.

- Runtime is `drizzle-orm` and the CLI is `drizzle-kit`. The stable line as of mid-2026 is 0.4x; a v1.0.0 release is in progress — confirm the exact pinned version in `package.json` before relying on APIs documented only for v1.
- `drizzle.config.ts` uses `dialect` (`"postgresql"`, `"mysql"`, or `"sqlite"`) plus `schema` and `out` paths; the older `driver` key is removed in the new config format.
- Keep one shared `db` client instance per process. Pass the `db` (or a `tx`) into functions as a parameter rather than importing a singleton inside deeply nested modules, so transaction boundaries stay explicit.
- For Postgres, create the client with `drizzle(pool, { schema })` where `pool` is a `node-postgres` Pool or `postgres` (postgres.js) instance. Passing the schema object enables the relational query API.

See /src/db for querying patterns and /src/db for relations v2 setup.

---

### [MEMORY] Relations v2: defineRelations and the relational query API  (path: /src/db)

Relations v2, introduced in Drizzle 0.36+, moves relation declarations out of individual table files and into a centralized definition. Agents frequently write the old per-table `relations()` pattern or skip the relational API entirely and write N+1 loops.

- Define all relations in a dedicated file (for example `src/db/relations.ts`) using `defineRelations` exported from `drizzle-orm`:
  ```ts
  import { defineRelations } from 'drizzle-orm';
  import * as schema from './schema';

  export const relations = defineRelations(schema, (r) => ({
    users: {
      posts: r.many.posts({ from: schema.users.id, to: schema.posts.authorId }),
    },
    posts: {
      author: r.one.users({ from: schema.posts.authorId, to: schema.users.id }),
    },
  }));
  ```
- Pass the relations object to `drizzle()` alongside the schema: `const db = drizzle(pool, { schema, relations })`.
- Query nested data with `db.query.users.findMany({ with: { posts: true } })`. This issues a single SQL query (a lateral join), not a loop of per-row queries. The old per-table `relations()` helper is still supported for compatibility but new code should use `defineRelations`.
- For optional or filtered eager loads, pass `{ with: { posts: { where: eq(posts.published, true) } } }` rather than filtering in application code after loading all rows.

---

### [MEMORY] Querying patterns: transactions and prepared statements  (path: /src/db)

Prefer Drizzle's built-in abstractions for transactions and repeated queries over hand-rolled SQL.

- Wrap multi-statement writes in `db.transaction(async (tx) => { ... })` and use only `tx` inside the callback. Throwing (or returning a rejected promise) rolls everything back automatically. Nested `db.transaction()` calls on the same `tx` become savepoints.
- For hot paths, build a prepared statement once with `.prepare('name')` and bind values via `sql.placeholder('name')`, then call `.execute({ name: value })`. Prepared statements send only bind parameters on repeated calls and let Postgres skip re-planning.
- Reach for the `sql` template tag for expressions Drizzle has no query builder for. Always pass user input as parameters in the template (`sql`SELECT * FROM users WHERE id = ${userId}``), never as interpolated string concatenation.
- Avoid `db.execute(sql.raw(...))` with unsanitized strings; it bypasses Drizzle's parameter binding and introduces SQL injection.

---

### [SKILL] drizzle-orm-review  (path: /)

---
name: drizzle-orm-review
description: Review a Drizzle ORM change before merge. Use on schema modifications, drizzle-kit migrations, relation definitions, query patterns, and transaction logic.
---

# Drizzle ORM review

## Schema and migrations
- [ ] Schema change lives in `src/db/schema` and the database was not edited directly.
- [ ] A `drizzle-kit generate` migration is committed in `drizzle/` for every schema diff.
- [ ] `drizzle-kit push` is not used against any shared or production database.
- [ ] Destructive changes (drop or retype a column) have tested reverse SQL and a staging plan.
- [ ] Row types come from `$inferSelect` / `$inferInsert`, not hand-written interfaces.

## Config and client
- [ ] `drizzle.config.ts` sets `dialect`, `schema`, and `out`; no legacy `driver` key.
- [ ] One shared `db` client instance per process; the `db` or `tx` is passed into functions, not re-imported from a singleton.

## Relations and queries
- [ ] Relations use `defineRelations` in a centralized file and are passed to `drizzle(pool, { schema, relations })`; no per-table `relations()` on new code.
- [ ] Nested reads use `db.query.<table>.findMany({ with: { ... } })`, not per-row loops.
- [ ] Multi-statement writes run inside `db.transaction` using only `tx` internally.
- [ ] Hot-path queries use `.prepare()` with `sql.placeholder`; user input stays parameterized and `sql.raw` is not used with unsanitized input.