# Best GitHub Copilot Prompts for Database Migration Scripts: Step-by-Step Guide

> source: https://promptsio.com/github-copilot-prompts-database-migration-scripts/
> published: 2026-10-06T17:54:51+00:00
> updated: 2026-10-06T17:54:51+00:00
> topic: Coding & Tech

Prompts for drafting safer database migrations with GitHub Copilot: engine-specific, reversible, idempotent, lock-aware, and always tested on a copy before production.

Schema changes are among the riskiest routine tasks in software. A bad migration can lock a table, lose data or take an application down, and an assistant that cheerfully writes `ALTER TABLE` statements does not know how big your tables are or what your traffic looks like. Used carefully, though, these **GitHub Copilot prompts for database migration scripts** can speed up the boring parts: boilerplate, naming, up and down pairs, and checklists, while you stay responsible for correctness.

The principle running through this guide: Copilot drafts, humans verify. Generated migrations must be reviewed, run against a copy of production-like data, and never executed directly on production unchecked. Check the tool's current features for what chat, inline and agent modes your editor supports.

## How Copilot Works for Migration Scripts

Copilot generates code from the context you provide: open files, selected code and your instructions. It does not know your row counts, indexes in use, replication setup or deploy process unless you tell it. So every migration prompt should state:

- **Engine and version** (for example PostgreSQL 15, MySQL 8.0, SQL Server 2019).

- **Framework or tool** (Flyway, Liquibase, Alembic, Prisma, Rails, Knex, etc.).

- **Direction:** up and down (or why down is not possible).

- **Safety constraints:** idempotency, locking, downtime tolerance, backfill approach.

- **Naming** conventions.

Because engine behaviour differs (what locks, what rewrites a table), always confirm Copilot's claims against the engine's own documentation.

## The Migration Prompt Formula

**Engine + version + tool + change + up/down + safety rules + data volume + naming + output format**

| Slot | Example | |---|---| | Engine | PostgreSQL [VERSION] | | Tool | Flyway / Alembic / Prisma / Rails | | Change | add nullable column, backfill, then enforce | | Safety | idempotent, short locks, no table rewrite | | Volume | about [ROWS] rows in the table | | Naming | `V[NUMBER]__add_[column]_to_[table].sql` |

## Basic Prompt vs Improved Prompt

**Basic:**

```
Write a migration to add a status column to orders.
```

**Improved:**

```
Write a Flyway migration for PostgreSQL [VERSION] that adds a "status" column to the "orders" table (about [ROW COUNT] rows, written to constantly). Use a nullable column first with no default that forces a table rewrite, make the statement idempotent (IF NOT EXISTS where supported), keep lock time minimal, and name the file V[NUMBER]__add_status_to_orders.sql. Provide a separate undo script as U[NUMBER]__add_status_to_orders.sql. Explain in comments which lock each statement takes and what to verify before deploying. Do not backfill in this file.
```

The improved prompt specifies engine, tool, volume, naming and what not to do, so the output can be reviewed against concrete expectations.

## Foundational Migration Prompts

### 1. Add a column safely

```
Using Alembic with SQLAlchemy and PostgreSQL [VERSION], write a migration that adds a nullable column "[COLUMN]" ([TYPE]) to "[TABLE]". Include upgrade() and downgrade(), guard against the column already existing, and add a comment explaining lock behaviour. Revision message: "add_[column]_to_[table]". Do not set a default or NOT NULL in this revision.
```

Splitting "add", "backfill", "enforce" into separate steps is the safest common pattern.

### 2. Create a table with constraints

```
Write a Rails migration for MySQL [VERSION] that creates a "[TABLE]" table with columns [LIST WITH TYPES], a primary key, a unique index on [COLUMN], a foreign key to "[PARENT]" with ON DELETE [BEHAVIOUR], and timestamps. Use the reversible "change" method where possible, otherwise define up and down. Follow the naming convention "Create[Table]". Mention any engine-specific limitation I should check.
```

### 3. Add an index without long locks

```
For PostgreSQL [VERSION] using [Flyway/Liquibase], write a migration to create an index on "[TABLE]([COLUMN])" for a table of about [ROWS] rows under heavy writes. Use a non-blocking approach where the engine supports it, note any transaction restrictions that apply to that approach for my tool, add an IF NOT EXISTS guard, and provide the rollback to drop the index. Explain the risks, including what happens if the build fails midway.
```

Ask Copilot to explain tradeoffs; then verify them in your engine's docs.

## Data and Backfill Prompts

### 4. Batched backfill

```
Write a backfill script for [ENGINE VERSION] that populates "[NEW_COLUMN]" in "[TABLE]" from "[SOURCE_COLUMN]" in batches of [BATCH SIZE] using a primary-key range, committing between batches, with a configurable pause, safe to re-run (only update rows where [NEW_COLUMN] IS NULL), and logging progress. Run it outside the schema migration as a separate job. Include a verification query to compare expected and actual counts.
```

Backfills that run inside one migration transaction are a common cause of long locks.

### 5. Splitting a column

```
Plan a migration in [TOOL] for [ENGINE VERSION] to split "[FULL_NAME]" into "first_name" and "last_name" in "[TABLE]" with minimal downtime. Give a step-by-step expand and contract sequence: add new columns, dual-write in the application, backfill in batches, validate, switch reads, then drop the old column in a later release. Provide each migration file and note how each step can be rolled back.
```

### 6. Changing a column type

```
Write migration steps for [ENGINE VERSION] and [TOOL] to change "[COLUMN]" in "[TABLE]" from [OLD TYPE] to [NEW TYPE] on a large table. Avoid a full table rewrite under lock if possible by adding a new column, backfilling, swapping and dropping later. List which operations the engine may rewrite the table for, flag anything I must confirm in the documentation, and supply the down migration.
```

## Safety, Rollback and Review Prompts

### 7. Down migrations and irreversibility

```
Review this migration: [PASTE]. Tell me whether the down migration truly reverses the up, what data would be lost on rollback, and which steps are irreversible. Suggest a safer rollback plan, such as restoring from a verified backup or a forward-fix migration, and mark where I must take a backup first.
```

Not every migration can be undone; a good prompt makes that explicit.

### 8. Lock and downtime risk review

```
Act as a cautious database reviewer. For [ENGINE VERSION], analyse this migration: [PASTE]. List each statement, the lock level it is likely to take, whether it can rewrite or scan the table, expected impact on [WORKLOAD], and a risk rating of low, medium or high with reasons. Mark anything you are unsure of as "verify in docs". Do not claim certainty about performance numbers.
```

### 9. Pre-deployment checklist

```
Generate a pre-deployment checklist for running the migration "[MIGRATION NAME]" on [ENGINE VERSION]. Include: verified fresh backup and restore test, dry run on a copy of production-sized data, timing measurement, lock monitoring, replication lag check, feature flag or maintenance window decision, rollback owner, communication plan, and post-run verification queries. Format as a numbered checklist with a check box for each.
```

### 10. Test plan on a production copy

```
Write a test plan for migration "[NAME]" using a masked copy of production data in a staging environment running [ENGINE VERSION]. Cover: restoring the copy, running up, measuring duration and locks, running application smoke tests, running down, running up again to prove repeatability, and comparing row counts and checksums. Add queries for each check and list failure signals that should stop the rollout.
```

### 11. Naming and file organisation

```
Suggest a naming and ordering convention for migrations in [TOOL] for a team of [N] developers working in parallel branches. Include examples for schema, data and repeatable migrations, how to avoid version number collisions, and how to label risky migrations for extra review. Keep it under 200 words.
```

## A Multi-Step Workflow

- **Context first:** describe the schema, engine version, tool and table sizes.

- **Plan, not code:** ask for an expand and contract plan (prompt 5 or 6).

- **Generate each step** as a separate file with up and down.

- **Self-review:** use prompts 7 and 8 on the generated files.

- **Human review:** have a teammate who knows the database review every file.

- **Staging run:** follow prompt 10 on a copy of production data after confirming a restorable backup exists.

- **Production rollout:** run through the checklist from prompt 9 in your normal controlled deployment process, never pasting generated SQL straight into a production console.

For wider automation ideas around deployment, see [AI prompts for workflow automation](https://promptsio.com/ai-prompts-for-workflow-automation/).

## Tips and Common Mistakes

- **Missing engine version.** Behaviour differs across versions; always include it.

- **Trusting stated lock behaviour.** Verify in the docs and in staging.

- **Doing everything in one migration.** Separate schema, backfill and constraints.

- **Skipping backups.** A backup you have not restored is a hope, not a backup.

- **Sensitive data in prompts.** Use schema and fake examples, not real records.

- **Running unreviewed output.** Generated code can contain subtle errors, wrong syntax for your version, or destructive statements.

- **Forgetting the application.** Code and schema must be compatible during rollout.

## FAQ

### Can GitHub Copilot write production-ready migrations?

It can write useful drafts, but production readiness depends on your review, testing and deployment controls.

### Which migration tool works best with Copilot?

Any of them, provided you name the tool and its conventions in the prompt.

### Should I always write a down migration?

Usually, but some changes are not reversible. Document those and plan a backup or forward-fix instead.

### How do I test a migration safely?

Restore a masked copy of production data to a staging environment and run up, down and up again while measuring time and locks.

### Is it safe to paste my real schema into Copilot?

Check your organisation's policy and the tool's current data handling terms; use redacted schemas when in doubt.

## Conclusion

Good migration prompts are specific about engine, tool, direction and safety rules. Treat Copilot as a fast drafter, then rely on backups, review and a rehearsal on production-like data before anything reaches production. That discipline is what turns a risky chore into a repeatable process.

---
Published by Promptsio. Canonical version: https://promptsio.com/github-copilot-prompts-database-migration-scripts/