drizzle-orm by mindrally/skills
npx skills add https://github.com/mindrally/skills --skill drizzle-orm您是一位精通 Drizzle ORM、TypeScript 和 SQL 数据库设计的专家,专注于类型安全和性能。
import { pgTable, serial, text, varchar, timestamp, boolean, integer } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: serial("id").primaryKey(),
email: varchar("email", { length: 255 }).notNull().unique(),
name: text("name"),
isActive: boolean("is_active").default(true),
createdAt: timestamp("created_at").defaultNow(),
updatedAt: timestamp("updated_at").defaultNow(),
});
export const posts = pgTable("posts", {
id: serial("id").primaryKey(),
title: varchar("title", { length: 255 }).notNull(),
content: text("content"),
authorId: integer("author_id").references(() => users.id),
publishedAt: timestamp("published_at"),
createdAt: timestamp("created_at").defaultNow(),
});
您可以通过多种方式组织模式:
// 选项 1:单一 schema.ts 文件(推荐用于小型项目)
// src/db/schema.ts
// 选项 2:按领域拆分(推荐用于大型项目)
// src/db/schema/users.ts
// src/db/schema/posts.ts
// src/db/schema/index.ts(重新导出所有内容)
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
使用 casing 选项进行自动的 camelCase 到 snake_case 映射:
import { drizzle } from "drizzle-orm/node-postgres";
const db = drizzle(pool, {
casing: "snake_case", // 自动将 camelCase 映射为 snake_case
});
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],
}),
}));
import { pgTable, serial, varchar, index, uniqueIndex } from "drizzle-orm/pg-core";
export const users = pgTable(
"users",
{
id: serial("id").primaryKey(),
email: varchar("email", { length: 255 }).notNull(),
name: varchar("name", { length: 255 }),
},
(table) => [
uniqueIndex("email_idx").on(table.email),
index("name_idx").on(table.name),
]
);
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 });
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";
const sqlite = new Database("sqlite.db");
export const db = drizzle(sqlite, { schema });
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";
const client = createClient({
url: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });
// 选择所有列
const allUsers = await db.select().from(users);
// 选择特定列
const userEmails = await db.select({ email: users.email }).from(users);
// 带条件
import { eq, and, or, gt, like } from "drizzle-orm";
const activeUsers = await db
.select()
.from(users)
.where(eq(users.isActive, true));
const filteredUsers = await db
.select()
.from(users)
.where(
and(
eq(users.isActive, true),
like(users.email, "%@example.com")
)
);
// 带关系的查询(需要定义了关系的模式)
const usersWithPosts = await db.query.users.findMany({
with: {
posts: true,
},
});
// 嵌套关系
const postsWithAuthor = await db.query.posts.findMany({
with: {
author: {
columns: {
id: true,
name: true,
},
},
},
});
// 单条插入
const newUser = await db
.insert(users)
.values({
email: "user@example.com",
name: "John Doe",
})
.returning();
// 批量插入
await db.insert(users).values([
{ email: "user1@example.com", name: "User 1" },
{ email: "user2@example.com", name: "User 2" },
]);
// 更新插入(冲突时插入或更新)
await db
.insert(users)
.values({ email: "user@example.com", name: "John" })
.onConflictDoUpdate({
target: users.email,
set: { name: "John Updated" },
});
await db
.update(users)
.set({ name: "Jane Doe", updatedAt: new Date() })
.where(eq(users.id, 1));
await db.delete(users).where(eq(users.id, 1));
await db.transaction(async (tx) => {
const [user] = await tx
.insert(users)
.values({ email: "user@example.com", name: "User" })
.returning();
await tx.insert(posts).values({
title: "First Post",
authorId: user.id,
});
});
# 根据模式更改生成迁移
npx drizzle-kit generate
# 将迁移应用到数据库
npx drizzle-kit migrate
# 直接推送模式(仅限开发环境)
npx drizzle-kit push
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
schema: "./src/db/schema.ts",
out: "./drizzle",
dialect: "postgresql",
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
import { InferSelectModel, InferInsertModel } from "drizzle-orm";
// 从表定义推断类型
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
// 在应用代码中使用
function createUser(data: NewUser): Promise<User> {
return db.insert(users).values(data).returning().then((r) => r[0]);
}
确保在 tsconfig.json 中启用严格模式:
{
"compilerOptions": {
"strict": true,
"strictNullChecks": true
}
}
始终为 WHERE 子句和 JOIN 中使用的列添加索引:
export const orders = pgTable(
"orders",
{
id: serial("id").primaryKey(),
userId: integer("user_id").notNull(),
status: varchar("status", { length: 50 }).notNull(),
createdAt: timestamp("created_at").defaultNow(),
},
(table) => [
index("user_id_idx").on(table.userId),
index("status_idx").on(table.status),
index("created_at_idx").on(table.createdAt),
]
);
// 不好:获取所有列
const users = await db.select().from(users);
// 好:仅获取需要的列
const userNames = await db
.select({ id: users.id, name: users.name })
.from(users);
const page = 1;
const pageSize = 20;
const paginatedUsers = await db
.select()
.from(users)
.limit(pageSize)
.offset((page - 1) * pageSize)
.orderBy(users.createdAt);
// 不好:N+1 查询模式
const users = await db.select().from(users);
for (const user of users) {
const posts = await db.select().from(posts).where(eq(posts.authorId, user.id));
}
// 好:使用关系查询或连接
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
每周安装量
157
代码仓库
GitHub 星标数
42
首次出现
2026年1月25日
安全审计
已安装于
gemini-cli133
opencode133
codex128
cursor121
github-copilot120
claude-code115
You are an expert in Drizzle ORM, TypeScript, and SQL database design with a focus on type safety and performance.
import { pgTable, serial, text, varchar, timestamp, boolean, integer } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: serial("id").primaryKey(),
email: varchar("email", { length: 255 }).notNull().unique(),
name: text("name"),
isActive: boolean("is_active").default(true),
createdAt: timestamp("created_at").defaultNow(),
updatedAt: timestamp("updated_at").defaultNow(),
});
export const posts = pgTable("posts", {
id: serial("id").primaryKey(),
title: varchar("title", { length: 255 }).notNull(),
content: text("content"),
authorId: integer("author_id").references(() => users.id),
publishedAt: timestamp("published_at"),
createdAt: timestamp("created_at").defaultNow(),
});
You can organize schemas in multiple ways:
// Option 1: Single schema.ts file (recommended for smaller projects)
// src/db/schema.ts
// Option 2: Split by domain (recommended for larger projects)
// src/db/schema/users.ts
// src/db/schema/posts.ts
// src/db/schema/index.ts (re-exports all)
Use the casing option for automatic camelCase to snake_case mapping:
import { drizzle } from "drizzle-orm/node-postgres";
const db = drizzle(pool, {
casing: "snake_case", // Automatically maps camelCase to snake_case
});
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],
}),
}));
import { pgTable, serial, varchar, index, uniqueIndex } from "drizzle-orm/pg-core";
export const users = pgTable(
"users",
{
id: serial("id").primaryKey(),
email: varchar("email", { length: 255 }).notNull(),
name: varchar("name", { length: 255 }),
},
(table) => [
uniqueIndex("email_idx").on(table.email),
index("name_idx").on(table.name),
]
);
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 });
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";
const sqlite = new Database("sqlite.db");
export const db = drizzle(sqlite, { schema });
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";
const client = createClient({
url: process.env.TURSO_DATABASE_URL!,
authToken: process.env.TURSO_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });
// Select all columns
const allUsers = await db.select().from(users);
// Select specific columns
const userEmails = await db.select({ email: users.email }).from(users);
// With conditions
import { eq, and, or, gt, like } from "drizzle-orm";
const activeUsers = await db
.select()
.from(users)
.where(eq(users.isActive, true));
const filteredUsers = await db
.select()
.from(users)
.where(
and(
eq(users.isActive, true),
like(users.email, "%@example.com")
)
);
// Query with relations (requires schema with relations defined)
const usersWithPosts = await db.query.users.findMany({
with: {
posts: true,
},
});
// Nested relations
const postsWithAuthor = await db.query.posts.findMany({
with: {
author: {
columns: {
id: true,
name: true,
},
},
},
});
// Single insert
const newUser = await db
.insert(users)
.values({
email: "user@example.com",
name: "John Doe",
})
.returning();
// Bulk insert
await db.insert(users).values([
{ email: "user1@example.com", name: "User 1" },
{ email: "user2@example.com", name: "User 2" },
]);
// Upsert (insert or update on conflict)
await db
.insert(users)
.values({ email: "user@example.com", name: "John" })
.onConflictDoUpdate({
target: users.email,
set: { name: "John Updated" },
});
await db
.update(users)
.set({ name: "Jane Doe", updatedAt: new Date() })
.where(eq(users.id, 1));
await db.delete(users).where(eq(users.id, 1));
await db.transaction(async (tx) => {
const [user] = await tx
.insert(users)
.values({ email: "user@example.com", name: "User" })
.returning();
await tx.insert(posts).values({
title: "First Post",
authorId: user.id,
});
});
# Generate migration based on schema changes
npx drizzle-kit generate
# Apply migrations to database
npx drizzle-kit migrate
# Push schema directly (development only)
npx drizzle-kit push
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
schema: "./src/db/schema.ts",
out: "./drizzle",
dialect: "postgresql",
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
import { InferSelectModel, InferInsertModel } from "drizzle-orm";
// Infer types from table definitions
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
// Use in application code
function createUser(data: NewUser): Promise<User> {
return db.insert(users).values(data).returning().then((r) => r[0]);
}
Ensure strict mode is enabled in tsconfig.json:
{
"compilerOptions": {
"strict": true,
"strictNullChecks": true
}
}
Always add indexes for columns used in WHERE clauses and JOINs:
export const orders = pgTable(
"orders",
{
id: serial("id").primaryKey(),
userId: integer("user_id").notNull(),
status: varchar("status", { length: 50 }).notNull(),
createdAt: timestamp("created_at").defaultNow(),
},
(table) => [
index("user_id_idx").on(table.userId),
index("status_idx").on(table.status),
index("created_at_idx").on(table.createdAt),
]
);
// Bad: Fetches all columns
const users = await db.select().from(users);
// Good: Fetches only needed columns
const userNames = await db
.select({ id: users.id, name: users.name })
.from(users);
const page = 1;
const pageSize = 20;
const paginatedUsers = await db
.select()
.from(users)
.limit(pageSize)
.offset((page - 1) * pageSize)
.orderBy(users.createdAt);
// Bad: N+1 query pattern
const users = await db.select().from(users);
for (const user of users) {
const posts = await db.select().from(posts).where(eq(posts.authorId, user.id));
}
// Good: Use relational queries or joins
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
Weekly Installs
157
Repository
GitHub Stars
42
First Seen
Jan 25, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
gemini-cli133
opencode133
codex128
cursor121
github-copilot120
claude-code115
Tailwind CSS v4 + shadcn/ui 生产级技术栈配置指南与最佳实践
2,600 周安装
币安查询代币信息API:搜索代币、获取元数据、实时价格、K线数据
2,400 周安装
Tavily Extract:从URL提取Markdown/文本内容,支持JS页面和批量处理
2,500 周安装
PPT视觉设计助手 | AI幻灯片布局、配色方案与视觉优化工具
2,600 周安装
Windows Flutter开发环境配置指南:从安装到打包分发完整教程
3,000 周安装
Flutter Linux环境配置指南:在Ubuntu/Debian系统安装Flutter开发工具链
3,000 周安装
Flutter macOS 环境配置指南:Xcode 与 CocoaPods 安装与验证
3,100 周安装