Flyway usage: what exactly is the migration concept?

6
votes

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 :-).

1
flyway
Have you read flywaydb.org/getstarted/why.html ? – Axel Fontaine
I see. Then I will do the usual approach of trying it and answering my own question later :-) – Peter Branforn
Yes you must have DDL scripts for each migration. These scripts can be as fine grained or epic as you care to have. You can use the output of a sql code generator or hand code them. People with existing DB that want to start using Flyway use a tool to create their "baseline" DDL and put that in the migration dir as V1_.... Since Flyway works with different DBs, it's up to you to figure out how to generate those scripts. – Lance Kind
Maybe this helps: stackoverflow.com/questions/33448636/whats-the-value-of-flyway/… – Lance Kind
stackoverflow.com/questions/37007987/… – Lance Kind

1 Answers

2
votes

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/