Jonathan Lalou's blog

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“`