When a database schema change happens in the development environment, some questions appear:
- Were scripts for the DB changes created?
- Where should they be saved?
- When should we apply these changes?
- Have the changes already been implemented?
- How do we track and manage database changes?
- Who did these changes?
Liquibase is a library that can help address these issues. It is an independent library open source database used to track, manage, and apply changes to the database. It helps to manage the source control of your database.
So why is Liquibase different?
From the Liquibase FAQs: “Liquibase works better because it understands what the changes are. For example, a database comparison program would simply see the “person” table on integration has a “firstname” and a “lastname” column, but on live, the “person” table has a “name” column. It would report that you need to drop the “name” column and add a “firstname” and a “lastname” column. While this would leave you with the correct schema, you would lose everyone’s name in the process. With Liquibase, you would have a changeset that says “rename ‘name’ to ‘lastname’ and add a ‘firstname’ column” or, even better, “split the name column on a space and place the values in new ‘firstname’ and ‘lastname’ columns, then drop the ‘name’ column.” Knowing why they are different allows changes to production databases without the fear of losing valuable data.”
In this post I will show you a basic example of how you can use the powerful tool Liquibase together with Gradle to automate these tasks. From there it will be easy to put Liquibase to work with your continuous integration tools.
Some important concepts:
- Is the file that contains references of all scripts that should be applied to the database in any environment.
- Are all the files recorded in a Changelog
- Changesets files can be written primarily in XML, YAML, JSON, SQL
- I chose SQL for this example
- IDs cannot be repeated, otherwise they will not run
- Scripts should be small
- A script Rollback should be added whenever possible
- New scripts should be added on the changelog.xml
- Everything that was executed is registered on the table DATABASECHANGELOG
- To execute:
- gradle task dev update
- gradle task qa update
- gradle task prod update
After that you can check in your changes, and see that these scripts 001 and 002 were applied.
On the table DATABASECHANGELOG is possible check the changes:
You can see this sample in my GitHub.