drizzle

Drizzle ORM

Drizzle ORM is a lightweight, type-safe TypeScript ORM that mirrors SQL syntax directly, featuring zero runtime overhead, instant cold starts, and seamless edge-runtime support.

When to Use

• TypeScript-First Object Relational Mapping: Lightweight, SQL-like ORM with zero code-generation overhead and maximum type safety.
• Serverless & Edge Runtimes: Next.js, Cloudflare Workers, Bun, Supabase, and AWS Lambda with zero cold-start penalty.
• Relational Databases with Precise Control: PostgreSQL, MySQL, and SQLite requiring explicit query execution and predictable SQL output.
• Schema-as-Code & Declarative Migrations: Managing migrations cleanly with drizzle-kit.

Quick Start

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

export const users = pgTable("users", { id: serial("id").primaryKey(), name: text("name").notNull(), email: text("email").notNull().unique(), createdAt: timestamp("created_at").defaultNow(), });

// query.ts import { drizzle } from "drizzle-orm/node-postgres"; import { users } from "./schema";

const db = drizzle(process.env.DATABASE_URL); const allUsers = await db.select().from(users);

## Core Concepts

### Declarative Schema Definition with Relationships

Type-safe table schemas and foreign key relations:

```typescript
import {
  pgTable,
  serial,
  text,
  timestamp,
  integer,
  boolean,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  fullName: text("full_name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  published: boolean("published").default(false).notNull(),
});

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

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

Relational Queries & SQL-Like Filtering

Intuitive query API combining SQL syntax with relational loading:

import { drizzle } from "drizzle-orm/node-postgres";
import { eq, desc, and } from "drizzle-orm";
import { users, posts } from "./schema";

const db = drizzle(process.env.DATABASE_URL!);

// Relational query mode
async function getAuthorWithPosts(userId: number) {
  return await db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      posts: {
        where: eq(posts.published, true),
        orderBy: [desc(posts.id)],
      },
    },
  });
}

// SQL-like query builder mode
async function updatePostStatus(postId: number, isPublished: boolean) {
  return await db
    .update(posts)
    .set({ published: isPublished })
    .where(eq(posts.id, postId))
    .returning();
}

Drizzle Kit Migrations & Introspection

Declarative migration generation and application:

// 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!,
  },
  verbose: true,
  strict: true,
});

Common Patterns

Relational Queries with Prepared Statements

Problem: Writing complex SQL joins while retaining strict compile-time TypeScript type inference.

Solution: Use Drizzle's Relational Queries API:

import { eq } from "drizzle-orm";

const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      columns: { title: true, createdAt: true },
      limit: 5,
    },
  },
});

Best Practices

Do:

• Split schemas into modular domain files and export them through a centralized schema.ts.
• Use db.query for nested relational reads and standard SQL query builder for mutations and bulk operations.
• Run drizzle-kit check and drizzle-kit generate in CI to detect schema discrepancies before deployment.
• Specify onDelete cascades explicitly on all foreign key constraints.

Don't:

• Perform multiple round-trips for insert-then-read; use .returning() to fetch modified rows immediately.
• Instantiate multiple database client connections in serverless edge handlers; reuse client instances.
• Mix multiple SQL dialects in a single schema definition.

Troubleshooting

Error	Cause	Solution
Error: relation "..." does not exist	Migrations have not been generated or pushed to database.	Run npx drizzle-kit generate and npx drizzle-kit migrate.
Type error: Property 'query' does not exist on type ...	Schema not passed to drizzle(client, { schema }) initialization.	Pass your schema object: drizzle(client, { schema: { ...schema } }).
duplicate key value violates unique constraint	Inserting record with existing unique column value.	Use .onConflictDoNothing() or .onConflictDoUpdate().

References

• Drizzle ORM Documentation