How to use Liquibase for DB Schema Management

What is Liquibase ?

Firstly, Liquibase is an open-source database schema change management solution which enables you to manage revisions of your database changes easily.

Where can we use Liquibase ?

• CD for Database schema change
• Version controlling of schema change
• Deploy database and application changes together so they always stay in sync.

Liquibase Concepts

Changelog

• It contains sequential changes that need to be made in DB.
• Changelogs supported fomats: SQL,XML,JSON,YAML
• Commonly used available attributes
• PreConditions
  • Used to check the Data Sanity.
• ChangeSet
  • It’s a change that needs to be executed in DataBase via Liquibase.
• Include
  • Additional File containing changeset to include in master changelog.

• Example

--liquibase formatted sql
				
--changeset author_name:id1
create table example (  
    id int primary key,
    name varchar(255)  
);  
--rollback drop table example; 

--changeset author_nmae:id2 
insert into example (id, name) values (1, ‘name 1′);
insert into example (id,  name) values (2, ‘name 2′);  

Tracking Tables

Overall, Liquibase creates and uses two tables for tracking the changeset in changelogfile and the lock for preventing multiple updates at a time.

DATABASECHANGELOG

Moreover, It’s used to compare and deploy the changeset in the changelog file.

For list of attributes in Table:

https://docs.liquibase.com/concepts/tracking-tables/databasechangelog-table.html

DATABASECHANGELOGLOCK

It prevents multiple instances of Liquibase from updating the database at the same time.

For the list of attributes in Table:

https://docs.liquibase.com/concepts/tracking-tables/databasechangeloglock-table.html

Database Connections

Also, Liquibase supports both SQL and NoSQL Database

For list of supported Database:

https://docs.liquibase.com/install/supported-databases.html

Liquibase Commands

Update:

The update command updates the SQL change defined in Changelog to existing DB.

For more info :

https://docs.liquibase.com/change-types/update.html

Example:

ChangeLogFile Contents

-- changeset liquibaseuser:1
UPDATE  liquibase.person  SET  address  =  'address value'  WHERE  name='Bob';

liquibase  --changeLogFile "<filePath>" --url <DataBaseUrl> --username "<username>" --password "<password>" update

Rollback

The rollback command rolls back changes made to the database based on the specified tag.

For more info:

https://docs.liquibase.com/commands/rollback/rollback-by-tag.html

Example

ChangelogFile Contents

-- changeset liquibaseuser:1
create table Details1 ( id int primary key, name varchar(255) );
-- rollback drop table Details1;

liquibase --changeLogFile="filePath" --url <DataBaseUrl> --username "<username>" --password "<password>" rollback <tag>

Snapshot

The snapshot command captures the current state of the database.

For more info:

Snapshot is in JSON or YAML Format only

https://docs.liquibase.com/commands/snapshot/snapshot.html

Example

liquibase --output-file=mySnapshot.json --url <DataBaseUrl> --username "<username>" --password "<password>" --snapshot --snapshotFormat=json

Diff

The diff command in Liquibase allows you to compare two databases of the same type, or different types, to one another.

For more info:

https://docs.liquibase.com/commands/diff/diff.html

Example

liquibase diff
--format=json
--url="<SOURCE_DATABSE URL>"
--username=<SOURCE_DATABASE USERNAME>
--password=<SOURCE_DATABASE PASSWORD>
--referenceUrl="<REFERENCE_DATABASE URL>"
--referenceUsername=<REFERENCE_DATABASE USERNAME>
--referencePassword=<REFERENCE_DATABASE PASSWORD>

Conclusion

Liquibase gives rich cli options and with support of docker image. As a result we create CI/CD pipeline for managing database as a code in version control system.