Jonathan Lalou's Blog

Posts Tagged ‘Flyway’

Managing database migrations can be tricky, but Flyway simplifies the process with versioned scripts for schema changes, data updates, and rollbacks.
Here’s a quick guide with useful examples:

✅ 1️⃣ Creating a Table (V1 – Initial migration)
“`
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)…,
email VARCHAR(100)…,
created_at TIMESTAMP…
);
“`
✅ 2️⃣ Inserting Sample Data (V2)
“`
INSERT INTO users (username, email) VALUES
(‘alice’, ‘alice@example.com‘),
(‘bob’, ‘bob@…’);
“`

✅ 3️⃣ Adding a New Column (V3 – Schema change)
“`
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
“`

✅ 4️⃣ Renaming a Column (V4)
“`
ALTER TABLE users RENAME COLUMN email TO contact;
“`

♻ Undo Script (U4__Rename_email_to_contact.sql)
“`
ALTER TABLE users RENAME COLUMN contact TO email;
“`

✅ 5️⃣ Deleting a Column (V5)
“`
ALTER TABLE users DROP COLUMN last_login;
“`

♻ Undo Script (U5__Revert_remove_last_login.sql)
“`
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
“`

✅ 6️⃣ Deleting Specific Data (V6)
“`
DELETE FROM users WHERE username = ‘alice’;
“`

♻ Undo Script (U6__Revert_delete_user.sql)
“`
INSERT INTO users (username, contact) VALUES (‘alice’, ‘alice@example.com‘);
“`

💡 Configuring Flyway in pom.xml
To integrate Flyway into your Spring Boot or Java project, add the following configuration in your `pom.xml`:
“`
<properties>
<flyway.version>11.4.1</flyway.version>
</properties>

<dependencies>
<!– Flyway Core –>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>${flyway.version}</version>
</dependency>

<!– Database Driver (Example: PostgreSQL) –>
<dependency>
“org.postgresql:postgresql:runtime”
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<version>${flyway.version}</version>
<configuration>
<url>jdbc:postgresql://localhost:5432/mydb</url>
<user>myuser</user>
<password>mypassword</password>
<schemas>public</schemas>
<locations>filesystem:src/main/resources/db/migration</locations>
</configuration>
</plugin>
</plugins>
</build>
“`

📂 Migration scripts should be placed in:
`src/main/resources/db/migration/`
Example:
“`
V1__Create_users_table.sql
V2__Insert_sample_data.sql
V3__Add_last_login_column.sql
“`

💡 Flyway Maven Plugin Commands
👉Apply migrations:
“`mvn flyway:migrate“`
👉Undo the last migration (if `flyway.licenseKey` is provided):
“`mvn flyway:undo“`
👉Check migration status:
“`mvn flyway:info“`
👉Repair migration history:
“`mvn flyway:repair“`
Clean database (⚠Deletes all tables!):
“`mvn flyway:clean“`

Axel Fontaine, a software development consultant and creator of Flyway, advocates for disciplined database schema evolution in agile environments. Based in Munich and passionate about continuous delivery, Axel presents Flyway as a lightweight solution to the chaos of ad-hoc migrations. His session, spanning 30 minutes of dense insights, covers Flyway’s mechanics, integration strategies, and recipes for complex changes, drawing from his three-year journey building the tool.

Flyway transforms migrations into versioned SQL scripts, ensuring traceability and repeatability. Axel demonstrates seamless Maven and Gradle plugins, embedding migrations in CI/CD pipelines for zero-downtime deployments.

Tackling Ad-Hoc Migration Challenges

Axel exposes the pitfalls of manual updates: uncertainty about applied changes, script sequencing errors, and application-database mismatches. Flyway counters with a schema history table, tracking versions automatically.

This audit trail, Axel illustrates, restores confidence, allowing teams to query migration status effortlessly.

Core Mechanics and Integration

Flyway’s simplicity shines: place versioned SQL files in a classpath directory, invoke via flyway:migrate. Axel demos Maven integration, applying scripts in order, rolling back if needed.

For Java, callbacks enable pre/post hooks, like data validation. Gradle and Ant support extend reach, fitting diverse build ecosystems.

Handling Complex Schema Changes

Complex alterations, like column renames, demand caution. Axel outlines a three-step recipe: add new columns with defaults, migrate data via triggers, then drop legacy structures—detailed in Refactoring Databases.

This methodical approach, he emphasizes, minimizes risks, preserving data integrity during transitions.

Future Horizons and Adoption

Axel previews Flyway’s roadmap: SBT support, Android/SQLite extensions, and web framework integrations for streamlined workflows. He urges adopting any migration tool—Flyway or alternatives—to conquer this perennial challenge.

GitHub hosts the project, inviting contributions to evolve this essential agile companion.

Links:

• Axel Fontaine on LinkedIn
• Flyway website