【TypeScript × Drizzle ORM】完全ガイド｜スキーマ定義・CRUD・JOIN・マイグレーション・Prismaとの比較まで徹底解説

# Drizzle ORM本体とPostgreSQLドライバ
npm install drizzle-orm postgres
npm install --save-dev drizzle-kit @types/pg

# SQLite使用時
# npm install drizzle-orm @libsql/client

# MySQL使用時
# npm install drizzle-orm mysql2

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

// usersテーブル
export const users = pgTable("users", {
  id:        serial("id").primaryKey(),
  name:      varchar("name", { length: 100 }).notNull(),
  email:     varchar("email", { length: 255 }).notNull().unique(),
  age:       integer("age"),
  isActive:  boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

// postsテーブル
export const posts = pgTable("posts", {
  id:        serial("id").primaryKey(),
  title:     varchar("title", { length: 255 }).notNull(),
  content:   text("content"),
  published: boolean("published").notNull().default(false),
  authorId:  integer("author_id").references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// --- 型の自動生成 ---
// SELECT結果の型（全カラムが含まれる）
export type User    = InferSelectModel<typeof users>;
export type Post    = InferSelectModel<typeof posts>;

// INSERT用の型（自動生成フィールドはOptionalになる）
export type NewUser = InferInsertModel<typeof users>;
export type NewPost = InferInsertModel<typeof posts>;

// --- リレーション定義 ---
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

// User型（InferSelectModel）
// {
//   id: number;
//   name: string;
//   email: string;
//   age: number | null;   ← notNullなしはnullableになる
//   isActive: boolean;
//   createdAt: Date;
//   updatedAt: Date;
// }

// NewUser型（InferInsertModel）
// {
//   name: string;
//   email: string;
//   age?: number | null;       ← Optionalに
//   isActive?: boolean;        ← default値があるのでOptional
//   createdAt?: Date;          ← defaultNow()があるのでOptional
//   id?: number;               ← serial（自動採番）なのでOptional
// }

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

const connectionString = process.env.DATABASE_URL!;

// 接続プールの作成
const client = postgres(connectionString, {
  max: 10,            // 最大接続数
  idle_timeout: 20,   // アイドルタイムアウト（秒）
});

// drizzleインスタンス（schemaを渡すとリレーションクエリが使えるようになる）
export const db = drizzle(client, { schema });

// 型エクスポート（テスト等で使いやすくする）
export type Database = typeof db;

import { db } from "./db";
import { users } from "./db/schema";
import { eq, and, gte, like, isNull, desc, asc } from "drizzle-orm";

// --- 全件取得 ---
const allUsers = await db.select().from(users);
// 型: User[]

// --- 条件指定（WHERE）---
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// --- AND条件 ---
const filtered = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      gte(users.age, 18),
    )
  );

// --- LIKE検索 ---
const searched = await db
  .select()
  .from(users)
  .where(like(users.name, "%田%"));

// --- カラムを絞って取得 ---
const names = await db
  .select({ id: users.id, name: users.name })
  .from(users);
// 型: { id: number; name: string }[]

// --- ORDER BY + LIMIT + OFFSET ---
const paginated = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)
  .offset(0);

// --- NULL チェック ---
const noAge = await db
  .select()
  .from(users)
  .where(isNull(users.age));

import { db } from "./db";
import { users } from "./db/schema";
import type { NewUser } from "./db/schema";

// --- 単件INSERT ---
const newUser: NewUser = {
  name: "山田太郎",
  email: "yamada@example.com",
};

const [inserted] = await db
  .insert(users)
  .values(newUser)
  .returning(); // INSERTした行を返す
// 型: User

// --- 複数件INSERT ---
const newUsers: NewUser[] = [
  { name: "鈴木花子", email: "suzuki@example.com" },
  { name: "田中次郎", email: "tanaka@example.com", age: 30 },
];

const insertedUsers = await db
  .insert(users)
  .values(newUsers)
  .returning({ id: users.id, name: users.name });
// 型: { id: number; name: string }[]

// --- UPSERT（ON CONFLICT） ---
import { onConflictDoUpdate } from "drizzle-orm";

await db
  .insert(users)
  .values({ name: "山田太郎", email: "yamada@example.com" })
  .onConflictDoUpdate({
    target: users.email, // 衝突判定カラム
    set: { name: "山田太郎（更新済み）", updatedAt: new Date() },
  });

import { db } from "./db";
import { users } from "./db/schema";
import { eq, lt } from "drizzle-orm";

// --- UPDATE ---
const 2026/03/22 = await db
  .update(users)
  .set({
    name: "山田太郎（更新）",
    updatedAt: new Date(),
  })
  .where(eq(users.id, 1))
  .returning();

// --- DELETE ---
await db
  .delete(users)
  .where(eq(users.id, 1));

// --- 古いレコードを一括削除 ---
const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000); // 30日前
await db
  .delete(users)
  .where(lt(users.createdAt, cutoff));

import { db } from "./db";
import { users, posts } from "./db/schema";
import { eq } from "drizzle-orm";

// --- INNER JOIN ---
const usersWithPosts = await db
  .select({
    userId:    users.id,
    userName:  users.name,
    postId:    posts.id,
    postTitle: posts.title,
  })
  .from(users)
  .innerJoin(posts, eq(posts.authorId, users.id));
// 型: { userId: number; userName: string; postId: number; postTitle: string }[]

// --- LEFT JOIN（投稿がいないユーザーも含む）---
const allUsersWithPosts = await db
  .select({
    user: users,
    post: posts,  // 投稿がない場合はnullになる
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));
// 型: { user: User; post: Post | null }[]

// --- 集計クエリ（投稿数をカウント）---
import { count, sql } from "drizzle-orm";

const userPostCounts = await db
  .select({
    userId:    users.id,
    userName:  users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .orderBy(desc(count(posts.id)));

import { db } from "./db";
import { users } from "./db/schema";
import { eq } from "drizzle-orm";

// --- ユーザーと投稿を一括取得 ---
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
});
// 型: (User & { posts: Post[] })[]

// --- 単一ユーザーの取得 ---
const user = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: { posts: true },
  columns: {
    id: true,
    name: true,
    email: true,
    // isActive: false にすると除外できる
  },
});
// 型: { id: number; name: string; email: string; posts: Post[] } | undefined

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

async function createUserWithPost(userData: NewUser, postTitle: string) {
  return await db.transaction(async (tx) => {
    // txはdbと同じAPIを持つ
    const [user] = await tx
      .insert(users)
      .values(userData)
      .returning();

    const [post] = await tx
      .insert(posts)
      .values({
        title: postTitle,
        authorId: user.id,
      })
      .returning();

    // エラーが発生すると自動でロールバックされる
    return { user, post };
  });
}

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

  try {
    await tx.transaction(async (innerTx) => {
      await innerTx.insert(users).values({ name: "B", email: "b@example.com" });
      throw new Error("内部ロールバック"); // 内側だけロールバック
    });
  } catch (_) {
    console.log("内部トランザクションがロールバックされました");
  }
  // 外側のトランザクション（ユーザーA）はコミットされる
});

import { db } from "./db";
import { sql } from "drizzle-orm";
import type { User } from "./db/schema";

// --- 型を指定したRawクエリ ---
const result = await db.execute<User>(
  sql`SELECT * FROM users WHERE age > ${18} ORDER BY created_at DESC`
);
// プレースホルダーは自動でバインドされるのでSQLインジェクション安全

// --- 集計や関数をカラムに使う ---
const stats = await db
  .select({
    total:   sql<number>`count(*)::int`,
    avgAge:  sql<number | null>`avg(${users.age})::numeric(5,1)`,
    maxAge:  sql<number | null>`max(${users.age})`,
  })
  .from(users)
  .where(eq(users.isActive, true));
// 型: { total: number; avgAge: number | null; maxAge: number | null }[]

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema:    "./src/db/schema.ts",  // スキーマファイルのパス
  out:       "./drizzle",            // マイグレーションファイルの出力先
  dialect:   "postgresql",           // "mysql" | "sqlite" も可
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose:   true,   // 差分の詳細を表示
  strict:    true,   // 破壊的変更の前に確認
});

# スキーマの差分からSQLマイグレーションを生成
npx drizzle-kit generate

# マイグレーションを適用（DB更新）
npx drizzle-kit migrate

# GUIでDB・スキーマを確認（Drizzle Studio）
npx drizzle-kit studio

# 現在のスキーマとDBの状態を比較
npx drizzle-kit check

# DBから既存スキーマを逆生成（既存DBからDrizzleを始める場合）
npx drizzle-kit introspect

import { db } from "../db";
import { users } from "../db/schema";
import { eq, like, and, desc } from "drizzle-orm";
import type { User, NewUser } from "../db/schema";

export class UserRepository {
  constructor(private readonly db: Database) {}

  async findById(id: number): Promise<User | undefined> {
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
    });
  }

  async findByEmail(email: string): Promise<User | undefined> {
    return this.db.query.users.findFirst({
      where: eq(users.email, email),
    });
  }

  async findMany(opts: {
    search?: string;
    isActive?: boolean;
    limit?: number;
    offset?: number;
  } = {}): Promise<User[]> {
    const { search, isActive, limit = 20, offset = 0 } = opts;

    const conditions = [];
    if (search)           conditions.push(like(users.name, `%${search}%`));
    if (isActive !== undefined) conditions.push(eq(users.isActive, isActive));

    return this.db
      .select()
      .from(users)
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(desc(users.createdAt))
      .limit(limit)
      .offset(offset);
  }

  async create(data: NewUser): Promise<User> {
    const [user] = await this.db
      .insert(users)
      .values(data)
      .returning();
    return user;
  }

  async update(id: number, data: Partial<NewUser>): Promise<User | undefined> {
    const [user] = await this.db
      .update(users)
      .set({ ...data, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  async delete(id: number): Promise<boolean> {
    const result = await this.db
      .delete(users)
      .where(eq(users.id, id))
      .returning({ id: users.id });
    return result.length > 0;
  }
}

// 利用例
const userRepo = new UserRepository(db);
const user = await userRepo.findByEmail("yamada@example.com");

import { describe, it, expect, beforeEach } from "vitest";
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import { migrate } from "drizzle-orm/libsql/migrator";
import * as schema from "../db/schema";
import { UserRepository } from "../repositories/user.repository";

// テスト用インメモリDB
const client = createClient({ url: ":memory:" });
const testDb = drizzle(client, { schema });

beforeEach(async () => {
  // マイグレーションを適用（テスト前にテーブルを作成）
  await migrate(testDb, { migrationsFolder: "./drizzle" });
});

describe("UserRepository", () => {
  it("ユーザーを作成して取得できる", async () => {
    const repo = new UserRepository(testDb);

    const created = await repo.create({
      name: "テストユーザー",
      email: "test@example.com",
    });

    expect(created.id).toBeDefined();
    expect(created.name).toBe("テストユーザー");

    const found = await repo.findById(created.id);
    expect(found).toEqual(created);
  });

  it("存在しないIDはundefinedを返す", async () => {
    const repo = new UserRepository(testDb);
    const found = await repo.findById(9999);
    expect(found).toBeUndefined();
  });
});