Back to Details

drizzle-pg

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Drizzle ORM — PostgreSQL

Drizzle ORM — PostgreSQL

Drizzle is a headless TypeScript ORM. Zero dependencies, SQL-like API, single-query output. Packages: 
drizzle-orm
(runtime), 
drizzle-kit
(CLI/migrations).
Drizzle是一个无依赖的TypeScript ORM工具。零依赖、类SQL API、单查询输出。 包：
drizzle-orm
（运行时）、
drizzle-kit
（CLI/迁移工具）。

Table of Contents

目录

Quick Start

快速开始

Connect

连接

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

const db = drizzle(process.env.DATABASE_URL, { schema, relations });
Or with existing Pool:
typescript
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool, schema, relations });
typescript
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";
import { relations } from "./relations";

const db = drizzle(process.env.DATABASE_URL, { schema, relations });
或使用已有的Pool：
typescript
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool, schema, relations });

Define Schema

定义模式

typescript
import {
  pgTable,
  pgEnum,
  serial,
  text,
  integer,
  timestamp,
  uuid,
  jsonb,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const statusEnum = pgEnum("status", ["active", "inactive", "banned"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id")
      .default(sql`gen_random_uuid()`)
      .primaryKey(),
    name: text("name").notNull(),
    email: text("email").notNull().unique(),
    status: statusEnum().default("active").notNull(),
    metadata: jsonb("metadata").$type<{ roles: string[] }>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [index("users_email_idx").on(t.email)],
);

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  authorId: uuid("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
});
typescript
import {
  pgTable,
  pgEnum,
  serial,
  text,
  integer,
  timestamp,
  uuid,
  jsonb,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const statusEnum = pgEnum("status", ["active", "inactive", "banned"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id")
      .default(sql`gen_random_uuid()`)
      .primaryKey(),
    name: text("name").notNull(),
    email: text("email").notNull().unique(),
    status: statusEnum().default("active").notNull(),
    metadata: jsonb("metadata").$type<{ roles: string[] }>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [index("users_email_idx").on(t.email)],
);

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

Define Relations

定义关系

typescript
import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }),
  },
  posts: {
    author: r.one.users({ from: r.posts.authorId, to: r.users.id }),
  },
}));
typescript
import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

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

CRUD

CRUD

typescript
import { eq, and, ilike, sql } from "drizzle-orm";

// SELECT
const allUsers = await db.select().from(users);
const user = await db.select().from(users).where(eq(users.id, id));

// INSERT
const [created] = await db
  .insert(users)
  .values({ name: "Dan", email: "dan@example.com" })
  .returning();

// UPDATE
await db.update(users).set({ name: "Updated" }).where(eq(users.id, id));

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

// UPSERT
await db
  .insert(users)
  .values({ id, name: "Dan", email: "dan@ex.com" })
  .onConflictDoUpdate({ target: users.id, set: { name: "Dan" } });
typescript
import { eq, and, ilike, sql } from "drizzle-orm";

// SELECT
const allUsers = await db.select().from(users);
const user = await db.select().from(users).where(eq(users.id, id));

// INSERT
const [created] = await db
  .insert(users)
  .values({ name: "Dan", email: "dan@example.com" })
  .returning();

// UPDATE
await db.update(users).set({ name: "Updated" }).where(eq(users.id, id));

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

// UPSERT
await db
  .insert(users)
  .values({ id, name: "Dan", email: "dan@ex.com" })
  .onConflictDoUpdate({ target: users.id, set: { name: "Dan" } });

Relational Queries

关系查询

typescript
// Nested eager loading (single SQL query)
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
  where: { status: "active" },
  orderBy: { createdAt: "desc" },
  limit: 10,
});

const user = await db.query.users.findFirst({
  where: { id: userId },
  with: { posts: { columns: { id: true, title: true } } },
});
typescript
// 嵌套预加载（单SQL查询）
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
  where: { status: "active" },
  orderBy: { createdAt: "desc" },
  limit: 10,
});

const user = await db.query.users.findFirst({
  where: { id: userId },
  with: { posts: { columns: { id: true, title: true } } },
});

Migrations

迁移

bash
undefined
bash
undefined

drizzle.config.ts -> see references/migrations.md

drizzle.config.ts -> 查看 references/migrations.md

npx drizzle-kit generate # schema diff -> SQL files npx drizzle-kit migrate # apply SQL to database npx drizzle-kit push # direct push (no SQL files) npx drizzle-kit pull # introspect DB -> Drizzle schema npx drizzle-kit studio # visual browser UI
undefined
npx drizzle-kit generate # 模式对比 -> SQL文件 npx drizzle-kit migrate # 将SQL应用到数据库 npx drizzle-kit push # 直接推送（无SQL文件） npx drizzle-kit pull # 反向解析数据库 -> Drizzle模式 npx drizzle-kit studio # 可视化浏览器UI
undefined

Import Cheat Sheet

导入速查表

Import pathKey exports
drizzle-orm/pg-core
pgTable
, 
pgEnum
, column types (
serial
, 
text
, 
integer
, 
uuid
, 
timestamp
, 
jsonb
, 
varchar
, 
boolean
, 
numeric
, 
bigint
, 
geometry
, 
vector
, ...), 
index
, 
uniqueIndex
, 
unique
, 
check
, 
primaryKey
, 
foreignKey
drizzle-orm
Operators: 
eq
, 
ne
, 
gt
, 
gte
, 
lt
, 
lte
, 
and
, 
or
, 
not
, 
isNull
, 
isNotNull
, 
inArray
, 
between
, 
like
, 
ilike
, 
exists
, 
sql
, 
asc
, 
desc
. Utilities: 
getColumns
, 
defineRelations
, 
cosineDistance
, 
l2Distance
drizzle-orm
(types)
InferSelectModel
, 
InferInsertModel
drizzle-zod
createInsertSchema
, 
createSelectSchema
导入路径主要导出内容
drizzle-orm/pg-core
pgTable
, 
pgEnum
, 列类型（
serial
, 
text
, 
integer
, 
uuid
, 
timestamp
, 
jsonb
, 
varchar
, 
boolean
, 
numeric
, 
bigint
, 
geometry
, 
vector
等）, 
index
, 
uniqueIndex
, 
unique
, 
check
, 
primaryKey
, 
foreignKey
drizzle-orm
运算符：
eq
, 
ne
, 
gt
, 
gte
, 
lt
, 
lte
, 
and
, 
or
, 
not
, 
isNull
, 
isNotNull
, 
inArray
, 
between
, 
like
, 
ilike
, 
exists
, 
sql
, 
asc
, 
desc
。工具：
getColumns
, 
defineRelations
, 
cosineDistance
, 
l2Distance
drizzle-orm
（类型）
InferSelectModel
, 
InferInsertModel
drizzle-zod
createInsertSchema
, 
createSelectSchema

Common Patterns

常见模式

Conditional filters

条件过滤

typescript
const filters: SQL[] = [];
if (name) filters.push(ilike(users.name, `%${name}%`));
if (status) filters.push(eq(users.status, status));
await db
  .select()
  .from(users)
  .where(and(...filters));
typescript
const filters: SQL[] = [];
if (name) filters.push(ilike(users.name, `%${name}%`));
if (status) filters.push(eq(users.status, status));
await db
  .select()
  .from(users)
  .where(and(...filters));

Transactions

事务

typescript
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ name: "Dan" }).returning();
  await tx.insert(posts).values({ title: "Hello", authorId: user.id });
});
typescript
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ name: "Dan" }).returning();
  await tx.insert(posts).values({ title: "Hello", authorId: user.id });
});

Type inference

类型推断

typescript
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;
typescript
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

Reference Files

参考文档

For detailed API coverage, see:
  • Column types, indexes, constraints, enums, PostGIS, pg_vector: references/schema-pg.md
  • Select, insert, update, delete, joins, filters: references/queries.md
  • Relations definition, relational query API (findMany/findFirst): references/relations.md
  • sql`` template: raw, empty, join, identifier, placeholders: references/sql-operator.md
  • drizzle-kit commands, drizzle.config.ts, migration workflows: references/migrations.md
  • Dynamic queries, transactions, custom types, Zod, utilities: references/advanced.md
如需详细API说明，请查看：
  • 列类型、索引、约束、枚举、PostGIS、pg_vector：references/schema-pg.md
  • 查询、插入、更新、删除、关联查询、过滤：references/queries.md
  • 关系定义、关系查询API（findMany/findFirst）：references/relations.md
  • sql``模板：原生SQL、空值、拼接、标识符、占位符：references/sql-operator.md
  • drizzle-kit命令、drizzle.config.ts、迁移工作流：references/migrations.md
  • 动态查询、事务、自定义类型、Zod、工具函数：references/advanced.md