Database Schema Migration Test Selection Prompt

This prompt is designed for data engineers, DBAs, and release managers who are planning a database schema migration and need a targeted, risk-based regression test plan. It takes a migration specification, current schema, and dependency map as input, then produces a prioritized list of tests covering data integrity, backward compatibility, rollback verification, referential integrity, constraint validation, and performance regression checks. Use this prompt when you need an auditable, rationale-backed test selection that reduces full-suite runtime while maintaining safety coverage for schema changes.

The ideal workflow involves feeding the prompt a structured migration spec (DDL diff or migration file), the current full schema DDL, and a dependency map showing which application modules, views, or stored procedures reference the affected objects. The prompt then reasons about impact propagation—for example, a column type change on a heavily referenced table should trigger tests for all dependent views and application queries, not just the table itself. The output is a test list with explicit rationales, making it suitable for compliance review and audit trails where you must demonstrate that testing was risk-driven rather than arbitrary.

Do not use this prompt for application-layer code changes, infrastructure-as-code diffs, or general release readiness assessments without a schema change component. It is not designed to evaluate business logic correctness, UI behavior, or API contract changes unless those changes are direct consequences of the schema migration. For those scenarios, use the sibling prompts in this category, such as the Change-Based Regression Test Selection Prompt or the API Endpoint Change Impact Assessment Prompt. Always pair the output with human review for migrations involving financial data, PII, or irreversible destructive operations like column drops or table truncations.

This prompt template is designed to produce a structured, risk-aware test selection for a database schema migration. It forces the model to reason about data integrity, backward compatibility, rollback safety, and performance before listing specific test cases. Replace each square-bracket placeholder with concrete details from your migration plan. The more precise your inputs—especially the migration DDL, current schema, and data volume estimates—the more actionable the output will be.

textCopy
You are a database reliability engineer reviewing a planned schema migration. Your task is to produce a test selection that covers data integrity, backward compatibility, rollback verification, and performance regression checks.

## Migration Context
- Migration DDL: [MIGRATION_DDL]
- Current Schema (relevant tables): [CURRENT_SCHEMA_DDL]
- Migration Type: [MIGRATION_TYPE] (e.g., add column, rename column, split table, change data type, add constraint)
- Estimated Table Sizes: [TABLE_ROW_COUNTS]
- Application Read/Write Patterns During Migration: [ACCESS_PATTERNS]
- Downtime Tolerance: [DOWNTIME_WINDOW]
- Rollback Plan: [ROLLBACK_PLAN]

## Output Schema
Return a JSON object with the following structure:
{
  "risk_assessment": {
    "data_loss_risk": "low|medium|high|critical",
    "downtime_risk": "low|medium|high|critical",
    "rollback_complexity": "low|medium|high|critical",
    "summary": "string"
  },
  "test_categories": [
    {
      "category": "data_integrity|backward_compatibility|rollback|performance|constraint_validation|referential_integrity",
      "tests": [
        {
          "test_name": "string",
          "objective": "string",
          "preconditions": ["string"],
          "steps": ["string"],
          "expected_result": "string",
          "risk_if_skipped": "low|medium|high|critical",
          "automation_feasibility": "high|medium|low|manual_only"
        }
      ]
    }
  ],
  "execution_order": ["test_name"],
  "rollback_tests": [
    {
      "test_name": "string",
      "rollback_step_validated": "string",
      "verification_query": "string"
    }
  ],
  "performance_regression_checks": [
    {
      "query_or_operation": "string",
      "baseline_metric": "string",
      "acceptable_threshold": "string",
      "measurement_method": "string"
    }
  ],
  "constraints_and_referential_integrity_checks": [
    {
      "constraint_name": "string",
      "validation_query": "string",
      "expected_outcome": "string"
    }
  ]
}

## Constraints
- Every test must include a clear verification query or check, not just a description.
- Rollback tests must validate both the rollback procedure and post-rollback data correctness.
- Flag any test that requires a production-sized data volume to be meaningful.
- If the migration changes a column type, include tests for overflow, truncation, and encoding issues.
- If foreign keys are affected, include referential integrity tests for both parent and child tables.
- Mark any test that cannot be automated as "manual_only" with a justification in the objective.
- Do not recommend skipping tests that protect against data loss.

Adapt this template by filling in the migration DDL exactly as it will be executed, including any intermediate steps if the migration spans multiple statements. For the current schema, include only the tables, columns, constraints, and indexes directly affected or referenced by the migration—providing the entire schema can dilute the model's focus. When estimating table row counts, use order-of-magnitude approximations if exact numbers are unavailable, but be explicit about the uncertainty. The access patterns field should describe whether reads and writes continue during the migration, which application paths touch the affected tables, and whether any queue or batch processes might interfere. If you lack a formal rollback plan, state that explicitly rather than omitting the field; the model will then generate rollback tests that also serve as a rollback plan draft. After receiving the output, validate that every test includes a concrete verification step before integrating the results into your test management system.

This prompt is designed to be called programmatically as part of a migration planning or release gate pipeline, not as a one-off chat interaction. The primary integration point is after a migration script has been written and reviewed but before it is applied to any environment beyond a developer sandbox. The application layer should assemble the required inputs—the full DDL diff, the migration script, the target database version, and any known risk constraints—into the prompt's [MIGRATION_SCRIPT], [SCHEMA_DIFF], [DATABASE_VERSION], and [RISK_CONSTRAINTS] placeholders. Because the output is a structured test list, the harness must validate that the model returns a parseable JSON array of test case objects, each containing the required fields (test_id, test_name, test_category, test_objective, test_steps, expected_result, risk_level, rollback_test). If the output fails schema validation, the harness should retry with a stricter [OUTPUT_SCHEMA] constraint or escalate for human review rather than silently accepting a malformed test plan.

For production use, implement a validation layer that checks semantic correctness beyond JSON structure. Confirm that each returned test case maps to a specific clause in the migration script or schema diff—a test that cannot be traced back to a concrete change is a hallucination risk. Validate that referential integrity tests cover all foreign key relationships present in the diff, that rollback tests exist for every destructive operation (DROP, TRUNCATE, ALTER COLUMN type changes), and that performance regression tests reference specific queries or indexes affected by the migration. Log every prompt invocation with the input diff hash, the model version, the output test count, and the validation result. This audit trail is essential when a migration causes a production incident and the team needs to determine whether the test selection was adequate. For high-risk migrations—those involving financial data, PII, or irreversible schema changes—route the generated test list through a human DBA review step before the harness marks it as approved.

Model choice matters here. Use a model with strong SQL and database internals understanding, such as Claude 3.5 Sonnet or GPT-4o, and set a low temperature (0.0–0.2) to maximize deterministic, repeatable test selection. If your migration scripts contain proprietary schema details, run this prompt against a self-hosted or private deployment rather than a public API. The harness should also enforce a maximum test count constraint—if the model returns more than 50 test cases, re-invoke with a tighter [CONSTRAINTS] block asking for the highest-risk subset only, because an overly large test list often indicates the model is enumerating generic checks rather than migration-specific ones. Finally, integrate the validated test list into your test execution system (e.g., by generating a pytest or JUnit suite) and track which tests pass or fail against the migration. Feed those results back into future prompt invocations as [HISTORICAL_FAILURE_DATA] to improve risk-weighted selection over time.