Liquibase: Create schema, tables, items in your database

Liquibase integration with Spring boot

There used to be a time when we maintained database scripts separately.

Gone are those days, when we used to backup tables from one system and import it into yours, just to make the app run!

If you are here, you might have already heard of liquibase and flyway. Both are similar libraries, for DB Versioning.

Let's get started with liquibase.

Download:

Please fork the complete project from my Github.

Scope

The article will teach

• How to create schema using liquibase
• How to create table, and insert data using liquibase.
• How to put liquibase tables, in your defined schema
• How to write test units for Repository, when you have liquibase

Project Structure

Project Structure

Maven Dependencies (POM)

Note: h2database is used to have an in-memory database while running unit tests on Repository.

Application Properties:

Here poc_db is the database name and liquibase_demo is the schema name.

We have setshow-sql:true , so that we can see the queries executed in the background.

We are providing default_schema:liquibase_demo , so that both liquibase changelog tables are created inside our schema rather than public schema.

Schema generation script

This script ensures that our schema is created before liquibase runs.

schema.sql

This file is placed under /resources folder.

Note the resource structure

Now we get into the liquibase configs. Liquibase maintains database changes in the changelog file. Here, for simplicity, we have used SQL. (You can also write the changes in XML, which can be ported easily across multiple databases.). Changelog file, maintains the changes to port to a database, with respect to its current state.

Changelog File

In the changelog, we have included the change files.

Changes

Changes are split into table_creation and data_insertion.

• Table Creation

• Data Insertion

By now, you will be able to generate schema, tables and insert data into those tables, by running your app.

Result

Writing Test Case for Repository

Now we will cover, how to write test units for the repository layer. We need to create a database in H2 (in-memory) database. We will generate the entity tables, using hibernate.hbm2ddl.auto=create. Finally, we insert mock data using @Before and then we execute the tests.

Note: We are disabling liquibase in test scope, because, our liquibase changes were in SQL. Some of the syntaxes might differ in H2 SQL. So it might fail due to this. Had it been .xml, it would have been possible to migrate the tables & data into H2 (didn’t try out).

Here we have application.properties under /test/resourcesfolder.

Test Application Properties

Repository Class

We have a CandidateRepository class with a custom function findCandidateWithLastName(). The query is written in HQL.

Repository Test Class

This is the test class for the above repository. Here we are inserting 2 dummy data’s and in the test case, we are validating the function findCandidateWithLastName().

Note: It is always recommended to write test cases for custom functions created in the Repository layer.

Hope this helped someone!

Found it Interesting?
Please show your support by 👏.