1 / 27

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 S upports various file formats for defining the DB structure. Why Liquibase?.

emerick
Download Presentation

Liquibase

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Liquibase Database change management

  2. 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

  3. 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

  4. What are the other similar tools? • Flyway • Liquibase • c5-db-migration • dbdeploy • mybatis • MIGRATEdb • migrate4j • dbmaintain • AutoPatch

  5. 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 changelogformat • build-in: XML, YAML, JSON and SQL

  6. 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

  7. Major concepts • Changelogfile • Changeset • Changes • Preconditions • Contexts

  8. Change log file • The root of all Liquibase changes is the databaseChangeLogfile. <databaseChangeLog> <changeSet id="1" ...> ... </changeSet> <changeSet id="2" ...> ... </changeSet> </databaseChangeLog>

  9. Change log file • It is possible to break up changelogsinto multiple manageable pieces <databaseChangeLog> <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"/> ... </databaseChangeLog>

  10. Change SET • The unit Liquibase tracks execution of • Database operation • Liquibase attempts to execute each changeSetin 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

  11. Change SET <changeSet id="2.1.0-update-display-order-admin-user-role-event-mgr" author="Vindya" dbms="mysql" context="prod, test"> <comment>Sample of changeset</comment> <!-- comment is optional --> <change .. /> <!-- will be explained later --</changeSet>

  12. Change • Each changeset contains a change which describes the change/refactoring to apply to the database.  • One change per changeset

  13. 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

  14. 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

  15. Change • Referential Integrity Refactorings • Add Foreign Key Constraint • Drop Foreign Key Constraint • Drop All Foreign Key Constraints • Add Primary Key Constraint • Drop Primary Key Constraint

  16. Change • Non-Refactoring Transformations • Insert Data • Load Data • Load Update Data • Update Data • Delete Data

  17. Change • Architectural Refactorings • Create Index • Drop Index • Custom Refactorings

  18. Change • Modifying Generated SQL • Custom SQL • Custom SQL File • Custom Refactoring Class • Execute Shell Command

  19. 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

  20. Preconditions <?xml version="1.0" encoding="UTF-8"?> <changeSet id="1" author=”sfesenko” > <preConditionsonFail="MARK_RAN"> <tableExiststableName="TEST" /> </preConditions> <dropTablecascadeConstraints="true" tableName=”TEST” /> </changeset>

  21. 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

  22. Christie Best practice • https://christiecowork.atlassian.net/wiki/spaces/CC/pages/1072005286/Database+Version+Control+with+Liquibase

  23. 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.

  24. Command Line? • Create property file liquibase.property with connection • parameters: • driver=com.mysql.jdbc.Driver • classpath=db_changelogs/api • changeLogFile=changelog-api-master.xml • username=<username> • password=<password> • url=jdbc:mysql://localhost:3306/christie • logLevel=DEBUG • Run liquibase as liquibase <command>

  25. Liquibase Commands? • update • updateSQL • validate • status • dropAll • rollbackCount • generateChangeLog • diff • changelogSync • changelogSyncSQL • clearCheckSums

  26. 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

  27. Demo

More Related