Drizzle ORM으로 타입 안전한 복잡 쿼리 작성하기 — 관계형 쿼리, 마이그레이션, Edge 런타임 대응까지

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

// db/schema.ts
import {
  pgTable, serial, integer, text, timestamp, 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(),
  name: text('name'),
  active: boolean('active').notNull().default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  // defaultNow()는 INSERT 기본값입니다.
  // UPDATE 시 자동 갱신이 필요하면 애플리케이션 레이어에서 new Date()를 명시적으로 전달하거나
  // DB 트리거를 별도로 구성해야 합니다.
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
 
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').notNull().default(false),
  // FK 컬럼은 integer()로 정의합니다. serial()은 auto-increment 생성 컬럼 전용입니다.
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  // updatedAt은 INSERT 기본값만 설정됩니다. UPDATE 시 자동 갱신 없음.
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
};
 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  ...timestamps,
});

import { db } from './db';
import { users, posts } from './db/schema';
import { eq, desc, count } from 'drizzle-orm';
 
// 기본 SELECT
const allUsers = await db.select().from(users);
 
// WHERE 조건
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.active, true));
 
// LEFT JOIN + 집계
const usersWithPostCount = await db
  .select({
    id: users.id,
    email: users.email,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.email)
  .orderBy(desc(count(posts.id)));

// db/schema.ts (기존 테이블 정의에 추가)
import { relations } from 'drizzle-orm';
 
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
 
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema'; // usersRelations, postsRelations 포함
 
export const db = drizzle(new Pool({ connectionString: process.env.DATABASE_URL }), { schema });

const result = await db.query.users.findMany({
  where: (u, { eq }) => eq(u.active, true),
  with: {
    posts: {
      where: (p, { eq }) => eq(p.published, true),
      orderBy: (p, { desc }) => [desc(p.createdAt)],
      limit: 5,
    },
  },
});
 
// result 타입이 자동으로 추론됩니다:
// Array<{
//   id: number;
//   email: string;
//   name: string | null;
//   active: boolean;
//   createdAt: Date;
//   updatedAt: Date;
//   posts: Array<{ id: number; title: string; published: boolean; ... }>;
// }>

# 로컬: 스키마 변경을 즉시 DB에 반영 (파일 생성 없음)
pnpm drizzle-kit push
 
# PR/스테이징: 검토 가능한 SQL 파일 생성
pnpm drizzle-kit generate
 
# 프로덕션: 생성된 SQL 파일 순차 적용
pnpm drizzle-kit migrate

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
 
export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

// db/db.module.ts
import { Global, Module } from '@nestjs/common';
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema'; // relations export 포함
 
export const DRIZZLE = Symbol('DRIZZLE');
 
@Global()
@Module({
  providers: [
    {
      provide: DRIZZLE,
      useFactory: () => {
        const pool = new Pool({ connectionString: process.env.DATABASE_URL });
        return drizzle(pool, { schema });
      },
    },
  ],
  exports: [DRIZZLE],
})
export class DbModule {}

// users/users.service.ts
import { Injectable, Inject } from '@nestjs/common';
import { eq } from 'drizzle-orm';
import type { NodePgDatabase } from 'drizzle-orm/node-postgres';
import { DRIZZLE } from '../db/db.module';
import * as schema from '../db/schema';
import { users } from '../db/schema';
 
@Injectable()
export class UsersService {
  constructor(
    @Inject(DRIZZLE) private db: NodePgDatabase<typeof schema>,
  ) {}
 
  async findById(id: number) {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id))
      .limit(1);
    return result[0] ?? null;
  }
 
  async findWithPublishedPosts(id: number) {
    return this.db.query.users.findFirst({
      where: (u, { eq }) => eq(u.id, id),
      with: {
        posts: {
          where: (p, { eq }) => eq(p.published, true),
        },
      },
    });
  }
 
  async create(body: unknown) {
    const validated = createUserSchema.parse(body);
    const [created] = await this.db.insert(users).values(validated).returning();
    return created;
  }
 
  async update(id: number, name: string) {
    await this.db
      .update(users)
      .set({ name, updatedAt: new Date() }) // updatedAt 수동 갱신 필수
      .where(eq(users.id, id));
  }
}

// users/users.schema.ts
import { createInsertSchema } from 'drizzle-zod';
import { z } from 'zod';
import { users } from '../db/schema';
 
export const createUserSchema = createInsertSchema(users, {
  email: z.string().email('유효한 이메일이 아닙니다'),
  name: z.string().min(2, '이름은 2자 이상이어야 합니다').optional(),
});
 
export type CreateUserInput = z.infer<typeof createUserSchema>;

// src/worker.ts
import { drizzle } from 'drizzle-orm/d1'; // Node.js 드라이버가 아닌 D1 드라이버
import * as schema from './db/schema';
import { posts } from './db/schema';
 
interface Env {
  DB: D1Database;
}
 
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB, { schema });
    const url = new URL(request.url);
 
    if (url.pathname === '/users') {
      const result = await db.query.users.findMany({
        where: (u, { eq }) => eq(u.active, true),
        with: {
          posts: {
            where: (p, { eq }) => eq(p.published, true),
            orderBy: (p, { desc }) => [desc(p.createdAt)],
            limit: 3,
          },
        },
      });
      return Response.json(result);
    }
 
    return new Response('Not Found', { status: 404 });
  },
};

# wrangler.toml
name = "my-worker"
main = "src/worker.ts"
compatibility_date = "2025-01-01"
 
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id"

pnpm drizzle-kit generate
wrangler d1 execute DB --file=./drizzle/migrations/0000_init.sql

// ❌ schema에 relations가 빠진 경우
const db = drizzle(pool); // schema 없음
const result = await db.query.users.findMany({ with: { posts: true } }); // 에러
 
// ✅ relations를 포함한 schema를 drizzle()에 전달
const db = drizzle(pool, { schema }); // usersRelations, postsRelations 포함