返回提示词目录第 1310 页

编写数据库迁移与回滚脚本

结合项目约定生成数据库变更脚本,说明锁表影响、发布顺序、回滚方式与验证结果。

完整提示词

<context>
Flyway and Liquibase both record what has run, and both punish editing history. In Flyway a versioned migration (`V20261011_1__add_status.sql`) runs once and its checksum is stored; changing it after it ran anywhere fails validation, and `flyway repair` only realigns the history table, it does not undo anything. Versions must be unique across branches, so timestamp-style versions avoid clashes that sequential numbers cause, and `outOfOrder` is a team decision, not a fix. Repeatable migrations (`R__view_name.sql`) rerun when their checksum changes and suit views, functions and procedures written as `CREATE OR REPLACE`. Liquibase tracks changesets by id, author and file path, so moving a changelog file reruns everything unless `logicalFilePath` is set; `runOnChange` is its repeatable equivalent, and preconditions guard environment differences. Rollback is not automatic for raw SQL in either tool: Flyway undo is a paid feature and Liquibase needs explicit rollback blocks for SQL changesets. On large tables the migration itself is the outage: adding a non-null column with a volatile default, adding a foreign key or index without `CONCURRENTLY` (Postgres) or `ALGORITHM=INPLACE, LOCK=NONE` (MySQL) locks writes, and Postgres `CREATE INDEX CONCURRENTLY` cannot run inside a transaction, which needs per-script transaction settings. Running migrations on app startup with several replicas relies on the tool's lock and makes a slow migration delay every pod's start.
</context>

<task>
Change: {{change}}
{{#setup}}Setup: {{setup}}{{/setup}}

1. Read the migration configuration (locations, naming, `baselineOnMigrate`, `outOfOrder`, `validateOnMigrate`), existing migrations and how they run in each environment.
2. Name and place the migration per the project's convention, with a version that cannot clash with other branches. Use a repeatable migration for views, functions and procedures.
3. Write the SQL (or changeset) so it is safe on the table sizes involved: nullable column then backfill then constraint, indexes built concurrently or online with the transaction disabled for that script, foreign keys added `NOT VALID` then validated on Postgres, and backfills in batches in a separate migration or job.
4. Write rollback: a Liquibase rollback block, or a written forward-fix or manual rollback SQL for Flyway, and say which steps cannot be undone without data loss.
5. Note the rollout order when app code and schema change together (expand first, deploy code, contract later).
6. Run the migration against a local database or the test container setup (`flyway migrate` or `liquibase update`, then `validate`), run the tests, and report the results. Confirm CI runs validation.
</task>

<constraints>
- Never edit or delete a migration that has run in any shared environment; write a new one.
- Do not run `repair`, `clean`, `dropAll` or migrations against shared or production databases yourself, and say that `clean` must stay disabled in production config.
- Do not combine a long backfill and a schema lock in one migration on a large table.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>

<output_format>
## Migration
The file(s) in fenced blocks with their paths and names.
## Locking and rollout
What each statement locks and for how long on the stated size, and the expand and contract order.
## Rollback
The rollback script or forward-fix, and what cannot be undone.
## Check
Commands run with real results.
## Open questions
Numbered, or "None".
</output_format>

可替换内容

  • {{change}}
  • {{#setup}}
  • {{setup}}
  • {{/setup}}
  • {{> guardrails/scope-discipline}}
  • {{> guardrails/verify-before-done}}

分类与标签

编程开发、数据库迁移、回滚脚本、锁表分析、发布验证

前往 AI 图片创作