In order to generate migrations, we need to configure Flyway to use a shadow database. This post looks at that process.
This is part of a series of working through Flyway and Flyway desktop to demo database changes. Disclosure: I work for Redgate Software.
Configuring the Shadow
This is an empty database where we run the migration scripts to verify them. Since a user might edit or create their scripts, we want to ensure there are no problems with the syntax or execution with other scripts. This is also a place where we keep the “previous state” of your development database and use this to detect the changes you’ve made.
This database gets cleaned, meaning objects get dropped, regularly, so you configure a space for this. It can be a separate database, or just a schema (more Oracle focused).
For me. I’m going to create a new database in postgreSQL to support this. As you can see below, I use the simple CREATE DATABASE syntax.
Once I do this, I go back to FWD and click the “Generate Migrations” tab. The first time I do this (and only the first time), it asks me to configure a Shadow database.
I click this and get a connection dialog, similar to what I have for my development database. In here I enter the credentials for my shadow database, which are similar to my development ones. I test the connection and verify I can connect.
That’s it. Now I’m configured for a Shadow.
The process is similar for other platforms, just with different credentials. If you need to learn more, you can read about this in the documentation.
Voice of the DBA 
I’m going to ask a dumb question about Flyway. As I understand it Flyway is a DB Migration tool, a tool for moving data from one DB to another. Why would I need Flyway when I can do something like INSERT INTO Myserve.Databse1 .TABLEA FROM Myserve.Databse2 .TABLEA ? O ris that the type of DB Migration you’d use a tool like Flyway for is when you’re moving data between 2 different types of DB like from Oracle to SQL Server? I’m trying to understand under what scenarios someone would need Flyway and if there’s an answer to that somewhere on Redgate just let me know. I briefly looked at the Flyway page and didn’t see an answer to this kind of question.
Thanks
LikeLike
Flyway is more a script runner. It ensures scripts run once on a database. It’s mainly for schema migration, but it works for DML as well. Really any SQL, and it can also have callbacks or triggers to Java/Bash/PoSh/etc.
The reason I’d use it for insert into, or similar scripts is the same reason we have customers that do this. Auditing and ensuring that scripts are run once and logged. The number of times DBAs make simple mistakes, re-run scripts, or forget to run them, is higher than most orgs would like. Using a script runner ensures that you have a repeatable, reliable process and you log/audit what happens.
LikeLike
Gothca. Thanks
LikeLike
Pingback: The Baseline for Flyway | Voice of the DBA