MyHome Drizzle ORM 도입기 — Next.js에서 PostgreSQL을 타입 안전하게 다루기

$ npm list --depth=0 drizzle-orm drizzle-kit postgres next
[email protected] /home/leechis/workspace/myhome
├── [email protected]
├── [email protected]
├── [email protected]
└── [email protected]

npm install drizzle-orm postgres
npm install -D drizzle-kit

drizzle.config.ts       drizzle-kit 설정
lib/db/schema.ts        테이블·제약·인덱스·타입
lib/db/index.ts         PostgreSQL 연결과 Drizzle 인스턴스
drizzle/                생성된 SQL 과 스키마 snapshot
scripts/migrate.mjs     운영 배포에서 SQL 을 적용하는 실행기

schema.ts 변경
    │
    ▼
drizzle-kit generate
    │
    ├── drizzle/0032_....sql
    └── drizzle/meta/...snapshot.json
              │
              ▼
       scripts/migrate.mjs
              │
              ▼
          PostgreSQL

import { defineConfig } from "drizzle-kit";

const envFiles =
  process.env.DRIZZLE_ENV === "dev"
    ? [".env.development.local", ".env.local"]
    : [".env.local"];

for (const file of envFiles) {
  try {
    process.loadEnvFile(file);
  } catch {
    // 파일이 없으면 이미 설정된 환경변수를 그대로 쓴다
  }
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio",
    "db:migrate": "node scripts/migrate.mjs",
    "db:migrate:dev": "DRIZZLE_ENV=dev node scripts/migrate.mjs"
  }
}

import { sql } from "drizzle-orm";
import {
  boolean,
  check,
  index,
  pgTable,
  serial,
  text,
  timestamp,
} from "drizzle-orm/pg-core";

export const posts = pgTable(
  "posts",
  {
    id: serial("id").primaryKey(),
    kind: text("kind").notNull().default("post"),
    title: text("title").notNull(),
    summary: text("summary"),
    content: text("content").notNull(),
    tags: text("tags").array().notNull().default([]),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    published: boolean("published").notNull().default(false),
    private: boolean("private").notNull().default(false),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    check("posts_kind", sql`${t.kind} in ('post', 'note')`),
    index("posts_kind_published_idx").on(t.kind, t.publishedAt.desc()),
    index("posts_tags_idx").using("gin", t.tags),
  ],
);

export type Post = typeof posts.$inferSelect;

ALTER TABLE "site_settings"
  ADD COLUMN IF NOT EXISTS "editor" text;

ALTER TABLE "site_settings"
  ADD CONSTRAINT "site_settings_editor"
  CHECK (
    "editor" IS NULL
    OR "editor" IN ('milkdown', 'tiptap', 'toast')
  );

COMMENT ON COLUMN site_settings.editor
  IS '글 편집기: milkdown / tiptap / toast. 비우면 milkdown';

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

type Db = ReturnType<typeof create>;

function create() {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL이 없습니다. .env.local을 확인하세요");
  }

  const client = postgres(url, {
    max: 5,
    onnotice: (notice) => console.log(`[NOTICE] ${notice.message}`),
  });

  return drizzle(client, { schema });
}

const globalForDb = globalThis as unknown as { db?: Db };

export function getDb(): Db {
  if (!globalForDb.db) {
    globalForDb.db = create();
  }
  return globalForDb.db;
}

export * from "./schema";

// DB에서 읽으므로 빌드 시점에 미리 렌더하지 않는다.
export const dynamic = "force-dynamic";

import { desc, eq } from "drizzle-orm";
import { getDb, profile, careers } from "@/lib/db";

export const dynamic = "force-dynamic";

export default async function AboutPage() {
  const db = getDb();

  const [profileRows, careerRows] = await Promise.all([
    db.select().from(profile).where(eq(profile.id, 1)).limit(1),
    db.select().from(careers).orderBy(desc(careers.startedOn)),
  ]);

  const me = profileRows.at(0);

  // JSX 렌더링...
}

const listColumns = {
  id: posts.id,
  title: posts.title,
  summary: posts.summary,
  tags: posts.tags,
  publishedAt: posts.publishedAt,
};

const rows = await getDb()
  .select(listColumns)
  .from(posts)
  .where(eq(posts.published, true))
  .orderBy(desc(posts.publishedAt))
  .limit(10);

"use server";

import { revalidatePath } from "next/cache";
import { redirect } from "next/navigation";
import { getDb, posts } from "@/lib/db";
import { requireAdmin } from "@/lib/auth";

export async function createPost(formData: FormData) {
  await requireAdmin();

  const title = String(formData.get("title") ?? "").trim();
  const content = String(formData.get("content") ?? "").trim();
  if (!title || !content) redirect("/admin/posts/new?e=required");

  const created = await getDb()
    .insert(posts)
    .values({
      title,
      content,
      published: false,
      private: false,
      publishedAt: null,
    })
    .returning({ id: posts.id });

  revalidatePath("/blog");
  redirect(`/admin/posts/${created[0].id}?ok=1`);
}

await db
  .update(posts)
  .set({ title, content, updatedAt: new Date() })
  .where(eq(posts.id, id));

await db
  .delete(posts)
  .where(eq(posts.id, id))
  .returning({ id: posts.id });

const pattern = `%${escapeLike(q)}%`;

const match = or(
  sql`${posts.title} ilike ${pattern}`,
  sql`coalesce(${posts.summary}, '') ilike ${pattern}`,
  sql`${posts.content} ilike ${pattern}`,
);

const sortedAt = sql`coalesce(${posts.revisedAt}, ${posts.publishedAt})`;

const rows = await db
  .select()
  .from(posts)
  .orderBy(desc(sortedAt));

const stamp = at.toISOString();
const condition = sql`${sortedAt} <= ${stamp}::timestamptz`;

function hasPgCode(err: unknown, code: string) {
  let current: unknown = err;

  for (let depth = 0; depth < 5 && current; depth += 1) {
    if (
      typeof current === "object" &&
      current !== null &&
      "code" in current &&
      (current as { code?: string }).code === code
    ) {
      return true;
    }
    current = (current as { cause?: unknown }).cause;
  }

  return false;
}

npm run db:generate

CREATE SCHEMA IF NOT EXISTS drizzle;

CREATE TABLE IF NOT EXISTS drizzle.__drizzle_migrations (
  id serial PRIMARY KEY,
  hash text NOT NULL,
  created_at bigint
);

PostgreSQL healthy
        │
        ▼
migrate 컨테이너 1회 실행
        │ 성공
        ▼
Next.js 앱 시작