Drizzle Orm

Drizzle ORM — schema as code, type-safe queries, drizzle-kit migrations

Drizzle is the SQL-transparent TypeScript ORM: you declare tables in plain .ts, the query builder emits SQL you can read, and there is no codegen client to regenerate. It runs in edge/serverless runtimes. You own the schema, the queries, and the drizzle-kit workflow here — the database engine, the ORM-agnostic migration strategy, and Prisma live in sibling skills.

Decide first

Three decisions before you write a line. Each branches, so use the table.

Driver entrypoints (pick one; full matrix + edge caveats in references/relations-and-drivers.md): drizzle-orm/node-postgres, /postgres-js, /neon-http, /libsql (Turso), /planetscale-serverless, /mysql2, /better-sqlite3, /bun-sqlite.

Install & config

npm i drizzle-orm
npm i -D drizzle-kit
npm i postgres            # the driver client — here postgres-js; swap per your DB

drizzle.config.ts — the four keys drizzle-kit reads. The dialect here MUST match your table helper (pgTable ⇒ 'postgresql'), or migrations target the wrong SQL.

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',          // 'postgresql' | 'mysql' | 'sqlite' | 'turso'
  schema: './src/db/schema.ts',   // where your tables live
  out: './drizzle',               // emitted migration SQL
  dbCredentials: { url: process.env.DATABASE_URL! },
});

package.json scripts — name them once so you never re-type the CLI:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Schema as code

A table is plain TypeScript. Declare columns with the helpers, then derive your row types from the table — never hand-write a parallel interface, it will drift.

import { pgTable, integer, text, timestamp, pgEnum, index } from 'drizzle-orm/pg-core';

export const roleEnum = pgEnum('role', ['admin', 'member']);

export const users = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  email: text().notNull().unique(),
  role: roleEnum().notNull().default('member'),
  createdAt: timestamp().notNull().defaultNow(),
}, (t) => [index('users_email_idx').on(t.email)]);

export const posts = pgTable('posts', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  authorId: integer().notNull().references(() => users.id),  // FK
  title: text().notNull(),
});

export type User = typeof users.$inferSelect;       // row as read
export type NewUser = typeof users.$inferInsert;    // row as inserted

• One schema file per domain, and export every table. drizzle-kit and the relational query builder only see what you pass them — an unexported table is invisible to migrations.
• Derive types with $inferSelect / $inferInsert. They stay in sync with the column defs for free; a separate interface is one more thing to forget to update.

Connect

drizzle() takes your driver client. Pass { schema } (0.45 / v1-compat) or { relations } (v2, the 1.0 rc line) — without one of them, db.query.* is undefined and relational reads silently break.

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });   // schema enables db.query.*

Query

Filter, join, and shape in SQL — not in JavaScript after the fact.

import { eq, and, inArray, desc } from 'drizzle-orm';

// select a column subset, not the whole row
const admins = await db.select({ id: users.id, email: users.email })
  .from(users)
  .where(and(eq(users.role, 'admin'), inArray(users.id, [1, 2, 3])))
  .orderBy(desc(users.createdAt));

// insert + get the row back
const [created] = await db.insert(users)
  .values({ email: 'a@b.com', role: 'member' })
  .returning();

// update / delete take the same operators
await db.update(users).set({ role: 'admin' }).where(eq(users.id, created.id));
await db.delete(posts).where(eq(posts.authorId, created.id));

// transaction — all or nothing
await db.transaction(async (tx) => {
  const [u] = await tx.insert(users).values({ email: 'c@d.com' }).returning();
  await tx.insert(posts).values({ authorId: u.id, title: 'hello' });
});

Relational read (nested objects) needs { schema }/{ relations } on the connection AND a with:

const withPosts = await db.query.users.findMany({
  where: (u, { eq }) => eq(u.role, 'admin'),
  with: { posts: true },          // <- the relation; omit it and you get no posts
});

For conditional builders use $dynamic(); for hot paths prepare() once and reuse.

const q = db.select().from(users).$dynamic();
const rows = await (role ? q.where(eq(users.role, role)) : q);

Bad → Good — the classic foot-gun:

// Bad: pull every row, filter in JS — full table scan, no index, ships rows you discard
const all = await db.query.users.findMany();
const admins = all.filter((u) => u.role === 'admin');

// Good: filter in the query — the DB uses the index and returns only what you need
const admins = await db.query.users.findMany({ where: (u, { eq }) => eq(u.role, 'admin') });

Relations

Relations let with hydrate nested objects. Two flavors — do not mix them in one project.

Decision line: use the relational query (db.query + with) for nested reads of related records; drop to a manual .leftJoin() for flat projections, aggregates, or GROUP BY — relational queries return trees, joins return rows. Full v1↔v2 migration walkthrough in references/relations-and-drivers.md.

Migrations

npm run db:generate   # diff schema.ts vs ./drizzle, emit a new .sql migration
# READ the emitted SQL in ./drizzle — confirm it does what you intended
npm run db:migrate    # apply pending migrations

• generate → review the SQL → migrate is the production path. The emitted SQL is the contract; eyeball it before applying so a rename does not silently become a drop+add.
• push is for throwaway prototyping only. It applies schema.ts straight to the DB with no migration file — fine for a scratch branch, never for an environment you cannot recreate.
• Never hand-edit an applied migration; write a new one. The migration journal tracks what ran; editing history desyncs every other environment.
• Recent drizzle-kit adds migration-conflict / commutativity detection (it checks whether open migration branches merge safely, with refined index/table footprint checks); --ignore-conflicts preserves open leaf parents when you intend to keep branches. node:sqlite is auto-detected for migrate and Studio.

Inspect data with npm run db:studio (Drizzle Studio GUI); pull an existing DB into a schema with drizzle-kit pull.

Anti-patterns

Verify

Run the static lint on any generated Drizzle file (no DB connection):

scripts/verify.sh src/db/schema.ts drizzle.config.ts

It checks a drizzle() call exists, the table helper matches the configured dialect, the config carries dialect/schema/out, that any db.query usage has { schema }/{ relations } passed to drizzle(), and bans Prisma-isms (schema.prisma, PrismaClient, prisma generate) that mean the wrong ORM leaked in.