跳到主内容
智客 ZICQ

技能库 智客分类:运维与云 database-migrations

Database Migrations

在PostgreSQL、MySQL和常见的ORMs(Prisma、Drizzle、Kysely、Django、TypeORM、Golang-migrate)之间,数据库迁移最佳做法用于计划变化、数据迁移、回滚和零下架部署。 在写入一个计划或数据迁移、规划回滚或瞄准零下调部署时使用.

10108 安装量

官方网址:skills.sh

技能介绍

先看中文介绍;官方 description 原文单独保留,不改写 SKILL.md。

做什么

在PostgreSQL、MySQL和普通ORMs(Prisma、Drizzle、Kysely、Django、TypeORM、Golang-migrate)之间,数据库迁移最佳做法用于计划变化、数据迁移、回滚和零下调部署

何时用

编写计划或数据迁移,规划回滚,或旨在零下调部署

代理如何加载

按 Agent Skills 渐进披露:启动时只加载 name 与 description(约 100 token);任务匹配后才读入整份 SKILL.md 正文;scripts/、references/、assets/ 仅在需要时再读。 本文件正文结构:Database Migration Patterns、When to Activate、Core Principles、Migration Safety Checklist、PostgreSQL Patterns、Adding a Column Safely。 其中含规范建议的小节:分步指令。

文件分析

文件分析:这是一份仅含 SKILL.md 的指令型技能,代理激活后整份正文进入上下文。

官方 description(原文)

Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate). Use when writing a schema or data migration, planning a rollback, or aiming for zero-downtime deployment.

Database Migration PatternsWhen to ActivateCore PrinciplesMigration Safety ChecklistPostgreSQL PatternsAdding a Column SafelyAdding an Index Without DowntimeRenaming a Column (Zero-Downtime)Removing a Column SafelyLarge Data MigrationsPrisma (TypeScript/Node.js)Workflow

来源分类:skills.sh agent-skill

SKILL.md 与 Agent 调用

官方规范 ↗
name
database-migrations
description
Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate). Use when writing a schema or data migration, planning a rollback, or aiming for zero-downtime deployment.
  1. 发现技能客户端向 Agent 提供名称与描述目录。
  2. 匹配与调用用户指定或任务匹配后,载入 SKILL.md 指令。
  3. 按需加载按步骤读取参考文档、使用脚本与素材。

具体调用语法与可用工具以目标 Agent 客户端为准。 查看调用机制说明 ↗

安装这个技能

Skills CLI ↗

先选择目标 Agent 和安装范围,保留技能包的附属文件,安装后检查客户端能否发现该技能。

交给 Agent 安装

复制安装指令给支持 Agent Skills 的代理,确认其中的目标目录与客户端匹配。

把 Agent Skill「database-migrations」安装到我的项目:SKILL.md 原文与官方 description 见 https://zicq.com/zh/skills/skl-0fdc8bd38f789b2a-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%81%E7%A7%BB.html
请存为 .cursor/skills/database-migrations/SKILL.md 或 .claude/skills/database-migrations/SKILL.md,frontmatter 的 name 与 description 保持原样,不要改写。

GitHub 完整包 ↗

终端安装 · Skills CLI

需要 Node.js 与 npx。先查看仓库技能列表,确认实际名称。

npx skills add 'https://github.com/affaan-m/ecc' --list

npx skills add 'https://github.com/affaan-m/ecc' --skill 'database-migrations'

CLI 会交互选择目标 Agent,默认安装到项目;用户级安装使用 -g。先通过查看命令核对仓库内容,再用 npx skills list 检查已安装技能。

阅读排版
--- name: database-migrations description: Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate). Use when writing a schema or data migration, planning a rollback, or aiming for zero-downtime deployment. metadata: origin: ECC --- # Database Migration Patterns Safe, reversible database schema changes for production systems. ## When to Activate - Creating or altering database tables - Adding/removing columns or indexes - Running data migrations (backfill, transform) - Planning zero-downtime schema changes - Setting up migration tooling for a new project ## Core Principles 1. **Every change is a migration** — never alter production databases manually 2. **Migrations are forward-only in production** — rollbacks use new forward migrations 3. **Schema and data migrations are separate** — never mix DDL and DML in one migration 4. **Test migrations against production-sized data** — a migration that works on 100 rows may lock on 10M 5. **Migrations are immutable once deployed** — never edit a migration that has run in production ## Migration Safety Checklist Before applying any migration: - [ ] Migration has both UP and DOWN (or is explicitly marked irreversible) - [ ] No full table locks on large tables (use concurrent operations) - [ ] New columns have defaults or are nullable (never add NOT NULL without default) - [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables) - [ ] Data backfill is a separate migration from schema change - [ ] Tested against a copy of production data - [ ] Rollback plan documented ## PostgreSQL Patterns ### Adding a Column Safely ```sql -- GOOD: Nullable column, no lock ALTER TABLE users ADD COLUMN avatar_url TEXT; -- GOOD: Column with default (Postgres 11+ is instant, no rewrite) ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true; -- BAD: NOT NULL without default on existing table (requires full rewrite) ALTER TABLE users ADD COLUMN role TEXT NOT NULL; -- This locks the table and rewrites every row ``` ### Adding an Index Without Downtime ```sql -- BAD: Blocks writes on large tables CREATE INDEX idx_users_email ON users (email); -- GOOD: Non-blocking, allows concurrent writes CREATE INDEX CONCURRENTLY idx_users_email ON users (email); -- Note: CONCURRENTLY cannot run inside a transaction block -- Most migration tools need special handling for this ``` ### Renaming a Column (Zero-Downtime) Never rename directly in production. Use the expand-contract pattern: ```sql -- Step 1: Add new column (migration 001) ALTER TABLE users ADD COLUMN display_name TEXT; -- Step 2: Backfill data (migration 002, data migration) UPDATE users SET display_name = username WHERE display_name IS NULL; -- Step 3: Update application code to read/write both columns -- Deploy application changes -- Step 4: Stop writing to old column, drop it (migration 003) ALTER TABLE users DROP COLUMN username; ``` ### Removing a Column Safely ```sql -- Step 1: Remove all application references to the column -- Step 2: Deploy application without the column reference -- Step 3: Drop column in next migration ALTER TABLE orders DROP COLUMN legacy_status; -- For Django: use SeparateDatabaseAndState to remove from model -- without generating DROP COLUMN (then drop in next migration) ``` ### Large Data Migrations ```sql -- BAD: Updates all rows in one transaction (locks table) UPDATE users SET normalized_email = LOWER(email); -- GOOD: Batch update with progress DO $$ DECLARE batch_size INT := 10000; rows_updated INT; BEGIN LOOP UPDATE users SET normalized_email = LOWER(email) WHERE id IN ( SELECT id FROM users WHERE normalized_email IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS rows_updated = ROW_COUNT; RAISE NOTICE 'Updated % rows', rows_updated; EXIT WHEN rows_updated = 0; COMMIT; END LOOP; END $$; ``` ## Prisma (TypeScript/Node.js) ### Workflow ```bash # Create migration from schema changes npx prisma migrate dev --name add_user_avatar # Apply pending migrations in production npx prisma migrate deploy # Reset database (dev only) npx prisma migrate reset # Generate client after schema changes npx prisma generate ``` ### Schema Example ```prisma model User { id String @id @default(cuid()) email String @unique name String? avatarUrl String? @map("avatar_url") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") orders Order[] @@map("users") @@index([email]) } ``` ### Custom SQL Migration For operations Prisma cannot express (concurrent indexes, data backfills): ```bash # Create empty migration, then edit the SQL manually npx prisma migrate dev --create-only --name add_email_index ``` ```sql -- migrations/20240115_add_email_index/migration.sql -- Prisma cannot generate CONCURRENTLY, so we write it manually CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email); ``` ## Drizzle (TypeScript/Node.js) ### Workflow ```bash # Generate migration from schema changes npx drizzle-kit generate # Apply migrations npx drizzle-kit migrate # Push schema directly (dev only, no migration file) npx drizzle-kit push ``` ### Schema Example ```typescript import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core"; export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), email: text("email").notNull().unique(), name: text("name"), isActive: boolean("is_active").notNull().default(true), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); ``` ## Kysely (TypeScript/Node.js) ### Workflow (kysely-ctl) ```bash # Initialize config file (kysely.config.ts) kysely init # Create a new migration file kysely migrate make add_user_avatar # Apply all pending migrations kysely migrate latest # Rollback last migration kysely migrate down # Show migration status kysely migrate list ``` ### Migration File ```typescript // migrations/2024_01_15_001_create_user_profile.ts import { type Kysely, sql } from 'kysely' // IMPORTANT: Always use Kysely, not your typed DB interface. // Migrations are frozen in time and must not depend on current schema types. export async function up(db: Kysely): Promise { await db.schema .createTable('user_profile') .addColumn('id', 'serial', (col) => col.primaryKey()) .addColumn('email', 'varchar(255)', (col) => col.notNull().unique()) .addColumn('avatar_url', 'text') .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull() ) .execute() await db.schema .createIndex('idx_user_profile_avatar') .on('user_profile') .column('avatar_url') .execute() } export async function down(db: Kysely): Promise { await db.schema.dropTable('user_profile').execute() } ``` ### Programmatic Migrator ```typescript import { Migrator, FileMigrationProvider } from 'kysely' import { promises as fs } from 'fs' import * as path from 'path' // ESM only — CJS can use __dirname directly import { fileURLToPath } from 'url' const migrationFolder = path.join( path.dirname(fileURLToPath(import.meta.url)), './migrations', ) // `db` is your Kysely database instance const migrator = new Migrator({ db, provider: new FileMigrationProvider({ fs, path, migrationFolder, }), // WARNING: Only enable in development. Disables timestamp-ordering // validation, which can cause schema drift between environments. // allowUnorderedMigrations: true, }) const { error, results } = await migrator.migrateToLatest() results?.forEach((it) => { if (it.status === 'Success') { console.log(`migration "${it.migrationName}" executed successfully`) } else if (it.status === 'Error') { console.error(`failed to execute migration "${it.migrationName}"`) } }) if (error) { console.error('migration failed', error) process.exit(1) } ``` ## Django (Python) ### Workflow ```bash # Generate migration from model changes python manage.py makemigrations # Apply migrations python manage.py migrate # Show migration status python manage.py showmigrations # Generate empty migration for custom SQL python manage.py makemigrations --empty app_name -n description ``` ### Data Migration ```python from django.db import migrations def backfill_display_names(apps, schema_editor): User = apps.get_model("accounts", "User") batch_size = 5000 users = User.objects.filter(display_name="") while users.exists(): batch = list(users[:batch_size]) for user in batch: user.display_name = user.username User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size) def reverse_backfill(apps, schema_editor): pass # Data migration, no reverse needed class Migration(migrations.Migration): dependencies = [("accounts", "0015_add_display_name")] operations = [ migrations.RunPython(backfill_display_names, reverse_backfill), ] ``` ### SeparateDatabaseAndState Remove a column from the Django model without dropping it from the database immediately: ```python class Migration(migrations.Migration): operations = [ migrations.SeparateDatabaseAndState( state_operations=[ migrations.RemoveField(model_name="user", name="legacy_field"), ], database_operations=[], # Don't touch the DB yet ), ] ``` ## golang-migrate (Go) ### Workflow ```bash # Create migration pair migrate create -ext sql -dir migrations -seq add_user_avatar # Apply all pending migrations migrate -path migrations -database "$DATABASE_URL" up # Rollback last migration migrate -path migrations -database "$DATABASE_URL" down 1 # Force version (fix dirty state) migrate -path migrations -database "$DATABASE_URL" force VERSION ``` ### Migration Files ```sql -- migrations/000003_add_user_avatar.up.sql ALTER TABLE users ADD COLUMN avatar_url TEXT; CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL; -- migrations/000003_add_user_avatar.down.sql DROP INDEX IF EXISTS idx_users_avatar; ALTER TABLE users DROP COLUMN IF EXISTS avatar_url; ``` ## Zero-Downtime Migration Strategy For critical production changes, follow the expand-contract pattern: ``` Phase 1: EXPAND - Add new column/table (nullable or with default) - Deploy: app writes to BOTH old and new - Backfill existing data Phase 2: MIGRATE - Deploy: app reads from NEW, writes to BOTH - Verify data consistency Phase 3: CONTRACT - Deploy: app only uses NEW - Drop old column/table in separate migration ``` ### Timeline Example ``` Day 1: Migration adds new_status column (nullable) Day 1: Deploy app v2 — writes to both status and new_status Day 2: Run backfill migration for existing rows Day 3: Deploy app v3 — reads from new_status only Day 7: Migration drops old status column ``` ## Anti-Patterns | Anti-Pattern | Why It Fails | Better Approach | |-------------|-------------|-----------------| | Manual SQL in production | No audit trail, unrepeatable | Always use migration files | | Editing deployed migrations | Causes drift between environments | Create new migration instead | | NOT NULL without default | Locks table, rewrites all rows | Add nullable, backfill, then add constraint | | Inline index on large table | Blocks writes during build | CREATE INDEX CONCURRENTLY | | Schema + data in one migration | Hard to rollback, long transactions | Separate migrations | | Dropping column before removing code | Application errors on missing column | Remove code first, drop column next deploy |

相关技能

运维与云

Docker Essentials

用于容器管理,图像操作,调试的基本道克命令和工作流程.

运维与云

Find Skills

从开放的代理技能生态系统中发现并安装技能. 使用时:(1)用户问"我如何做X",X可能拥有现有技能,(2)用户说"为X找到技能"或"是否为X有技能",(3)用户问"你能否做X",X是专门能力,(4)用户想扩展代理能力,(5)用户想搜索工具,模板,或工作流程,(6)用户提到他们希望…

运维与云

Microsoft Foundry

构建,部署,评价,优化,微调,管理微软创始代理,模型,资源最终结束. 用法:铸造、azd ai代理、azd 提供/部署、托管代理脚手架/开发/运行/部署/拖放、即时代理创建、创建代理、更新代理、添加代理工具、援引代理、代理.yaml、评估代理、批量活化、连续活化、持续监测、代理C…

运维与云

Azure Deploy

执行已有.azure/部署计划.md和基础设施文件的ALREADY-PREPARED应用程序的Azure部署。 当用户向 CREATE 请求新应用程序时, 请不要使用此技能 。 这种技能运行了 azd上行, azd 部署, trarraform 应用, 和 az 部署命令带有内置…