<!-- Canonical: https://lucioduran.com/blog/drizzle-orm-type-safe-sql-postgresql
Language: en
Content-SHA256: 830985f96b8f25d6f9eca1ca1363f8c6e85d809ad45b51581d01ae7a650c6ee5
-->

Backend

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

2026-03-118 min read

[Lucio Durán](<https://lucioduran.com/>)
Engineering Manager & AI Solutions Architect

Also available in: [Español](<https://lucioduran.com/es/blog/drizzle-orm-type-safe-sql-postgresql>)

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

[Drizzle](<https://orm.drizzle.team/>)'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:

```typescript
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:

```typescript
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:

```typescript
// 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

```bash
# 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

```bash
# 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:

```sql
-- 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:

```sql
-- 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:

```typescript
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:

```typescript
// 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](<https://neon.com/>)'s pgbouncer), you need to be careful:

```typescript
// 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](<https://www.prisma.io/>) 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:

```typescript
// 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 (...)
```

```typescript
// 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](<https://kysely.dev/>) is the closest competitor in philosophy. Both provide type-safe SQL. The key differences:

```typescript
// 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:

```typescript
// 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.

## Frequently asked questions

### What makes Drizzle ORM different from Prisma?

Drizzle generates SQL at the TypeScript level with zero runtime overhead, your queries compile to plain SQL strings. Prisma uses a Rust-based query engine binary that adds ~10-15MB to your deployment and introduces its own query planning layer. Drizzle's API mirrors SQL syntax, while Prisma's is more abstracted.

### Can Drizzle ORM run on edge runtimes like Cloudflare Workers?

Yes. Drizzle has no native binary dependencies, making it compatible with Cloudflare Workers, Vercel Edge Functions, Deno Deploy, and any other edge runtime. Prisma requires their edge-compatible adapter which adds complexity.

### How do Drizzle migrations work?

Drizzle Kit generates SQL migration files by diffing your TypeScript schema against the current database state or previous migration snapshots. You get plain .sql files that you can review, edit, and run through any migration tool, they're not locked to Drizzle's ecosystem.

## Tips

1. **Tip:** Use ` drizzle-kit push ` for rapid prototyping and ` drizzle-kit generate ` + ` migrate ` for production. It is common to over-engineer custom migration pipelines before realizing ` push ` does exactly what is needed for dev environments, it diffs your schema against the database and applies changes directly.
2. **Tip:** Always define your schema in a separate ` schema/ ` directory with one file per table. When your schema is in a single ` schema.ts ` file and you hit 40+ tables, the file becomes unmanageable and drizzle-kit's introspection slows to a crawl.
3. **Tip:** Wrap complex queries in ` db.$with() ` CTEs instead of subqueries, Drizzle's CTE support preserves type inference across the entire chain, and PostgreSQL's query planner handles CTEs as optimization barriers (which is often what you want for readability and predictable plans).
4. **Tip:** For Neon serverless, set ` prepare: false ` in your Drizzle config. Neon's connection pooler (pgbouncer in transaction mode) doesn't support prepared statements across connections, and you'll get cryptic ` prepared statement does not exist ` errors under load.

- drizzle
- orm
- postgresql
- type-safe-sql
- prisma
- kysely
- edge-runtime
- migrations

[Back to Blog](<https://lucioduran.com/blog>)