第五十六篇：Drizzle ORM 完全指南 —— 类型化数据库制图局宇宙

数据库表         = 城市地块
schema          = 城市总平面图
migration       = 施工变更单
query builder   = 规划路线的尺子和图钉
relations       = 地图上的道路连通关系
raw SQL         = 直接看经纬度自己开车

纯 SQL  <------  Drizzle  ------>  重抽象 ORM
控制力高                          自动化高
魔法少                            魔法多
SQL感强                           框架感强

Raw SQL
  你自己写 SQL、自己维护类型、自己做结果映射

Drizzle
  你用 TypeScript 声明 schema，用接近 SQL 的方式写查询，
  类型跟着 schema 和 select 结果走

Prisma
  你先写 Prisma 自己的 schema DSL，再生成 Prisma Client，
  查询体验更像“操作模型对象”

TypeORM
  你先写实体类和装饰器，依赖运行时元数据，
  查询可以走 repository，也可以转去 QueryBuilder

SQL 仍然是世界本体
TypeScript 负责把 SQL 世界精确投影到代码里
Drizzle 就是那台投影仪

                ┌──────────────────────┐
                │   TypeScript Schema  │
                │  表/列/约束/索引定义   │
                └──────────┬───────────┘
                           │
                  推导类型 │ 生成迁移/对比差异
                           │
          ┌────────────────┴──────────────┐
          │                               │
┌─────────▼─────────┐           ┌─────────▼─────────┐
│  Query Builder     │           │   Drizzle Kit     │
│ select/insert/...  │           │ generate/push/... │
└─────────┬─────────┘           └─────────┬─────────┘
          │                               │
          └──────────────┬────────────────┘
                         │
                 ┌───────▼───────┐
                 │   SQL 数据库   │
                 └───────────────┘

Schema-first：先定义数据库结构
TS-first：这份结构定义直接写在 TypeScript 中

Schema-first inside TypeScript
在 TypeScript 里做 schema-first

// src/db/schema.ts
import {
  pgTable,
  serial,
  text,
  integer,
  timestamp,
  primaryKey,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  body: text("body").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const comments = pgTable("comments", {
  id: serial("id").primaryKey(),
  postId: integer("post_id")
    .notNull()
    .references(() => posts.id, { onDelete: "cascade" }),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const tags = pgTable("tags", {
  id: serial("id").primaryKey(),
  name: text("name").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" }),
  },
  (t) => [primaryKey({ columns: [t.postId, t.tagId] })]
);

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 commentsRelations = relations(comments, ({ one }) => ({
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
}));

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

src/
  db/
    schema/
      users.ts
      posts.ts
      comments.ts
      tags.ts
      index.ts

// src/db/client.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, {
  schema,
  casing: "snake_case",
});

1. generate + migrate
   TS schema --> SQL migration files --> apply to DB

2. push
   TS schema --> diff --> direct apply to DB

3. pull
   existing DB --> introspect --> schema.ts

// drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

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

npx drizzle-kit generate
npx drizzle-kit migrate

npx drizzle-kit push

npx drizzle-kit pull

npx drizzle-kit generate --custom --name=seed-users

const createdUser = await db
  .insert(users)
  .values({
    name: "Lin",
    email: "lin@example.com",
  })
  .returning();

insert into users ... values ... returning ...

import { eq, desc } from "drizzle-orm";

const latestPosts = await db
  .select({
    id: posts.id,
    title: posts.title,
    authorName: users.name,
    createdAt: posts.createdAt,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(10);

const updated = await db
  .update(posts)
  .set({ title: "Drizzle ORM 深入理解" })
  .where(eq(posts.id, 1))
  .returning();

const deleted = await db
  .delete(comments)
  .where(eq(comments.id, 9))
  .returning();

import { sql } from "drizzle-orm";

const stats = await db
  .select({
    authorId: posts.authorId,
    postCount: sql<number>`count(${posts.id})`.mapWith(Number),
  })
  .from(posts)
  .groupBy(posts.authorId);

const userId = 42;

const result = await db.execute(
  sql`select * from ${users} where ${users.id} = ${userId}`
);

foreign key = 数据库层的硬约束
relations   = 应用层的导航关系

foreign key = 真实存在的道路护栏
relations   = 导航软件里的可通行路线

users (1)  --------<  posts (n)

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

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

posts (n)  ----< posts_to_tags >----  tags (n)

const feed = await db.query.posts.findMany({
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
    },
  },
});

import { desc, eq } from "drizzle-orm";

const list = await db
  .select({
    postId: posts.id,
    title: posts.title,
    authorName: users.name,
    createdAt: posts.createdAt,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(20);

const detail = await db.query.posts.findFirst({
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
    },
  },
});

import { sql } from "drizzle-orm";

const topAuthors = await db
  .select({
    authorId: users.id,
    authorName: users.name,
    postCount: sql<number>`count(${posts.id})`.mapWith(Number),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .orderBy(sql`count(${posts.id}) desc`);

列表 -> SQL-like builder
详情 -> relations query
统计 -> builder + sql

const query = db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .where(eq(users.name, "Lin")); // 类型报错

function withPagination<T extends typeof baseQuery>(qb: T, page = 1, pageSize = 10) {
  return qb.limit(pageSize).offset((page - 1) * pageSize);
}

const baseQuery = db.select().from(users).$dynamic();
const pagedQuery = withPagination(baseQuery, 2, 20);

Prisma：先写 Prisma 世界，再生成访问数据库的客户端
Drizzle：直接在 TypeScript 里画数据库地图，然后沿着 SQL 走

想让框架替你想很多，Drizzle 可能不够“厚”
想自己想清楚并且要类型系统全程跟随，Drizzle 很合适

团队是否接受 SQL 是一等公民？
  否 -> 更偏 Prisma / 更高抽象方案
  是 -> 继续

是否希望 schema 直接写在 TypeScript 中？
  否 -> 更偏 Prisma DSL / 传统 SQL migration 流
  是 -> 继续

是否希望复杂查询依然保留编译期类型约束？
  否 -> Raw SQL 也可以
  是 -> Drizzle 很有竞争力

数据库不是对象仓库，而是结构化地图
schema 是地图底稿
migration 是地图改版记录
query builder 是有类型的量尺
relations 是导航连线
sql`` 是保底也是增强