Back to Details

drizzle-migrations

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Drizzle ORM Migrations

Drizzle ORM 迁移

This skill helps you manage database schema changes using Drizzle ORM with SQLite.
本技能可帮助你使用Drizzle ORM结合SQLite管理数据库Schema变更。

When to Use

适用场景

✅ USE this skill for:
  • Adding new tables or modifying existing columns
  • Generating and running database migrations
  • Drizzle-specific query patterns and relations
  • SQLite schema best practices with Drizzle
  • Setting up Drizzle configuration
❌ DO NOT use for:
  • Supabase/PostgreSQL → use 
    supabase-admin
    skill
  • Raw SQL without Drizzle → use standard SQL resources
  • Prisma ORM → different syntax and patterns
  • General database design theory → use database architecture resources
✅ 推荐使用本技能的场景：
  • 添加新表或修改现有列
  • 生成并执行数据库迁移
  • Drizzle专属查询模式与关联关系
  • 结合Drizzle的SQLite Schema最佳实践
  • 配置Drizzle环境
❌ 请勿使用本技能的场景：
  • Supabase/PostgreSQL → 请使用
    supabase-admin
    技能
  • 不借助Drizzle的原生SQL → 请使用标准SQL资源
  • Prisma ORM → 语法与模式不同
  • 通用数据库设计理论 → 请使用数据库架构相关资源

Project Setup

项目设置

Configuration: 
drizzle.config.ts
typescript
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: './data/app.db',
  },
});
Commands:
bash
npm run db:generate  # Generate migration files
npm run db:push      # Push schema directly (dev only)
npm run db:studio    # Open Drizzle Studio GUI
配置文件：
drizzle.config.ts
typescript
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: './data/app.db',
  },
});
命令：
bash
npm run db:generate  # 生成迁移文件
npm run db:push      # 直接推送Schema（仅开发环境使用）
npm run db:studio    # 打开Drizzle Studio图形界面

Schema Definition

Schema定义

Location: 
src/db/schema.ts
文件位置：
src/db/schema.ts

Table Definition

表定义

typescript
import { sqliteTable, text, integer, real, blob } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

// Basic table
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').notNull(),
  passwordHash: text('password_hash'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at'),
});

// Table with foreign key
export const checkIns = sqliteTable('check_ins', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  mood: integer('mood').notNull(),
  cravingLevel: integer('craving_level').notNull(),
  sleepHours: real('sleep_hours'),
  notes: text('notes'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// Table with composite index
export const auditLog = sqliteTable('audit_log', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull(),
  action: text('action').notNull(),
  targetType: text('target_type'),
  targetId: text('target_id'),
  details: text('details'),  // JSON string
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
}, (table) => ({
  userActionIdx: index('idx_audit_user_action').on(table.userId, table.action),
  createdAtIdx: index('idx_audit_created').on(table.createdAt),
}));
typescript
import { sqliteTable, text, integer, real, blob } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

// 基础表
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').notNull(),
  passwordHash: text('password_hash'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at'),
});

// 带外键的表
export const checkIns = sqliteTable('check_ins', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  mood: integer('mood').notNull(),
  cravingLevel: integer('craving_level').notNull(),
  sleepHours: real('sleep_hours'),
  notes: text('notes'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// 带复合索引的表
export const auditLog = sqliteTable('audit_log', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull(),
  action: text('action').notNull(),
  targetType: text('target_type'),
  targetId: text('target_id'),
  details: text('details'),  // JSON字符串
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
}, (table) => ({
  userActionIdx: index('idx_audit_user_action').on(table.userId, table.action),
  createdAtIdx: index('idx_audit_created').on(table.createdAt),
}));

Relations

关联关系

typescript
export const usersRelations = relations(users, ({ many }) => ({
  checkIns: many(checkIns),
  sessions: many(sessions),
  journalEntries: many(journalEntries),
}));

export const checkInsRelations = relations(checkIns, ({ one }) => ({
  user: one(users, {
    fields: [checkIns.userId],
    references: [users.id],
  }),
}));
typescript
export const usersRelations = relations(users, ({ many }) => ({
  checkIns: many(checkIns),
  sessions: many(sessions),
  journalEntries: many(journalEntries),
}));

export const checkInsRelations = relations(checkIns, ({ one }) => ({
  user: one(users, {
    fields: [checkIns.userId],
    references: [users.id],
  }),
}));

Column Types

列类型

SQLite Types in Drizzle

Drizzle中的SQLite类型

typescript
import {
  sqliteTable,
  text,           // TEXT - strings, JSON, dates
  integer,        // INTEGER - numbers, booleans (0/1)
  real,           // REAL - floating point
  blob,           // BLOB - binary data
} from 'drizzle-orm/sqlite-core';

const examples = sqliteTable('examples', {
  // Strings
  name: text('name').notNull(),
  description: text('description'),

  // Numbers
  count: integer('count').notNull().default(0),
  rating: real('rating'),

  // Booleans (stored as 0/1)
  isActive: integer('is_active', { mode: 'boolean' }).default(true),

  // Dates (stored as ISO strings)
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  expiresAt: text('expires_at'),

  // JSON (stored as TEXT)
  metadata: text('metadata', { mode: 'json' }),

  // Enums (stored as TEXT)
  status: text('status', { enum: ['pending', 'active', 'archived'] }),
});
typescript
import {
  sqliteTable,
  text,           // TEXT - 字符串、JSON、日期
  integer,        // INTEGER - 数字、布尔值(0/1)
  real,           // REAL - 浮点数
  blob,           // BLOB - 二进制数据
} from 'drizzle-orm/sqlite-core';

const examples = sqliteTable('examples', {
  // 字符串
  name: text('name').notNull(),
  description: text('description'),

  // 数字
  count: integer('count').notNull().default(0),
  rating: real('rating'),

  // 布尔值（存储为0/1）
  isActive: integer('is_active', { mode: 'boolean' }).default(true),

  // 日期（存储为ISO字符串）
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  expiresAt: text('expires_at'),

  // JSON（存储为TEXT）
  metadata: text('metadata', { mode: 'json' }),

  // 枚举（存储为TEXT）
  status: text('status', { enum: ['pending', 'active', 'archived'] }),
});

Migration Strategies

迁移策略

Strategy 1: Push (Development Only)

策略1：直接推送（仅开发环境）

bash
npm run db:push
  • Directly applies schema changes
  • Fast for development
  • Never use in production
bash
npm run db:push
  • 直接应用Schema变更
  • 开发环境下速度快
  • 切勿在生产环境使用

Strategy 2: Generate & Migrate (Production)

策略2：生成并执行迁移（生产环境）

bash
undefined
bash
undefined

1. Generate migration file

1. 生成迁移文件

npm run db:generate
npm run db:generate

2. Review generated SQL in /drizzle folder

2. 检查/drizzle文件夹中生成的SQL

3. Apply migration (in code or manually)

3. 执行迁移（代码中或手动执行）

undefined
undefined

Applying Migrations in Code

在代码中执行迁移

typescript
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

const sqlite = new Database('./data/app.db');
const db = drizzle(sqlite);

// Run migrations
migrate(db, { migrationsFolder: './drizzle' });
typescript
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

const sqlite = new Database('./data/app.db');
const db = drizzle(sqlite);

// 执行迁移
migrate(db, { migrationsFolder: './drizzle' });

Common Schema Changes

常见Schema变更

Adding a New Table

添加新表

typescript
// 1. Add to schema.ts
export const newFeature = sqliteTable('new_feature', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id),
  name: text('name').notNull(),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// 2. Add relations
export const newFeatureRelations = relations(newFeature, ({ one }) => ({
  user: one(users, {
    fields: [newFeature.userId],
    references: [users.id],
  }),
}));

// 3. Generate migration
// npm run db:generate
typescript
// 1. 添加到schema.ts
export const newFeature = sqliteTable('new_feature', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id),
  name: text('name').notNull(),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// 2. 添加关联关系
export const newFeatureRelations = relations(newFeature, ({ one }) => ({
  user: one(users, {
    fields: [newFeature.userId],
    references: [users.id],
  }),
}));

// 3. 生成迁移
// npm run db:generate

Adding a Column

添加列

typescript
// In schema.ts, add the new column
export const users = sqliteTable('users', {
  // existing columns...
  newColumn: text('new_column'),  // Add this
});

// Generate migration
// npm run db:generate
typescript
// 在schema.ts中添加新列
export const users = sqliteTable('users', {
  // 现有列...
  newColumn: text('new_column'),  // 添加此行
});

// 生成迁移
// npm run db:generate

Adding an Index

添加索引

typescript
export const messages = sqliteTable('messages', {
  id: text('id').primaryKey(),
  conversationId: text('conversation_id').notNull(),
  createdAt: text('created_at').notNull(),
}, (table) => ({
  // Add index
  convCreatedIdx: index('idx_messages_conv_created')
    .on(table.conversationId, table.createdAt),
}));
typescript
export const messages = sqliteTable('messages', {
  id: text('id').primaryKey(),
  conversationId: text('conversation_id').notNull(),
  createdAt: text('created_at').notNull(),
}, (table) => ({
  // 添加索引
  convCreatedIdx: index('idx_messages_conv_created')
    .on(table.conversationId, table.createdAt),
}));

Renaming (Requires Manual SQL)

重命名（需手动编写SQL）

SQLite doesn't support direct column renames in older versions. For complex changes:
sql
-- drizzle/XXXX_rename_column.sql
-- Manual migration for column rename

-- 1. Create new table with desired schema
CREATE TABLE users_new (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,  -- renamed from username
  created_at TEXT NOT NULL
);

-- 2. Copy data
INSERT INTO users_new SELECT id, email, username, created_at FROM users;

-- 3. Drop old table
DROP TABLE users;

-- 4. Rename new table
ALTER TABLE users_new RENAME TO users;
旧版本SQLite不支持直接重命名列。对于复杂变更：
sql
-- drizzle/XXXX_rename_column.sql
-- 用于列重命名的手动迁移

-- 1. 创建包含目标Schema的新表
CREATE TABLE users_new (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,  -- 从username重命名而来
  created_at TEXT NOT NULL
);

-- 2. 复制数据
INSERT INTO users_new SELECT id, email, username, created_at FROM users;

-- 3. 删除旧表
DROP TABLE users;

-- 4. 重命名新表
ALTER TABLE users_new RENAME TO users;

Query Patterns

查询模式

Basic Queries

基础查询

typescript
import { db } from '@/db';
import { eq, and, or, desc, asc, like, gte, lte } from 'drizzle-orm';
import { users, checkIns } from '@/db/schema';

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

// Select with conditions
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// Select specific columns
const userEmails = await db
  .select({ id: users.id, email: users.email })
  .from(users);

// Complex where clause
const results = await db
  .select()
  .from(checkIns)
  .where(
    and(
      eq(checkIns.userId, userId),
      gte(checkIns.createdAt, startDate),
      lte(checkIns.createdAt, endDate)
    )
  )
  .orderBy(desc(checkIns.createdAt))
  .limit(30);
typescript
import { db } from '@/db';
import { eq, and, or, desc, asc, like, gte, lte } from 'drizzle-orm';
import { users, checkIns } from '@/db/schema';

// 查询所有数据
const allUsers = await db.select().from(users);

// 带条件查询
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// 查询指定列
const userEmails = await db
  .select({ id: users.id, email: users.email })
  .from(users);

// 复杂条件查询
const results = await db
  .select()
  .from(checkIns)
  .where(
    and(
      eq(checkIns.userId, userId),
      gte(checkIns.createdAt, startDate),
      lte(checkIns.createdAt, endDate)
    )
  )
  .orderBy(desc(checkIns.createdAt))
  .limit(30);

Insert

插入数据

typescript
// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    id: generateId(),
    email: 'user@example.com',
    username: 'newuser',
  })
  .returning();

// Bulk insert
await db.insert(checkIns).values([
  { id: '1', userId, mood: 7, cravingLevel: 2 },
  { id: '2', userId, mood: 8, cravingLevel: 1 },
]);

// Upsert (insert or update)
await db
  .insert(users)
  .values({ id: 'user-1', email: 'new@example.com' })
  .onConflictDoUpdate({
    target: users.id,
    set: { email: 'new@example.com' },
  });
typescript
// 单条插入
const [newUser] = await db
  .insert(users)
  .values({
    id: generateId(),
    email: 'user@example.com',
    username: 'newuser',
  })
  .returning();

// 批量插入
await db.insert(checkIns).values([
  { id: '1', userId, mood: 7, cravingLevel: 2 },
  { id: '2', userId, mood: 8, cravingLevel: 1 },
]);

// 插入或更新（Upsert）
await db
  .insert(users)
  .values({ id: 'user-1', email: 'new@example.com' })
  .onConflictDoUpdate({
    target: users.id,
    set: { email: 'new@example.com' },
  });

Update

更新数据

typescript
await db
  .update(users)
  .set({ username: 'newname', updatedAt: new Date().toISOString() })
  .where(eq(users.id, userId));
typescript
await db
  .update(users)
  .set({ username: 'newname', updatedAt: new Date().toISOString() })
  .where(eq(users.id, userId));

Delete

删除数据

typescript
// Always use WHERE clause!
await db
  .delete(checkIns)
  .where(eq(checkIns.id, checkInId));

// Delete with multiple conditions
await db
  .delete(sessions)
  .where(
    and(
      eq(sessions.userId, userId),
      lte(sessions.expiresAt, new Date().toISOString())
    )
  );
typescript
// 务必使用WHERE子句！
await db
  .delete(checkIns)
  .where(eq(checkIns.id, checkInId));

// 多条件删除
await db
  .delete(sessions)
  .where(
    and(
      eq(sessions.userId, userId),
      lte(sessions.expiresAt, new Date().toISOString())
    )
  );

Joins

联表查询

typescript
const userWithCheckIns = await db
  .select({
    user: users,
    checkIn: checkIns,
  })
  .from(users)
  .leftJoin(checkIns, eq(users.id, checkIns.userId))
  .where(eq(users.id, userId));
typescript
const userWithCheckIns = await db
  .select({
    user: users,
    checkIn: checkIns,
  })
  .from(users)
  .leftJoin(checkIns, eq(users.id, checkIns.userId))
  .where(eq(users.id, userId));

Aggregations

聚合查询

typescript
import { count, avg, sum, max, min } from 'drizzle-orm';

const stats = await db
  .select({
    totalCheckIns: count(),
    avgMood: avg(checkIns.mood),
    maxStreak: max(checkIns.streak),
  })
  .from(checkIns)
  .where(eq(checkIns.userId, userId));
typescript
import { count, avg, sum, max, min } from 'drizzle-orm';

const stats = await db
  .select({
    totalCheckIns: count(),
    avgMood: avg(checkIns.mood),
    maxStreak: max(checkIns.streak),
  })
  .from(checkIns)
  .where(eq(checkIns.userId, userId));

Best Practices

最佳实践

  1. Always use transactions for related changes
typescript
await db.transaction(async (tx) => {
  await tx.insert(users).values(userData);
  await tx.insert(profiles).values(profileData);
});
  1. Always include WHERE on DELETE/UPDATE
  2. Use indexes for frequently queried columns
  3. Store dates as ISO strings for SQLite
  4. Use 
    returning()
    to get inserted/updated rows
  5. Generate migrations, don't push to production
  1. 关联变更务必使用事务
typescript
await db.transaction(async (tx) => {
  await tx.insert(users).values(userData);
  await tx.insert(profiles).values(profileData);
});
  1. 删除/更新操作务必添加WHERE子句
  2. 为频繁查询的列添加索引
  3. 在SQLite中以ISO字符串格式存储日期
  4. 使用
    returning()
    获取插入/更新后的行数据
  5. 生成迁移文件，切勿直接推送到生产环境

References

参考资料