Chapter 8 — ORMs: Prisma, Drizzle, Mongoose

The why

ORMs promise "never write SQL again." That promise is a lie — and also the wrong goal. A good ORM doesn't replace SQL; it augments it with type safety, migrations, and ergonomic query building. A bad ORM generates terrible queries and hides them from you. This chapter teaches you which is which.

Core concepts

Prisma — type-safe, schema-first, great DX

Prisma is the most popular Node.js ORM. You define your schema in prisma/schema.prisma and Prisma generates a fully type-safe client.

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
generator client {
  provider = "prisma-client-js"
}
 
model User {
  id        Int      @id @default(autoincrement())

// Usage — fully typed
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
 
// Create
const user = await prisma.user.create({
  data: {

Prisma's strengths:

• Best-in-class type safety — the generated client knows your exact schema
• Excellent migration system (prisma migrate dev)
• include and select make eager loading explicit and type-safe
• Prisma Studio — visual database browser

Prisma's weaknesses:

• Performance overhead — adds a Rust binary layer between Node and the database
• Generated SQL can be suboptimal for complex queries — sometimes generates multiple queries where one JOIN would suffice
• No native JOIN in the query API — uses include which can trigger separate queries
• Cold starts are slow (~100ms to initialize Prisma Client)

Drizzle — SQL-like, lightweight, maximum control

Drizzle is the new contender. It feels like writing SQL, but with 100% TypeScript type safety.

import { pgTable, serial, text, integer, boolean, timestamp } from 'drizzle-orm/pg-core';
import { eq, desc } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres

Drizzle's strengths:

• SQL-level control — you write the exact query, Drizzle adds types
• No code generation step — schema is just TypeScript
• Much faster than Prisma (no Rust binary, no code generation)
• Excellent for complex queries — JOINs, subqueries, window functions all feel natural
• Smaller bundle, faster cold starts

Drizzle's weaknesses:

• Newer ecosystem — fewer tutorials, smaller community
• Migration system is less polished than Prisma's
• No visual studio equivalent to Prisma Studio
• Manual JOINs required — no magic include

Mongoose — the MongoDB ORM

Mongoose is a schema-based ODM (Object-Document Mapper) for MongoDB.

import mongoose, { Schema, Document } from 'mongoose';
 
interface IUser extends Document {
  email: string;
  name: string;
  createdAt: Date;
}
 
const

Mongoose strengths: Middleware hooks, schema validation, populate() for references.

Mongoose weaknesses: Slower than the native MongoDB driver, schema overhead defeats MongoDB's "schemaless" advantage, populate() is not a real JOIN — it makes separate queries.

When to use which — and when to use raw SQL

The Prisma N+1 trap (and how to avoid it)

Prisma's include looks like a JOIN but sometimes generates multiple queries:

// ❌ N+1: Prisma may execute 1 query for users + N queries for posts
const users = await prisma.user.findMany({
  include: { posts: true }
});
 
// ✅ Use select with a specific shape (Prisma may optimize to a JOIN)
// ✅ Or batch: fetch users, collect IDs, fetch all posts in one query
const users = await

Real-world example: How companies pick ORMs

• Vercel (Next.js creators) — Prisma (they employ several Prisma team members)
• Linear (project management) — Drizzle ("we wanted SQL control with types")
• Airbnb — Custom ORM built on raw SQL (they migrated off ActiveRecord for performance)
• Stripe — No ORM for payment code. Raw SQL for writes, ORM for internal tools.

The pattern: as companies scale, they tend to move away from ORMs for critical paths and keep ORMs for internal/admin tooling.

Common mistakes

Production notes

• Enable query logging in development and staging. Prisma: log: ['query', 'info', 'warn', 'error']. Drizzle: pass a logger. This is how you catch N+1s and slow queries before production.
• Prisma's connection pool is limited. Default connection_limit = num_cpus * 2 + 1. For serverless, use Prisma Data Proxy or pgbouncer in front of your database.
• Drizzle has no built-in migration runner in production. Use drizzle-kit generate then run the SQL files manually or via a custom migration script.

Performance notes

• Prisma adds ~1–5ms overhead per query vs raw SQL. For a page with 20 queries, that's 20–100ms extra. Acceptable for most apps, unacceptable for latency-sensitive endpoints.
• Drizzle adds ~0.1–0.5ms overhead — essentially zero. It compiles to nearly identical SQL.
• Mongoose adds ~2–10ms due to hydration, validation, and middleware. Use lean() for read-only queries to skip hydration.

Security notes

• ORMs prevent SQL injection by default — all three use parameterized queries. This is their #1 security benefit.
• Prisma's $queryRaw and Drizzle's sql template allow raw SQL — these are injection vectors if you concatenate user input. Always use parameterized forms.

Exercises

Beginner

1. Set up Prisma with SQLite. Define a Task model with id, title, done, createdAt. Write CRUD operations. Use Prisma Studio to inspect data.
2. Set up Drizzle with SQLite. Define the same Task schema. Write equivalent CRUD operations. Compare the developer experience.

Intermediate

1. Use Prisma's event-based logging to capture all queries. Create an N+1 scenario with include. Observe the generated queries. Rewrite to use a single query or batch pattern.
2. Compare the generated SQL from Prisma and Drizzle for: find all users with their 5 most recent posts. Use EXPLAIN ANALYZE to compare query plans.

Advanced

1. Build a data access layer that abstracts the ORM: define an interface (UserRepository) with CRUD methods. Implement it with Prisma and Drizzle. Swap implementations with zero code changes in the rest of the app.
2. Measure cold start time for Prisma vs Drizzle in a serverless environment (Lambda or Vercel Edge). Measure connection time + first query time.

Interview questions

Beginner

1. What is an ORM? Object-Relational Mapper — a library that maps database tables to classes/objects in your programming language, letting you query the database using your language instead of SQL.

2. Name three popular Node.js ORMs and what database each targets. Prisma (PostgreSQL, MySQL, SQLite, SQL Server), Drizzle (PostgreSQL, MySQL, SQLite), Mongoose (MongoDB). TypeORM and Sequelize are older alternatives.

3. What is an N+1 query? When an ORM executes 1 query to fetch N records, then N additional queries to fetch related data — totaling N+1 queries. Avoid by using eager loading (include, with) or batch queries.

Senior

4. When would you use raw SQL over an ORM? For complex reporting queries (multi-table aggregations, window functions), payment/billing code that requires exact control over locking and isolation levels, performance-critical hot paths where ORM overhead matters, and data migrations that need precise control.

5. Compare Prisma and Drizzle — when would you choose each? Prisma: rapid prototyping, teams that value DX over raw performance, projects with heavy migration needs. Drizzle: performance-critical applications, teams comfortable with SQL who want type safety, serverless/edge deployments where cold start matters, complex query requirements where you need JOIN/CTE/window function control.

6. How do you prevent ORM-generated queries from being slow in production? Enable query logging. Review generated SQL for every new query. Use EXPLAIN ANALYZE on the generated SQL. Set up slow query monitoring. For Prisma, use $queryRaw when the generated query is suboptimal. For Drizzle, the generated SQL is nearly identical to hand-written SQL, so the concern is smaller.

Summary

ORMs trade a small performance cost for massive developer experience gains. Prisma is the most polished — great for teams that want schema-first development and excellent tooling. Drizzle is the most SQL-like — great for developers who want type-safe SQL control. Mongoose is the MongoDB standard. The real-world pattern is hybrid: ORM for 80% of queries, raw SQL for the performance-critical 20%. Never let the ORM be a black box — always know what SQL it generates.

Quick recall

• Prisma: schema-first, best DX, include for relations. Watch for N+1.
• Drizzle: SQL-first, no codegen, fastest. Best for complex queries.
• Mongoose: MongoDB ODM. populate() not a real JOIN.
• Hybrid approach: ORM for CRUD, raw SQL for complex/payment code.
• Always enable query logging. Always review generated SQL.

Quiz

1. What does Prisma's include do? Eagerly loads related records. May generate a JOIN or may generate a separate query — check the logs.
2. How is Drizzle different from Prisma in terms of architecture? Drizzle has no code generation step and no Rust binary — schemas are TypeScript, queries compile to SQL at runtime with minimal overhead.
3. What ORM would you choose for a MongoDB project? Mongoose (most popular) or the native MongoDB driver with a validation layer.
4. When should you use raw SQL instead of an ORM? Complex reporting, payment code, performance-critical paths, or when the generated SQL is suboptimal.
5. What's the main security benefit of using an ORM? Automatic parameterized queries prevent SQL injection.
6. What's the tradeoff with Prisma's type safety? Generated client adds ~1–5ms overhead per query and ~50–100ms cold start time.
7. What does Mongoose's lean() do? Returns plain JavaScript objects instead of Mongoose documents — skips hydration, validation, and getters/setters. Faster for read-only queries.