Drizzle ORM Production Operation Guide: Generating Types from the Schema and Hardening Migrations, Transactions, and Edge in a Type-Safe Way

"I want to make data access type-safe" — as a requirement it's one line. But when you actually try to put it into production, the things to decide explode at once. Where do you declare the schema? Write it SQL-like, or relationally? How do you generate, review, and apply migrations? Where do you draw the transaction boundary? How do you prevent SQL injection? How do you handle connections on Edge/serverless?

This article is an implementation guide for operating Drizzle ORM at production quality. Drizzle is designed as "a thin TypeScript SQL layer where types flow to every corner," and it draws a line from other tools in trying to reconcile the convenience of an ORM with the transparency of SQL. On the foundation of facts faithful to the official documentation, I'll layer the design philosophy I always thoroughly apply — "push runtime correctness into types as much as possible."

The rule of this article: The API specs, commands, and code are based on the Drizzle official documentation (as of June 2026). Drizzle is actively evolving, and API and helper names can be revised, so always confirm the latest spec at the official documentation before going to production. DB connection information (connection strings, credentials) is handled on the premise of environment variables (hardcoding strictly forbidden).

0. Mental model: harden the ORM boundary with "types" too

Before getting into the main topic, let me share the single axis that runs through this article.

In developing a subscription-billing platform with thorough type safety, I fully banned as / any / enum / non-null assertions. Instead, I combined union types + satisfies + NeverError(value: never) to create a state where "if there's a missed branch, compilation fails," and validated all external-input boundaries with Zod (down to details like handling payment amounts in integer minor units to avoid rounding errors, protecting them with types).

This philosophy — "knock down runtime invalidity at compile time as much as possible" — applies as-is to the ORM boundary (DB access) too. Rather, the database is the boundary most prone to type collapse in the app. If the result of SELECT * flows through as any, then however carefully you write the downstream logic, it's a castle in the air.

Drizzle's mental model is simple.

Declare the schema in TypeScript → types are auto-generated with $inferSelect / $inferInsert → types flow all the way to the query result = a "type-safe thin layer" of SQL.

The schema becomes the single source of truth, and from it both types and migrations are derived. This is Drizzle's core, and the reason it meshes straight with my design principles.

1. Schema-as-code: declare with pgTable

Drizzle supports PostgreSQL / MySQL / SQLite / SingleStore per dialect. This article uses PostgreSQL (drizzle-orm/pg-core) as the subject.

1.1 First, one table

import { pgTable, integer, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar().notNull(),
  email: varchar().notNull().unique(),
});

What's important here is that this is neither a migration file nor a type definition, but just TypeScript. From the single declaration pgTable("users", {...}), all of the table definition, types, and migrations are derived. .primaryKey() / .notNull() / .unique() are constraints chained directly onto the column, and generatedAlwaysAsIdentity() represents PostgreSQL's GENERATED ALWAYS AS IDENTITY (the recommended auto-numbering).

1.2 Frequently used column helpers

What you often write in practice is a form like the following, including created/updated timestamps.

import { pgTable, integer, varchar, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const posts = pgTable("posts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
  title: varchar({ length: 200 }).notNull(),
  body: text().notNull(),
  published: boolean().default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Declare a foreign key with .references(() => users.id). Lazily evaluating with a callback is to resolve mutual references (cycles) between tables. .default(false) / .defaultNow() are DB-side default values, and combining them with .notNull() creates a state where "even if the app doesn't pass a value, NOT NULL is guaranteed at the DB level."

A design tip (SRP): consolidate the schema definition in db/schema.ts (or split per table), and physically separate it from the app's logic. The schema should hold only the one responsibility of "the shape of data," and don't mix business logic in here.

2. Type inference: $inferSelect / $inferInsert are the leads

This is Drizzle's true value. From the schema, you can extract the type of the SELECT result and the type of the INSERT input with zero additional description.

// users テーブルの SELECT 結果の型（id は number、email は string、...）
type User = typeof users.$inferSelect;

// INSERT 入力の型（id は省略可、generatedAlwaysAsIdentity なので渡せない）
type NewUser = typeof users.$inferInsert;

These two types are decisively important because "the shape the DB returns" and "the shape the DB can receive" are different things. id is generatedAlwaysAsIdentity() so it can't be passed at INSERT. createdAt is defaultNow() so it can be omitted. $inferInsert accurately reflects these constraints at the type level. With hand-written DTO types, a human keeps syncing this difference, and it will definitely drift somewhere (a textbook DRY violation).

// この差分が型で表現される
const ok: NewUser = { name: "Hinata", email: "h@example.com" }; // ✅ id は不要
// const ng: NewUser = { id: 1, name: "x", email: "y" };        // ❌ 型エラー

Translated into my design principles, $inferSelect / $inferInsert are the very mechanism of "machine-generating types from the schema, the single source of truth." The instant you interpose a manual cast like as User here, type safety collapses. Infer the boundary's types, and don't name them yourself — this is the first discipline of using Drizzle.

3. The two query APIs: SQL-like vs. relational

Drizzle has two query APIs of differing natures, and confusing them makes the design waver. The official docs state clearly that "Drizzle always outputs exactly one SQL query," and both APIs are designed so the N+1 problem doesn't implicitly occur.

3.1 Connection (drizzle())

First, the connection. Pass a connection string (an environment variable) to drizzle().

import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

// DATABASE_URL は環境変数。ハードコード厳禁。
export const db = drizzle(process.env.DATABASE_URL!, { schema });

Pass { schema } and the relational queries (db.query) described later become usable.

3.2 SQL-like: db.select().from().where()

It has the writing feel of copying SQL straight into TypeScript. You can describe JOINs, aggregations, and complex conditions in SQL's structure as-is.

import { eq } from "drizzle-orm";

// SELECT ... FROM users WHERE id = 1
const rows = await db.select().from(users).where(eq(users.id, 1));
// rows の型は User[]（$inferSelect 由来）— 自分で型注釈は書かない

// JOIN もSQLの形のまま
const joined = await db
  .select()
  .from(countries)
  .leftJoin(cities, eq(cities.countryId, countries.id))
  .where(eq(countries.id, 10));

Condition helpers like eq / and / or / lt / gt are imported from drizzle-orm. Because they generate parameterized SQL rather than string concatenation, all values are passed via placeholders, and SQL injection structurally doesn't occur (detailed in Section 7).

3.3 Relational: db.query.<table>.findMany()

When you want to fetch nested related data "as a tree," the relational query API becomes overwhelmingly more readable. First, declare the relations with relations().

import { relations } from "drizzle-orm";

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

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

one() is a single reference (post → author), and many() is a multiple reference (user → posts). Make the join key explicit with fields / references. With this, db.query becomes usable.

// ユーザーと、その投稿群を「ネストしたまま」取得
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

// 単一取得・部分カラム・絞り込み・並び順を組み合わせる
const post = await db.query.posts.findFirst({
  columns: { id: true, title: true },     // 必要なカラムだけ
  where: (posts, { eq }) => eq(posts.id, 1),
  with: {
    author: { columns: { name: true } },  // 関連先も部分選択
  },
});

// ネストの絞り込み・並び順・件数制限も宣言的に
const feed = await db.query.posts.findMany({
  limit: 5,
  offset: 0,
  orderBy: (posts, { desc }) => [desc(posts.createdAt)],
  with: {
    author: true,
  },
});

The return value of findMany / findFirst too changes type accurately according to the with and columns specifications. Write columns: { id: true, title: true } and body disappears from the return type. This is a behavior that guarantees with types the runtime fact that "you can only access the columns you fetched."

3.4 Which to use (decision table)

In practice you use both together. "Parent-child data shown on screen" short with db.query, "aggregation reports and complex search" using SQL's expressiveness with db.select. Not trying to unify into one API conversely keeps readability (KISS: tools each in their place).

4. Migrations: "generate → review → apply" with drizzle-kit

Once you change the schema, you need to reflect it in the DB. Drizzle handles this with a separate package called drizzle-kit and recommends a workflow that leaves migrations as artifacts.

4.1 The config file

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema.ts",
  out: "./drizzle",                       // 生成物の出力先
  dbCredentials: {
    url: process.env.DATABASE_URL!,        // 環境変数から（ハードコード厳禁）
  },
});

4.2 generate → migrate (the recommended flow)

# 1. スキーマと既存マイグレーションを比較し、差分のSQLを生成する
#    out/ に timestamp 付きフォルダ（migration.sql + snapshot.json）が出来る
npx drizzle-kit generate

# 2. 未適用のマイグレーションをDBに適用する
#    適用履歴はDB側のテーブルで管理され、二重適用されない
npx drizzle-kit migrate

The crux of this flow is that a human can review the SQL generate produces before applying it. The migration.sql under ./drizzle/ is an artifact committed to Git, and snapshot.json tracks the schema's history. Put migrations on the subject of code review — this is an absolute condition in production operation (described later).

4.3 push (prototype only)

# スキーマを直接DBへ反映（SQLファイルを生成しない）
npx drizzle-kit push

push doesn't leave an SQL file; it compares the current state of the schema and DB and applies directly. It's fast and convenient for local prototyping, but don't use it in production. Because it leaves no history, there's a risk that destructive changes (column deletion = data loss) run unaudited. In production, always generate → migrate, and draw the line that push is for throwaway verification.

4.4 Migration at app startup

For zero-downtime deploys or serverless, there's also the hand of applying at startup with the migrate() function.

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";

const db = drizzle(process.env.DATABASE_URL!);

await migrate(db, { migrationsFolder: "./drizzle" });

The official docs position this for "a zero-downtime deploy of a monolithic app, or a serverless environment that runs the migration only once (via a custom resource)." But because in a configuration where multiple instances start simultaneously, startup migration can conflict, there are also many scenes where running it just once in the CI/CD deploy procedure is safer. This is architecture-dependent, so choose according to your requirements.

4.5 Inspecting destructive changes (operational wisdom)

This isn't a Drizzle-specific feature but operational discipline. The generated migration.sql can contain DROP COLUMN / DROP TABLE / ALTER COLUMN ... NOT NULL (which fails if there are existing rows) / lock-incurring ALTER, and the like. In PostgreSQL, it's robust to build a tool that inspects such "dangerous migrations" (squawk etc.) into CI and detect destructive changes mechanically in addition to human review.

The principle: a migration is "code that gets executed." Just as you don't blindly trust --fix on app code, always read the generated SQL. If the diff has become an unreadable amount, that's a sign you've crammed too many changes into one migration (SRP).

5. Transactions: confine the boundary to a single function

An operation that should be "all writes succeed or all fail," like "subtract from A and add to B," is wrapped in a transaction.

import { sql, eq } from "drizzle-orm";

await db.transaction(async (tx) => {
  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - 100.00` })
    .where(eq(users.name, "Dan"));

  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} + 100.00` })
    .where(eq(users.name, "Andrew"));
});

If the callback finishes normally it's COMMIT, and if it throws an exception it's automatically ROLLBACK. Inside a transaction, use tx, not db is the iron rule. Forget tx and write with db, and that operation runs outside the transaction, breaking atomicity.

5.1 Conditional rollback and return value

// 残高不足なら明示的にロールバック
await db.transaction(async (tx) => {
  const [account] = await tx
    .select({ balance: accounts.balance })
    .from(accounts)
    .where(eq(users.name, "Dan"));

  if (account.balance < 100) {
    tx.rollback(); // 以降を中断してロールバック
  }
  // ... 残りの処理
});

// トランザクションの結果を値として返せる（型も通る）
const newBalance: number = await db.transaction(async (tx) => {
  await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - 100.00` })
    .where(eq(users.name, "Dan"));

  const [account] = await tx
    .select({ balance: accounts.balance })
    .from(accounts)
    .where(eq(users.name, "Dan"));

  return account.balance; // newBalance: number に推論される
});

Nested transactions (savepoints) are also supported.

await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: sql`${accounts.balance} - 100.00` })
    .where(eq(users.name, "Dan"));

  await tx.transaction(async (tx2) => {
    await tx2.update(users).set({ name: "Mr. Dan" }).where(eq(users.name, "Dan"));
  });
});

Each dialect can also set the isolation level and access mode. How you draw the boundary is the design crux. Keep transactions short, and don't await an external API call (a payment gateway, etc.) inside it — hold a lock for a long time and get dragged by external latency, and it's a breeding ground for connection exhaustion and deadlock. Separate it: external calls outside the transaction, only the DB's commit processing inside (SRP).

6. Idempotent upsert and prepared statements

6.1 Idempotent upsert (make it resilient to double execution)

The property that "even if the same request comes twice, the result amounts to only once" = idempotency, is essential for webhook reception and retry-premised jobs. In Drizzle, express it with onConflictDoUpdate / onConflictDoNothing.

import { sql } from "drizzle-orm";

// email が衝突したら name を上書き（INSERT ... ON CONFLICT DO UPDATE）
await db
  .insert(users)
  .values({ name: "Hinata", email: "h@example.com" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name` },
  });

// 衝突したら何もしない（重複登録を黙って弾く）
await db
  .insert(users)
  .values({ name: "Hinata", email: "h@example.com" })
  .onConflictDoNothing();

onConflictDoNothing pays off in cases like "even if the same event arrives twice on a webhook resend, don't create a second row." Idempotency is the premise of retry design, and without it, "resend because it might have failed" instantly turns into a double registration (reliability).

6.2 Prepared statement: speed up the hot path

If you hit the same-shaped query at high frequency, you can fix the SQL compilation to once with a prepared statement.

import { sql, eq } from "drizzle-orm";

// プレースホルダで値を後から差し込む
const userById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder("id")))
  .prepare("user_by_id");

// 実行時に値をバインド（SQLの再パースが起きない）
const a = await userById.execute({ id: 10 });
const b = await userById.execute({ id: 20 });

The official docs explain "with a prepared statement, Drizzle does the SQL concatenation only once, and afterward the driver can reuse the precompiled binary SQL. For large SQL there's an immense performance effect." In SQLite, use .all() / .get() instead of .execute().

A YAGNI caution: put a prepared statement only into a hot path you've measured to be a bottleneck. Preemptively making all queries prepared is a too-early optimization that only lowers readability. First write it straightforwardly, profile, then make it take effect.

7. Security: structurally prevent injection with parameterization

Drizzle's query builder (eq, where, the conditions of db.query, etc.) passes all values as placeholders. Because it doesn't assemble SQL with string concatenation, there's no room for user input to be interpreted as SQL syntax in the first place. This is injection countermeasure at the design level.

The problem is when you want to write raw SQL. Drizzle provides the sql template tag, and use this correctly and raw SQL is safe too.

import { sql } from "drizzle-orm";

// ❌ 絶対にやってはいけない：文字列連結（インジェクション）
// const bad = sql.raw(`SELECT * FROM users WHERE name = '${userInput}'`);

// ✅ 正しい：s`` テンプレートに値を埋めると、自動でパラメータ化される
const safe = await db.execute(
  sql`SELECT * FROM users WHERE name = ${userInput}`,
);

A value put into the ${} of sql\... ${userInput}`is **not directly embedded into the string but becomes a bind parameter.** This is the body of safety.sql.raw()` embeds the value as-is, so never use it on user input (limit it to trusted constants like table names).

Here too my principle takes effect. Validate external input at the boundary. Before passing userInput to Drizzle, validate "is it the expected shape" with Zod etc. and narrow the type. Drizzle's parameterization guarantees "it's not injected," but it doesn't guarantee "is the value semantically valid." Types and validation are a two-tier setup.

import { z } from "zod";

const SearchInput = z.object({ name: z.string().min(1).max(100) });

// 境界で検証 → 検証済みの値だけを Drizzle に渡す
const { name } = SearchInput.parse(req.body);
const rows = await db.select().from(users).where(eq(users.name, name));

8. Edge / serverless compatibility: how to handle connections

Drizzle itself is a zero-dependency, ~31kb lightweight library, designed with Edge/serverless in mind. But the type story and the connection story are separate, and what trips you up in production is almost always the latter.

On serverless (Vercel Functions / Lambda, etc.) or an Edge runtime, because function instances line up in large numbers, naively using the conventional "1 connection = 1 TCP connection" instantly eats up the DB's connection limit. The countermeasure changes by the driver you use.

On an Edge runtime, Node's TCP sockets can't be used, so choose an HTTP-based driver (e.g. Neon's HTTP driver). Because Drizzle is driver-independent, just by swapping the client passed to drizzle(...), the way you write queries (db.select / db.query) doesn't change at all. This is a good example of ETC (Easy To Change), being able to separate "the description of data access" from "the physics of the connection."

// Edge: HTTP ドライバに差し替えても、db のAPIは同じ
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
// → 以降の db.select().from(...) / db.query.users.findMany(...) はそのまま動く

The principle (ETC): the connection strategy changes per environment, but the domain's queries must not change. Assemble db in one place, and have the app side depend only on that instance. With this, "Node → Edge migration" is done with just a driver swap.

9. Testability: protect not only types but behavior

Types passing is only half of "correctness." The other half — whether the query returns the intended data — is protected by tests. Because Drizzle is a set of plain functions, tests are straightforward.

• Tests against a real DB are the most reliable. In CI, spin up a throwaway PostgreSQL with Testcontainers etc., apply migrations, then verify. The biggest benefit is being able to verify with the same SQL dialect as production.
• For unit tests you want lightweight, there's also the hand of running against SQLite (better-sqlite3's in-memory). But because of dialect differences (PostgreSQL-specific types / functions) there's drift, so make the last line of defense a test against the same DB as production.
• Carve out the data-access layer into repository functions and fix arguments and return values with $inferSelect / $inferInsert-derived types, and the call site is easy to mock and the layer's responsibility is clear too (SRP / ETC).

// データアクセスを関数として切り出す（テストしやすい・差し替えやすい）
export async function findUserById(id: number): Promise<User | undefined> {
  const [user] = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return user; // 戻り値は $inferSelect 由来の型
}

10. Drizzle vs. Prisma: an honest usage distinction

Just because this is an article pushing Drizzle, I have no intention of saying "Prisma is outdated." The two just differ in philosophy, and their suited scenes differ.

Drizzle suits: a team that understands SQL, wanting to fully use SQL's expressiveness (complex JOINs, window functions), wanting to keep the bundle light, running on Edge/serverless, wanting to eliminate the generation step.

Prisma suits: many members unfamiliar with SQL, valuing a declarative, kind API and GUI (Studio), prioritizing the thickness and track record of the ecosystem, mostly CRUD where SQL's deep expressiveness isn't needed.

To say my own preference honestly, from the values of "types should come from inference, SQL should show through unhidden, and the fewer generation steps the better," I'm strongly drawn to Drizzle. But that's a story under the premise of my team and projects. If members unfamiliar with SQL are the mainstay, there are normally scenes where Prisma's kindness wins on productivity. Tools are chosen by the premise conditions, not by faith.

11. Summary: a cheat sheet

Finally, a quick-reference table for when you're unsure.

• Schema: consolidate pgTable("name", { ... }) in db/schema.ts. Chain constraints onto the column.
• Types: derive with typeof table.$inferSelect / $inferInsert. Hand-written DTOs and as are forbidden.
• Query choice: nested fetching with db.query.*.findMany({ with }), aggregation / complex search with db.select().from(). Both 1 query.
• Migrations: in production, drizzle-kit generate (review the SQL) → migrate. push is for throwaway verification only. Inspect destructive changes in CI.
• Transactions: db.transaction(async (tx) => ...). Always tx inside. Keep external API calls outside the boundary.
• Idempotency: make it retry-resilient with onConflictDoUpdate / onConflictDoNothing.
• Performance: after measuring, prepare() + sql.placeholder() on the hot path. Don't preemptively optimize.
• Security: all values are parameterized and not injected. Raw SQL is sql\${value}`, and don't use sql.raw()` on user input. Validate input at the boundary with Zod.
• Edge: swap the driver. Don't change the way you write queries (ETC).

Data access looks like "a one-line requirement," but it's the work of simultaneously designing type safety, migration safety, transaction boundaries, connection strategy, and injection countermeasures. Drizzle, with the consistent philosophy of "make the schema the truth, infer types from it, and thinly wrap SQL without hiding it," cleanly supports this design.

I always develop with the discipline of excluding as / any / enum, creating a state where "a missed branch fails compilation" with satisfies and exhaustiveness checks (NeverError), and boundary-validating external input with Zod — pushing runtime correctness into types as much as possible (the track record of a thoroughly type-safe subscription learning platform is one example. ※This doesn't mean that project adopted Drizzle; it's a project assembled with the same philosophy of "protecting runtime with types"). Drizzle is a well-matched tool that extends that philosophy all the way to the DB boundary.

"How do I design my company's data access to be type-safe, migration-safe, and in a form that withstands production operation?" — from that selection through implementation and operation, I accompany you fast and safely in a one-person × generative AI (Claude Code) structure. Even from the technology-selection stage, feel free to consult me.

Reference (official documentation)

• Drizzle ORM — Overview — the two query APIs, 1-query output, supported DBs, zero dependencies
• Schema declaration (SQL schema) — pgTable, column helpers, $inferSelect / $inferInsert
• Relational Queries (db.query) — relations / one / many, findMany / findFirst, with / columns / where
• Migrations — drizzle-kit generate / migrate / push, the migrate() function
• Transactions — db.transaction, tx.rollback, nesting (savepoints)
• Prepared statements / performance — prepare() / sql.placeholder() / execute()