How to edit the existing SQL file in Flyway

Question

I am working with flyway Db migration, and I have download flyway zip folder and placed into my local computer.

I have two files in the sql folder, i.e V1__Create_person_table.sql and V2__Add_people.sql.

Flyway info

• Flyway Community Edition 5.2.4 by Boxfuse
• Database: jdbc:h2:file:./foobardb (H2 1.4)

Schema version: << Empty Schema >>

+-----------+---------+---------------------+------+--------------+---------+
| Category  | Version | Description         | Type | Installed On | State   |
+-----------+---------+---------------------+------+--------------+---------+
| Versioned | 1       | Create person table | SQL  |              | Pending |
| Versioned | 2       | Add people          | SQL  |              | Pending |
+-----------+---------+---------------------+------+--------------+---------+

Flyway migrate

• Flyway Community Edition 5.2.4 by Boxfuse
• Database: jdbc:h2:file:./foobardb (H2 1.4)
• Successfully validated 2 migrations (execution time 00:00.020s)
• Creating Schema History table: "PUBLIC"."flyway_schema_history"
• Current version of schema "PUBLIC": << Empty Schema >>
• Migrating schema "PUBLIC" to version 1 - Create person table
• Migrating schema "PUBLIC" to version 2 - Add people
• Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.092s)

Flyway info

• Flyway Community Edition 5.2.4 by Boxfuse
• Database: jdbc:h2:file:./foobardb (H2 1.4)

Schema version: 2

+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2019-08-19 12:12:40 | Success |
| Versioned | 2       | Add people          | SQL  | 2019-08-19 12:12:40 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

Now, here the question is: if I want to update or edit somehing in above two sql files, how can I do that, should I edit existing file version 1, version 2 and save the file and run all the above command again?

Answer 1

You should not edit your existed scripts. I have to add a new one e.g. V3__Update_person_table.sql and correctly update it.

P.S.

In the big project, we have tens of scripts that iteratively modify the empty database to achieve a current status.

After that, usually, when moving to the next release version, we merge all existed scripts into one or two (when we do not need to keep history anymore).

Notes

This is correct. We do not change existed scripts (this is part of CI/CD). All changes should be added additionally with new scripts. Flyway accepts a directory with all scripts.