Drizzle ORM: Type-Safe SQL for PostgreSQL — Migrations, Prepared Statements, and Prisma Comparison

The Core Idea: SQL as a First-Class TypeScript Citizen

Drizzle's philosophy is simple: SQL is already a good query language. Don't abstract it away — make it type-safe. The following shows what that looks like in practice:

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

// Schema definition — this IS your source of truth
export const users = pgTable('users', {
 id: serial('id').primaryKey(),
 email: text('email').notNull().unique(),
 name: text('name').notNull(),
 role: text('role', { enum: ['admin', 'user', 'viewer'] }).notNull().default('user'),
 createdAt: timestamp('created_at').defaultNow().notNull(),
 lastLoginAt: timestamp('last_login_at'),
});

export const posts = pgTable('posts', {
 id: serial('id').primaryKey(),
 title: text('title').notNull(),
 content: text('content').notNull(),
 published: boolean('published').notNull().default(false),
 authorId: integer('author_id').notNull().references(() => users.id),
 viewCount: integer('view_count').notNull().default(0),
 createdAt: timestamp('created_at').defaultNow().notNull(),
 updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const comments = pgTable('comments', {
 id: serial('id').primaryKey(),
 body: text('body').notNull(),
 postId: integer('post_id').notNull().references(() => posts.id),
 authorId: integer('author_id').notNull().references(() => users.id),
 createdAt: timestamp('created_at').defaultNow().notNull(),
});

// Relations — separate from schema, used for query builder
export const usersRelations = relations(users, ({ many }) => ({
 posts: many(posts),
 comments: many(comments),
}));

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

Now watch what happens when you query. Every field, every join, every condition is fully type-checked:

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import { eq, and, gt, desc, sql, count } from 'drizzle-orm';
import * as schema from './schema';

const queryClient = neon(process.env.DATABASE_URL!);
const db = drizzle(queryClient, { schema });

// Simple select — the return type is inferred exactly
const activeUsers = await db
 .select({
 id: schema.users.id,
 email: schema.users.email,
 name: schema.users.name,
 })
 .from(schema.users)
 .where(eq(schema.users.role, 'admin'))
 .orderBy(desc(schema.users.createdAt))
 .limit(10);

// TypeScript knows: activeUsers is Array<{ id: number; email: string; name: string }>
// Try accessing activeUsers[0].role — compile error! It wasn't selected.

This is the key difference from Prisma. In Prisma, if you use select, you get type safety. But complex queries with raw SQL escape hatches lose type information. In Drizzle, even raw SQL fragments can carry type information:

// Complex query with subquery, aggregation, and type-safe raw SQL
const topAuthors = await db
 .select({
 authorId: schema.posts.authorId,
 authorName: schema.users.name,
 postCount: count(schema.posts.id).as('post_count'),
 totalViews: sql<number>`sum(${schema.posts.viewCount})`.as('total_views'),
 avgViews: sql<number>`avg(${schema.posts.viewCount})::integer`.as('avg_views'),
 })
 .from(schema.posts)
 .innerJoin(schema.users, eq(schema.posts.authorId, schema.users.id))
 .where(eq(schema.posts.published, true))
 .groupBy(schema.posts.authorId, schema.users.name)
 .having(gt(count(schema.posts.id), 5))
 .orderBy(desc(sql`sum(${schema.posts.viewCount})`))
 .limit(20);

// topAuthors: Array<{
// authorId: number;
// authorName: string;
// postCount: number;
// totalViews: number;
// avgViews: number;
// }>

That sql<number> template literal tag is genius. You write actual SQL but annotate the return type, and Drizzle threads it through the entire query's type inference.

Migration Strategies: Where Drizzle Kit Shines

Drizzle Kit is the companion CLI tool for schema management. It has two modes that serve different purposes:

drizzle-kit push — The Development Mode

# Reads your schema files, compares against the actual database,
# generates and applies ALTER statements directly
npx drizzle-kit push

# Output:
# [✓] Reading schema files...
# [✓] Connecting to database...
# [i] Changes detected:
# ALTER TABLE "posts" ADD COLUMN "slug" text;
# CREATE UNIQUE INDEX "posts_slug_idx" ON "posts" ("slug");
# [✓] Applied 2 changes

This is for rapid iteration. You change your schema TypeScript file, run push, and your database matches. No migration files, no versioning. Perfect for local dev and prototyping.

drizzle-kit generate + migrate — The Production Mode

# Generate a migration file from schema changes
npx drizzle-kit generate

# Creates: drizzle/0003_add_post_slug.sql
# Contains the same ALTER statements, but as a reviewable file

The generated migration file:

-- drizzle/0003_add_post_slug.sql
ALTER TABLE "posts" ADD COLUMN "slug" text;--> statement-breakpoint
CREATE UNIQUE INDEX "posts_slug_idx" ON "posts" ("slug");

The statement-breakpoint comments are how Drizzle Kit tracks which statements have been applied. You can edit these files — add data migrations, wrap in transactions, whatever you need:

-- drizzle/0003_add_post_slug.sql (edited)
BEGIN;

ALTER TABLE "posts" ADD COLUMN "slug" text;--> statement-breakpoint

-- Data migration: generate slugs from titles
UPDATE "posts" SET "slug" = lower(
 regexp_replace(
 regexp_replace("title", '[^a-zA-Z0-9\s-]', '', 'g'),
 '\s+', '-', 'g'
 )
);--> statement-breakpoint

-- Now make it NOT NULL after backfilling
ALTER TABLE "posts" ALTER COLUMN "slug" SET NOT NULL;--> statement-breakpoint
CREATE UNIQUE INDEX "posts_slug_idx" ON "posts" ("slug");--> statement-breakpoint

COMMIT;

Apply in production:

import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL!, { max: 1 });
const db = drizzle(migrationClient);

await migrate(db, { migrationsFolder: './drizzle' });
await migrationClient.end();

Prepared Statements and the Edge Runtime Story

This is where Drizzle's architecture pays dividends. Because there's no query engine binary, Drizzle runs anywhere JavaScript runs:

// Cloudflare Workers — works out of the box
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

export default {
 async fetch(request: Request, env: Env): Promise<Response> {
 const sql = neon(env.DATABASE_URL);
 const db = drizzle(sql);

 // Prepared statement — compiled once, executed many times
 const getUser = db
 .select()
 .from(schema.users)
 .where(eq(schema.users.id, sql.placeholder('userId')))
 .prepare('get_user_by_id');

 const user = await getUser.execute({ userId: 42 });

 return Response.json(user);
 }
};

The .prepare() method creates a named prepared statement on the PostgreSQL side. For serverless environments using connection poolers (like Neon's pgbouncer), you need to be careful:

// Neon with pooled connections — disable prepared statements
const db = drizzle(sql, {
 // pgbouncer in transaction mode can't track prepared statements
 // across different backend connections
 logger: true,
});

// Alternative: use the HTTP driver which doesn't use prepared statements
import { neon } from '@neondatabase/serverless';
const httpClient = neon(env.DATABASE_URL);
// HTTP driver sends each query as a standalone request — no prepared stmts

The Honest Prisma Comparison

Prisma is a good tool with years of production validation. But here is where the two genuinely differ:

Cold Start Time

This is measurable and significant for serverless:

Prisma cold start (AWS Lambda, Node.js 20):
 - Query engine binary load: ~180ms
 - Schema parsing: ~45ms
 - First query: ~80ms
 Total: ~305ms

Drizzle cold start (same environment):
 - Module import: ~12ms
 - First query: ~75ms
 Total: ~87ms

That ~220ms difference is the Prisma query engine loading. On Cloudflare Workers with stricter cold start budgets, it's even more painful.

Query Generation

Prisma generates its own query plan and translates it to SQL. Sometimes this produces suboptimal queries:

// Prisma — you write this:
const result = await prisma.post.findMany({
 where: { published: true },
 include: {
 author: true,
 comments: {
 include: { author: true },
 orderBy: { createdAt: 'desc' },
 take: 5,
 },
 },
});

// Prisma generates: multiple separate queries (1+N potential)
// SELECT * FROM "posts" WHERE "published" = true
// SELECT * FROM "users" WHERE "id" IN (...)
// SELECT * FROM "comments" WHERE "post_id" IN (...) ORDER BY ...
// SELECT * FROM "users" WHERE "id" IN (...)

// Drizzle — you write this:
const result = await db.query.posts.findMany({
 where: eq(schema.posts.published, true),
 with: {
 author: true,
 comments: {
 with: { author: true },
 orderBy: [desc(schema.comments.createdAt)],
 limit: 5,
 },
 },
});

// Drizzle generates: lateral joins where possible
// SELECT ... FROM "posts"
// LEFT JOIN LATERAL (
// SELECT ... FROM "comments"
// LEFT JOIN "users" ON ...
// WHERE "comments"."post_id" = "posts"."id"
// ORDER BY "created_at" DESC LIMIT 5
// ) ON true
// LEFT JOIN "users" ON "users"."id" = "posts"."author_id"
// WHERE "posts"."published" = true

The lateral join approach is a single query that PostgreSQL can optimize holistically. The multiple-query approach relies on the application layer to stitch results together.

Kysely: The Other Contender

Kysely is the closest competitor in philosophy. Both provide type-safe SQL. The key differences:

// Kysely — type-safe but more verbose
const result = await db
 .selectFrom('users')
 .select(['id', 'email', 'name'])
 .where('role', '=', 'admin')
 .orderBy('created_at', 'desc')
 .limit(10)
 .execute();

// Drizzle — uses schema objects instead of strings
const result = await db
 .select({ id: users.id, email: users.email, name: users.name })
 .from(users)
 .where(eq(users.role, 'admin'))
 .orderBy(desc(users.createdAt))
 .limit(10);

Kysely infers types from a global database interface type. Drizzle infers types from the schema definition objects. In practice, Drizzle's approach means you get autocomplete and type errors at the point of use without needing to maintain a separate type definition file.

Transactions and Error Handling

Drizzle's transaction API is clean and handles the common patterns well:

// Basic transaction
const newPost = await db.transaction(async (tx) => {
 const [author] = await tx
 .select()
 .from(schema.users)
 .where(eq(schema.users.id, authorId))
 .for('update'); // SELECT FOR UPDATE — row-level lock

 if (!author) throw new Error('Author not found');

 const [post] = await tx
 .insert(schema.posts)
 .values({
 title,
 content,
 authorId: author.id,
 published: false,
 })
 .returning();

 // Update author's post count (if you're denormalizing)
 await tx
 .update(schema.users)
 .set({
 lastLoginAt: new Date(),
 })
 .where(eq(schema.users.id, authorId));

 return post;
});
// Transaction auto-commits on success, auto-rolls-back on throw

Notable Limitations

After extended production use with Drizzle, here are notable limitations:

1. The relational query builder (db.query.*) is less flexible than the SQL-like builder. For complex queries, I always drop down to the select/from/where API. The findMany with with is great for simple nested reads but falls apart for anything involving custom joins or CTEs.

2. Error messages from the schema diff can be cryptic. When drizzle-kit push detects a column type change, the error sometimes doesn't tell you which table or column changed. You end up diffing the schema snapshot manually.

3. No built-in connection pooling. You need to bring your own (postgres.js pool, Neon serverless driver, etc.). Prisma bundles this, which is simpler for getting started.

But these are paper cuts compared to the fundamental benefits: zero binary dependencies, genuine SQL-level type safety, and the freedom to deploy anywhere JavaScript runs. Drizzle represents the direction the ORM space needed to take.