DEV Community

myougaTheAxo
myougaTheAxo

Posted on

Drizzle ORM Practical Patterns: Type-Safe Database Access Design

#typescript #database #drizzle #postgres

What is Drizzle ORM

Drizzle ORM is a lightweight, TypeScript-first ORM. Compared to Prisma, it's "closer to SQL" with strong type inference, small bundle size, and works in Edge Runtime (Cloudflare Workers, etc.).

Key differences from Prisma:

  • Schema defined in TypeScript (not .prisma files)
  • Type inference is automatic
  • Full Edge Runtime support
  • Smaller bundle size
  • More SQL-proximate (less abstraction)

"Writing Drizzle queries is writing SQL" is the underlying philosophy.

Schema Definition 

import {
  pgTable, serial, text, varchar, integer,
  timestamp, boolean, decimal, jsonb, index, uniqueIndex, pgEnum,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const roleEnum = pgEnum("role", ["admin", "user", "viewer"]);

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  role: roleEnum("role").default("user").notNull(),
  metadata: jsonb("metadata").$type<Record<string, unknown>>().default({}),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("email_idx").on(table.email),
}));

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

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
Enter fullscreen mode Exit fullscreen mode

Database Connection and Basic Queries 

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";
import { eq, and, desc, like, sql } from "drizzle-orm";

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
export const db = drizzle(pool, { schema });

async function findUser(id: number) {
  const result = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return result[0];
}

async function findPostsWithAuthor(limit = 10) {
  return db.query.posts.findMany({
    with: { author: true },
    orderBy: desc(posts.createdAt),
    limit,
  });
}

async function createUser(data: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}
Enter fullscreen mode Exit fullscreen mode

Dynamic Query Building 

interface UserFilter {
  role?: "admin" | "user" | "viewer";
  search?: string;
  createdAfter?: Date;
}

async function findUsers(filter: UserFilter, page = 1, pageSize = 20) {
  const conditions = [];
  if (filter.role) conditions.push(eq(users.role, filter.role));
  if (filter.search) conditions.push(like(users.name, `%${filter.search}%`));

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const [data, countResult] = await Promise.all([
    db.select().from(users).where(whereClause)
      .orderBy(desc(users.createdAt)).limit(pageSize).offset((page - 1) * pageSize),
    db.select({ count: sql<number>`count(*)::int` }).from(users).where(whereClause),
  ]);

  return { data, total: countResult[0].count, page, pageSize };
}
Enter fullscreen mode Exit fullscreen mode

Transactions 

async function transferCredits(fromUserId: number, toUserId: number, amount: number) {
  await db.transaction(async (tx) => {
    const [sender] = await tx.select().from(users).where(eq(users.id, fromUserId)).for("update");
    const currentCredits = (sender.metadata as any)?.credits ?? 0;

    if (currentCredits < amount) throw new Error("Insufficient credits");

    await tx.update(users)
      .set({ metadata: sql`jsonb_set(metadata, '{credits}', to_jsonb(${currentCredits - amount}))` })
      .where(eq(users.id, fromUserId));

    await tx.update(users)
      .set({ metadata: sql`jsonb_set(COALESCE(metadata, '{}'), '{credits}', to_jsonb(COALESCE((metadata->>'credits')::int, 0) + ${amount}))` })
      .where(eq(users.id, toUserId));
  });
}
Enter fullscreen mode Exit fullscreen mode

Migration Management 

# Generate migration
npx drizzle-kit generate

# Apply migration
npx drizzle-kit migrate

# Check diff
npx drizzle-kit check

# Drizzle Studio (GUI)
npx drizzle-kit studio
Enter fullscreen mode Exit fullscreen mode

Edge Environment Usage 

import { drizzle } from "drizzle-orm/d1";

export default {
  async fetch(req: Request, env: { DB: D1Database }): Promise<Response> {
    const db = drizzle(env.DB, { schema });
    const allUsers = await db.select().from(schema.users).limit(10);
    return Response.json(allUsers);
  },
};
Enter fullscreen mode Exit fullscreen mode

Drizzle lets you leverage SQL knowledge while gaining TypeScript type safety. If you've been scared of "SQL hidden inside ORM black boxes," Drizzle is a great fit.

This article is from the Claude Code Complete Guide (7 chapters) on note.com.
myouga (@myougatheaxo) - VTuber axolotl. Sharing practical AI development tips.

Top comments (0)