Skip to main content

Database Versioning

We leverage Liquidbase for database version management & provisioning new tenant databases.

Configuration​

Liquidbase configurations for the LMS sit in the following locations:

pom.xml/plugins/liquibase-maven-plugin

  • This includes database connections to the reference/ base database
  • It also includes settings for output locations for changelogs generated in the steps below.

src/main/resources/liquidbase

  • This is the base location for all LMS changelogs

src/main/resources/liquidbase/changelog/root-changelog.xml

  • This is the root changelog, which is generated as part of Liquidbase setup.
  • This changelog will be the starting point for all new accounts.
  • It's assumed that all existing databases will already be up-to-date with all the scripts in this changelog.
  • This will rarely change, but every major LMS may involve a reset of this root change-log (Additional research is required here.)

src/main/resources/liquidbase/sql

  • All additional sql scripts should be numbered and added to this folder.
  • Please refer to the steps below on how to properly annotate the scripts for Liquidbase to load & sequence your scripts

Generate Initial Changelog​

We need to generate initial changelogs to

  1. Generate an initial database - generation script
  2. Generate an initial state for existing databases
  3. Use the changelog to existing databases so as to avoid duplicate script executions.

To generate the initial changelog, run the command below:

mvn liquibase:generateChangeLog

Initializing existing databases​

  • All existing databases must be marked as up-to-date with the generated changelog so as to avoid duplicate excutions.
  • To do this, run:
liquibase changelog-sync --changelog-file=changelog-root.xml
  • This will populate Liquibase's tracking table (ad_DATABASECHANGELOG) without actually applying the changes to the database.
  • This ensures Liquibase recognizes the existing schema as already deployed
  • We'll need to apply this changelog across all existing databases

Adding Scripts​

  1. Create a numbered script in liquidbase/sql
  2. Each script must start with liquidbase metadata - sample below

Create file liquidbase/sql/1.alter_b_user_new_col.sql

Add script content as:

-- liquibase formatted sql  

-- changeset duggan:1759197560-1

alter table b_user add column xyz varchar(45);

Changeset ID Generation

  • Each changeset ID should be formatted in the format Author:EPOCHSECONDS-INDEX

  • To generate EPOCHSECONDS, run the following command.

echo $EPOCHSECONDS

Adding Procedures/ Functions scripts​

  • Procedures and functions require usage of delimiters in their scripts. This is not natively supported by Liquidbase.
  • To setup procedures, replace delimiters with the Liquidbase endDelimiter annotations as shown in the sample below
-- liquibase formatted sql

-- changeset duggan:1762222855-1

drop procedure if exists proc_trial_balance_v3;


-- changeset duggan:1762222855-2 endDelimiter:$$

CREATE PROCEDURE `proc_trial_balance`(p_param_1 date)
BEGIN
    -- procedure code here
END
$$
-- endDelimiter:;

Script Validation​

To validate that your scripts are properly annotated, run:

mvn liquibase:validate

Future database updates​

To update your database with new changesets as follows:

Local changes​

mvn liquibase:update

Web endpoint​

Using swagger, send a PUT request as follows:

PUT http://lending.me.co.ke/kopesha/api/v1/tenants-management/t10008