Database Migrations with Flyway and Liquibase

In production, you never use ddl-auto=update. Schema changes are managed through migrations — versioned SQL scripts applied in order.

---

Why Migrations?

Approach	Dev	Production
ddl-auto=create-drop	✅	❌ Data loss
ddl-auto=update	⚠️	❌ Unreliable
Flyway / Liquibase	✅	✅ Safe, versioned

---

Flyway Setup

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

spring:
  jpa:
    hibernate:
      ddl-auto: validate    # validate schema matches entities
  flyway:
    enabled: true
    locations: classpath:db/migration

---

Migration Files

Place SQL files in src/main/resources/db/migration/:

db/migration/
├── V1__create_authors_table.sql
├── V2__create_books_table.sql
├── V3__add_status_to_books.sql
└── V4__create_categories_table.sql

Naming Convention

V{version}__{description}.sql
│            │
│            └── Description (double underscore before)
└── Version number (must be unique, sequential)

V1__create_authors_table.sql

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

V2__create_books_table.sql

CREATE TABLE books (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    isbn VARCHAR(17) UNIQUE,
    price DECIMAL(8, 2),
    status VARCHAR(20) DEFAULT 'DRAFT',
    author_id BIGINT NOT NULL REFERENCES authors(id),
    published_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_isbn ON books(isbn);

V3__add_status_to_books.sql

-- Add new column
ALTER TABLE books ADD COLUMN page_count INTEGER DEFAULT 0;

-- Data migration
UPDATE books SET page_count = 0 WHERE page_count IS NULL;

---

How Flyway Works

1. App starts
2. Flyway checks flyway_schema_history table
3. Finds which migrations have been applied
4. Runs new migrations in version order
5. Records each migration in the history table
6. App continues startup

The flyway_schema_history table:

version	description	script	checksum	success
1	create authors table	V1__create_authors_table.sql	12345	true
2	create books table	V2__create_books_table.sql	67890	true

Never modify an applied migration — Flyway checksums will fail. Always create a new version.

---

Liquibase (Alternative)

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

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: sushil
      changes:
        - createTable:
            tableName: authors
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: name
                  type: VARCHAR(200)
                  constraints:
                    nullable: false

---

Best Practices

Practice	Why
One change per migration	Easier rollback and debugging
Never edit applied migrations	Breaks checksums
Test migrations on a copy of prod data	Catch issues early
Use validate in production	Ensures entities match schema
Include indexes in migrations	Don’t rely on JPA auto-creation

---

Key Takeaways

1. Never use ddl-auto=update in production — use Flyway or Liquibase.
2. Flyway: SQL files with V{n}__{description}.sql naming.
3. Migrations are applied once and recorded — never modify applied migrations.
4. Set ddl-auto=validate to ensure entities match the schema.
5. Flyway is simpler (SQL files); Liquibase is more flexible (YAML/XML/JSON).

---

What’s Next?

Next: Spring Boot with PostgreSQL — production database setup with Docker.