Mastering Database Version Control: A Hybrid Strategy from State-Based to Flyway Migration

This approach ensures consistency across environments, minimizes human error, and integrates well into modern Dev(Sec)Ops pipeline.
 

Keep continuous integration with Database Version Control (DBVC)

Before continue with the article, If you are interested in further discussing Database Version Control (DBVC) based on your specific implementation case, please do not hesitate to contact us at NaradaCode. We would be pleased to offer our guidance and expertise.

Phase 1: The Preparation (State-Based Setup)

 
Before implementing Flyway, we must ensure all environments start from the same "truth." We utilize a State-Based approach for the initial phase, as it provides the most efficient method for synchronizing schemas across all environments.

Create Flyway State-based project, using Production Database (clone or restored is recommended) as Schema Model (Source Database). By default, Flyway will automatically be scanning structure only (tables structure), sequences, foreign key, index key, SP, function, etc. with no data. You also add static data by additional step as optional.

 
Let’s continue to synchronize the database.
 
 

 
Before Synchronization Process:
 

 
After Synchronization Process:
 

 

Let’s continue Flyway State-based project from preparation step:

1. We can also see the comparison before after to the target database. Example to dev database.

  
2. Click deploy to target database (dev database or test database)

  

Pro Tip: At this stage, your Dev/Test databases are "mirror images" of Production. This is our "Point Zero."

Phase 2: Initializing Flyway (The Baseline)

 
Once your databases are aligned, we need to tell Flyway: "Don't try to recreate what's already there; just start tracking from here." This is called Baselining.
 

1. Project Configuration

Create a Flyway Project with Migration-based. Setup Development Database as source DB and shadow DB for generate baseline/migration pool purpose temporary database.

Upon project setup, the Flyway UI will automatically prompt you to generate a Baseline Script using the Production database as the target.
 

 

In migration menu, baseline script automatically generated. We can also verify the generated query and edit if needed.

2. Executing the Baseline

After preparing the baseline script, let’s execute baseline. In Migration Script page, choose target database. And choose baseline execution. Do the baseline to the target database (test or dev and prod)
 

 
Result: Flyway creates a flyway_schema_history table and marks version 1.0 as "Success," even though no script was run.

Result from Flyway UI:
 

 

Result from DB. Flyway will automatically generate table flyway_schema_history as baseline.
 

 
 

Phase 3: The Shift to Migration-Based Workflow as Daily Operation

To maintain environment parity, manual schema changes are strictly prohibited from this point forward. All modifications must be captured as versioned migration scripts.

 
Creating and Applying the Migration

Apply the changes to your local or Dev environment:

Example there is some change from products.
 

 

Back to Flyway Migration-based UI. From Schema model menu automatically scan database changes. Example products table.
 

 
Also, we can see the query detail changes from database.
 

 

  
After saving the schema model. It’s time to generate migration script. Use clear description to help give clear information. We can edit the query too.
 

 

Also, we can check the undo script before generate the migration script. "Undo script" verification before generating migrations is a “Pro" feature in Flyway.
 

Migration script saved in project.
 

 

To make sure migration script is working well, we can run check to comparing changes, code, drift and doing dry run before running migration.
 

In html files we can check the comparison details.
 

 

After check, Run Migration.
 

  

After migration, table flyway_schema_migration added automatically with new row from migration version.
 

 

Also you can compare source database and target database after migration.
 

 

Don’t forget commit the project to GitLab!