drizzle-orm

Drizzle ORM

压缩后约 7.4kb，无依赖，适用于无服务器环境。

快速开始

安装

# PostgreSQL
npm i drizzle-orm pg
npm i -D drizzle-kit @types/pg

# MySQL
npm i drizzle-orm mysql2
npm i -D drizzle-kit

# SQLite
npm i drizzle-orm better-sqlite3
npm i -D drizzle-kit @types/better-sqlite3

# Turso / LibSQL
npm i drizzle-orm @libsql/client
npm i -D drizzle-kit

# Bun SQL (PostgreSQL — zero extra deps)
bun add drizzle-orm
bun add -D drizzle-kit

# Bun SQLite (zero extra deps, sync APIs)
bun add drizzle-orm
bun add -D drizzle-kit

配置

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

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

Schema

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

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

export const posts = pgTable("posts", {
  id: serial().primaryKey(),
  title: text().notNull(),
  content: text(),
  authorId: integer("author_id").references(() => users.id),
});

连接与查询

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

const db = drizzle(process.env.DATABASE_URL!, { schema });

// select
const allUsers = await db.select().from(schema.users);

// insert
const [user] = await db.insert(schema.users)
  .values({ name: "Dan", email: "[email protected]" })
  .returning();

// update
await db.update(schema.users)
  .set({ name: "Daniel" })
  .where(eq(schema.users.id, 1));

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

所有提供商的配置方法（Neon、Turso、Supabase、D1 等），请参阅 references/connections.md。

Schema 声明

从特定方言的模块中导入表和列构建器：

// PG:     import { pgTable, serial, text, ... } from "drizzle-orm/pg-core";
// MySQL:  import { mysqlTable, int, varchar, ... } from "drizzle-orm/mysql-core";
// SQLite: import { sqliteTable, integer, text, ... } from "drizzle-orm/sqlite-core";

常见列类型（PG）

类型	用法	备注
serial()	自增主键	新项目请使用 integer().generatedAlwaysAsIdentity()
integer()	4 字节整数
bigint({ mode: "number" })	8 字节整数	超过 2^53 时使用 "bigint" 模式
text()	无长度限制的文本	使用 { enum: [...] } 定义 TS 联合类型
varchar({ length: n })	可变长度
boolean()	true/false
timestamp()	日期/时间	{ withTimezone: true }、mode: "date"
date()	日历日期	使用 mode: "date" 获取 JS Date
json() / jsonb()	JSON 数据	使用 .$type<T>() 指定类型
uuid()	UUID	使用 .defaultRandom() 调用 gen_random_uuid()
numeric({ precision, scale })	精确小数	默认返回字符串

约束修饰符

column.notNull()
column.default(value)
column.default(sql`now()`)
column.$defaultFn(() => createId())   // runtime default
column.$onUpdate(() => new Date())    // runtime on update
column.primaryKey()
column.unique()
column.references(() => other.id, { onDelete: "cascade" })
column.$type<CustomType>()            // branded types

自动映射大小写

// drizzle.config.ts
export default defineConfig({
  casing: "snake_case", // camelCase TS keys -> snake_case DB columns
});

完整的列类型目录：PG | MySQL | SQLite | MSSQL/CockroachDB/SingleStore

CRUD 操作

所有运算符均从 "drizzle-orm" 导入：

import { eq, ne, gt, gte, lt, lte, and, or, not, like, ilike, inArray, between, isNull, sql } from "drizzle-orm";

查询

// basic
await db.select().from(users);

// partial + where
await db.select({ id: users.id, name: users.name })
  .from(users)
  .where(and(eq(users.role, "admin"), gt(users.age, 18)))
  .orderBy(asc(users.name))
  .limit(10).offset(20);

// aggregation
await db.select({ role: users.role, count: sql<number>`count(*)` })
  .from(users).groupBy(users.role).having(gt(sql`count(*)`, 5));

插入

// single + returning
const [user] = await db.insert(users).values({ name: "Dan" }).returning();

// bulk
await db.insert(users).values([{ name: "A" }, { name: "B" }]);

// upsert (PG/SQLite)
await db.insert(users).values({ id: 1, name: "Dan" })
  .onConflictDoUpdate({ target: users.id, set: { name: "Dan" } });

// upsert (MySQL)
await db.insert(users).values({ id: 1, name: "Dan" })
  .onDuplicateKeyUpdate({ set: { name: "Dan" } });

更新 / 删除

await db.update(users).set({ name: "Jane" }).where(eq(users.id, 1)).returning();
await db.delete(users).where(eq(users.id, 1)).returning();

完整查询参考：references/queries.md

联接

// inner join
await db.select().from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// left join
await db.select().from(users)
  .leftJoin(orders, eq(users.id, orders.userId));

// self-join with alias
import { alias } from "drizzle-orm/pg-core";
const parent = alias(users, "parent");
await db.select().from(users).leftJoin(parent, eq(users.managerId, parent.id));

关系

V1（稳定版）

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

关系查询

将 schema 传递给 drizzle，以启用 db.query：

const db = drizzle(url, { schema });

const result = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: { posts: { with: { comments: true } } },
  where: (users, { eq }) => eq(users.id, 1),
  orderBy: (users, { desc }) => desc(users.createdAt),
  limit: 10,
});

const user = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.name, "Dan"),
  with: { posts: true },
});

V2（Beta 版）

import { defineRelations } from "drizzle-orm";

export const relations = defineRelations(schema, (r) => ({
  users: { posts: r.many.posts() },
  posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }) },
}));

V2 增加了 .through()，可实现多对多关系，无需编写连接表样板代码。

完整关系参考：references/relations.md

Drizzle Kit

命令	说明
npx drizzle-kit generate	根据 schema 差异创建 SQL 迁移文件
npx drizzle-kit migrate	应用待处理的迁移
npx drizzle-kit push	直接同步 schema（仅用于原型开发）
npx drizzle-kit pull	检查 DB 并生成 Drizzle schema
npx drizzle-kit check	验证迁移的一致性
npx drizzle-kit studio	可视化数据库浏览器

完整迁移参考：references/migrations.md

事务

const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ name: "Dan" }).returning();
  await tx.insert(accounts).values({ userId: user.id, balance: 0 });
  return user;
});

使用 tx.rollback() 回滚。嵌套事务会创建保存点。

完整参考：references/transactions-and-batch.md

原始 SQL

import { sql } from "drizzle-orm";

// typed expression
const lower = sql<string>`lower(${users.name})`;

// full raw query
await db.execute(sql`SELECT * FROM ${users} WHERE ${users.id} = ${id}`);

// placeholder for prepared statements
const prepared = db.select().from(users)
  .where(eq(users.id, sql.placeholder("id")))
  .prepare("get_user");
await prepared.execute({ id: 1 });

参考索引

主题	文件
PostgreSQL 列类型、枚举、模式、索引、视图、RLS	references/schema-postgresql.md
MySQL 列类型、枚举、索引、视图	references/schema-mysql.md
SQLite 列类型、索引、视图	references/schema-sqlite.md
MSSQL、CockroachDB、SingleStore 类型	references/column-types-minor-dialects.md
完整查询运算符、高级 select、连接、CTE、集合运算、$count	references/queries.md
Relations v1 + v2、关系查询 API	references/relations.md
Drizzle Kit 配置、命令、编程式迁移	references/migrations.md
事务、保存点、批处理 API	references/transactions-and-batch.md
drizzle-zod、drizzle-valibot、drizzle-typebox 等	references/schema-validation.md
各提供商的连接设置（Neon、Turso、Supabase、D1、PGlite、Expo 等）	references/connections.md
使用 drizzle-seed 进行数据库填充、版本控制	references/drizzle-seed.md
只读副本、自定义类型、缓存、ESLint、注意事项、drizzle-graphql	references/advanced-patterns.md