Database Migration Rollback Test Prompt

This prompt is for backend engineers and DBAs who need to verify that a database migration is safe to deploy by generating a targeted test suite. The job-to-be-done is not just to test the forward migration, but to prove that the rollback path is complete, data-preserving, and free of destructive side effects. Use this when you have a migration script written in SQL or an ORM definition, and you need to catch irreversible operations like DROP COLUMN, data type changes that truncate values, or constraint deletions before they reach production.

The ideal user provides the full migration up and down scripts, the current database schema, and the target database engine (e.g., PostgreSQL, MySQL). The prompt works best when you also supply a [CONSTRAINTS] block listing known risky operations to flag, such as DROP TABLE, ALTER COLUMN ... TYPE, or DELETE statements without WHERE clauses. Do not use this prompt for simple schema additions that have no data transformation or for migrations where rollback is explicitly not required by policy. It is also not a substitute for running the actual migration against a production-scale anonymized backup; it generates the test logic, not the execution environment.

The output is a structured test plan, not a passing test suite. You must wire the generated test cases into your CI pipeline, run them against a staging database, and verify that the rollback restores the exact pre-migration state. For high-risk migrations involving financial or healthcare data, require a human DBA to approve the test plan before execution. The next section provides the exact prompt template you will adapt with your schema and migration details.

This prompt template generates a comprehensive test suite for validating database migration rollback safety. It is designed to be dropped into an AI coding agent or test-generation pipeline where you provide the migration file, schema context, and risk constraints. The template forces the model to reason about data preservation, irreversible operations, constraint violations, and edge cases before producing executable test code. Use it when you need systematic rollback coverage beyond manual review—but never as a replacement for staging environment validation or DBA approval on production migrations.

textCopy
You are a database reliability engineer reviewing a migration for rollback safety.

Generate a complete test suite that validates the forward migration, rollback integrity, and data preservation for the migration defined below.

[MIGRATION_FILE]

[SCHEMA_CONTEXT]

[TEST_FRAMEWORK]

[EXISTING_TEST_PATTERNS]

[CONSTRAINTS]
- Target database: [DATABASE_TYPE]
- Risk level: [RISK_LEVEL]
- Irreversible operations to flag: [IRREVERSIBLE_OPERATIONS]
- Data volume context: [DATA_VOLUME]

[OUTPUT_SCHEMA]
{
  "test_suite_name": "string",
  "forward_migration_tests": [
    {
      "test_name": "string",
      "description": "string",
      "setup_sql": "string | null",
      "migration_sql": "string",
      "assertions": ["string"],
      "expected_state": "string"
    }
  ],
  "rollback_tests": [
    {
      "test_name": "string",
      "description": "string",
      "pre_migration_state": "string",
      "post_migration_state": "string",
      "rollback_sql": "string",
      "assertions": ["string"],
      "data_preservation_check": "string"
    }
  ],
  "irreversible_operations": [
    {
      "operation": "string",
      "location_in_migration": "string",
      "risk_description": "string",
      "mitigation_suggestion": "string",
      "requires_manual_review": true
    }
  ],
  "constraint_violation_risks": [
    {
      "constraint_type": "string",
      "table_column": "string",
      "violation_scenario": "string",
      "rollback_impact": "string"
    }
  ],
  "edge_cases": [
    {
      "scenario": "string",
      "test_approach": "string",
      "expected_behavior": "string"
    }
  ],
  "execution_order": ["string"],
  "warnings": ["string"]
}

[EXAMPLES]
Example rollback test for a column drop migration:
{
  "test_name": "rollback_restores_dropped_column_data",
  "description": "Verify that rolling back a DROP COLUMN restores the column with original data intact",
  "pre_migration_state": "Table 'users' has columns: id, email, temporary_flag",
  "post_migration_state": "Table 'users' has columns: id, email",
  "rollback_sql": "ALTER TABLE users ADD COLUMN temporary_flag BOOLEAN DEFAULT false;",
  "assertions": [
    "Column 'temporary_flag' exists after rollback",
    "Row count matches pre-migration count",
    "Default value applied correctly for rows inserted during migration window"
  ],
  "data_preservation_check": "Rows inserted between forward migration and rollback must not lose data; default value applied to new column for those rows"
}

Generate the complete test suite following the output schema above. For each irreversible operation detected, flag it with requires_manual_review: true and explain why rollback cannot guarantee data recovery. Include edge cases for: empty tables, tables with null values, concurrent writes during migration, and large table performance thresholds.

Adapt this template by replacing each square-bracket placeholder with concrete values from your migration context. [MIGRATION_FILE] should contain the full migration SQL or file path. [SCHEMA_CONTEXT] needs the relevant table definitions, indexes, constraints, and foreign key relationships before and after the migration. [TEST_FRAMEWORK] specifies your target test runner—pytest, Jest, Go testing, or a database-specific framework like pgTAP. [EXISTING_TEST_PATTERNS] should include snippets of your team's current test conventions so the generated tests match your codebase style. For high-risk migrations where [RISK_LEVEL] is set to critical, always route the output through human DBA review before execution and add a pre-flight check that validates the test suite against a staging clone. Never skip the irreversible operations section—migrations that drop columns, truncate tables, or reshape data types require explicit acknowledgment that rollback cannot fully recover lost data without a backup.

This prompt is designed to be called programmatically as part of a pre-commit hook, CI pipeline stage, or a developer CLI tool. The primary integration point is a script that extracts the migration file content and the target database schema, injects them into the prompt's [MIGRATION_SQL] and [SCHEMA_SNAPSHOT] placeholders, and then parses the structured output for downstream actions. The model's response should be treated as a test plan generator, not an executor—the generated SQL test cases must be reviewed and run in a sandboxed environment.

Integration workflow: 1) A git hook or CI trigger detects a new migration file. 2) The harness extracts the migration's UP and DOWN SQL blocks. 3) A schema dump is captured from a staging or ephemeral database that matches the target state. 4) The prompt is rendered with these inputs and sent to a capable model (e.g., GPT-4o, Claude 3.5 Sonnet). 5) The response is parsed into a structured object containing forward_tests, rollback_tests, data_preservation_checks, and irreversible_operations arrays. 6) Each test case is written to a temporary SQL file and executed against an isolated database instance. 7) Results are logged, and any test failure blocks the pipeline or flags the PR. Model choice: Use a model with strong SQL reasoning and structured output support. For cost-sensitive pipelines, a smaller model can be used if the output is validated with a strict JSON schema and retried on parse failure.

Validation and safety: Before executing any generated SQL, validate that all statements are read-only or run within a transaction that rolls back. The harness must reject any generated test that contains DROP, TRUNCATE, or unqualified DELETE without explicit review. Implement a retry loop: if the model's output fails JSON schema validation or contains SQL syntax errors, retry up to two times with the validation error appended to the prompt as [PREVIOUS_ERROR]. Logging and review: Log every prompt version, model response, validation result, and test execution outcome. For migrations tagged with [RISK_LEVEL]: HIGH (e.g., those involving column drops, type changes, or large data backfills), require a human approval step before the generated tests are merged. The harness should post a summary comment on the PR with the count of generated tests, any flagged irreversible operations, and a link to the full execution log.

What to avoid: Do not run generated tests directly against production or shared development databases. Do not skip the schema snapshot step—stale schema context is the most common cause of incorrect test generation. Do not treat the model's output as a substitute for manual review of rollback logic, especially for migrations that involve data transformations where semantic correctness matters more than syntactic validity.