How to properly use liquibase `searchPath` option to indicate the respective resource folders?

Question

I'm trying to invoke the update command of liquibase like follows:

liquibase update --changelog-file=./persistence/src/main/resources/changelog/db.changelog-dev.xml \
                 --url="jdbc:postgresql://localhost:5432/sigma"

This results in:

[...]
Starting Liquibase at 23:44:47 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase

Unexpected error running Liquibase: The file classpath:/changelog/db.changelog-master.xml was not found in the configured search path:
    - /Users/ikaerom/Dev/sigma-backend
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/liquibase-core.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/lib
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/jaybird.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/ojdbc8.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/snakeyaml.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/snowflake-jdbc.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/picocli.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/jaxb-runtime.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/jaxb-api.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/jaxb-core.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/hsqldb.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/connector-api.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/mssql-jdbc.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/h2.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/mariadb-java-client.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/liquibase-commercial.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/commons-lang3.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/postgresql.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/sqlite-jdbc.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/opencsv.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/commons-text.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/commons-collections4.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib/jcc.jar
    - /opt/homebrew/Cellar/liquibase/4.17.2/libexec/internal/lib
More locations can be added with the 'searchPath' parameter.

The db.changelog-dev.xml is essentially including db.changelog-master.xml, which then also references some SQL scripts. The two XML files lie in the same resource folder $PROJECT_ROOT/persistence/src/main/resources/changelog. The imported/included SQL files referenced within the changelog XML all lie in the resource folder's subfolders.

Any way of specifying this eluding searchPath or even --search-path parameter (as indicated in the documentation) seems to fail spectacularly:

$> liquibase update --searchPath="./persistence/src/main/resources/" --changelog-file=./persistence/src/main/resources/changelog/db.changelog-dev.xml --url="jdbc:postgresql://localhost:5432/sigma"
Unexpected argument(s): --searchPath=./persistence/src/main/resources/

So let's try the other indicated syntax:

$> liquibase update --search-path="./persistence/src/main/resources/" --changelog-file=./persistence/src/main/resources/changelog/db.changelog-dev.xml --url="jdbc:postgresql://localhost:5432/sigma"
Unexpected argument(s): --search-path=./persistence/src/main/resources/

If I attempt to use LIQUIBASE_SEARCH_PATH=, I end up with this:

[...]
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase

Liquibase Community detected and ignored the following environment variables:
- LIQUIBASE_SEARCH_PATH
To configure Liquibase with environment variables requires a Liquibase Pro or Liquibase Labs license. Get a free trial at https://liquibase.com/trial. Options include the liquibase.licenseKey in the defaults file, adding a flag in the CLI, and more. Learn more at https://docs.liquibase.com.
[...]

I don't really want to buy a pro version just to get this feature working ;).

My question is: how do I specify the search path for liquibase to pick it up in my bash shell?

I find it hard to believe that this wouldn't work, given liquibase is so well documented, and it tries to always give you the correct hints and pointers, if you don't use it correctly. What did I miss?

Update: I have a suspicion that the order of invocation matters. So, the update command should be last in the list. However, no luck so far:

$> liquibase \
          --changelog-file=./persistence/src/main/resources/changelog/db.changelog-dev.xml \
          --url="jdbc:postgresql://localhost:5432/sigma" \
          --searchpath="./persistence/src/main/resources/changelog/" \
          update
[...]
Starting Liquibase at 14:29:51 (version 4.17.2 #5255 built at 2022-11-01 18:07+0000)
Liquibase Version: 4.17.2
Liquibase Community 4.17.2 by Liquibase

Unexpected error running Liquibase: The file ./persistence/src/main/resources/changelog/db.changelog-dev.xml was not found in the configured search path:
    - /Users/ikaerom/Dev/sigma-backend/persistence/src/main/resources/changelog
More locations can be added with the 'searchPath' parameter.

For more information, please use the --log-level flag

Hausmann

Answer 1

Found the solution myself, after digging through the liquibase source code.

In my db.changelog-dev.xm I had a line which included db.changelog-master.xml as follows. That classpath:/ has to be removed:

-    <include file="classpath:/changelog/db.changelog-master.xml"/>
+    <include file="changelog/db.changelog-master.xml"/>

Then, this invocation finally works (mind the adapted searchPath and the relative designation of the changelog parameter settings):

    liquibase \
          --hub-mode=off \
          --headless=true \
          --url="jdbc:postgresql://localhost:5432/sigma" \
          --searchPath="./persistence/src/main/resources" \
          --changelog-file=changelog/db.changelog-dev.xml \
          update 2>&1 | grep -Ev -- "^##"

The --hub-mode=off will prevent liquibase from asking if you want to connect to the liquibase hub. The rest is sugar-coating.

The only problem open is that when liquibase is invoked from the shell CLI, the user ending up owning the changelog/lock tables is the user invoking the liquibase command:

ikaerom@/tmp:sigma> \dt databasechangeloglock
+--------+-----------------------+-------+---------+
| Schema | Name                  | Type  | Owner   |
|--------+-----------------------+-------+---------|
| public | databasechangeloglock | table | ikaerom |
+--------+-----------------------+-------+---------+
SELECT 1
Time: 0.011s
ikaerom@/tmp:sigma> \dt databasechangeloglock
+--------+-----------------------+-------+---------+
| Schema | Name                  | Type  | Owner   |
|--------+-----------------------+-------+---------|
| public | databasechangeloglock | table | ikaerom |
+--------+-----------------------+-------+---------+
SELECT 1
Time: 0.010s

However, when liquibase is updated by invoking the Spring boot application, then the table owner user is the one the application context is setting (in my case sigma):

ikaerom@/tmp:sigma> \dt databasechangeloglock
+--------+-----------------------+-------+-------+
| Schema | Name                  | Type  | Owner |
|--------+-----------------------+-------+-------|
| public | databasechangeloglock | table | sigma |
+--------+-----------------------+-------+-------+
SELECT 1
Time: 0.010s
ikaerom@/tmp:sigma> \dt databasechangelog
+--------+-------------------+-------+-------+
| Schema | Name              | Type  | Owner |
|--------+-------------------+-------+-------|
| public | databasechangelog | table | sigma |
+--------+-------------------+-------+-------+
SELECT 1
Time: 0.009s

This clashes if you run your liquibase update first:

Caused by: liquibase.exception.DatabaseException: ERROR: relation "databasechangeloglock" already exists [Failed SQL: (0) CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:135)
    at liquibase.lockservice.StandardLockService.init(StandardLockService.java:115)
    at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:286)
    ... 94 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "databasechangeloglock" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)

This again can be solved by a proper GRANT for the sigma or a re-assignment of the owner to the rightful user. Or simply by adding the --username property to the name of the spring boot application context or database user owner:

    liquibase \
          --hub-mode=off \
          --headless=true \
          --username="sigma" \
          --url="jdbc:postgresql://localhost:5432/sigma" \
          --searchPath="./persistence/src/main/resources" \
          --changelog-file=changelog/db.changelog-dev.xml \
          update 2>&1 | grep -Ev -- "^##"

Hausmann

Answer 2

For anyone who is using Maven and wants a simple solution, the key is just to set the searchPath under the configuration node of the spring-boot-maven-plugin plugin as shown below. The idea is that the searchPath property is the location of the directory where to get the liquibase.properties file. And the liquibase.properties file can contain references to other settings you may want to do.

There are more information on https://docs.liquibase.com/concepts/changelogs/how-liquibase-finds-files.html and https://docs.liquibase.com/parameters/search-path.html

        <plugin>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-maven-plugin</artifactId>
            <version>4.27.0</version>
            <configuration>
                <propertyFile>liquibase.properties</propertyFile>
                <searchPath>src/main/resources/liquibase/config</searchPath>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>org.liquibase.ext</groupId>
                    <artifactId>liquibase-mssql</artifactId>
                    <version>4.27.0</version>
                </dependency>
            </dependencies>
        </plugin>

The above configuration means that liquibase.properties file will be searched under the src/main/resources/liquibase/config directory.

Also please note that the above example uses liquibase-mssql. If you are using a different database engine, for example, mysql, you can use liquibase-mysql instead.

Shelbyshelden