LIQUIBASE Database change management WHAT IS LIQUIBASE Liquibase
LIQUIBASE Database change management
WHAT IS LIQUIBASE? Liquibase is an open-source solution It is for managing revisions of database schema scripts It works across various types of databases Supports various file formats for defining the DB structure
WHY LIQUIBASE? We used to maintain DB scripts(text based) and add them manually to DB But there are more issues � easy to lost sync between code and DB state � hard to recover from error during development (ex: in case of applying wrong statement to DB) � often require to re-create DB from scratch during development � hard to find out state of particular DB environment For these issues, Liquibase is a good solution
WHAT ARE THE OTHER SIMILAR TOOLS? Flyway Liquibase c 5 -db-migration dbdeploy mybatis MIGRATEdb migrate 4 j dbmaintain Auto. Patch
HOW LIQUIBASE IS DIFFERENT FROM OTHERS? Java based and easy to manage with java project Supports for many database types Easy setup Database independent migration support Rollback database changes feature Database diff report Extensibility Support for different changelog format � build-in: XML, YAML, JSON and SQL
HOW TO SETUP? Liquibase can be downloaded from the official site. As of this writing, the stable version is 3. 6. 2 ( released on July 05, 2018) https: //download. liquibase. org/download/? frm=n Download Zip and extract Set the path variable
MAJOR CONCEPTS Changelog file Changeset Changes Preconditions Contexts
CHANGE LOG FILE The root of all Liquibase changes is the database. Change. Log file. <database. Change. Log> <change. Set id="1". . . >. . . </change. Set> <change. Set id="2". . . >. . . </change. Set> </database. Change. Log>
CHANGE LOG FILE It is possible to break up changelogs into multiple manageable pieces <database. Change. Log> <include file="src/api/changelog-api 2. 1. 0. xml"/> <include file="src/api/changelog-api 2. 2. 0. xml"/> <include file="src/api/changelog-api 2. 3. 0. xml"/>. . . </database. Change. Log>
CHANGE SET The unit Liquibase tracks execution of Database operation Liquibase attempts to execute each change. Set in a transaction that is committed at the end, or rolled back if there is an error. Uniquely identified by the "author" and "id" attribute Runs only change sets which are not in executed state yet in DATABASECHANGELOG table
CHANGE SET <change. Set id="2. 1. 0 -update-display-order-adminuser-role-event-mgr" author="Vindya" dbms="mysql" context="prod, test"> <comment>Sample of changeset</comment> <!-- comment is optional --> <change. . /> <!-- will be explained later -</change. Set>
CHANGE Each changeset contains a change which describes the change/refactoring to apply to the database. One change per changeset
CHANGE Structural Refactorings � � � � Add Column Rename Column Modify Column Drop Column Alter Sequence Create Table Rename Table Drop Table Create View Rename View Drop View Merge Columns Create Stored Procedure
CHANGE Data Quality Refactorings � Add Lookup Table � Add Not-Null Constraint � Remove Not-Null Constraint � Add Unique Constraint � Drop Unique Constraint � Create Sequence � Drop Sequence � Add Auto-Increment � Add Default Value � Drop Default Value
CHANGE Referential Integrity Refactorings � Add Foreign Key Constraint � Drop All Foreign Key Constraints � Add Primary Key Constraint � Drop Primary Key Constraint
CHANGE Non-Refactoring Transformations � Insert Data � Load Update Data � Delete Data
CHANGE Architectural Refactorings � Create Index � Drop Index � Custom Refactorings
CHANGE Modifying Generated SQL � Custom SQL File � Custom Refactoring Class � Execute Shell Command
PRECONDITIONS Preconditions can be applied to either the changelog as a whole or individual change sets precondition - assertion, that will be evaluated before execution of changeset. If a precondition fails, Liquibase will stop execution � check for dbms type � check for current user name � check if changeset has been executed � check if table exists � check if table has column � check if view exists � check if FK constraint exists
PRECONDITIONS <? xml version="1. 0" encoding="UTF-8"? > <change. Set id="1" author=”sfesenko” > <pre. Conditions on. Fail="MARK_RAN"> <table. Exists table. Name="TEST" /> </pre. Conditions> <drop. Table cascade. Constraints="true" table. Name=”TEST” /> </changeset>
CHANGESET CONTEXT It’s possible to specify for each changeset in what context it should be run. Context value can be specified on liquibase run. Contexts can be applied to changesets to control which are ran in different environments. Example – prod for production and test for test
CHRISTIE BEST PRACTICE https: //christiecowork. atlassian. net/wiki/spaces/CC/ pages/1072005286/Database+Version+Control+wit h+Liquibase
HOW TO RUN? On Demand � Command Line � Ant � Maven Automated � Servlet Listener � Spring Listener � JEE CDI Listener Java APIs � Liquibase can easily be embedded and executed through its Java APIs.
COMMAND LINE? Create property file liquibase. property with connection � parameters: driver=com. mysql. jdbc. Driver classpath=db_changelogs/api change. Log. File=changelog-api-master. xml username=<username> password=<password> url=jdbc: mysql: //localhost: 3306/christie log. Level=DEBUG Run liquibase as liquibase <command>
LIQUIBASE COMMANDS? update. SQL validate status drop. All rollback. Count generate. Change. Log diff changelog. Sync. SQL clear. Check. Sums
TIPS AND TRICKS Use one file per changeset (greatly simplified merges) Use convention for file names Use “run on change” attribute for stored procedures, views, triggers, etc Decide early if rollback feature will be used Test both incremental and full update Do not change “wrong” changeset - just add new one with fix Consider possibility of “compressing” changesets when full update became slow
DEMO
- Slides: 27