Drizzle ORM: Mastering TypeScript Database Schemas from Data

JSON to Drizzle ORM Schema: TypeScript-First Database Modeling for Edge Runtimes

Drizzle ORM solves a specific problem: you want the full type safety of a TypeScript schema definition but you don't want a runtime that adds megabytes of overhead and makes Cloudflare Workers or Vercel Edge Functions impractical. Drizzle's schema layer is pure TypeScript objects — no code generation step, no separate .prisma file, no CLI daemon required at runtime. The schema you write IS the migration source, the TypeScript type source, and the query builder's type information — all in one file.

Live Example: Multi-Dialect Schema from JSON

// Input JSON
{
  "id": 1,
  "name": "Project Alpha",
  "ownerId": "usr_9921",
  "metadata": {
    "priority": "high",
    "deadline": "2024-12-31",
    "labels": ["web", "internal"]
  },
  "isPublic": false,
  "createdAt": "2024-01-15T08:30:00Z"
}

// Generated Drizzle Schema (PostgreSQL)
import {
  pgTable, serial, text, boolean, jsonb,
  timestamp, index, uniqueIndex,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

type ProjectMetadata = {
  priority: 'low' | 'medium' | 'high';
  deadline: string;
  labels:   string[];
};

export const projects = pgTable('projects', {
  id:        serial('id').primaryKey(),
  name:      text('name').notNull(),
  ownerId:   text('owner_id').notNull(),
  metadata:  jsonb('metadata').$type<ProjectMetadata>(),
  isPublic:  boolean('is_public').notNull().default(false),
  createdAt: timestamp('created_at', { withTimezone: true })
               .notNull()
               .default(sql`now()`),
}, (table) => ({
  ownerIdx:     index('projects_owner_idx').on(table.ownerId),
  nameOwnerIdx: uniqueIndex('projects_name_owner_uidx').on(table.name, table.ownerId),
}));

// Inferred TypeScript types — no separate interface needed
export type Project       = typeof projects.$inferSelect;
export type NewProject    = typeof projects.$inferInsert;
export type ProjectUpdate = Partial<typeof projects.$inferInsert>;

The .$type<ProjectMetadata>() call annotates the JSONB column with a TypeScript type — queries that return metadata will be typed, and inserts that include metadata will be type-checked against ProjectMetadata. No runtime overhead — this is purely a TypeScript-level annotation.

Schema-Driven Type Inference

Drizzle's $inferSelect and $inferInsert extract TypeScript types from the schema definition automatically:

export const users = pgTable('users', {
  id:        text('id').primaryKey(),
  email:     text('email').notNull().unique(),
  name:      text('name').notNull(),
  role:      text('role', { enum: ['admin', 'user', 'guest'] }).notNull().default('user'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  deletedAt: timestamp('deleted_at'),
});

type User    = typeof users.$inferSelect;
// { id: string; email: string; name: string; role: 'admin'|'user'|'guest'; createdAt: Date; deletedAt: Date | null }

type NewUser = typeof users.$inferInsert;
// { id?: string; email: string; name: string; role?: 'admin'|'user'|'guest'; createdAt?: Date; deletedAt?: Date | null }
// Note: fields with defaults are optional in NewUser, required in User

This means your TypeScript types are always in sync with your schema. There's no prisma generate to run, no generated client to check in — the type is derived at compile time from the schema file you already have.

Relations: Type-Safe JOINs

import { relations } from 'drizzle-orm';
import { pgTable, text, serial, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id:    text('id').primaryKey(),
  email: text('email').notNull(),
});

export const posts = pgTable('posts', {
  id:       serial('id').primaryKey(),
  authorId: text('author_id').notNull().references(() => users.id),
  title:    text('title').notNull(),
  body:     text('body').notNull(),
});

// Define relation metadata for Drizzle's query API
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

// Type-safe relational query
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      columns: { title: true, id: true },
      where: (posts, { eq }) => eq(posts.status, 'published'),
    }
  }
});
// usersWithPosts[0].posts — typed Post[] with only { title, id }

Drizzle vs. Prisma: When to Choose Each

Both are TypeScript-first ORMs with similar safety goals. The differences matter depending on your deployment target:

• Runtime size: Drizzle's runtime is ~33kb; Prisma's query engine is a native binary (~7MB on Linux). This makes Drizzle the only practical choice for Cloudflare Workers, Vercel Edge Functions, or any environment with a size budget.
• Schema language: Prisma uses a separate .prisma file with its own syntax. Drizzle uses TypeScript — your schema is just TypeScript objects, no separate language to learn.
• Migrations: Prisma's migration tooling is more mature with a richer diff/deploy workflow. Drizzle Kit generates SQL migrations from schema diffs but requires more manual management of migration history.
• Query API: Drizzle's SQL-like chainable API stays close to the underlying SQL. Prisma's API is higher-level, which is more ergonomic for simple CRUD but less flexible for complex queries (window functions, lateral joins, CTEs).

drizzle-kit Migrations

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

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
});

# Generate migration from schema changes
npx drizzle-kit generate

# Push changes directly to database (dev only)
npx drizzle-kit push

# Review pending migrations
npx drizzle-kit migrate

drizzle-kit push is for development only — it applies schema changes directly without a migration file. In production, always generate a migration file, review it, and apply it with drizzle-kit migrate or your deployment pipeline.

SQLite Dialect for Local and Edge-Embedded Databases

// Same schema, different import — works for local dev, Bun/SQLite, Turso
import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';

export const products = sqliteTable('products', {
  id:       integer('id').primaryKey({ autoIncrement: true }),
  sku:      text('sku').notNull().unique(),
  name:     text('name').notNull(),
  price:    real('price').notNull(),
  metadata: text('metadata', { mode: 'json' }).$type<Record<string, unknown>>(),
});
// SQLite has no native JSON type; Drizzle serializes to TEXT automatically

Best Practices for Production

• Export inferred types alongside your schema: Add export type X = typeof table.$inferSelect and export type NewX = typeof table.$inferInsert for every table. Import these types across your app instead of repeating the inference expression.
• Use withTimezone: true for all timestamps in PostgreSQL: timestamp('col', { withTimezone: true }) maps to TIMESTAMPTZ, which stores UTC and converts on read. Without it, you get TIMESTAMP which has no timezone information.
• Separate schema from database connection: Keep schema definitions in schema.ts and the Drizzle db instance in db.ts. Schema files should have no I/O; they're pure type definitions.
• Review generated migrations before running them: drizzle-kit generate produces SQL files — read them. Column renames are sometimes interpreted as DROP + ADD (data loss) rather than RENAME. Add a --custom migration to fix this before running.

FAQ

Q: Does Drizzle work with Cloudflare D1 (SQLite at the edge)?
A: Yes. Drizzle has a dedicated drizzle-orm/d1 dialect that wraps Cloudflare's D1 database binding. The schema syntax is identical to SQLite — swap the driver and the dialect import.

Q: Can Drizzle handle complex queries like CTEs or window functions?
A: Yes. Use the sql tagged template literal for any SQL Drizzle doesn't expose directly: sql<number>`ROW_NUMBER() OVER (PARTITION BY ${table.userId} ORDER BY ${table.createdAt})`. This embeds raw SQL fragments inside otherwise type-safe queries.

Q: How do I do transactions in Drizzle?
A: await db.transaction(async (tx) => { await tx.insert(users).values(...); await tx.insert(posts).values(...); }). The tx object is a scoped database instance — all operations within the callback run in the same transaction and roll back together on error.

Q: Does Drizzle support seeding and test database utilities?
A: Drizzle doesn't have a built-in seeding system. Use a seed.ts script that imports your schema and uses the normal Drizzle insert API. For test isolation, wrap each test in a transaction and roll it back: a common pattern with beforeEach / afterEach.