Writing type-safe complex queries with Drizzle ORM — from relational queries and migrations to Edge runtime compatibility

When working with Prisma, there are friction points that arise. When using complex include chains spanning multiple tables, it's difficult to directly adjust the SQL that Prisma generates even when N+1 query problems or inefficient JOIN strategies occur, and the Rust-based query engine becomes an obstacle when deploying to Edge environments like Cloudflare Workers. After running into both of these issues, I looked into Drizzle ORM and ended up migrating several projects to it.

Drizzle uses the phrase "SQL-first ORM." Rather than abstracting away and hiding SQL, it lets you write queries with a TypeScript API that stays as close as possible to SQL syntax, while inferring TypeScript types for column nullability, JOIN results, and nested objects. The core idea is that you can have SQL visibility and TypeScript type safety at the same time.

This post walks through Drizzle ORM's schema definition, its two query APIs, migration strategies, NestJS integration, and Cloudflare Workers support — all with real code.

Structural Differences Between Prisma and Drizzle

The fundamental difference between the two ORMs lies in "where types come from."

Prisma treats .prisma schema files as the source of truth. TypeScript types are generated at the prisma generate step, and a query engine written in Rust is loaded at runtime. Drizzle is the opposite: TypeScript files themselves are the schema. There is no separate code generation step, and types are inferred directly from the schema definition.

drizzle-kit is a CLI tool for migration management and is completely separate from the runtime query path. Only drizzle-orm is used at runtime.

Comparison at a Glance

Before diving into code examples, here is a summary of the criteria that matter for practical decision-making.

When Drizzle is a good fit: When you're comfortable with SQL, have Edge runtime or bundle size constraints, or need direct control over the execution plan of complex queries.

When Prisma is a good fit: When team members aren't comfortable with SQL, rapid prototyping is a priority, or you need Prisma ecosystem third-party integrations.

Schema Definition: TypeScript Files as a Single Source of Truth

In Prisma, you write the schema in a DSL file:

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

In Drizzle, you write it directly in a TypeScript file:

// db/schema.ts
import {
  pgTable, serial, integer, text, timestamp, boolean
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  active: boolean('active').notNull().default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  // defaultNow() sets the default value on INSERT.
  // For auto-update on UPDATE, pass new Date() explicitly at the application layer
  // or configure a DB-level trigger separately.
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
 
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').notNull().default(false),
  // FK columns must use integer(). serial() is exclusively for auto-increment generated columns.
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

You can also use TypeScript spreads to reuse common columns — a pattern that isn't possible with Prisma's DSL:

const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  // updatedAt only sets a default on INSERT. No automatic update on UPDATE.
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
};
 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  ...timestamps,
});

Two Query APIs: When to Use Which

There are two ways to write queries in Drizzle. It's natural to use both interchangeably within a single project depending on the situation.

In cases where the two APIs overlap in use — such as when you need both nested relationships and GROUP BY — the SQL-like API with explicit JOINs is more readable and maintainable.

SQL-like API

This maps 1:1 to SQL syntax, and is used when you need precise control over the executed SQL — for JOINs, aggregations, and subqueries:

import { db } from './db';
import { users, posts } from './db/schema';
import { eq, desc, count } from 'drizzle-orm';
 
// Basic SELECT
const allUsers = await db.select().from(users);
 
// WHERE condition
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.active, true));
 
// LEFT JOIN + aggregation
const usersWithPostCount = await db
  .select({
    id: users.id,
    email: users.email,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.email)
  .orderBy(desc(count(posts.id)));

Relational Query Builder (RQB)

Used for declaratively loading nested relationships. It compiles to a single optimized SQL query internally, so N+1 problems do not occur.

Complete Relational Query Example

Here are the three steps needed to use RQB, presented as a single flow.

Step 1: Define relationships

The simplest approach is to define relationships with relations() directly inside schema.ts:

// db/schema.ts (add to existing table definitions)
import { relations } from 'drizzle-orm';
 
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
 
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

Step 2: Include relations in the schema when initializing drizzle()

// db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema'; // includes usersRelations, postsRelations
 
export const db = drizzle(new Pool({ connectionString: process.env.DATABASE_URL }), { schema });

Step 3: Write the RQB query

where conditions are passed as callback functions:

const result = await db.query.users.findMany({
  where: (u, { eq }) => eq(u.active, true),
  with: {
    posts: {
      where: (p, { eq }) => eq(p.published, true),
      orderBy: (p, { desc }) => [desc(p.createdAt)],
      limit: 5,
    },
  },
});
 
// result type is automatically inferred:
// Array<{
//   id: number;
//   email: string;
//   name: string | null;
//   active: boolean;
//   createdAt: Date;
//   updatedAt: Date;
//   posts: Array<{ id: number; title: string; published: boolean; ... }>;
// }>

Migration Strategy: push vs generate + migrate

The three commands provided by drizzle-kit are used differently depending on the environment.

# Local: apply schema changes directly to DB (no files generated)
pnpm drizzle-kit push
 
# PR/Staging: generate reviewable SQL files
pnpm drizzle-kit generate
 
# Production: apply generated SQL files sequentially
pnpm drizzle-kit migrate

The configuration file goes in the project root:

// 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!,
  },
});

Because drizzle-kit push leaves no migration files, using it in production destroys the change history and makes integration with CI/CD pipelines difficult. It's recommended to use it only for local development, and from PR stage onward, explicitly wire the generate → migrate flow into CI.

Recent versions of drizzle-kit push display a confirmation prompt for operations with potential data loss, such as column deletion or type changes.

NestJS Integration

The official way to connect Drizzle in NestJS is to configure a custom provider directly. The pattern of using a Symbol as an injection token is the clearest approach.

// db/db.module.ts
import { Global, Module } from '@nestjs/common';
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema'; // includes relations exports
 
export const DRIZZLE = Symbol('DRIZZLE');
 
@Global()
@Module({
  providers: [
    {
      provide: DRIZZLE,
      useFactory: () => {
        const pool = new Pool({ connectionString: process.env.DATABASE_URL });
        return drizzle(pool, { schema });
      },
    },
  ],
  exports: [DRIZZLE],
})
export class DbModule {}

// users/users.service.ts
import { Injectable, Inject } from '@nestjs/common';
import { eq } from 'drizzle-orm';
import type { NodePgDatabase } from 'drizzle-orm/node-postgres';
import { DRIZZLE } from '../db/db.module';
import * as schema from '../db/schema';
import { users } from '../db/schema';
 
@Injectable()
export class UsersService {
  constructor(
    @Inject(DRIZZLE) private db: NodePgDatabase<typeof schema>,
  ) {}
 
  async findById(id: number) {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id))
      .limit(1);
    return result[0] ?? null;
  }
 
  async findWithPublishedPosts(id: number) {
    return this.db.query.users.findFirst({
      where: (u, { eq }) => eq(u.id, id),
      with: {
        posts: {
          where: (p, { eq }) => eq(p.published, true),
        },
      },
    });
  }
 
  async create(body: unknown) {
    const validated = createUserSchema.parse(body);
    const [created] = await this.db.insert(users).values(validated).returning();
    return created;
  }
 
  async update(id: number, name: string) {
    await this.db
      .update(users)
      .set({ name, updatedAt: new Date() }) // must manually update updatedAt
      .where(eq(users.id, id));
  }
}

Community packages (e.g., drizzle-nestjs) can reduce dependency injection boilerplate, but you'll need to verify their maintenance status and version compatibility yourself. The pattern above achieves the same result using only NestJS built-in features, with no external packages.

Runtime Validation: drizzle-zod Integration

Drizzle provides compile-time type safety, but runtime validation for external input such as HTTP request bodies must be handled separately. Using drizzle-zod lets you auto-generate Zod validation schemas from your schema, reducing duplication.

// users/users.schema.ts
import { createInsertSchema } from 'drizzle-zod';
import { z } from 'zod';
import { users } from '../db/schema';
 
export const createUserSchema = createInsertSchema(users, {
  email: z.string().email('Invalid email address'),
  name: z.string().min(2, 'Name must be at least 2 characters').optional(),
});
 
export type CreateUserInput = z.infer<typeof createUserSchema>;

createInsertSchema builds a Zod schema based on the $inferInsert type of the users table. A value that passes createUserSchema.parse(body) can be passed directly to db.insert(users).values(validated) without any additional type casting.

Edge Runtime: Cloudflare Workers + D1

So far we've looked at traditional server environments (Node.js + PostgreSQL). Now we move to Edge, where the environment is completely different.

Cloudflare Workers has strict bundle size and runtime constraints. Drizzle's ~7KB (min+gzip) bundle is well-suited for the Workers environment, and it officially supports D1 (Cloudflare's serverless SQLite-based DB). The reason this is where the bundle size difference between the two ORMs is most pronounced is that Drizzle operates purely as a TypeScript query builder with no runtime engine.

// src/worker.ts
import { drizzle } from 'drizzle-orm/d1'; // D1 driver, not the Node.js driver
import * as schema from './db/schema';
import { posts } from './db/schema';
 
interface Env {
  DB: D1Database;
}
 
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB, { schema });
    const url = new URL(request.url);
 
    if (url.pathname === '/users') {
      const result = await db.query.users.findMany({
        where: (u, { eq }) => eq(u.active, true),
        with: {
          posts: {
            where: (p, { eq }) => eq(p.published, true),
            orderBy: (p, { desc }) => [desc(p.createdAt)],
            limit: 3,
          },
        },
      });
      return Response.json(result);
    }
 
    return new Response('Not Found', { status: 404 });
  },
};

# wrangler.toml
name = "my-worker"
main = "src/worker.ts"
compatibility_date = "2025-01-01"
 
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id"

For D1 migrations, generate SQL files with drizzle-kit generate and apply them with wrangler:

pnpm drizzle-kit generate
wrangler d1 execute DB --file=./drizzle/migrations/0000_init.sql

Common Mistakes in Practice

1. Using RQB Without relations()

If you don't define relationships with relations(), or you do define them but fail to pass the schema to drizzle() on initialization, db.query.users.findMany({ with: { posts: true } }) will not behave as expected. This is easy to confuse at first because JOINs in the SQL-like API work without relations.

// ❌ When relations are missing from schema
const db = drizzle(pool); // no schema
const result = await db.query.users.findMany({ with: { posts: true } }); // error
 
// ✅ Pass schema including relations to drizzle()
const db = drizzle(pool, { schema }); // includes usersRelations, postsRelations

2. Using push in Production

drizzle-kit push does not generate migration files. Using push against a production DB leaves no change history, making rollbacks and audit trails difficult. It's best to explicitly wire the generate → migrate flow into CI.

3. Using serial() for FK Columns

serial('author_id') creates an FK column using the SERIAL type (which creates an auto-increment sequence), resulting in an unintended sequence and an incorrect schema. FK columns should be defined as integer('author_id').references(() => users.id).

4. IDE Performance Degradation in Large Projects

In projects with hundreds of tables, complex TypeScript inference can slow down IDE autocomplete. Setting "incremental": true and "skipLibCheck": true in tsconfig.json, or splitting the schema into domain-specific files, can help.

Closing Thoughts

Drizzle ORM is a solid choice for developers who know SQL and want to maintain TypeScript type safety while retaining SQL-level control. Its advantages are clearest in Edge runtimes with bundle size constraints or situations where you need precise control over the execution plan of complex queries.

On the other hand, if your team has members who aren't comfortable with SQL, rapid prototyping is a priority, or you need Prisma ecosystem third-party integrations, Prisma remains a reasonable choice. Neither is categorically superior — picking the right tool for your project's context is what matters.

If you want to get started now, here's the recommended order:

1. Start with schema definition: Install drizzle-orm and drizzle-kit, migrate your existing Prisma schema to pgTable, and verify how $inferSelect and $inferInsert type inference works.
2. SQL-like API first, then RQB: Start by writing queries with db.select().from().where(), then switch to relations() and db.query when you need nested relationships.
3. Establish a migration workflow: Define a clear per-environment strategy — push for local, generate + migrate for PR/production — and wire it into CI/CD.

References

• Drizzle ORM Official Docs
• Drizzle ORM - Relational Query Builder
• Drizzle ORM - Migrations Official Guide
• Drizzle ORM - drizzle-kit push vs generate
• Drizzle ORM - Connecting to Cloudflare D1
• Drizzle vs Prisma 2026 — Encore
• Drizzle vs Prisma 2026 — Bytebase
• NestJS & DrizzleORM: A Great Match — Trilon Consulting
• Cloudflare D1 + Drizzle ORM at the Edge — DEV Community
• GitHub - drizzle-team/drizzle-orm