Version controlling DB changes has been the elephant in the room nobody wants to address. Since we implemented Liquibase our releases have been much smoother and less stressful. In this post we walk through the whats, when, whys and hows of Liquibase in release management. WHAT is Liquibase?
Liquibase is a DB version tool which helps with tracking, managing and applying DB changes.
WHEN to use Liquibase?
We use it primarily for DDL and for selected few DMLs to populate seed data into pre requisite tables.
WHY did we consider Liquibase ?
Our primary concern with traditional DB change requests was a foolproof rollback plan. With our rapidly changing system and a team of engineers simultaneously working on the same DB schemas it became increasingly unmanageable :
- To track which feature a change was a part of
- When the change was executed
- Rollback the DB to a previous stable state without fat-fingering.
- Make these changes part of the CI/CD pipeline to automate deployments.
HOW to get started with Liquibase ?
10 Steps to get started with liquibase
- STEP 1 : Pre-requisites
a. Hardware
Disk space: 300 MB
Memory: 1 GB RAM
b. Java 11+
1. Check java version : java –version
2. Java is included automatically with the Liquibase installer.
3. To install java manually:
Go to https://www.oracle.com/java/technologies/javase-downloads.html , download the .dmg for your configuration.
Install java using the dmg. Take note of the destination where it is installed. (in Mac: /Library/Java/JavaVirtualMachines/jdk-17.0.2.jdk/Contents/Home)
Add java to your PATH - Open the .bashrc file and add the 2 export statements to it
> vi ~/.bashrc
> export JAVA_HOME=/path/to/extracted-jdk-folder
> export PATH=$PATH:$JAVA_HOME/binSave and execute .bashrc file and verify java is installed successfully.
> source ~/.bashrc
> java --version- STEP 2: Install Liquibase
a. Liquibase setup using installer (Preferred):
Download the Liquibase Mac installer from https://www.liquibase.org/download
i. If you get a ““Liquibase Installer” can’t be opened because Apple cannot check it for malicious software.” Right click on the installer and Open. This will add the software to your security exceptions. You might see another modal with 3 options, click Open again.
ii. It is advisable to have both Java and Liquibase in your PATH variable.
b. Liquibase setup using tar file:
Download the tar file from https://www.liquibase.org/download
Copy it to /usr/local/opt and then add to your PATH variable.
> vi ~/.bashrcAdd the following to your bashrc file
> export PATH=$PATH:/path/to/extracted-iquibase-folder
> source ~/.bashrcc. Liquibase using homebrew:
> brew install gcc
> brew install liquibaseAdd to your PATH variable by updating the following to your bashrc file:
> vi ~/.bashrc
> export LIQUIBASE_HOME=$(brew --prefix)/opt/liquibase/libexec
> export PATH=$PATH:/path/to/extracted-liquibase-folder
> source ~/.bashrc- STEP 3 : Install and Create a postgres DB (Optional)
a. Check out the post here
b. Default public schema is the only one present.
The liquibase scripts will create a new schema “gym”.
- STEP 4 : Checkout github repo https://github.com/sshetty10/liquibase-seed
a. db/ folder has JSON changelogs and SQL changelogs
We preferred SQL changelogs to reduce the learning curve
Easier transition of existing scripts
JSON brings a level of abstraction where a change of the underlying database
does not entail updating changelog , but how often do we change a database ?
Tagging is NOT available in SQL changelogs.
To work around this, tagging of the DB is added as part of the shell script.
- STEP 5: Run the code
a. To run this code please create the same DB as mentioned in step 3.
b. Update Liquibase.properties with
- JDBC url,username and password.
- Postgres jar path - this should be in $your_liquibase_folder/lib.
c. Follow naming convention for the changelogs: sp-$schemaname-changelog-$changelogversion.sql
e. -m : Migrates or pushes changelogs to the DB
f. -r : Rollback until the changelog version tag in the DB.
Check the databasechangelog table to see how the magic unfolds.
g. Migrate:
> cd db
> ./migrate.sh 1.0 -m
Refresh the schema in pgadmin
New schema 'gym' and new table 'trainers' will get created in the DB.
The databasechangelog table gets updated. Notice the tag column
To run all changelogs. Changelogs already executed wont execute again.
> ./migrate.sh all -m ()Observe the tags - gym 1.0 and gym 1.1 tags are now present.
A new table 'students' is also created after gym 1.1 changelog was executed
h. Rollback :
> ./migrate.sh 1.1 -r This will rollback until the 1.1 DB tag in the databasechangelog table.
'students' table is now gone.
To rollback all changelogs. Changelogs already rolled back wont execute again.
> ./migrate.sh all -r 
The 'mygym' schema and 'trainers' table are gone too.
- STEP 6 : Dry Run Mode
To see what the script will do before executing it, we can run the script in a Dry run mode
> ./migrate.sh 1.0 -m -d
> ./migrate.sh 1.1 -r -d - STEP 7 : Integrate in CI/CD pipeline
You can integrate with your pipeline by calling the script using the -m and “all” option.
This will execute only those changelogs which have not been executed.
- STEP 8 : To RESET your DB
While playing around with the script if you need to reset your DB to the previous state
please run the following
DROP TABLE databasechangelog,databasechangeloglock;
DROP TABLE gym.students,gym.trainers;
DROP schema gym;
- STEP 9 : To generate changelogs for your existing DB
> liquibase --driver=org.postgresql.Driver --changeLogFile=localdb.postgresql --classpath=postgresql-9.4-1201-jdbc41.jar --url="jdbc:postgresql://localhost:5432/mydb " --username=scott --password=tiger --defaultSchemaName=gym --classpath=/usr/local/opt/liquibase/lib/postgresql-42.3.2.jar generateChangeLog - STEP 10 : List of Command References
Comentarios