Database Migration Scripts in Spring Boot

What Are Database Migrations?

Database migrations are version-controlled scripts that manage schema changes in your Spring Boot application. They ensure your database structure stays consistent across all environments.

Why Use Migrations?

Track database changes alongside code. Automate schema updates during deployment. Enable rollback when things go wrong. Prevent conflicts in team environments.

Two Popular Tools

Flyway: Simple and SQL-First

Setup:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Configuration in application.properties:

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

Migration Files in src/main/resources/db/migration:

V1__Create_users_table.sql

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

V2__Add_status_column.sql

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'ACTIVE';

Naming Pattern: V{version}__{description}.sql

Liquibase: Flexible and Database-Agnostic

Setup:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

Configuration:

spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.yaml

Master Changelog in db.changelog-master.yaml:

databaseChangeLog:
  - include:
      file: db/changelog/v1-create-users.yaml
  - include:
      file: db/changelog/v2-add-status.yaml

Changeset in v1-create-users.yaml:

databaseChangeLog:
  - changeSet:
      id: 1
      author: developer
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: username
                  type: VARCHAR(50)
              - column:
                  name: email
                  type: VARCHAR(100)

Quick Comparison

Feature	Flyway	Liquibase
Learning Curve	Easy	Moderate
File Format	SQL	SQL, YAML, XML, JSON
Rollback	Paid	Built-in
Best For	Simple projects	Complex/multi-DB projects

Essential Best Practices

1. Never modify executed migrations - Create new ones instead
2. Use descriptive names - V3__Add_user_authentication_tables.sql
3. Test in dev first - Always validate before production
4. Keep migrations small - One logical change per migration
5. Add comments - Document why, not just what

Example: Adding a New Feature

Flyway Approach:

-- V5__Add_user_profiles.sql
CREATE TABLE user_profiles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Liquibase Approach:

databaseChangeLog:
  - changeSet:
      id: 5
      author: developer
      changes:
        - createTable:
            tableName: user_profiles
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: user_id
                  type: BIGINT
              - column:
                  name: bio
                  type: TEXT
        - addForeignKeyConstraint:
            baseTableName: user_profiles
            baseColumnNames: user_id
            referencedTableName: users
            referencedColumnNames: id

When Migrations Run

Spring Boot automatically runs pending migrations on application startup. The tools track executed migrations in dedicated database tables:

• Flyway: flyway_schema_history
• Liquibase: databasechangelog

Troubleshooting Tip

If a migration fails, both tools mark it as failed. Fix the issue, then:

Flyway:

flyway.repair()

Or delete the failed record manually

Liquibase:

liquibase.clearCheckSums()

Or manually update the status in the changelog table

Choosing Your Tool

Choose Flyway if you:

• Prefer writing SQL directly
• Want simplicity and minimal configuration
• Have a single database type

Choose Liquibase if you:

• Need database portability between MySQL, PostgreSQL, etc.
• Want advanced features like contexts and labels
• Need built-in rollback capabilities

Both integrate seamlessly with Spring Boot and get the job done reliably!