Liquibase not picking up seed data with Spring Boot

Question

I am using Spring-Boot 1.2.1, and Liquibase to create both H2 (testing) and PostgreSQL (QA & Production) databases. I have a couple of tables that I want to seed when the db is created. However, despite trying both dataLoad and sqlFile, nothing is getting inserted. My sql file is just a bunch of insert statements such as:

INSERT INTO state (Name, Code) VALUES('Alabama','AL');
INSERT INTO state (Name, Code) VALUES('Alaska','AK');

Here is my relevant changelog-master.xml:

    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.3.xsd"
    objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">

...
<changeSet id="3" author="me">
        <createTable tableName="STATE">
            <column name="code" type="VARCHAR(10)">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(100)"/>
        </createTable>

         <sqlFile dbms="h2, PostgreSQL"
                 encoding="utf8"
                 endDelimiter="\nGO"
                 path="src/main/resources/db/changelog/data/states.sql"
                 relativeToChangelogFile="true"
                 splitStatements="true"
                 stripComments="true"/>
    </changeSet>

Here is my project structure:

When I startup my spring-boot app, I can see that the State table is created, but it has zero rows in it. I also tried taking the out of changeset 3 and using this:

  <changeSet id="4" author="me">
            <loadData file="data/state.csv" tablename="STATE" schemaName="edentalmanager" relativeToChangelogFile="true">
            <column name="name" type="VARCHAR(100)"/>
            <column name="code" type="VARCHAR(10)"/>
            </loadData>
        </changeSet>

The csv file is basically:

Alabama,AL
Alaska,AK
...

I dont' see any messages in the console logs that Liquibase is trying to create or insert the data into the table. Nor do I get any exceptions or error messages.

UPDATE: If I copy off the state.sql as /resources/data.sql then spring-boot picks up the file and executes the sql just fine. Unfortunately, this means every time I startup, it will try and insert those values again, causing startup exceptions (duplicate key violations) But, rather than rely on a single file, I would prefer Liquibase to execute them as part of the changeset as data needs change.

Answer 1

I think there are two issues in your changeset 3:

1. The value of the path property directs to a not existing resource. When you define relativeToChangelogFile="true" then Liquibase will look for classpath:/db/changelog/src/main/resources/.../states.sql. The correct path should be path="../data/states.sql".
2. If the given path is not correct Liquibase should throw an exception. If you didn't get one means Liquibase decided to not execute that part because of other conditions. One of those conditions could be the dbms property. Your changeset should work with a H2 database. It should not work with a PostgreSQL database because of a wrong type name. Try dbms="h2, postgresql" instead.

After those changes I got a filled table based on your project structure.

Answer 2

This is because by default Hibernate drops the schema when initializing. So first Liquibase creates the data and when finished Hibernate drops the tables and recreates them according to the JPA entities you defined.

You can verify this by looking for the log record:

2017-01-19 11:03:48.692  INFO 15161 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export

You can tell Hibernate not to do anything with the DB schema by setting this application property:

spring.jpa.hibernate.ddl-auto=none

Answer 3

I ran into this when upgrading to spring boot 2.4.1 from 2.2.0-RELEASE version. The 2.2.0-RELEASE version had liquibase-core:jar:3.8.0 as dependency and it was accurately taking relativeToChangelogFile="true". However the 2.4.1 version has liquibase-core:jar:3.10.3 version which breaks the loadData feature.

The csv file I was loading and the changelog.xml file were in the same class directory in my case src/main/resources/liquibase/version2/changelog.xml and src/main/resources/liquibase/version2/xref_specialty_codes.csv

Here is the snippet from my changelog.xml for loading the data which did not work with the later version of liquibase

<changeSet author="anon"
               id="xref_specialty_codes_load_data"
               objectQuotingStrategy="LEGACY">
        <loadData catalogName="adb"
                  schemaName="public"
                  encoding="UTF-8"
                  file="xref_specialty_codes.csv"
                  relativeToChangelogFile="true"
                  separator=","
                  tableName="xref_specialty_codes"
                  usePreparedStatements="true">
            <column name="id" type="NUMERIC"/>
            ...
            ....
        </loadData>
    </changeSet>

I load a hierarchy of change log files with the top one at src/main/resources/liquibase/changelog.xml with the following layout.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <property name="now" value="now()" dbms="postgresql"/>
    <include file="src/main/resources/liquibase/version1/changelog.xml" />
    <include file="src/main/resources/liquibase/version2/changelog.xml" />
    ....
</databaseChangeLog>