database-migrations-sql-migrations

SQL Database Migrations - Zero-Downtime Database Migration Specialist

Skill Overview

This is an AI skill focused on SQL database migrations, helping you achieve zero-downtime deployments for PostgreSQL, MySQL, and SQL Server, and providing production-grade migration scripts, rollback plans, and data integrity validation.

Use Cases

1. Production Environment Schema Changes

When you need to modify table structures, add indexes, or refactor data models in a production environment, this skill can design zero-downtime migration plans to ensure business continuity. It supports the expand-contract pattern and blue-green deployment strategies, effectively avoiding table lock risks caused by long-running transactions.

2. Multi-Database Platform Migrations

If a project needs to support PostgreSQL, MySQL, and SQL Server simultaneously, or is undergoing a cross-database platform migration, this skill can generate migration scripts compatible with multiple databases and integrate seamlessly with mainstream frameworks like Flyway, Liquibase, and Alembic.

3. Large-Scale Data Migrations

For schema changes on tables with millions of rows, the skill provides performance optimization solutions such as batch processing and parallel execution, along with progress monitoring and alerting mechanisms to ensure large-scale data migrations are safe and controllable.

Core Features

1. Zero-Downtime Migration Strategy Design

Automatically analyze the impact scope of changes and generate expand-contract or blue-green deployment plans. By breaking complex changes into multiple atomic steps, it avoids long table locks and minimizes impact on online services.

2. Complete Migration Script Generation

Generate version-controlled SQL migration scripts that include pre-change data snapshots, incremental DDL statements, and data validation logic. Supports integration with mainstream migration frameworks to ensure script versioning and traceability.

3. Automated Rollback and Validation

Provide executable rollback scripts for every migration, including pre-checks, rollback operations, and post-rollback verification. Also output a comprehensive validation suite covering data consistency before and after migration, performance impact, and business functionality checks.

Frequently Asked Questions

What is zero-downtime database migration?

Zero-downtime migration refers to completing database schema changes without interrupting online services. It typically uses the expand-contract pattern: first add new fields or tables (expand), wait for application code to be compatible, then remove old fields or tables (contract). This approach avoids long table locks and ensures business continuity.

How to choose between Flyway and Liquibase?

Flyway is SQL-first and suits teams that prefer writing SQL directly; it’s simple to get started and favors convention over configuration. Liquibase supports SQL as well as XML/JSON/YAML formats, offering stronger change abstraction and rollback capabilities, making it suitable for complex scenarios. If the project mainly involves simple DDL changes, Flyway is lighter; if you need cross-database compatibility or complex change management, Liquibase is more appropriate.

How do you roll back if a database migration fails?

First, ensure every migration script has a corresponding rollback script that has been validated in a test environment. Typical rollback steps include: 1) stop application deployments to prevent new code from accessing the changed structure; 2) execute the rollback SQL to restore the pre-migration state; 3) run data integrity checks to confirm there is no corruption; 4) record the failure cause, fix the issue, and re-run the migration. Using Flyway or Liquibase allows rolling back to a specified version with a single command.