Drizzle ORM実践ガイド - 型安全でSQLライクなORMの活用法

Drizzle ORMとは

Drizzle ORMは、TypeScriptファーストで設計された軽量なORMです。SQLに近い構文で、型安全なクエリを実現しながら、ゼロランタイムオーバーヘッドを目指しています。

flowchart TB
    subgraph App["TypeScript Application"]
        AppCode["アプリケーションコード"]
    end

    subgraph DrizzleORM["Drizzle ORM Layer"]
        Schema["Schema Definition"]
        QueryBuilder["Query Builder<br/>(SQL-like)"]
        Relations["Relations API"]
    end

    subgraph Databases["Databases"]
        PostgreSQL["PostgreSQL"]
        MySQL["MySQL"]
        SQLite["SQLite"]
    end

    App --> DrizzleORM
    Schema & QueryBuilder & Relations --> PostgreSQL & MySQL & SQLite

セットアップ

# PostgreSQLの場合
npm install drizzle-orm postgres
npm install -D drizzle-kit

# MySQLの場合
npm install drizzle-orm mysql2

# SQLiteの場合
npm install drizzle-orm better-sqlite3

設定ファイル

// 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,
});

スキーマ定義

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

// Enum定義
export const userRoleEnum = pgEnum('user_role', ['admin', 'user', 'guest']);
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);

// ユーザーテーブル
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  role: userRoleEnum('role').default('user').notNull(),
  profile: jsonb('profile').$type<{
    bio?: string;
    website?: string;
    social?: Record<string, string>;
  }>(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// 投稿テーブル
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  status: postStatusEnum('status').default('draft').notNull(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  viewCount: integer('view_count').default(0).notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// タグテーブル
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 50 }).notNull().unique(),
  slug: varchar('slug', { length: 50 }).notNull().unique(),
});

// 投稿とタグの中間テーブル
export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id, { onDelete: 'cascade' }),
});

// コメントテーブル
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  parentId: integer('parent_id'), // 自己参照
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

リレーション定義

// src/db/relations.ts
import { relations } from 'drizzle-orm';
import { users, posts, comments, tags, postsToTags } from './schema';

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

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

export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'replies',
  }),
  replies: many(comments, { relationName: 'replies' }),
}));

データベース接続

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import * as relations from './relations';

const connectionString = process.env.DATABASE_URL!;

// クエリ用クライアント
const queryClient = postgres(connectionString);

// Drizzleインスタンス
export const db = drizzle(queryClient, {
  schema: { ...schema, ...relations },
  logger: process.env.NODE_ENV === 'development',
});

export type Database = typeof db;

基本的なCRUD操作

Create（挿入）

import { db } from './db';
import { users, posts } from './db/schema';

// 単一レコードの挿入
const newUser = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
    role: 'user',
    profile: { bio: 'Developer', website: 'https://example.com' },
  })
  .returning();

// 複数レコードの挿入
const newPosts = await db
  .insert(posts)
  .values([
    { title: 'First Post', content: 'Hello World', authorId: newUser[0].id },
    { title: 'Second Post', content: 'Drizzle is awesome', authorId: newUser[0].id },
  ])
  .returning();

// ON CONFLICT（Upsert）
const upsertedUser = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'Updated Name',
  })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name', updatedAt: new Date() },
  })
  .returning();

Read（取得）

import { eq, and, or, gt, like, inArray, desc, asc, sql } from 'drizzle-orm';

// 全件取得
const allUsers = await db.select().from(users);

// 条件指定
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.role, 'user'));

// 複合条件
const filteredPosts = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.status, 'published'),
      gt(posts.viewCount, 100),
      or(
        like(posts.title, '%TypeScript%'),
        like(posts.title, '%Drizzle%')
      )
    )
  );

// ソートとページネーション
const paginatedPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(20);

// 特定カラムのみ取得
const userEmails = await db
  .select({
    id: users.id,
    email: users.email,
  })
  .from(users);

// 集計関数
const stats = await db
  .select({
    totalPosts: sql<number>`count(*)`,
    totalViews: sql<number>`sum(${posts.viewCount})`,
    avgViews: sql<number>`avg(${posts.viewCount})`,
  })
  .from(posts)
  .where(eq(posts.status, 'published'));

JOINクエリ

// INNER JOIN
const postsWithAuthors = await db
  .select({
    post: posts,
    author: users,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// LEFT JOIN
const usersWithPosts = await db
  .select({
    user: users,
    postCount: sql<number>`count(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

// 複数テーブルJOIN
const fullPostData = await db
  .select({
    postId: posts.id,
    postTitle: posts.title,
    authorName: users.name,
    commentCount: sql<number>`count(distinct ${comments.id})`,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(posts.id, comments.postId))
  .groupBy(posts.id, users.name);

リレーションクエリ（Query API）

// ネストしたデータ取得
const postsWithRelations = await db.query.posts.findMany({
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
      limit: 5,
      orderBy: (comments, { desc }) => [desc(comments.createdAt)],
    },
    postsToTags: {
      with: {
        tag: true,
      },
    },
  },
  where: eq(posts.status, 'published'),
  orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
  limit: 10,
});

// 単一レコード取得
const post = await db.query.posts.findFirst({
  where: eq(posts.id, 1),
  with: {
    author: {
      columns: {
        id: true,
        name: true,
        profile: true,
      },
    },
  },
});

Update（更新）

// 条件指定で更新
const updatedPosts = await db
  .update(posts)
  .set({
    status: 'published',
    publishedAt: new Date(),
  })
  .where(eq(posts.id, 1))
  .returning();

// 複数条件で更新
await db
  .update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(
    and(
      eq(posts.status, 'published'),
      gt(posts.publishedAt, new Date('2024-01-01'))
    )
  );

Delete（削除）

// 条件指定で削除
const deletedPosts = await db
  .delete(posts)
  .where(eq(posts.status, 'archived'))
  .returning();

// 複数条件で削除
await db
  .delete(comments)
  .where(
    and(
      eq(comments.authorId, userId),
      inArray(comments.postId, [1, 2, 3])
    )
  );

トランザクション

// 基本的なトランザクション
const result = await db.transaction(async (tx) => {
  const [newPost] = await tx
    .insert(posts)
    .values({
      title: 'New Post',
      content: 'Content',
      authorId: userId,
    })
    .returning();

  await tx.insert(postsToTags).values([
    { postId: newPost.id, tagId: 1 },
    { postId: newPost.id, tagId: 2 },
  ]);

  return newPost;
});

// ネストしたトランザクション（セーブポイント）
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'a@example.com', name: 'A' });

  await tx.transaction(async (nested) => {
    await nested.insert(users).values({ email: 'b@example.com', name: 'B' });
    // エラー時はこのセーブポイントまでロールバック
  });
});

// 明示的なロールバック
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();

  if (someCondition) {
    tx.rollback(); // トランザクション全体をロールバック
  }
});

マイグレーション

# マイグレーションファイル生成
npx drizzle-kit generate

# マイグレーション実行
npx drizzle-kit migrate

# スキーマをDBに直接プッシュ（開発用）
npx drizzle-kit push

# スキーマの差分確認
npx drizzle-kit check

# Drizzle Studioでデータ確認
npx drizzle-kit studio

flowchart TB
    subgraph DevFlow["開発フロー"]
        direction LR
        Schema1["Schema 変更"] --> Push["push<br/>(直接)"] --> DB1["Database 更新"]
    end

    subgraph ProdFlow["本番フロー"]
        direction TB
        Schema2["Schema 変更"] --> Generate["generate<br/>(SQL生成)"] --> Migrate["migrate<br/>(適用)"]
        Generate --> SQLFile["drizzle/0001_xxx.sql<br/>(レビュー可能なSQL)"]
    end

型安全なヘルパー関数

// src/db/helpers.ts
import { db } from './index';
import { posts, users } from './schema';
import { eq, and, desc, sql, type SQL } from 'drizzle-orm';

// 型安全なページネーション
export async function getPaginatedPosts(options: {
  page: number;
  limit: number;
  status?: 'draft' | 'published' | 'archived';
}) {
  const { page, limit, status } = options;
  const offset = (page - 1) * limit;

  const conditions: SQL[] = [];
  if (status) {
    conditions.push(eq(posts.status, status));
  }

  const [data, countResult] = await Promise.all([
    db
      .select()
      .from(posts)
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(desc(posts.createdAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)` })
      .from(posts)
      .where(conditions.length > 0 ? and(...conditions) : undefined),
  ]);

  return {
    data,
    pagination: {
      page,
      limit,
      total: countResult[0].count,
      totalPages: Math.ceil(countResult[0].count / limit),
    },
  };
}

// 型推論を活用した汎用関数
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Prismaとの比較

参考リンク

• Drizzle ORM公式ドキュメント
• Drizzle Kit
• GitHub: drizzle-team/drizzle-orm