Correct way to add liquibase to an existing spring boot project

Question

I have an existing spring boot project and a database for the same. Now, I would like to add liquibase to handle further database migration. What are the correct steps to do this?

I have followed this article to add liquibase and generate changelog. Most articles I've found talk about using liquibase in a project starting from scratch or are not too detailed about the implementation. So far, I've done the following:

Added the dependencies and plugin in pom.xml

<dependencies>
    //..other dependencies
    <dependency>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-core</artifactId>
    </dependency>
    <dependency>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>3.6.2</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <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>
</build>

Added the liquibase.properties file under src/main/resources

url=jdbc:mysql://localhost:3306/demodb
username=root
password=root
driver=com.mysql.jdbc.Driver
outputChangeLogFile=src/main/resources/db/changelog/changes/demodb-changelog.xml

Updated the application.properties file under src/main/resources to handle changelogs

#Hibernate
spring.datasource.url=jdbc:mysql://localhost:3306/demodb
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root

#Jpa
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

#Liquibase
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml

Created the db.changelog-master.xml file under src/main/resources/db/changelog

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation = "http://www.liquibase.org/xml/ns/dbchangelog/1.9
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">

</databaseChangeLog>

Ran the spring boot app so that two new tables are created in the database - DATABASECHANGELOG (which is empty at the moment) and DATABASECHANGELOGLOCK (which has a single null entry at the moment)

Generated the demodb-changelog.xml file from the terminal to create the changelog for the current state of the database

mvn liquibase:generateChangeLog

Then to sync the current changelogs as executed, in liquibase.properties added:

changeLogFile=src/main/resources/db/changelog/changes/demodb-changelog.xml

And then from the terminal ran:

mvn liquibase:changelogSync

Now, the DATABASECHANGELOG table has entries for the changelogs as executed.

Next in the db.changelog-master.xml file, added the generated file:

<include file="db/changelog/changes/demodb-changelog.xml"/>

Now, when I run the app, I get the exception:

Caused by: liquibase.exception.MigrationFailedException: 
Migration failed for change set db/changelog/changes/demodb-changelog.xml
Reason: liquibase.exception.DatabaseException: Table 'abc' already exists

So, this is trying to run the changelog files again. How do I configure to run only those changesets that have not yet been run? I thought that the function of the DATABASECHANGELOG was to handle the changesets that have been executed, but I guess I'm wrong here.

I could run the application without the include tag in db.changelog-master.xml, but I guess all the changelog files need to be listed here as I would need all the changelog files if I were to run this app in a different machine to create the entire database from scratch.

So how to configure liquibase to run only changelogs that have not yet been executed?

Answer 1

The problem here is filename field stored in the DATABASECHANGELOG table.

Liquibase determines identity of the changeSet not just by id or by id and author, but instead it determines identity of the changeSet based on value of all three fields: id, author, filename.

When you run mvn liquibase:update or mvn liquibase:changelogSync, the filename of your changelog is src/main/resources/db/changelog/changes/demodb-changelog.xml. This is value that stored by liquibase into DATABASECHANGELOG.FILENAME field in each table row.

But, when you start your spring-boot application, the filename of your changelog is classpath:db/changelog/db.changelog-master.xml, and filename of included into it changelog is db/changelog/changes/demodb-changelog.xml.

Then, Liquibase checks what changesets are already applied.

It checks the id, author and filename of each changeset from DATABASECHANGELOG table and each changeset in the db/changelog/changes/demodb-changelog.xml file, and it found that nothing matched, because filename field differs. In the DB it's value is src/main/resources/db/changelog/changes/demodb-changelog.xml.

src/main/resources/db/changelog/changes/demodb-changelog.xml != db/changelog/changes/demodb-changelog.xml

So, Liquibase thinks these are different changesets and tries to apply them. It fails because table already exist. But if you change your changesets to something that can be applied multiple times, you will see that Liqibase created a new set for rows in the DATABASECHANGELOG table with same id, same author but different filename...

Honestly, I don't know how to solve this problem. It looks like a fundamental problem of how Liquibase in Spring Boot works. When changelog files are read on spring-boot application start, they MUST be in classpath, especially if default fat-jar used. But when you run Liquibase as maven plugin, they are usually on local file system (it's not true, see my "Update 2").

Maybe some workaround exist, please add it in comment and I'll update the answer.

Update

One workaround that I found is to use logicalFilePath attribute of <databaseChangeLog> tag.

Use to override the file name and path when creating the unique identifier of change sets. Required when moving or renaming change logs.

If you carefully set it in all your changelogs/changesets, it should work.

An example for your db/changelog/changes/demodb-changelog.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd"
        logicalFilePath="db/changelog/changes/demodb-changelog.xml">

</databaseChangeLog>

But take care of <include> tag. It supports including of raw *.sql files. And you can't set logicalFilePath for them. And logicalFilePath that is set on <databaseChangeLog> tag is also not inherited.

Update 2

I found a more robust solution.

Just don't specify the prefix src/main/resources in changeLogFile property of maven plugin. This way maven will search changelog in classpath, not in filesystem. So, you must do mvn process-resources first, before executing liquibase maven plugin.

So, your changeLogFile property of liquibase maven plugin (liquibase.properties file) should look like this:

changeLogFile=db/changelog/changes/demodb-changelog.xml

Then, when you run

mvn process-resources liquibase:changelogSync

Liquibase will create records in DATABASECHANGELOG table with FILENAME field equal to db/changelog/changes/demodb-changelog.xml, which is equal to filename, used by spring-boot when it runs migrations on startup (classpath: prefix automatically stripped by Liquibase when it compares filenames). And finally this makes Liquibase to understand, that these are the same set of changesets, and they are already applied.

Answer 2

MySQL will maintain the log for liquibase in table,

Liquibase uses the DATABASECHANGELOG table to track which changeSets have been ran.

Whenever the changelog runs, for each change log one row will be added the table, Based on the ids we can know what Change logs are ran,

There are some flags like runAlways, runOnChange, these will help us to execute/ not execute.

you can refer to:

For understanding flags:

https://www.liquibase.org/documentation/changeset.html

For understanding DATABASECHANGELOG Table: 

https://www.liquibase.org/documentation/databasechangelog_table.html