Drizzle ORM — Type-Safe SQL for TypeScript

Prisma taught the JavaScript ecosystem that ORMs could have great developer experience. Then developers started hitting its limitations: the generated client is massive, complex queries require raw SQL escapes, and the abstraction leaks in uncomfortable ways. Drizzle ORM appeared as the counter-argument — what if you could have type safety without hiding SQL behind a custom query language?

Drizzle is a TypeScript ORM that looks like SQL. You write schemas in TypeScript, and the query builder produces SQL that maps closely to what you'd write by hand. The types are inferred from your schema, so you get autocomplete and compile-time checks without code generation. No Prisma client, no migration engine running as a separate binary, no black box.

Setup

# Install Drizzle and your database driver
bun add drizzle-orm postgres        # PostgreSQL
bun add drizzle-orm better-sqlite3  # SQLite
bun add drizzle-orm mysql2          # MySQL

# Install Drizzle Kit for migrations
bun add -d drizzle-kit

Defining a Schema

Schemas are TypeScript files. Each table is a function call that returns a typed object. This is your single source of truth — no separate schema file, no code generation step.

// src/db/schema.ts
import { pgTable, serial, text, integer, boolean, timestamp, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  role: text("role", { enum: ["admin", "user", "moderator"] }).default("user"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false),
  authorId: integer("author_id")
    .references(() => users.id, { onDelete: "cascade" })
    .notNull(),
  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")
    .references(() => posts.id, { onDelete: "cascade" })
    .notNull(),
  authorId: integer("author_id")
    .references(() => users.id)
    .notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Every column has full type inference. When you query users, TypeScript knows that name is a string, role is "admin" | "user" | "moderator", and createdAt is a Date. No generated types file to keep in sync.

Database Connection

// src/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

For SQLite:

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";

const sqlite = new Database("app.db");
export const db = drizzle(sqlite, { schema });

Queries — The SQL-Like API

This is where Drizzle shines. The query API mirrors SQL so closely that if you know SQL, you already know Drizzle.

import { db } from "./db";
import { users, posts, comments } from "./db/schema";
import { eq, and, or, gt, like, desc, asc, count, sql } from "drizzle-orm";

// SELECT * FROM users WHERE email = 'alice@example.com'
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, "alice@example.com"))
  .limit(1);

// SELECT id, name FROM users WHERE role = 'admin' ORDER BY name ASC
const admins = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(eq(users.role, "admin"))
  .orderBy(asc(users.name));

// INSERT INTO users (name, email, role) VALUES (...)
const newUser = await db
  .insert(users)
  .values({ name: "Bob", email: "bob@example.com", role: "user" })
  .returning();

// UPDATE users SET role = 'moderator' WHERE id = 5
await db
  .update(users)
  .set({ role: "moderator" })
  .where(eq(users.id, 5));

// DELETE FROM users WHERE id = 10
await db
  .delete(users)
  .where(eq(users.id, 10));

Complex queries:

// JOIN with conditions
const postsWithAuthors = await db
  .select({
    postTitle: posts.title,
    authorName: users.name,
    authorEmail: users.email,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.createdAt))
  .limit(20);

// Aggregate queries
const postCounts = await db
  .select({
    authorId: posts.authorId,
    authorName: users.name,
    totalPosts: count(posts.id),
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .groupBy(posts.authorId, users.name)
  .having(gt(count(posts.id), 5));

// Subqueries
const activeAuthors = db
  .select({ authorId: posts.authorId })
  .from(posts)
  .where(eq(posts.published, true))
  .groupBy(posts.authorId)
  .as("active_authors");

const result = await db
  .select()
  .from(users)
  .innerJoin(activeAuthors, eq(users.id, activeAuthors.authorId));

The Relational Query API

For simpler queries, especially nested data fetching, Drizzle has a relational API that's closer to Prisma's style.

// Define relations
import { relations } from "drizzle-orm";

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

Now you can query with nested includes:

// Fetch posts with author and comments
const postsWithDetails = await db.query.posts.findMany({
  where: eq(posts.published, true),
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
    },
  },
  orderBy: [desc(posts.createdAt)],
  limit: 10,
});

// Single record with relations
const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      where: eq(posts.published, true),
    },
  },
});

Migrations

Drizzle Kit handles migrations by diffing your schema against the database.

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

# Generate migration from schema changes
bunx drizzle-kit generate

# Apply migrations
bunx drizzle-kit migrate

# Push schema directly (development — no migration files)
bunx drizzle-kit push

# Open Drizzle Studio (database GUI in your browser)
bunx drizzle-kit studio

Drizzle Studio is a surprisingly good local database GUI. It reads your schema and gives you a spreadsheet-like interface to browse and edit data. No separate tool to install.

Drizzle vs Prisma

This comparison comes up constantly, so here's an honest breakdown:

Feature	Drizzle	Prisma
Query syntax	SQL-like, feels familiar	Custom query language
Type generation	Inferred from schema, no codegen	Generated client (requires prisma generate)
Bundle size	~50KB	~2MB+ (generated client)
Raw SQL	Seamless, same db object	$queryRaw escape hatch
Complex queries	Natural — just chain SQL operations	Awkward for JOINs, aggregates
Migrations	Schema diffing + SQL files	Schema diffing + custom format
Learning curve	Know SQL? You're done.	Prisma-specific syntax
Relations	SQL JOINs or relational API	Implicit through schema
Database support	PG, MySQL, SQLite, Turso, Neon	PG, MySQL, SQLite, MongoDB, CockroachDB
Edge runtime	Works everywhere	Requires Prisma Accelerate for edge

Choose Drizzle when:

• You're comfortable with SQL and want to stay close to it
• Bundle size matters (serverless, edge)
• You need complex queries without escaping to raw SQL
• You want zero code generation in your build process

Choose Prisma when:

• Your team prefers a higher-level abstraction
• You need MongoDB support
• You want the most mature migration tooling
• The application is mostly simple CRUD

Transactions and Prepared Statements

// Transactions
await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: "Charlie", email: "charlie@example.com" })
    .returning();

await tx.insert(posts).values({
    title: "My First Post",
    content: "Hello world",
    authorId: user.id,
    published: true,
  });
});

// Prepared statements (reusable, faster for repeated queries)
const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, sql.placeholder("email")))
  .prepare("get_user_by_email");

const alice = await getUserByEmail.execute({ email: "alice@example.com" });
const bob = await getUserByEmail.execute({ email: "bob@example.com" });

Using Drizzle with Hono (Full Example)

A realistic API setup combining Drizzle with the Hono web framework:

// src/index.ts
import { Hono } from "hono";
import { db } from "./db";
import { users, posts } from "./db/schema";
import { eq, desc } from "drizzle-orm";

const app = new Hono();

app.get("/api/posts", async (c) => {
  const allPosts = await db.query.posts.findMany({
    where: eq(posts.published, true),
    with: { author: true },
    orderBy: [desc(posts.createdAt)],
    limit: 20,
  });
  return c.json(allPosts);
});

app.post("/api/posts", async (c) => {
  const body = await c.req.json();
  const [post] = await db
    .insert(posts)
    .values({
      title: body.title,
      content: body.content,
      authorId: body.authorId,
    })
    .returning();
  return c.json(post, 201);
});

app.get("/api/users/:id", async (c) => {
  const id = Number(c.req.param("id"));
  const user = await db.query.users.findFirst({
    where: eq(users.id, id),
    with: {
      posts: {
        where: eq(posts.published, true),
      },
    },
  });

if (!user) return c.json({ error: "Not found" }, 404);
  return c.json(user);
});

export default app;

Drizzle doesn't fight you. It doesn't hide SQL or invent a new language. If you think in SQL already, Drizzle just adds type safety to what you'd write anyway. That's a strong position to be in — explore more database patterns and tooling at CodeUp.