Drizzle ORM in Production — Type-Safe SQL Without the Magic

Introduction

Drizzle ORM provides type-safe SQL generation without hiding the actual SQL from you. Unlike ORMs that use opaque query builders, Drizzle generates explicit SQL you can reason about and optimize. This post covers schema definition, migrations with drizzle-kit, complex queries with joins and subqueries, transactions, prepared statements for performance, comparisons with Prisma, and query logging for debugging.

Schema Definition with Type Safety
Migrations with drizzle-kit
Complex Queries with Joins and Subqueries
Transactions and Atomic Operations
Prepared Statements for Performance
Drizzle vs Prisma Decision Matrix
Query Logging for Debugging
Production Checklist
Conclusion

Schema Definition with Type Safety

Define your entire database schema in TypeScript. Drizzle generates migrations automatically, and types flow through all queries.

import {
  pgTable,
  text,
  integer,
  timestamp,
  boolean,
  uuid,
  index,
  unique,
  foreignKey,
  decimal,
  jsonb,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

export const users = pgTable(
  'users',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    email: text('email').notNull().unique(),
    username: text('username').notNull(),
    passwordHash: text('password_hash').notNull(),
    role: text('role', { enum: ['admin', 'user', 'moderator'] }).default('user'),
    isActive: boolean('is_active').default(true),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [
    index('idx_users_email').on(table.email),
    index('idx_users_username').on(table.username),
    index('idx_users_created_at').on(table.createdAt),
  ]
);

export const posts = pgTable(
  'posts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    authorId: uuid('author_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    slug: text('slug').notNull().unique(),
    content: text('content').notNull(),
    excerpt: text('excerpt'),
    published: boolean('published').default(false),
    viewCount: integer('view_count').default(0),
    publishedAt: timestamp('published_at'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [
    index('idx_posts_author_id').on(table.authorId),
    index('idx_posts_slug').on(table.slug),
    index('idx_posts_published_created').on(table.published, table.createdAt),
  ]
);

export const comments = pgTable(
  'comments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    postId: uuid('post_id')
      .notNull()
      .references(() => posts.id, { onDelete: 'cascade' }),
    authorId: uuid('author_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    content: text('content').notNull(),
    parentCommentId: uuid('parent_comment_id').references(
      () => comments.id,
      { onDelete: 'cascade' }
    ),
    approved: boolean('approved').default(false),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [
    index('idx_comments_post_id').on(table.postId),
    index('idx_comments_author_id').on(table.authorId),
    index('idx_comments_parent_comment_id').on(table.parentCommentId),
  ]
);

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

export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  parentComment: one(comments, {
    fields: [comments.parentCommentId],
    references: [comments.id],
  }),
}));

Migrations with drizzle-kit

Generate migrations automatically, then apply them with full version control.

# drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  migrations: {
    table: '__drizzle_migrations__',
    schema: 'public',
  },
} satisfies Config;

# Generate migration file
drizzle-kit generate:pg

# Apply migrations
drizzle-kit migrate:pg

Generated migration maintains explicitness:

CREATE TABLE IF NOT EXISTS "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "email" text NOT NULL UNIQUE,
  "username" text NOT NULL,
  "password_hash" text NOT NULL,
  "role" text DEFAULT 'user',
  "is_active" boolean DEFAULT true,
  "metadata" jsonb,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "idx_users_email" ON "users" ("email");
CREATE INDEX IF NOT EXISTS "idx_users_created_at" ON "users" ("created_at");

ALTER TABLE "posts" ADD CONSTRAINT "posts_author_id_users_id_fk"
  FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE cascade;

Complex Queries with Joins and Subqueries

Drizzle generates optimized SQL for complex queries with type safety.

import { db } from './db';
import { users, posts, comments } from './schema';
import { eq, desc, and, gt, inArray } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

// Complex join with aggregation
const userPostStats = await db
  .select({
    userId: users.id,
    username: users.username,
    email: users.email,
    postCount: sql<number>`cast(count(${posts.id}) as integer)`,
    totalViews: sql<number>`coalesce(sum(${posts.viewCount}), 0)`,
    avgComments: sql<string>`round(avg(comment_count)::numeric, 2)`,
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.username, users.email)
  .having(gt(sql`count(${posts.id})`, 0))
  .orderBy(desc(sql`count(${posts.id})`));

// Subquery for recent comments
const recentCommentSubquery = db
  .select({ postId: comments.postId })
  .from(comments)
  .where(gt(comments.createdAt, sql`now() - interval '7 days'`))
  .as('recent_comments');

const trendingPosts = await db
  .select({
    id: posts.id,
    title: posts.title,
    authorName: users.username,
    commentCount: sql<number>`count(${comments.id})`,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .innerJoin(
    recentCommentSubquery,
    eq(posts.id, recentCommentSubquery.postId)
  )
  .leftJoin(comments, eq(comments.postId, posts.id))
  .where(eq(posts.published, true))
  .groupBy(posts.id, posts.title, users.username)
  .orderBy(desc(sql`count(${comments.id})`))
  .limit(20);

// Recursive CTE for comment threads
const commentThreads = await db.execute(sql`
  WITH RECURSIVE comment_tree AS (
    SELECT id, post_id, author_id, content, parent_comment_id, created_at, 0 as depth
    FROM comments
    WHERE parent_comment_id IS NULL

    UNION ALL

    SELECT c.id, c.post_id, c.author_id, c.content, c.parent_comment_id, c.created_at, ct.depth + 1
    FROM comments c
    INNER JOIN comment_tree ct ON c.parent_comment_id = ct.id
    WHERE ct.depth < 10
  )
  SELECT * FROM comment_tree ORDER BY depth, created_at
`);

Transactions and Atomic Operations

Execute multiple operations atomically with rollback on error.

import { db } from './db';

const transferPoints = async (fromUserId: string, toUserId: string, amount: number) => {
  return await db.transaction(async trx => {
    // Deduct from sender
    await trx
      .update(users)
      .set({ points: sql`points - ${amount}` })
      .where(eq(users.id, fromUserId));

    // Add to recipient
    await trx
      .update(users)
      .set({ points: sql`points + ${amount}` })
      .where(eq(users.id, toUserId));

    // Log transaction
    await trx.insert(auditLog).values({
      type: 'point_transfer',
      fromUserId,
      toUserId,
      amount,
      timestamp: new Date(),
    });
  });
};

const bulkPublishPosts = async (postIds: string[], userId: string) => {
  return await db.transaction(async trx => {
    const updated = await trx
      .update(posts)
      .set({ published: true, publishedAt: new Date() })
      .where(
        and(
          inArray(posts.id, postIds),
          eq(posts.authorId, userId)
        )
      )
      .returning();

    await trx.insert(auditLog).values(
      updated.map(post => ({
        action: 'post_published',
        postId: post.id,
        userId,
        timestamp: new Date(),
      }))
    );

    return updated;
  });
};

Prepared Statements for Performance

Pre-compile queries for repeated execution with different parameters.

import { db } from './db';
import { eq } from 'drizzle-orm';

// Prepare query
const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('userId')))
  .prepare('getUserById');

// Execute with parameter binding
const user1 = await getUserById.execute({ userId: 'uuid-1' });
const user2 = await getUserById.execute({ userId: 'uuid-2' });

// Batch prepared queries
const getUsersByIds = db
  .select()
  .from(users)
  .where(inArray(users.id, sql.placeholder('ids')))
  .prepare('getUsersByIds');

const users = await getUsersByIds.execute({ ids: ['uuid-1', 'uuid-2', 'uuid-3'] });

// Complex prepared query with joins
const getPostWithComments = db
  .select({
    post: posts,
    comments: {
      id: comments.id,
      content: comments.content,
      authorName: users.username,
    },
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(comments.postId, posts.id))
  .where(eq(posts.id, sql.placeholder('postId')))
  .prepare('getPostWithComments');

const postWithComments = await getPostWithComments.execute({
  postId: 'post-uuid',
});

Drizzle vs Prisma Decision Matrix

Feature	Drizzle	Prisma
SQL visibility	Explicit SQL	Hidden query builder
Type safety	Full	Full
Performance	Thin wrapper, faster	Abstraction overhead
Migrations	Manual SQL review	Automated, opaque
Learning curve	SQL knowledge required	Lower if SQL unfamiliar
Complex queries	Native SQL when needed	Query builder only
Prepared statements	Built-in	Not exposed
Relations loading	Manual control	Automatic with risk
Database features	Use raw SQL	Limited support

Choose Drizzle for performance-critical systems and complex queries. Choose Prisma for rapid development and teams unfamiliar with SQL.

Query Logging for Debugging

Enable SQL logging to verify generated queries and spot N+1 problems.

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export const db = drizzle(pool, {
  logger: process.env.NODE_ENV === 'development',
});

// Custom logger for production debugging
const customLogger = {
  logQuery: (query: string, params: unknown[]) => {
    if (process.env.LOG_SLOW_QUERIES) {
      const start = performance.now();
      // Monitor execution time
      console.log(`[SQL] ${query}`, params);
    }
  },
};

export const db = drizzle(pool, { logger: customLogger });

// Middleware to track query counts per request
import { NextRequest, NextResponse } from 'next/server';

const queryLogger = {
  queryCount: 0,
  logQuery: (query: string) => {
    queryLogger.queryCount++;
    console.log(`Query ${queryLogger.queryCount}: ${query}`);
  },
};

export async function middleware(request: NextRequest) {
  queryLogger.queryCount = 0;

  const response = await NextResponse.next();

  if (queryLogger.queryCount > 10) {
    console.warn(
      `[N+1 WARNING] ${queryLogger.queryCount} queries for ${request.nextUrl.pathname}`
    );
  }

  response.headers.set('X-Query-Count', queryLogger.queryCount.toString());
  return response;
}

Production Checklist

Migrations versioned in git before deployment
Indexes created for all foreign keys and WHERE/ORDER BY columns
Prepared statements used for frequently-executed queries
N+1 query problems identified with query logging
Transactions wrap multi-table operations
Connection pooling configured (PgBouncer or Postgres built-in)
Query timeouts set to prevent runaway queries
Audit logs or event tables track important mutations

Conclusion

Drizzle ORM strikes a balance between type safety and control. Write explicit SQL when it matters, generate type-safe queries automatically, and optimize with prepared statements and indexes. Use migrations to version your schema, and enable query logging to find performance issues before they reach production.