Drizzle ORM: Schema Definition, Queries, Migrations, and Type Safety

Overview

Drizzle ORM provides SQL-like query building with full TypeScript type inference. Schema is the source of truth — types flow from schema definition, not from a separate type generator. Lighter than Prisma, closer to raw SQL.

Installation

npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

Schema Definition

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

export const roleEnum = pgEnum("user_role", ["admin", "member", "viewer"]);

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  clerkId: text("clerk_id").notNull().unique(),
  email: text("email").notNull(),
  firstName: text("first_name"),
  lastName: text("last_name"),
  role: roleEnum("role").default("member").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  name: text("name").notNull(),
  description: text("description"),
  isPublic: boolean("is_public").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Relations
export const usersRelations = relations(users, ({ many }) => ({
  projects: many(projects),
}));

export const projectsRelations = relations(projects, ({ one }) => ({
  user: one(users, { fields: [projects.userId], references: [users.id] }),
}));

// Inferred types
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;

Database Connection

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

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 10,
  idleTimeoutMillis: 30_000,
});

export const db = drizzle(pool, { schema });

Queries

import { db } from "@/db";
import { users, projects } from "@/db/schema";
import { eq, and, desc, like, count } from "drizzle-orm";

// Select all
const allUsers = await db.select().from(users);

// Select with filter
const activeUsers = await db
  .select({ id: users.id, email: users.email, role: users.role })
  .from(users)
  .where(eq(users.role, "admin"))
  .orderBy(desc(users.createdAt))
  .limit(50);

// Find one
const user = await db
  .select()
  .from(users)
  .where(eq(users.clerkId, clerkId))
  .limit(1)
  .then(rows => rows[0] ?? null);

// Count
const [{ total }] = await db
  .select({ total: count() })
  .from(projects)
  .where(eq(projects.userId, userId));

// Search
const results = await db
  .select()
  .from(projects)
  .where(
    and(
      eq(projects.userId, userId),
      like(projects.name, `%${search}%`)
    )
  );

With Relations

// Join with relations (using drizzle query API)
const usersWithProjects = await db.query.users.findMany({
  with: {
    projects: {
      limit: 5,
      orderBy: desc(projects.createdAt),
    },
  },
  where: eq(users.role, "member"),
  limit: 20,
});

// Single user with projects
const userWithProjects = await db.query.users.findFirst({
  with: { projects: true },
  where: eq(users.id, userId),
});

Mutations

// Insert
const [newProject] = await db
  .insert(projects)
  .values({
    userId,
    name: "My Project",
    description: "Project description",
  })
  .returning();

// Update
const [updated] = await db
  .update(projects)
  .set({ name: "New Name", updatedAt: new Date() })
  .where(and(eq(projects.id, projectId), eq(projects.userId, userId)))
  .returning();

// Delete
await db.delete(projects).where(eq(projects.id, projectId));

// Upsert
await db
  .insert(users)
  .values({ clerkId, email, firstName, lastName })
  .onConflictDoUpdate({
    target: users.clerkId,
    set: { email, firstName, lastName, updatedAt: new Date() },
  });

Transactions

const result = await db.transaction(async (tx) => {
  const [project] = await tx
    .insert(projects)
    .values({ userId, name: "New Project" })
    .returning();

  await tx.insert(projectMembers).values({
    projectId: project.id,
    userId,
    role: "owner",
  });

  return project;
});

Migrations with drizzle-kit

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

export default {
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

# Generate migration from schema changes
npx drizzle-kit generate:pg

# Apply migrations
npx drizzle-kit push:pg

# Studio (visual DB explorer)
npx drizzle-kit studio

Drizzle vs Prisma

| | Drizzle | Prisma | |--|---------|--------| | Query style | SQL-like builder | Active Record | | Type generation | From schema (instant) | From schema (requires generate step) | | Bundle size | ~35KB | ~600KB | | Runtime performance | Faster (less abstraction) | Slightly slower | | Migrations | Manual with drizzle-kit | Auto with migrate | | Learning curve | Steeper (SQL knowledge helps) | Gentler | | Best for | SQL-heavy, performance-sensitive | Rapid prototyping, DX focus |

Use Drizzle when you want SQL control and minimal overhead. Use Prisma when DX and auto-migrations matter more than bundle size.