Database Migration with Flyway

Table of Contents

The Problem — Why ddl-auto=update is Dangerous in Production

Since Lecture 5, we have been using this configuration:

spring.jpa.hibernate.ddl-auto=update

This tells Hibernate: “Compare my entity classes to the database and apply schema changes automatically.” It feels magical during development, but it hides serious problems that surface in production.

Problem 1: It Never Removes Anything

You renamed a field from summary to excerpt in your Post entity. What does Hibernate do? It creates a new excerpt column but leaves the old summary column in the database. Now you have two columns — one full of data, one empty. Over months, your database accumulates ghost columns that nobody remembers.

Before:  posts (id, title, content, summary, ...)
After:   posts (id, title, content, summary, excerpt, ...)
                                     ↑ still there, abandoned

Problem 2: It Cannot Handle Complex Changes

Rename a table? Split a column into two? Migrate data from one format to another? Convert a string status to an enum table? Hibernate update cannot do any of these. It only handles the simplest case: adding new columns and tables.

Problem 3: No History

There is no record of what changes were applied and when. If something goes wrong, you have no way to know what Hibernate changed or how to undo it.

Problem 4: No Review Process

Schema changes happen silently at application startup. Nobody reviews them. Nobody approves them. A developer adds a field to an entity, pushes to production, and the database schema changes without anyone knowing. In any serious project, schema changes need code review just like application code.

Problem 5: Different Environments Drift

Developer A runs the app on Monday and gets schema version X. Developer B runs it on Wednesday and gets schema version Y. The staging environment was last updated a month ago and has a different schema. Production is different from all of them. Nobody knows which schema is “correct.”

The Solution: Version-Controlled Migrations

Instead of letting Hibernate guess what to do, you write explicit SQL migration scripts that are:

Versioned — each change has a number (V1, V2, V3…)
Reviewed — they go through code review like any other code
Tracked — a history table records which migrations have been applied
Reproducible — running all migrations from scratch produces an identical schema
Reversible — you can plan rollback strategies for each migration

This is what Flyway provides.

What is Database Migration?

The Concept

A database migration is a single, atomic change to the database schema. Each migration is a SQL file (or Java class) that transforms the database from one version to the next.

Think of migrations as commits for your database schema. Just as Git tracks every change to your source code, Flyway tracks every change to your database.

V1 — Create users table
V2 — Create categories table
V3 — Create posts table (with foreign keys to users and categories)
V4 — Create tags table and post_tags junction table
V5 — Create comments table
V6 — Add views_count column to posts
V7 — Add index on posts.status for query performance
V8 — Rename users.name to users.full_name

Each migration runs exactly once. Flyway records which migrations have been applied in a special tracking table (flyway_schema_history). When the application starts, Flyway checks: “What is the latest migration in the database? What new migration files exist? Run the new ones.”

The Migration Lifecycle

App starts
    ↓
Flyway checks flyway_schema_history table
    ↓
"Database is at V5. Migration files go up to V8."
    ↓
Flyway runs V6, V7, V8 in order
    ↓
Updates flyway_schema_history: V6 ✓, V7 ✓, V8 ✓
    ↓
Spring Boot continues startup → Hibernate validates schema
    ↓
Application is ready

If V7 fails (bad SQL), Flyway stops immediately. V8 is not executed. The database stays at V6. You fix V7 and redeploy.

Flyway vs Liquibase — Comparison

Flyway and Liquibase are the two most popular database migration tools in the Java ecosystem. Both integrate seamlessly with Spring Boot.

Feature	Flyway	Liquibase
Migration format	Raw SQL files	XML, YAML, JSON, or SQL
Learning curve	Very low — just write SQL	Higher — need to learn the changelog format
Database-specific features	Full access (it is SQL)	Abstracted — may not support all features
Portability across databases	Low (SQL is database-specific)	High (abstract format translates to any DB)
Rollback	Manual (write a separate undo script)	Auto-generated for some changes
Popularity	Very popular in Spring Boot projects	Popular in enterprise/multi-DB projects
Philosophy	Simple and opinionated	Flexible and feature-rich

When to Choose Flyway

You use one database (MariaDB) and do not plan to switch
You are comfortable writing SQL
You value simplicity over flexibility
You are building a typical Spring Boot application

When to Choose Liquibase

You support multiple database vendors
You want auto-generated rollback scripts
You prefer a database-agnostic format
Your organization mandates it

For this series, we use Flyway. It is simpler, SQL-native, and the most popular choice in the Spring Boot ecosystem. Since we are committed to MariaDB, the portability advantage of Liquibase does not apply.

Setting Up Flyway with Spring Boot

Step 1: Add the Dependency

Add Flyway to your pom.xml:

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

<!-- Flyway needs a database-specific module for MariaDB/MySQL -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

Spring Boot auto-configures Flyway when it detects these dependencies on the classpath. No additional annotation or bean configuration is needed — Flyway runs automatically at application startup, before Hibernate initializes.

Step 2: Update application.properties

# ============================================
# DataSource — same as before
# ============================================
spring.datasource.url=jdbc:mariadb://localhost:3306/blogdb
spring.datasource.username=bloguser
spring.datasource.password=blogpass
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

# ============================================
# JPA / Hibernate — CHANGE ddl-auto to validate
# ============================================
# Previously: spring.jpa.hibernate.ddl-auto=update
# Now: Flyway manages the schema, Hibernate only validates
spring.jpa.hibernate.ddl-auto=validate

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect

# ============================================
# Flyway Configuration
# ============================================
# Where Flyway looks for migration files (default is classpath:db/migration)
spring.flyway.locations=classpath:db/migration

# Enable Flyway (it is enabled by default when on the classpath)
spring.flyway.enabled=true

# The schema history table name (default: flyway_schema_history)
spring.flyway.table=flyway_schema_history

The critical change: spring.jpa.hibernate.ddl-auto=validate. Hibernate no longer modifies the schema. It only checks that the entities match the tables. If there is a mismatch, the application fails to start with a clear error message. This is a safety net — if you forget to write a migration for a new entity field, Hibernate catches it immediately.

Step 3: Create the Migration Directory

Create the directory where Flyway looks for migration files:

src/
└── main/
    └── resources/
        └── db/
            └── migration/
                ├── V1__create_users_table.sql
                ├── V2__create_categories_table.sql
                └── ... (more migration files)

The default location is src/main/resources/db/migration/. Flyway scans this directory at startup and executes any new migration files.

Migration File Naming Convention

Flyway uses the file name to determine the migration version and order. The naming convention is strict:

V{version}__{description}.sql

Examples:
V1__create_users_table.sql
V2__create_categories_table.sql
V3__create_posts_table.sql
V4__create_tags_and_post_tags.sql
V5__create_comments_table.sql
V6__add_views_count_to_posts.sql
V7__add_index_on_posts_status.sql

The Rules

V prefix — Marks this as a versioned migration (required).

Version number — Determines execution order. Can be integers (1, 2, 3), decimals (1.1, 1.2), or timestamps (20240115120000). Integers are simplest.

Double underscore __ — Separator between version and description (two underscores, not one).

Description — A human-readable description of the change. Use underscores instead of spaces. This is for documentation — Flyway does not use it for ordering.

.sql extension — Tells Flyway this is an SQL migration.

Common Mistakes

❌ V1_create_users.sql           → Single underscore (need double)
❌ v1__create_users.sql           → Lowercase v (must be uppercase V)
❌ V1__create users table.sql     → Spaces in filename
❌ V1__create-users-table.sql     → Hyphens in filename (use underscores)
❌ create_users_table.sql         → Missing V prefix
✅ V1__create_users_table.sql     → Correct

Version Ordering

Flyway executes migrations in version order:

V1   → runs first
V2   → runs second
V3   → runs third
V10  → runs tenth (numeric order, not alphabetical)
V1.1 → runs after V1, before V2

Writing SQL Migration Scripts

The Golden Rules

Rule 1: Each migration must be self-contained. It should not depend on Java code, external files, or application logic. It is pure SQL.

Rule 2: Migrations are immutable. Once a migration has been applied to any environment, you must NEVER change it. If you need to fix something, create a new migration. Flyway checksums each migration file — if a file changes after being applied, Flyway refuses to start.

Rule 3: Each migration should be atomic. One logical change per migration. “Create the users table” is one migration. “Add an index to the posts table” is another. Do not combine unrelated changes.

Rule 4: Always test migrations locally. Run the migration on your local database before committing. A broken migration in production is painful to fix.

Example Migrations for Our Blog

File: V1__create_users_table.sql

-- V1: Create the users table
-- This is the foundation table — other tables reference it via foreign keys.
CREATE TABLE users (
    id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50)     NOT NULL,
    email           VARCHAR(255)    NOT NULL,
    password_hash   VARCHAR(255)    NOT NULL,
    full_name       VARCHAR(100),
    bio             TEXT,
    role            VARCHAR(20)     NOT NULL DEFAULT 'ROLE_USER',
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_users_username (username),
    UNIQUE INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

File: V2__create_categories_table.sql

-- V2: Create the categories table
CREATE TABLE categories (
    id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL,
    description TEXT,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_categories_name (name),
    UNIQUE INDEX idx_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

File: V3__create_tags_table.sql

-- V3: Create the tags table
CREATE TABLE tags (
    id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50)     NOT NULL,
    slug        VARCHAR(50)     NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_tags_name (name),
    UNIQUE INDEX idx_tags_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

File: V4__create_posts_table.sql

-- V4: Create the posts table with foreign keys to users and categories
CREATE TABLE posts (
    id                  BIGINT          AUTO_INCREMENT PRIMARY KEY,
    title               VARCHAR(500)    NOT NULL,
    slug                VARCHAR(500)    NOT NULL,
    content             TEXT            NOT NULL,
    excerpt             VARCHAR(1000),
    status              VARCHAR(20)     NOT NULL DEFAULT 'DRAFT',
    author_id           BIGINT          NOT NULL,
    category_id         BIGINT,
    meta_title          VARCHAR(200),
    meta_description    VARCHAR(500),
    published_at        TIMESTAMP       NULL,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX idx_posts_slug (slug),
    INDEX idx_posts_status (status),
    INDEX idx_posts_author_id (author_id),
    INDEX idx_posts_category_id (category_id),
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

File: V5__create_post_tags_table.sql

-- V5: Create the post_tags junction table (many-to-many between posts and tags)
CREATE TABLE post_tags (
    post_id     BIGINT NOT NULL,
    tag_id      BIGINT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

File: V6__create_comments_table.sql

-- V6: Create the comments table with self-referencing for nested replies
CREATE TABLE comments (
    id          BIGINT      AUTO_INCREMENT PRIMARY KEY,
    content     TEXT        NOT NULL,
    post_id     BIGINT      NOT NULL,
    author_id   BIGINT      NOT NULL,
    parent_id   BIGINT      NULL,
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
                            ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_comments_post_id (post_id),
    INDEX idx_comments_author_id (author_id),
    INDEX idx_comments_parent_id (parent_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Additive Migrations (Adding Columns, Indexes)

After the initial schema is created, most migrations are additive changes:

File: V7__add_views_count_to_posts.sql

-- V7: Add views_count column to posts table for analytics
ALTER TABLE posts ADD COLUMN views_count INT NOT NULL DEFAULT 0;

File: V8__add_index_on_posts_published_at.sql

-- V8: Add index on published_at for efficient "recent posts" queries
CREATE INDEX idx_posts_published_at ON posts(published_at);

Data Migrations

Sometimes you need to migrate data, not just schema:

File: V9__normalize_post_status_values.sql

-- V9: Normalize inconsistent status values
-- Some old posts have lowercase status values — standardize to uppercase.
UPDATE posts SET status = 'DRAFT' WHERE status IN ('draft', 'Draft');
UPDATE posts SET status = 'PUBLISHED' WHERE status IN ('published', 'Published');
UPDATE posts SET status = 'ARCHIVED' WHERE status IN ('archived', 'Archived');

Destructive Migrations (Careful!)

File: V10__remove_deprecated_meta_columns.sql

-- V10: Remove deprecated meta columns that are no longer used
-- CAUTION: This permanently deletes data. Ensure no code references these columns.
-- Verified in PR #247 that no entity or query references meta_title or meta_description.
ALTER TABLE posts DROP COLUMN meta_title;
ALTER TABLE posts DROP COLUMN meta_description;

Always add comments explaining why the destructive change is safe. Include the PR or ticket number for traceability.

Flyway Commands — Migrate, Info, Repair, Clean

Migrate (Automatic on Startup)

By default, Flyway runs migrate automatically when your Spring Boot application starts. It finds all pending migration files and executes them in order.

You can also run it manually via Maven:

# Run pending migrations without starting the application
./mvnw flyway:migrate -Dflyway.url=jdbc:mariadb://localhost:3306/blogdb \
                      -Dflyway.user=bloguser \
                      -Dflyway.password=blogpass

Info

Shows the status of all migrations — which ones have been applied, which are pending:

./mvnw flyway:info -Dflyway.url=jdbc:mariadb://localhost:3306/blogdb \
                   -Dflyway.user=bloguser \
                   -Dflyway.password=blogpass

Output:

+-----------+---------+----------------------------+------+---------------------+
| Category  | Version | Description                | Type | Installed On        |
+-----------+---------+----------------------------+------+---------------------+
| Versioned | 1       | create users table          | SQL  | 2024-01-15 10:00:00 |
| Versioned | 2       | create categories table     | SQL  | 2024-01-15 10:00:01 |
| Versioned | 3       | create tags table            | SQL  | 2024-01-15 10:00:02 |
| Versioned | 4       | create posts table           | SQL  | 2024-01-15 10:00:03 |
| Versioned | 5       | create post tags table       | SQL  | 2024-01-15 10:00:04 |
| Versioned | 6       | create comments table        | SQL  | 2024-01-15 10:00:05 |
| Versioned | 7       | add views count to posts     | SQL  |                     |
| Versioned | 8       | add index on posts           | SQL  |                     |
+-----------+---------+----------------------------+------+---------------------+

Migrations 7 and 8 have no “Installed On” date — they are pending and will be applied on the next startup.

Repair

If a migration fails halfway (MariaDB does not support transactional DDL for all statements), the flyway_schema_history table may record the migration as failed. repair removes these failed entries so you can fix the migration file and re-run it.

./mvnw flyway:repair -Dflyway.url=jdbc:mariadb://localhost:3306/blogdb \
                     -Dflyway.user=bloguser \
                     -Dflyway.password=blogpass

Clean (DANGER — Never in Production!)

clean drops ALL objects in the database — tables, views, indexes, everything. This is a nuclear option useful only for development and testing.

# ⚠️ DESTROYS ALL DATA — never run this against production!
./mvnw flyway:clean -Dflyway.url=jdbc:mariadb://localhost:3306/blogdb \
                    -Dflyway.user=bloguser \
                    -Dflyway.password=blogpass

To prevent accidental execution against production, add this safety net:

# Prevent flyway:clean from running (recommended for production configs)
spring.flyway.clean-disabled=true

Validate

Checks that applied migrations have not been modified (compares checksums):

./mvnw flyway:validate

If someone edited a migration file that was already applied, Flyway reports a checksum mismatch. This protects against accidental changes to applied migrations.

Handling Migration Conflicts in Team Environments

The Problem

Developer Alice creates V7__add_views_count.sql on her branch. Developer Bob creates V7__add_featured_flag.sql on his branch. Both merge to main. Now there are two V7 migrations — Flyway refuses to start.

Strategy 1: Timestamp-Based Versioning

Use timestamps instead of sequential numbers:

V20240115100000__add_views_count.sql        (Alice, Jan 15 at 10:00)
V20240115143000__add_featured_flag.sql      (Bob, Jan 15 at 14:30)

Timestamps naturally avoid conflicts because two developers never create migrations at the exact same second.

# You do NOT need any special config — just use timestamps as version numbers.
# Flyway orders by numeric value, so timestamps sort correctly.

Strategy 2: Sequential with Communication

Stick with sequential numbers but coordinate via a shared document or naming convention:

Assign version ranges to each developer or feature
Alice uses V7-V9, Bob uses V10-V12
Or agree on the next version in the team’s Slack channel before creating a migration

Strategy 3: Let CI Catch It

Add a CI check that detects duplicate version numbers. If two migration files share a version, the CI build fails before merging to main.

Our Recommendation

For small teams (2-5 developers), sequential numbering with communication works fine. For larger teams, timestamp-based versioning eliminates conflicts entirely. In this series, we use sequential numbers for readability.

Resolving a Conflict

If a conflict does occur:

One developer renames their file to the next available version
Run flyway:repair if the conflicting migration was partially applied
Verify with flyway:info that the migration order is correct
Run flyway:migrate to apply the renamed migration

Seed Data and Repeatable Migrations

Seed Data with Versioned Migrations

For initial reference data (categories, tags, roles), include it in your versioned migrations:

File: V100__seed_initial_data.sql

-- V100: Seed initial reference data
-- Using V100 so there is room for schema migrations before this.

-- Default categories
INSERT INTO categories (name, slug, description) VALUES
    ('Tutorial', 'tutorial', 'Step-by-step programming tutorials'),
    ('News', 'news', 'Latest tech news and announcements'),
    ('Opinion', 'opinion', 'Personal thoughts and perspectives'),
    ('Review', 'review', 'Product and technology reviews');

-- Default tags
INSERT INTO tags (name, slug) VALUES
    ('Java', 'java'),
    ('Spring Boot', 'spring-boot'),
    ('MariaDB', 'mariadb'),
    ('Docker', 'docker'),
    ('REST API', 'rest-api'),
    ('JPA', 'jpa'),
    ('Security', 'security'),
    ('Testing', 'testing');

-- Admin user (password: admin123 — BCrypt hashed)
-- In production, create admin users through a secure process, not in migrations.
INSERT INTO users (username, email, password_hash, full_name, role) VALUES
    ('admin', 'admin@blog.com', '$2a$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy',
     'System Admin', 'ROLE_ADMIN');

Repeatable Migrations

Flyway supports repeatable migrations — files that are re-executed whenever their content changes. They use the R__ prefix instead of V{number}__:

R__create_or_replace_views.sql
R__refresh_materialized_data.sql

Repeatable migrations run after all versioned migrations, in alphabetical order. They are useful for:

Database views that are recreated when the underlying schema changes
Stored procedures or functions
Reference data that changes frequently

File: R__create_post_statistics_view.sql

-- Repeatable: this view is recreated whenever this file changes
DROP VIEW IF EXISTS post_statistics;

CREATE VIEW post_statistics AS
SELECT
    p.id AS post_id,
    p.title,
    p.status,
    u.username AS author,
    c.name AS category,
    p.views_count,
    (SELECT COUNT(*) FROM comments cm WHERE cm.post_id = p.id) AS comment_count,
    (SELECT COUNT(*) FROM post_tags pt WHERE pt.post_id = p.id) AS tag_count,
    p.published_at,
    p.created_at
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN categories c ON p.category_id = c.id;

Every time you modify this file, Flyway detects the checksum change and re-runs it on the next startup. This is perfect for views because DROP VIEW IF EXISTS + CREATE VIEW is idempotent — running it multiple times produces the same result.

Development-Only Seed Data

For development and testing, you can create a seed data migration that only runs in specific profiles:

File: V999__seed_dev_test_data.sql

-- V999: Development/test seed data
-- This migration should NOT exist in production migration files.
-- Keep it in a separate location or use Spring profiles.

INSERT INTO users (username, email, password_hash, full_name, role) VALUES
    ('alice', 'alice@example.com', '$2a$10$dummyhash1', 'Alice Johnson', 'ROLE_ADMIN'),
    ('bob', 'bob@example.com', '$2a$10$dummyhash2', 'Bob Smith', 'ROLE_AUTHOR'),
    ('carol', 'carol@example.com', '$2a$10$dummyhash3', 'Carol Davis', 'ROLE_USER');

INSERT INTO posts (title, slug, content, excerpt, status, author_id, category_id, published_at) VALUES
    ('Getting Started with Spring Boot', 'getting-started-spring-boot',
     'A comprehensive guide to Spring Boot...', 'Learn Spring Boot basics',
     'PUBLISHED', 1, 1, NOW()),
    ('Understanding JPA Relationships', 'understanding-jpa-relationships',
     'Deep dive into JPA entity relationships...', 'Master JPA mappings',
     'PUBLISHED', 1, 1, NOW()),
    ('Docker for Java Developers', 'docker-java-developers',
     'Running Java apps in Docker containers...', 'Docker basics',
     'DRAFT', 2, 1, NULL);

A cleaner approach is to use separate Flyway locations per profile:

# application-dev.properties
spring.flyway.locations=classpath:db/migration,classpath:db/devdata

# application-prod.properties
spring.flyway.locations=classpath:db/migration

Place dev seed data in src/main/resources/db/devdata/. It only runs in the dev profile.

Hands-on: Migrate Blog Database from DDL-Auto to Flyway

Let us convert our blog application from Hibernate ddl-auto=update to Flyway migrations. This is a common real-world task — migrating an existing database to version-controlled migrations.

Step 1: Export the Current Schema

If you have an existing database with data, you need to capture its current state as the first migration. Connect to MariaDB and export the schema:

# Export only the schema (no data) from the existing database
docker exec blogdb mariadb-dump -u bloguser -pblogpass --no-data blogdb > schema_export.sql

Alternatively, if starting fresh, use the migrations we wrote in Section 6.

Step 2: Create the Migration Files

Create the directory structure and files:

src/main/resources/
└── db/
    └── migration/
        ├── V1__create_users_table.sql
        ├── V2__create_categories_table.sql
        ├── V3__create_tags_table.sql
        ├── V4__create_posts_table.sql
        ├── V5__create_post_tags_table.sql
        ├── V6__create_comments_table.sql
        └── V100__seed_initial_data.sql

Copy the SQL from Section 6 into these files.

Step 3: Handle the Existing Database

If your database already has tables from ddl-auto=update, you have two options:

Option A: Clean start (development — easiest)

Drop the existing database and let Flyway recreate it:

# Drop and recreate the database
docker exec -it blogdb mariadb -u root -prootpass \
  -e "DROP DATABASE IF EXISTS blogdb; CREATE DATABASE blogdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL PRIVILEGES ON blogdb.* TO 'bloguser'@'%'; FLUSH PRIVILEGES;"

Now start the application. Flyway creates all tables from scratch.

Option B: Baseline an existing database (production — when data matters)

If the database has real data, use baseline to tell Flyway: “The database is already at V6, start tracking from here.”

# Tell Flyway the existing database represents V6
spring.flyway.baseline-on-migrate=true
spring.flyway.baseline-version=6
spring.flyway.baseline-description=Baseline existing schema

Flyway inserts a “baseline” record in flyway_schema_history and skips V1-V6. Future migrations (V7, V8, …) will be applied normally.

Step 4: Update application.properties

# DataSource
spring.datasource.url=jdbc:mariadb://localhost:3306/blogdb
spring.datasource.username=bloguser
spring.datasource.password=blogpass

# JPA — validate only, Flyway manages the schema
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect
spring.jpa.open-in-view=false

# Flyway
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.clean-disabled=true

Step 5: Start the Application and Verify

./mvnw spring-boot:run

Watch the console output. You should see Flyway executing the migrations:

Flyway Community Edition 9.x by Redgate
Database: jdbc:mariadb://localhost:3306/blogdb (MariaDB 11.x)
Successfully validated 7 migrations
Creating Schema History table `blogdb`.`flyway_schema_history` ...
Current version of schema `blogdb`: << Empty Schema >>
Migrating schema `blogdb` to version "1 - create users table"
Migrating schema `blogdb` to version "2 - create categories table"
Migrating schema `blogdb` to version "3 - create tags table"
Migrating schema `blogdb` to version "4 - create posts table"
Migrating schema `blogdb` to version "5 - create post tags table"
Migrating schema `blogdb` to version "6 - create comments table"
Migrating schema `blogdb` to version "100 - seed initial data"
Successfully applied 7 migrations to schema `blogdb`

Then Hibernate validates the schema:

Hibernate: validating schema for blogdb
Schema validation successful

Step 6: Verify the Schema History

Connect to MariaDB and check the history table:

SELECT installed_rank, version, description, type, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;

+----------------+---------+----------------------------+------+------------+---------------------+---------+
| installed_rank | version | description                | type | checksum   | installed_on        | success |
+----------------+---------+----------------------------+------+------------+---------------------+---------+
| 1              | 1       | create users table          | SQL  | 1234567890 | 2024-01-15 10:00:00 | 1       |
| 2              | 2       | create categories table     | SQL  | 2345678901 | 2024-01-15 10:00:01 | 1       |
| 3              | 3       | create tags table            | SQL  | 3456789012 | 2024-01-15 10:00:02 | 1       |
| 4              | 4       | create posts table           | SQL  | 4567890123 | 2024-01-15 10:00:03 | 1       |
| 5              | 5       | create post tags table       | SQL  | 5678901234 | 2024-01-15 10:00:04 | 1       |
| 6              | 6       | create comments table        | SQL  | 6789012345 | 2024-01-15 10:00:05 | 1       |
| 7              | 100     | seed initial data            | SQL  | 7890123456 | 2024-01-15 10:00:06 | 1       |
+----------------+---------+----------------------------+------+------------+---------------------+---------+

Every migration is tracked with its version, description, checksum, timestamp, and success status.

Step 7: Add a New Migration

Let us simulate adding a new feature — a views_count column to the posts table.