Flyway usage: what exactly is the migration concept?

Question

I looked at the Flyway samples and documentation and tried to understand if it is useful in my environment. The following conceptual detail is unclear to me: How does Flyway manage the changes between database versions? It obviously does NOT compare database life-instances (see answer here:Can Flyway find out and generate migration files from datamodel?)

In detail my setup looks like this: I create SQL create and insert scripts when coding (automatically and manually). This means every version of my database is represented by a number of insert/create statements. In my world I execute these scripts through a database tool (sqlplus from Oracle). Each run would setup the database _from_scratch_ (!).

Can I put these very same scripts 1 to 1 inside the "migration" path of Flyway? What happens if the target database is way older than the last "migration step" I did (or flyway did not yet exist when it was installed)?

Update:

I got some input from another Flyway user:

It seems like each "migration" (version of the database) has to be hand-written SQL/Java code and contains only "updates" from the previous "migration" of database.

If this is true, I wonder how this can be used with traditional coding technics: in my world SQL statements are generated automatically and contain all database init/create statements, not just "updates" to some previous version. If my SQL code generator could do that, then I wouldn't even need a tool like Flyway :-).

Answer 1

Your question about "how to handle a DB that has a longer history than there are migration scripts?" You need to create a V1_ migration/sql script that matches/recreates your latest DB schema. Something that can take a blank DB to what you have today. Create/generate that sql script using your existing DB tools and then put it in flyways migration directory. (And test V1 by using flyway against a clean DB and see if you get what you expect.) http://flywaydb.org/documentation/existing.html

After that point in time, all later versions must be added in as you work. When you decide you need a new table, in your dev environment, write a new V*_.sql that modifies your schema to the way you need it.

This blog goes over this situation for a Spring/SQL application. https://blog.synyx.de/2012/10/database-migration-using-flyway-and-spring-and-existing-data/