Let’s say you want to migrate your database to another environment.
For example , you have your database set up in development environment and you want to migrate that to test environment.
The traditional way to do that is to create scripts for each database change , store them in a file and fire them in the environment you want to migrate to.
This is error prone and time consuming.
You need to manually run the scripts every time a database change is done , even if it is a very minor change.
Also every time a change is done , you need to make sure you run only the new scripts and not the old ones (accidentally).
There is a solution for this.
And it is automated!
You just need to place your scripts in a folder with version number and the latest version will be automatically executed when your spring boot application starts!
The tool which does this is called flywaydb
Let’s say explore this with an example.
Let’s say you created a new table and inserted a record in your development database and you want to migrate these changes to another database.
These are the scripts:
create schema demo; create table demo.test( name varchar(30)); insert into demo.test(name) values ('vijay');
And you want to migrate this to the below database ( I tried with a local postgres database):
To do this you need to follow the below steps:
STEP1: Add the relevant dependencies:
You need to add the below dependencies:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>
spring-boot-starter-web for the core spring dependencies
spring-jdbc – this is required by flywaydb to perform the database operations
postgresql – this is the database driver
flyway-core – this is the flywaydb dependency
STEP2: Configure application.yml with the database migration details:
Here is a sample:
spring: flyway: url: jdbc:postgresql://localhost:5432/postgres user: postgres password: table: schema_version
The url , user and password details are self explanatory , they are the details of the database we want to migrate to.
table: schema_version – this property is used to indicate the table which will be used to maintain the version of your scripts.
Every time your application starts , only the latest version of your script will be run and this is done by storing the versions in the table you specify here (the default and standard name used is schema_version)
STEP3: Add the scripts in resources folder
Create a .sql script file. Name it by starting with the capital letter V.
Then follow it by the major version name (example V1).
If you have minor versions you can separate them using underscores (example V1_1_1)
Then add the file name with the prefix of two underscores (example V1__CREATE_SCRIPTS.sql)
This naming convention is important . Flyway looks for it. It stores the executed version in the table schema_version so that the next time you start your application flyway will refer this table and skip the already run versions and run only the latest version in your project.
I used the name V1__INSERT_RECORDS.sql for this demo.
And where to place this script?
By default flyway looks in folder db/migration under resources folder:
If you want to include your own path , you can do it by mentioning the location using locations keyword in application.yml
spring: flyway: locations: classpath:database/scripts url: jdbc:postgresql://localhost:5432/postgres user: postgres password: admin table: schema_version
In the above case flyway will be looking for the scripts in database/scripts under resources folder.
We are all set!
Now just start the application and the migration takes place automatically.
Here is the output I got in the console on the application start up:
The scripts have been executed and schema_version table has been updated about the latest version.
Let’s check it out.
I checked in psql command line for the table:
The script got executed!
Now let’s check the table schema_version:
The version details have been updated.
Now let’s say we need to add a new change.
I want to insert a record in the test table just migrated .
Let me create a new file and name it V2__INSERT_MORE.sql and add the below content:
insert into demo.test(name) values ('RAJA');
Now when I restart the application , flyway ignores the version V1 and executes the version V2.
Here is the github repository url with the code:
For more refined configurations check out https://flywaydb.org/
Automating database migration is easier using flywaydb!