lobehub/drizzle

drizzle 是做什么的？

LobeHub Drizzle ORM 架构和查询样式。适用于 pgTable 架构、索引、连接、推断类型、db.select/db.query、架构字段、外键、连接表或 Postgres 查询模式。

表定义

使用 pgTable 定义表，并通过 inferSelectSchema 和 inferInsertSchema 推断类型。

import { pgTable, uuid, text, timestamp, integer, boolean, jsonb, foreignKey, uniqueIndex } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  age: integer('age'),
  isActive: boolean('is_active').default(true),
  metadata: jsonb('metadata').$type<Record<string, unknown>>().default({}),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// 推断类型
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

索引和约束

import { index, uniqueIndex, foreignKey } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  content: text('content'),
  authorId: uuid('author_id').notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  titleIdx: index('title_idx').on(table.title),
  authorIdx: index('author_idx').on(table.authorId),
  uniqueSlugIdx: uniqueIndex('unique_slug_idx').on(table.slug),
  authorFk: foreignKey({
    columns: [table.authorId],
    foreignColumns: [users.id],
    name: 'fk_post_author'
  }),
}));

连接表

export const categories = pgTable('categories', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
});

export const postCategories = pgTable('post_categories', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  categoryId: uuid('category_id').notNull().references(() => categories.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.categoryId] }),
}));

查询模式

使用 db.select

// 基础查询
const allUsers = await db.select().from(users);

// 带过滤器的查询
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.isActive, true));

// 连接查询
const postsWithAuthor = await db.select({
  post: posts,
  author: users,
})
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

// 聚合查询
import { count, avg } from 'drizzle-orm';
const userStats = await db.select({
  totalUsers: count(),
  averageAge: avg(users.age),
})
  .from(users);

使用 db.query

// 通过主键查找
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
});

// 带关系的查找
const postWithRelations = await db.query.posts.findFirst({
  where: eq(posts.id, postId),
  with: {
    author: true,
    categories: {
      with: {
        category: true,
      },
    },
  },
});

// 列表查询
const recentPosts = await db.query.posts.findMany({
  limit: 10,
  orderBy: [desc(posts.createdAt)],
  where: isNotNull(posts.publishedAt),
});

插入和更新

// 插入
const newUser = await db.insert(users).values({
  name: 'John Doe',
  email: '[email protected]',
}).returning();

// 批量插入
await db.insert(users).values([
  { name: 'User 1', email: '[email protected]' },
  { name: 'User 2', email: '[email protected]' },
]);

// 更新
await db.update(users)
  .set({ name: 'Jane Doe', updatedAt: new Date() })
  .where(eq(users.id, userId));

// 删除
await db.delete(users)
  .where(eq(users.id, userId));

可选字段和默认值

export const products = pgTable('products', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  description: text('description'), // 可选字段
  price: integer('price').notNull(),
  discount: integer('discount').default(0),
  isAvailable: boolean('is_available').default(true),
  attributes: jsonb('attributes').$type<Record<string, unknown>>().default({}),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

生成的列

// Drizzle Kit 支持生成列（PostgreSQL 15+）
export const orders = pgTable('orders', {
  id: uuid('id').defaultRandom().primaryKey(),
  subtotal: integer('subtotal').notNull(),
  tax: integer('tax').notNull(),
  total: integer('total').generatedAlwaysAs().notNull(), // 计算列
});