Differences

This shows you the differences between two versions of the page.

--- development:applications:liquibase:database_migration [2017/12/04 17:45] – adsilva
+++ development:applications:liquibase:database_migration [2021/06/25 10:09] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+====== Database Migration using Liquibase ======
+At the time of writing, we currently use upgrade scripts to migrate to a new version of the schema.
+These upgrade scripts are basically files with SQL commands. The process of developing these SQL scripts is manual and laborious.
+The proposal here is to use Liquibase which is a tried and tested database migration tool.
+Liquibase provides three primary benefits in comparison to our existing migration model:
+  - Changelog
+  - Rollback
+  - Database diffing
+====== Changelog ======
+Unlike the upgrade scripts, the input to Liquibase is a changelog itself.
+Liquibase is able to keep track of the changes that were applied and the changes that haven't been applied yet.
+A changelog can be in any one of the formats:
+  * XML Format
+  * YAML Format
+  * JSON Format
+  * SQL Format
+an example of a changelog is given below:
+<code yaml>
+databaseChangeLog:
+  - changeSet:
+      id: 1
+      author: avi
+      changes:
+        - createTable:
+            tableName: person
+            columns:
+              - column:
+                  name: id
+                  type: int
+                  autoIncrement: true
+                  constraints:
+                    primaryKey: true
+                    nullable: false
+              - column:
+                  name: firstname
+                  type: varchar(50)
+  - changeSet:
+      id: 2
+      author: avi
+      changes:
+        - addColumn:
+            tableName: person
+            columns:
+              - column:
+                  name: username
+                  type: varchar(8)
+</code>
+This also allows us to maintain a linear history of changes.
+If a particular changeset fails, the changeset can be fixed and re-run from the last point of execution.
+====== Rollback ======
+The Rollback feature allows us to rollback our database to a given id, date or tag.
+It can also automatically generate Rollback commands for a given changeset.
+an example of a Rollback is given below:
+<code yaml>
+databaseChangeLog:
+  - changeSet:
+      id: RollbackTest
+      author: avi
+      failOnError: true
+      changes:
+        - createTable:
+            tableName: rollback1
+            column:
+            - column:
+                name: id
+                type: int
+      rollback:
+        - sql:
+            sql: drop table rollback1;
+</code>
+**Note:** commands such as “drop table” and “insert data” have no corresponding rollback commands that can be automatically generated.
+====== Database Diffing ======
+This is one of the most useful features as this allows us to diff our local development database with a QA or Production database.
+With this, we no longer have to manually search for changed schema which can be very error prone. There are high chances that a schema change may be missed.
+Liquibase also allows one to generate the changeset by looking at the diff. This helps to cuts down a lot of manual query writing.
+An example command is given below:
+<code bash>
+liquibase.sh --driver=com.mysql.jdbc.Driver\
+        --url=jdbc:mysql://qaserver:3306/test \
+        --username=bob \
+        --password=bob \
+    diff \
+        --referenceUrl=jdbc:mysql://localhost:3306/test \
+        --referenceUsername=bob \
+        --referencePassword=bob
+</code>
+Liquibase website: http://www.liquibase.org/

Sidebar

Internal Errigal Collaboration Wiki

Differences

Sidebar

Internal Errigal Collaboration Wiki

User Tools

Site Tools

Differences

Page Tools