Database Population Using Flyway and Spring Boot

Database Population Using Flyway and Spring Boot

This post is an extension of previously published post on database migration: Database Migration Using Flyway and Spring Boot. So same local environment setup will be used. In this post we will take a look into Flyway Callback functions and how to setup them in Spring Boot.

Use Case

We are building an application which needs to be deployed on multiple environments, like: DEV, QA, PROD. We have version control and continuous integration pipeline for our code to be deployed automatically setup, but now we want to go one step further and instead manually updating database every-time schema changes we want to do it automatically and we need to populate database with default data in tables created.

Stack

  • Spring Boot 2
  • Hibernate
  • Flyway
  • Docker
  • MariaDB

Database Population

As mentioned before this post is just an extension. So we wont go through the environment setup. All details about that can be find in a post: Database Migration Using Flyway and Spring Boot or if you just want a source code GitHub.

To populate our coffee DB with data, after schema migration is done, we will use one of Flyways lifecycle hooks called afterMigrate. Full list of callbacks can be found here.

So to make use of Flyway callbacks in Spring Boot we simply need to add extra SQL script with callback name in resources/db.migration directory, in our case afterMigrate.sql.

Let’s assume that the requirement is to have 3 coffees added by default since our coffee shop will be selling them no matter what, content of afterMigrate.sql:

INSERT IGNORE INTO coffee (id, name, cup_size, price) VALUES (1, "Latte", "Medium", 2.99);
INSERT IGNORE INTO coffee (id, name, cup_size, price) VALUES (2, "Americano", "Large", 3.99);
INSERT IGNORE INTO coffee (id, name, cup_size, price) VALUES (3, "Capuchino", "Small", 1.99);

INSERT IGNORE statements are used because Flyway will populate DB on first start up, but next time data will be there and INSERT would break, but with a little help of INSERT IGNORE we can check if data were populated already and if yes INSERT will be just skipped.

After that is added we can start our demo app with ./gradlew bootRun command and message that callback was triggered will be printed out into logs:

o.f.c.i.c.SqlScriptCallbackFactory       : Executing SQL callback: afterMigrate

Coffee table after script ran:

MariaDB [test]> select * from coffee;
+----+-----------+----------+-------+
| id | name      | cup_size | price |
+----+-----------+----------+-------+
|  1 | Latte     | Medium   |  2.99 |
|  2 | Americano | Large    |  3.99 |
|  3 | Capuchino | Small    |  1.99 |
+----+-----------+----------+-------+
3 rows in set (0.001 sec)

Next time app will be started warnings that data is already added will be visible, but application startup or database won’t be effected:

2019-11-14 22:32:13.219  WARN 62189 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Duplicate entry '1' for key 'PRIMARY' (SQL State: 23000 - Error Code: 1062)
2019-11-14 22:32:13.220  WARN 62189 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Duplicate entry '2' for key 'PRIMARY' (SQL State: 23000 - Error Code: 1062)
2019-11-14 22:32:13.222  WARN 62189 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Duplicate entry '3' for key 'PRIMARY' (SQL State: 23000 - Error Code: 1062)

Thank you for reading, full source code can be found on GitHub.

Add Comment

Your email address will not be published. Required fields are marked *