Safe Database Migrations with AI: From Schema Changes to Data Migration

Introduction

Database migrations are among the riskiest tasks in software development. A schema change mistake can lead to data loss, and rolling back in production is never simple.

Many development teams struggle with:

• Data corruption from migration script errors
• Incomplete rollback procedures
• Inconsistencies between application code and database schema
• Differences between test and production environments
• Extended downtime during migrations

DevLoop Runner brings structure to this process. From migration design to testing and implementation, the 10-phase Dev Run workflow helps you handle database changes methodically. The AI assists with rollback strategies and data integrity considerations, supporting safer migrations.

This article covers practical approaches to database migrations using DevLoop Runner.

Types of Database Migrations

Database migrations fall into three main categories.

Schema Changes

Operations that modify the table structure itself.

Operation	Risk Level	Example
Add column	Low	Adding a nullable column
Drop column	High	Potential dangling references to existing data
Change column type	High	Data loss from implicit type conversion
Split table	Very high	Affects all related queries
Add index	Medium	Impact on lock time
Modify foreign key	High	Referential integrity impact

Data Migrations

Operations that transform existing data to a new structure or format.

• Converting record values (e.g., strings to numbers)
• Moving data to different tables
• Merging or splitting data
• Setting default values for existing records

Index and Performance Improvements

Changes aimed at improving query performance.

• Adding or removing indexes
• Configuring partitions
• Table denormalization

Writing Issues for Database Migrations

Basic Structure

Database migration Issues require more detail than typical feature development Issues. Use the following template.

## Issue: DB Migration - [Operation Summary]

### Overview
[Brief description of the change's purpose and context]

### Current Schema
[Describe the table structure before the change]

### Target Schema
[Describe the table structure after the change]

### Migration Steps
1. [Step 1]
2. [Step 2]
3. ...

### Rollback Strategy
[Procedure to revert if problems occur]

### Data Preservation Requirements
- [Requirements for data that must be preserved]
- [Acceptable downtime]

### Test Requirements
- [Scenarios to test]

For general Issue writing guidance, see the GitHub Issue writing guide.

Specifying Rollback Strategies

The rollback strategy is the most critical part of a migration Issue. Always include it explicitly so the AI generates safe migration code.

### Rollback Strategy

#### Immediate Rollback (right after migration)
- Use DOWN migration script to revert to original schema
- Assumes no data has been written to new columns

#### Delayed Rollback (after a period of operation)
- Back up data in new columns
- Execute DOWN migration
- Revert corresponding application code changes

#### Non-Reversible Cases
- Contains destructive type conversions (precision loss),
  so rollback requires restoring from backup

For more on rollback capabilities, see the rollback guide.

Review the Design with Plan Only First

Database migrations have a wide blast radius, so jumping straight into implementation is risky. Start with Plan Only mode to review the design.

Key points to verify in Plan Only mode:

• Is the migration execution order correct?
• Is the rollback procedure realistic?
• Is there any risk of data loss?
• What is the impact on application code?

Loading diagram...

Review the Plan Only results with your team. If there are concerns, revise the Issue before running a Full Dev Run. This extra step prevents production incidents.

Test Strategies for Migrations

Database migrations require migration-specific tests in addition to standard unit tests.

Scenarios to Test

Specifying the following test requirements in your Issue helps DevLoop Runner generate appropriate test code.

### Test Requirements

#### Forward Migration Tests
- CRUD operations work correctly with the new schema
- Existing data is correctly transformed
- New constraints function properly

#### Reverse Migration Tests
- Schema reverts to original after rollback
- Existing data is preserved after rollback

#### Data Integrity Tests
- Foreign key constraints function correctly
- NOT NULL constraints are properly applied
- Unique constraints function correctly

#### Performance Tests
- Migration execution time with large datasets (100,000+ records)
- Query performance after index additions

For general test strategy guidance, see the AI testing automation guide.

Preparing Test Data

Migration tests require realistic test data. Specify test data requirements in your Issue.

### Test Data Requirements
- Normal data: 1,000+ records per table
- Boundary values: NULL values, empty strings, maximum-length strings
- Edge cases: Records referenced by foreign keys
- Large dataset: 100,000 records for performance verification

Coordinating Migrations with Application Code

Database changes almost always require corresponding application code changes. DevLoop Runner recommends managing related changes as coordinated Issues.

Issue Splitting Strategy

Loading diagram...

Rather than combining everything into a single Issue, split changes across multiple Issues and execute Dev Runs incrementally. This approach catches problems at each stage.

Designing Issues for Staged Deployment

Safe migrations require a staged deployment strategy. Design your Issues using the following pattern.

Phase 1: Add new column (backward compatible)

## Issue 1: Add display_name column to users table

### Migration
- Add display_name column (VARCHAR(100), nullable) to users table
- Default value: NULL

### Application Changes
- Add display_name property to User model
- Do not migrate data from name field (deferred to next phase)

### Rollback Strategy
- Prepare DOWN migration to drop the display_name column

Phase 2: Migrate data

## Issue 2: Migrate data from name to display_name

### Migration
- Copy existing name column values to display_name
- Execute only for records where display_name is NULL

### Application Changes
- Reads: Prefer display_name (fall back to name if NULL)
- Writes: Write to both columns

### Rollback Strategy
- Clear display_name data (set back to NULL)
- Revert application code changes

Phase 3: Remove old column

## Issue 3: Drop name column from users table

### Prerequisites
- Verified that display_name is not NULL for all records
- Verified that no application code references the name column

### Migration
- Drop the name column
- Add NOT NULL constraint to display_name

### Rollback Strategy
- Re-add the name column
- Copy display_name values to name

Practical Examples: Common Migration Scenarios

Example 1: Adding a Column

The simplest type of migration.

## Issue: Add tracking_number column to orders table

### Overview
Add a column to store shipping tracking numbers in the orders table.

### Change Details
- Column name: tracking_number
- Type: VARCHAR(50)
- Nullable: Yes
- Default value: NULL
- Index: None

### Application Changes
- Add tracking_number to Order model
- Include in order detail API response
- Display tracking number in admin order list

### Rollback Strategy
- Drop the tracking_number column
- Low data loss risk since the column is nullable

### Test Requirements
- Forward and reverse migration tests
- Order model CRUD tests
- Verify tracking_number is included in API response

Example 2: Splitting a Table

A typical high-risk migration.

## Issue: Separate profile data from users into user_profiles table

### Overview
The users table has grown too large. Separate profile-related
columns into a dedicated user_profiles table.

### Current Schema
users table:
| Column | Type | Description |
|:---|:---|:---|
| id | BIGINT | Primary key |
| email | VARCHAR(255) | Email address |
| password_hash | VARCHAR(255) | Password hash |
| display_name | VARCHAR(100) | Display name |
| bio | TEXT | Bio |
| avatar_url | VARCHAR(500) | Avatar image URL |
| created_at | TIMESTAMP | Creation timestamp |

### Target Schema
users table:
| Column | Type | Description |
|:---|:---|:---|
| id | BIGINT | Primary key |
| email | VARCHAR(255) | Email address |
| password_hash | VARCHAR(255) | Password hash |
| created_at | TIMESTAMP | Creation timestamp |

user_profiles table (new):
| Column | Type | Description |
|:---|:---|:---|
| id | BIGINT | Primary key |
| user_id | BIGINT | Foreign key to users |
| display_name | VARCHAR(100) | Display name |
| bio | TEXT | Bio |
| avatar_url | VARCHAR(500) | Avatar image URL |

### Migration Steps
1. Create user_profiles table
2. Migrate data from users to user_profiles
3. Add foreign key constraint
4. Update application code (separate Issue)
5. After sufficient verification, drop columns from users (separate Issue)

### Rollback Strategy
- Copy data from user_profiles back to users table
- Drop user_profiles table

### Data Preservation Requirements
- Complete profile data migration (zero records lost)
- Guarantee integrity between user_id and users.id
- Proper handling of NULL values

Example 3: Changing a Relationship

## Issue: Convert orders-products to many-to-many relationship

### Overview
Currently, orders have a single product_id column creating a
one-to-one relationship. Change this to many-to-many so an order
can contain multiple products.

### Changes
1. Create order_items junction table
2. Migrate existing orders.product_id data to order_items
3. Drop product_id from orders (separate Issue)

### order_items Table
| Column | Type | Description |
|:---|:---|:---|
| id | BIGINT | Primary key |
| order_id | BIGINT | Foreign key to orders |
| product_id | BIGINT | Foreign key to products |
| quantity | INT | Quantity |
| unit_price | DECIMAL(10,2) | Unit price |

### Rollback Strategy
- Copy order_items data back to orders.product_id
  (for multi-product orders, keep only the first product)
- Drop order_items table

### Test Requirements
- Existing order data is correctly migrated
- Multiple products can be added to a single order
- Order total calculation is correct
- API response format is updated

Security Considerations

Security is a critical concern in database migrations. Include the following security requirements in your Issues.

### Security Requirements
- Encryption requirements for columns containing PII
- Migration logs must not output sensitive data
- Test data must not use production data
- Minimize migration execution privileges

For general security guidance, see AI security best practices.

The Complete Dev Run Workflow for Migrations

Here is how each Dev Run phase contributes to database migrations.

Phase	Role in Database Migrations
Planning	Define the overall migration strategy
Requirements	Detail schema changes and data preservation needs
Design	Design migration scripts and rollback procedures
Test Scenarios	Design migration-specific test cases
Implementation	Generate migration scripts and application code
Test Implementation	Generate forward and reverse test code
Test Execution	Run tests to verify correctness
Documentation	Generate migration procedure documentation
Report	Create a summary of changes
Evaluation	Output risk assessment and improvement suggestions

See the Dev Run workflow guide for details on each phase.

Code Quality Verification

Always review generated migration code for the following:

• Is the migration script idempotent (safe to run multiple times)?
• Does the rollback script actually work?
• Is performance acceptable with large datasets?
• Is transaction management appropriate?
• Is lock time minimized?

Use the AI code quality checklist alongside your review to ensure quality.

Summary

Database migrations are high-risk development tasks, but DevLoop Runner can significantly improve their safety.

The key principles are:

• Always specify a rollback strategy in your Issue. This is the most important information for the AI to generate safe migration code.
• Review the design with Plan Only mode first. Do not jump straight into implementation -- review the migration design with your team.
• Split migration and application code into separate Issues. Break large changes into stages to distribute risk.
• Specify test requirements in detail. Cover forward tests, reverse tests, and data integrity tests comprehensively.

Start with a low-risk column addition to try DevLoop Runner for migrations. As you build experience incrementally, you will gain the confidence to tackle high-risk migrations like table splits and relationship changes.