How to handle database migrations in a fully automated CI/CD pipeline without data loss?

All answers to this question.

You absolutely need to look into tools like Liquibase or Flyway. These tools treat your database schema as version-controlled code. In a CI/CD pipeline, the tool checks a "schema_version" table and applies only the missing migration scripts. For safety, we always run migrations in a staging environment that is a clone of production first. Also, follow the "expand and contract" pattern: first, add the new column (expand), then deploy code that uses both, and finally delete the old column (contract). This avoids downtime and allows the code to function even if a rollback is needed.

   Answered 2024-03-25 by Susan Walker

Are you using a specific ORM like Entity Framework or Sequelize, or are you writing raw SQL migration scripts for your pipeline?

   Answered 2024-03-27 by Steven Hall

• We are using raw SQL scripts because we want full control over the execution plan. My main fear is a long-running ALTER TABLE command locking the UI. How do you handle migrations that might take several minutes to run on a table with millions of rows?

     Commented 2024-03-29 by Jeffrey Young

We use a "pre-deployment" job in our pipeline that runs the migration. If the migration script fails, the entire deployment stops before the new code is even touched.

   Answered 2024-03-31 by Linda Young

• This is a solid fail-safe, Linda. It ensures the environment state matches the code requirements before anything goes live.

     Commented 2024-04-01 by Susan Walker