Database Initialization Using Liquibase

Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-catalog https://github.com/terawarehouse/terawarehouse-react Af...

Slack Discussion

Repositories

After you have finished running some tests using either the code or script base database initialization it’s now time to think of how we can prepare the production script and how we can maintain it as we go along with the development.

I’ve known and experience 2 tools that we can leverage to help us in evolving our database. Namely Flyway and Liquibase. But personally, I prefer Liquibase, the reason is that Liquibase is more portable vs Flyway when dealing with multiple database types. Flyway is much more effective when you are locked down on a single database as you can issue native database-specific SQL statements.

Before we proceed you must download and install the PostgreSQL database first.

[open pom.xml]

Add PostgreSQL and liquibase-core dependencies while commenting on the h2 database.

[open application.properties]
Set the data source connection properties in application.properties file.
Why Postgres? Because h2 is an in-memory database that wipes out the schema and data after each restart. Therefore, we will not be able to demonstrate how Liquibase handles the database versioning and updates.

[open pom.xml again]
How do we run Liquibase to create our database schema?
Before that, we need to define a plugin under the pluginManagement in pom.xml file.
[highlight liquibase-maven-plugin]

It lets us control Liquibase using maven. We intentionally use variables in this plugin so that we can change the connection depending on the profile. For example, we can have development, staging, and production.

[scroll down to profiles]
Which we can see here. Notice, that we have a different value for property liquibase.changeLogFile in our profiles. That is because we normally execute 2 database operation using Liquibase and that is to rebuild or update the database. In the development profile we are in update mode, In rebuild profile, we are rebuilding our database. Rebuild means it will delete all the tables in our database so take note of that.

To run Liquibase we need to define a maven build. In Eclipse it can be done via Run / Run Configurations menu select Maven Build and create a new profile. Set:

Update Mode

Goal=liquibase:update
Profiles=development

Rebuild Mode

Goals=liquibase:dropAll liquibase:update
Profiles=development, rebuild

For more Liquibase maven commands check:
https://www.liquibase.org/documentation/maven/index.html

How does the change logs look like?

[open db.changelog-rebuild.xml]
It includes files which contain both the actual rebuild and data changesets.

[open rebuild and current schema and data SQL files]
These are how they were defined.

Run Liquibase Rebuild.
Open PGAdmin and execute the following SQL: select * from cat_category.

Now, what if we have some changes in our schema? For example, we want to add a new category.

[open current data.xml]
Then we need to update data.xml in the current folder. As the name implies schema.xml contains the schema definition statements while data.xml contains the insert and update of data statements.

Run Liquibase Update.
And then execute the same SQL: select * from cat_category. Now we have 1 more category as expected.

This is how Liquibase works with this we can version our database. Normally we update the current changesets for version x.
[open current changesets]