Drizzle ORM: TypeScript-First SQL for Modern Applications

Drizzle ORM occupies a distinct position in the TypeScript database layer: it's lightweight, type-safe, and SQL-first. Unlike Prisma (which abstracts SQL behind its own query language) or TypeORM (heavy class-based decorators), Drizzle stays close to SQL while providing TypeScript inference. If you know SQL, Drizzle feels natural.

Why Drizzle

Type inference from schema: Define your schema in TypeScript; Drizzle infers column types for all query results. No type gymnastics.

SQL-first: Drizzle's query builder closely mirrors SQL syntax. Complex queries translate directly. For anything Drizzle's builder doesn't support, sql template literals drop to raw SQL with full type inference.

No runtime magic: No dependency injection, no decorators, no global state. Just functions.

Small bundle: ~38KB gzipped. Relevant for edge deployments.

Multiple databases: PostgreSQL, MySQL, SQLite, LibSQL (Turso). Same API across all.

Installation

# With PostgreSQL
bun add drizzle-orm postgres
bun add -d drizzle-kit

# With SQLite (better-sqlite3)
bun add drizzle-orm better-sqlite3
bun add -d drizzle-kit @types/better-sqlite3

Schema Definition

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  active: boolean("active").default(true).notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 512 }).notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  publishedAt: timestamp("published_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  authorIdx: index("posts_author_idx").on(table.authorId),
}));

// Inferred types
export type User = typeof users.$inferSelect;    // { id: number, email: string, ... }
export type NewUser = typeof users.$inferInsert; // { id?: number, email: string, ... }

Database Connection and Initialization

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

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

For SQLite:

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

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

Querying

Select

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

// Select all
const allUsers = await db.select().from(users);
// Type: { id: number, email: string, name: string, createdAt: Date, active: boolean }[]

// Select specific columns
const emails = await db.select({ id: users.id, email: users.email }).from(users);

// Where clause
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.active, true));

// Multiple conditions
const results = await db.select()
  .from(posts)
  .where(
    and(
      eq(posts.authorId, userId),
      gt(posts.createdAt, new Date("2024-01-01"))
    )
  )
  .orderBy(desc(posts.createdAt))
  .limit(20);

// Like
const search = await db.select()
  .from(users)
  .where(like(users.email, "%@company.com"));

Insert

// Insert one
const [newUser] = await db.insert(users).values({
  email: "[email protected]",
  name: "Alice",
}).returning();  // returns the inserted row

// Insert many
await db.insert(users).values([
  { email: "[email protected]", name: "Bob" },
  { email: "[email protected]", name: "Carol" },
]);

// Upsert (insert or update)
await db.insert(users)
  .values({ email: "[email protected]", name: "Alice Updated" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name` },
  });

Update and Delete

// Update
const [updated] = await db.update(users)
  .set({ active: false })
  .where(eq(users.id, userId))
  .returning();

// Delete
await db.delete(users).where(eq(users.id, userId));

// Delete with returning
const [deleted] = await db.delete(posts)
  .where(eq(posts.id, postId))
  .returning();

Joins

// Inner join
const postsWithAuthors = await db.select({
  post: posts,
  author: { name: users.name, email: users.email }
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.active, true));

// Type: { post: Post, author: { name: string, email: string } }[]

Relational Queries (ORM-style)

Drizzle has a "relational" query API that handles joins more declaratively:

// db/schema.ts — add relations
import { relations } from "drizzle-orm";

export const userRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// Query with nested relations
const usersWithPosts = await db.query.users.findMany({
  where: eq(users.active, true),
  with: {
    posts: {
      where: isNotNull(posts.publishedAt),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
  },
});
// Type: (User & { posts: Post[] })[]

Migrations

# drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./migrations",
  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 (dev only — skips migration files)
bunx drizzle-kit push

# Open Drizzle Studio (web-based DB browser)
bunx drizzle-kit studio

The push command is convenient during development — it syncs your schema to the database without creating migration files. For production, use migrate with versioned migration files.

Transactions

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users)
    .values({ email: "[email protected]", name: "Alice" })
    .returning();

  await tx.insert(posts).values({
    title: "First Post",
    content: "Hello world",
    authorId: user.id,
  });
  // If any operation throws, transaction is rolled back
});

Raw SQL with Type Safety

import { sql } from "drizzle-orm";

// sql template literal — parameterized, type-safe
const result = await db.execute(
  sql`SELECT COUNT(*) as count FROM ${users} WHERE ${users.active} = true`
);

Drizzle vs Prisma

Prisma has more ecosystem tooling and a larger community. Drizzle is better for edge deployments, developers who prefer staying close to SQL, and applications where bundle size matters.