Database Migration Tool - Liquibase

Database Migration Tool - Liquibase

1. Liquibase

1.1 Overview

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. It

• Supports code branching and merging
• Supports multiple developers
• Supports multiple database types
• Supports XML, YAML, JSON and SQL formats
• Supports context-dependent logic
• Cluster-safe database upgrades
• Generate Database change documentation
• Generate Database “diffs”
• Run through your build process, embedded in your application or on demand
• Automatically generate SQL scripts for DBA code review
• Does not require a live database connection

1.2 Concepts

Changelog file

Developers store database changes in text-based files on their local development machines and apply them to their local databases. Changelog files can be be arbitrarily nested for better management.

Change Set

Change Sets are uniquely identified by the “author” and “id” attribute along with with the location of the changelog file and are the units Liquibase tracks execution of. When Liquibase runs, it queries the DATABASECHANGELOG table for the changesets that are marked as executed and then executes all changesets in the changelog file that have not yet been executed.

Changes

Each changeset generally contains a change which describes the change/refactoring to apply to the database. Liquibase supports both descriptive changes that generate SQL for supported databases and raw SQL. Generally there should be just one change per changeset to avoid failed autocommit statements that can leave the database in an unexpected state.

Preconditions

Preconditions can be applied to either the changelog as a whole or individual change sets. If a precondition fails, Liquibase will stop execution.

Contexts

Contexts can be applied to changesets to control which are ran in different environments. For example, some changesets can be tagged as “production” and others as “test”. If no context is specified, the changeset will run regardless of the execution context

2. Bootstrap with existing Spring Boot

2.1 Generate Legacy Changesets

Since we want to apply Liquibase to an existing project, we need to generate legacy changeSets for tables that already exist. With added liquibase-maven-plugin maven dependency and properties from liquibase.properties in classpath, we can run mvn liquibase:generateChangeLog to generate changeLog file.

<!-- maven plugin -->
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>3.6.2</version>
</dependency> 

<plugins>
    <plugin>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>3.6.2</version>
        <configuration>                  
            <propertyFile>src/main/resources/liquibase.properties</propertyFile>
        </configuration>              
    </plugin> 
</plugins>

#properties for plugin
url=jdbc:postgresql://localhost:5432/postgresql
username=root
password=root
driver=org.postgresql.Driver
outputChangeLogFile=src/main/resources/liquibase-outputChangeLog.xml
changeSetContext=legacy

2.2 Run Liquibase on startup

With adding liquibase-core as maven dependency, Spring boot will auto configure it for us. Spring properties related have liquibase as prefix. Every time the application starts, it will check the changeLog file automatically to apply upgrade if present and there is nothing extra needs to do.

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.6.2</version>
</dependency>

#Spring Liquibase properties
liquibase.change-log=classpath:/db/changelog-postgresql.xml
liquibase.contexts=non-legacy

2.3 Tips

• Shut down DDL auto-generation in JPA
• To set-up a new database, comment liquibase.contexts=non-legacy for the first start up so that it will run legacy changeSets. After that, keep using iquibase.contexts=non-legacy to check upgrade
• You can access /actuator/liquibase to check changeSets executed