Database Migrations in the Real World

Siva Katamreddy

Database migrations are critical to modern application development and deployment, enabling teams to evolve their database schema alongside their application code. However, as systems grow in complexity and scale, managing migrations becomes increasingly challenging. At scale, a simple schema update can impact several microservices, terabytes of data, and global user traffic. It requires careful planning, automation, and coordination to avoid downtime, ensure data integrity, and support seamless rollbacks if necessary.

In this article, we’ll explore the best practices, tools, and strategies for handling database migrations at scale, ensuring your systems remain robust, efficient, and adaptable.

Introduction

Using database migration tools like Flyway or Liquibase is essential for managing schema changes consistently, reliably, and automatically. These tools enable teams to version control database changes alongside application code, ensuring seamless collaboration and traceability. They also eliminate the risk of manual errors by providing a structured way to apply, track, and roll back migrations across environments.

By incorporating database migration tools into your development workflow, you can ensure smooth, efficient database evolution, even in complex and large-scale systems.

However, there are challenges associated with implementing these solutions effectively, especially in large-scale systems. Let’s explore some of those challenges and how they can be mitigated.

Executing database migrations during application startup

Many frameworks offer out-of-the-box support for integrating database migration tools. The default behaviour of these framework integrations is to check if there are any pending migrations and apply them when the application is started.

Running database migrations every time an application starts can introduce some challenges, particularly in larger or high-throughput systems.

• Running migrations on every startup, even if no changes are necessary, adds the unnecessary overhead of verifying the integrity of migration script checksums and can slow down the application’s initialization time.
• In distributed systems with multiple application instances, simultaneous migration attempts can lead to race conditions, locks, or conflicts, potentially causing downtime or inconsistent states.
• Running migrations during application startup couples schema changes tightly with application deployment, making it harder to decouple release and migration processes. This can complicate rolling updates, blue-green deployments, or canary releases.

To avoid these issues:

• Run migrations separately: Execute migrations as a distinct pre-deployment step, ensuring they are applied before the application starts.
• Controlled execution: Use database migration tools like Flyway or Liquibase with mechanisms to prevent concurrent migrations and ensure only one instance applies changes.
• Use a dedicated database user for migrations: To enhance security, use a separate user with elevated privileges specifically for running database migrations. The application’s database user typically doesn’t require permissions like dropping tables, renaming columns, etc.

Database support for transactional DDL statements

Database migration tools like Flyway and Liquibase try to execute each migration script in a transaction so that if an error occurs at any point in execution, then all pending statements will be rolled back.

However, this is not always possible if the underlying database does not support transactions for DDL statements like CREATE TABLE, ALTER TABLE, etc.

For example, PostgreSQL supports transactional DDL, so you can safely include DDL operations like CREATE TABLE, ALTER TABLE, or DROP TABLE within transactions, allowing you to group multiple schema changes together and roll them back if needed.

However, some databases like MySQL, MariaDB, and Oracle (before 12c) don’t support transactions for DDL statements.

Let’s say you have a migration script as follows:

CREATE TABLE employees ( 
  id int not null,
  name varchar(100) not null,
  email varchar(200),
  primary key (id)
);

INSERT INTO employees(id, name, email) VALUES (1, null, 'emp1@gmail.com');

When you try to apply this migration in a PostgreSQL database, it will throw an error because a null value is being inserted into a non-nullable column. As PostgreSQL supports transactional DDL statements and transactions are used for applying this migration, the table creation will also be rolled back.

If you try to apply the same migration using a MySQL or Oracle database, then the migration will throw an error while executing the INSERT statement, but the employees table remains created, as the CREATE TABLE DDL statement won’t be rolled back. 

You can use Testcontainers to test the migrations in lower environments (local, dev) to ensure that the migration scripts are valid.

You can check out the following articles on how to use Flyway database migrations and testing the application using Testcontainers with a Spring Boot application.

• How to Use Flyway for Database Migrations in Spring Boot Applications
• Testing Spring Boot Applications Using Testcontainers

Database migrations with backward compatibility

Some applications may require backward compatibility of their APIs to support clients using older and newer versions. Backward-compatible database migrations are essential for minimizing disruption during deployment and allowing the application and database to evolve independently. Backward compatibility ensures that old versions of the application can still work with the updated database schema, enabling safer rollouts and zero-downtime deployments.

We should aim to create backward-compatible database migrations by following these guidelines:

• Add non-breaking changes first: Always introduce changes that do not break any existing functionality. While adding new columns, always add them with a default value or allow them to be nullable.
• Avoid destructive operations: Avoid dropping columns, tables, or indexes that the current application might depend on. Instead of dropping a column, mark it as deprecated and remove it in a later migration. Don’t rename columns or tables directly – add new ones and phase out old ones gradually.
• Maintain dual reads/writes temporarily: Write data to both the old and new schema during the transition period. When renaming a column, create the new column and update the application logic to write to both the old and new columns, either from application code or by using database triggers.
• Use views for logical changes: To reduce the application code changes supporting both old and new schemas, use database views when possible.

Use a phase-based approach

While making database changes, you should plan to follow a phased approach to be backward-compatible.

Phase 1 – Safe first, clean later: Prioritize making the schema safe for new features before cleaning up deprecated parts. In this phase, we only add tables or columns that don’t break existing application logic.

Phase 2 – Maintain dual writes temporarily: Write data to old and new tables/columns simultaneously using triggers or from application code.

Phase 3 – Read from the new source: Update the application code to read from the new tables/columns.

Phase 4 – Clean up: Remove the triggers or application code that populate data from old tables/columns to new tables/columns. Remove the deprecated tables, columns, indexes, etc.

Always test the migrations in QA/staging environments before applying them to the production database. Create a testing strategy to validate that the database migration works with old and new versions of the application code using automated tests.

Structuring database migrations

When structuring Flyway migration scripts, following best practices ensures that migrations are manageable, clear, and maintainable as your application grows.

1. Use one script per feature or change

For each feature or bug fix requiring schema changes, create a single migration script with a descriptive name. Avoid bundling unrelated changes in one script and keep each migration focused and atomic. This makes migrations modular, reduces merge conflicts in teams, and makes rollback and debugging easier.

Examples:

   V1.1__add_users_table.sql
   V1.2__add_index_on_email.sql
   V1.3__rename_column_lastname.sql

2. Maintain sequential versioning

Flyway executes scripts based on their version order, so consistent versioning is crucial.

For hotfixes or concurrent development, adopt a convention like the following:

• Use V3.1__hotfix_script.sql for minor adjustments after V3.
• Use timestamp-based versioning in addition to version numbers if your team often works on migrations concurrently.

V20250127__1_add_disabled_column.sql
V20250127__2_add_index_on_status.sql

• Alternatively, you can reserve blocks for specific teams/features (e.g. V1000-1099 for Team A, V1100-1199 for Team B).

3. Group scripts by version or feature in folders

While Flyway doesn’t require scripts to be in subfolders, using folders can help organize migrations in large projects.

You can group migration scripts by version as follows:

migrations/
 ├── v1/
 │   ├── V1__create_initial_schema.sql
 │   ├── V1.1__add_roles_table.sql
 │   └── V1.2__alter_users_add_column.sql
 ├── v2/
 │   ├── V2__add_orders_table.sql
 │   └── V2.1__add_order_details_table.sql
 └── v3/
     └── V3__optimize_indexes.sql

Alternatively, you can group migrations by feature as follows:

migrations/
  ├── users/
  │   └── V1.1__add_users_table.sql
  ├── catalog/
  │   ├── V2__add_products_table.sql
  │   └── V2.1__update_products.sql
  └── orders/
      ├── V3__add_orders_table.sql
      └── V3.1__optimize_orders_index.sql

Consolidation of historical migrations

Over time, the number of migration scripts can grow significantly, making it time-consuming to create a new database from these migrations for development or testing purposes.

We can consolidate all of the migrations into a baseline migration script that represents the current state of the database schema and use the baseline script to create new databases.

Imagine you have 10 migration scripts created over time to create the current state of the database schema:

V1__create_users_table.sql
V2__add_status_column.sql
V3__add_forgot_pwd_token_column.sql
V4__create_products_table.sql
...
...
V10__add_index_on_status.sql

Whenever you apply migrations on a new database, it will execute all of these scripts to create the target state of the database schema. 
While using Flyway, we can create a baseline migration script that creates the target state of the  database schema with the file name B10__baseline.sql. Then you can add further versioned migrations as follows:

V1__create_users_table.sql
V2__add_status_column.sql
V3__add_forgot_pwd_token_column.sql
V4__create_products_table.sql
...
...
V10__add_index_on_status.sql
B10__baseline.sql
V11__create_inventory_table.sql
V12__create_promotions_table.sql

With these migrations in place, when you run Flyway migration on a new database, then only B10, V11, and V12 migrations will be executed. This will create the schema faster as it doesn’t need to execute versioned migrations before the baseline version.

When Flyway migration is applied on an existing database (production or staging), Flyway will ignore the baseline migration and apply V11 and V12 versioned migrations.

To learn more about how to use Flyway Baseline Migrations for consolidating historical migrations, visit Flyway’s Baseline Migrations Explained Simply.

Testing migration scripts

Testing migrations is crucial, as data is the lifeblood of any business and its integrity must always remain intact. You should always ensure that the migration scripts are working correctly and are compatible with the application code.

If your database migration tool supports dry runs to get a consolidated view of all pending changes without actually running them, it would be helpful for DBAs to review them.

Test the migration scripts using a production-like database, mask sensitive data, and verify all changes are applied correctly. You can use Testcontainers and Synthesized for testing migrations in an isolated environment with production-like masked data.

Use professional tooling support

Although we can manually write database migrations, the process can be tedious, involving writing SQL statements in different database dialects and ensuring backward compatibility. Using professional tools like IntelliJ IDEA can help streamline these tasks, reducing errors and improving productivity.

With IntelliJ IDEA, you can generate migration scripts directly from JPA entity models, update JPA entities, and automatically create corresponding migration scripts.

Additionally, IntelliJ IDEA provides visual cues to help you understand the impact of database changes, making it easier to track and manage migrations.

Each change type is color-coded according to its danger level: green for SAFE, yellow for CAUTION, and red for DANGER. SAFE operations are the ones that cannot cause loss of data in any way. For example, adding a column does not affect the existing data. Operations marked with CAUTION are generally safe but require your attention. For instance, adding a NOT NULL constraint can fail if there are null values in the column. DANGER operations can cause a loss of data, like dropping a column or modifying a data type.

This color-coding will encourage you to be aware of the changes and ensure database migration scripts remain backward-compatible.

Summary

We have explored various real-world challenges in implementing database migrations in large-scale applications. By adopting a phased approach, we can minimize disruptions and ensure the migrations are backward-compatible. Additionally, we can simplify writing migration scripts by following recommended naming conventions and organizing the scripts by version or features.

A well-structured database migration strategy combined with automated testing can help teams maintain database integrity while supporting evolving application requirements.

While you can create migration scripts manually, it can be a time-consuming and error-prone process. You can use IntelliJ IDEA to create migration scripts in various scenarios such as:

• Generating migration scripts from JPA entities.
• Generating new migrations after updating JPA entities with delta changes from existing databases.

You can read How to Use Flyway for Database Migrations in Spring Boot Applications to learn more about leveraging IntelliJ IDEA’s capabilities to work with database migrations more easily.

Subscribe to IntelliJ IDEA Blog updates

Subscribe form

By submitting this form, I agree to the JetBrains Privacy Policy Notification icon

By submitting this form, I agree that JetBrains s.r.o. ("JetBrains") may use my name, email address, and location data to send me newsletters, including commercial communications, and to process my personal data for this purpose. I agree that JetBrains may process said data using third-party services for this purpose in accordance with the JetBrains Privacy Policy. I understand that I can revoke this consent at any time in my profile. In addition, an unsubscribe link is included in each email.

Submit

Thanks, we've got you!