Using Liquibase for Schema Upgrades and Data Updates

Generating SQL From Broadleaf Liquibase Configuration

Broadleaf recommends using our Liquibase approach for the greatest flexibility and ease during upgrades. However, if you are not using Liquibase as your strategy for database schema maintenance and wish to acquire raw SQL for Broadleaf schema changes during an upgrade, you may do so using standard utilities provided by Liquibase. This involves several steps:

Establish a baseline schema and databasechangelog reference for you current version in a temporary database using Liquibase utilities.
Emit a migration sql file by referencing your target version liquibase changelog file and temporary baseline database using Liquibase utilities.
Repeat steps 1 and 2 for each microservice you plan to upgrade.

The end result of steps 1 & 2 should be a migrate.sql file that represents the delta between the baseline and the target version. This SQL will then be useful to apply manually against a development environment for confirmation of functionality with the upgraded codebase.

More Detail

💡

If you have a lower environment (local, dev, etc…) where you do leverage the Liquibase lifecycle and simply want migration sql for a higher environments, then you may be able to skip the baseline creation step, since you already have this state in the lower environment. In such a case, you could create a temporary copy of the relevant schemas in the database to serve as your baseline instead.

Baseline Schema

Identify the pom.xml for the FlexPackage (e.g. Min, Browse, Cart, Processing, Supporting), or granular service for which an upgrade is desired.
Before upgrading any release train information in this pom (before you target any new versions of Broadleaf code), you will first create a baseline schema.
Create a temporary database using your desired RDBMS platform in which the baseline can be stored.
Add support for the liquibase maven plugin to the pom.xml file—similar to Liquibase Maven Example.
In the same directory as the pom.xml file, add a file called liquibase.properties.
In the liquibase.properties file, add values to inform liquibase of how to connect to your temporary database. Refer to Liquibase Properties Example.
In a terminal in the same directory as the pom.xml file, execute mvn liquibase:updateSQL. This should result in a file called migrate.sql being created in the ./target directory of your maven project. Review this file briefly to see what will be created for the baseline. When satisfied, execute mvn liquibase:update to actually create your table structures in your previously empty target database/schema. Use a sql editor to review your update and confirm the baseline is now populated.

Create Migration Upgrade SQL

Once you have created a baseline database/schema (or have identified a copy of a lower environment where Liquibase is used), you may proceed with generating the migration sql required to upgrade to a newer codebase.

Upgrade your project pom.xml to reference the release train BOM for your target version (or upgrade your pom with more granular overrides) to cause your codebase to adopt the newer release of Broadleaf.
Review liquibase.properties and make sure it’s still pointing to the correct baseline or lower environment copy.
In a terminal in the same directory as the pom.xml file, execute mvn liquibase:updateSQL. This should result in a file called migrate.sql being created in the ./target directory of your maven project. Review this file. It should contain the sql required to migrate from the baseline to the newer version of Broadleaf. This SQL will be useful to apply manually against a development environment for confirmation of functionality with the upgraded codebase. Note, this file may contain SQL updates to databasechangeloglock and databasechangelog (internal Liquibase maintenance tables) that may not be applicable to the target environment for which you want to apply the SQL. If so, you can manually remove these lines from the sql file.

Caveats

Sometimes indexes are added as part of Broadleaf changes. The way the indexes are emitted to migrate.sql is basic and does not take into account possible blocking that could occur during the execution of migrate.sql during the index creation (especially for large tables). You may wish to review any indexes created in migrate.sql and manually change the sql to better take blocking into account (mostly a production concern). See Mitigate Index Creation Blocking for more information. Furthermore, you may already have these indexes in place as part of previous performance tuning, so it’s also useful at this time to confirm if the index creation is even needed.

Liquibase Maven Example

<build>
    <pluginManagement>
        <plugins>
            <plugin>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-maven-plugin</artifactId>
                <version>4.9.1</version>
                <configuration>
                    <propertyFile>liquibase.properties</propertyFile>
                </configuration>
            </plugin>
        </plugins>
    </pluginManagement>
</build>

See https://docs.liquibase.com/tools-integrations/maven/workflows/using-liquibase-maven-plugin-and-springboot.html for more information on the liquibase maven plugin.

Liquibase Properties Example

changelogFile=classpath:/db/changelog/adminnavigation.postgresql.changelog-master.xml (1)
url=jdbc:postgresql://localhost:5432/broadleaf (2)
username=broadleaf (3)
password=demo (4)
defaultSchemaName=adminnavigation (5)
liquibaseSchemaName=adminnavigation (6)

Path pointing to the Broadleaf liquibase changelog for the target service. Note, if you have your own changelog that internally references the Broadleaf changelog, then you may use that here instead.
JDBC URL pointing to the temporary database
Username for the JDBC connection
Password for the JDBC connection
The name of the schema in which the baseline Broadleaf database objects should be created. You may wish to create this schema if it does not already exist in the temporary database (if applicable).
The name of the schema in which liquibase specific maintenance database objects should be created. This can be the same as the default schema declared above.

See https://docs.liquibase.com/concepts/connections/creating-config-properties.html for more information on the contents and properties available to liquibase.properties.

Mitigate Index Creation Blocking

MySQL - No changes to index required. MySQL already supports online behavior by default. See https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes.
Oracle - End the index creation statement with the ONLINE keyword. See https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes003.htm.
MariaDB - You’ll likely want to rewrite the index creation sql according to this template: ALTER ONLINE TABLE [table name] ADD INDEX IF NOT EXISTS [index name] ([column names]). The column names value is a comma delimited list of table column names to include in the index. See https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-inplace-alter-algorithm/#alter-table-add-index-and-create-index.
Postgresql - Add CONCURRENTLY IF NOT EXISTS immediately after the CREATE INDEX portion of the SQL statement. For example, CREATE INDEX idxmsrmtlej61uxhx0yiwpb2rcrv ON blc_notification_state(acked, stopped, notification_name, change_timestamp, next_attempt); becomes CREATE INDEX CONCURRENTLY IF NOT EXISTS idxmsrmtlej61uxhx0yiwpb2rcrv ON blc_notification_state(acked, stopped, notification_name, change_timestamp, next_attempt);. See https://www.postgresql.org/docs/current/sql-createindex.html.
Yugabyte - Yugabyte already supports concurrent behavior during index creation. It is still useful to add the clause IF NOT EXISTS immediately after the CREATE INDEX portion of the SQL statement (similar to Postgresql). See https://github.com/yugabyte/yugabyte-db/issues/10799.

Show Index Creation SQL skipped during Liquibase migration

Indexes are specifically NOT applied during a liquibase upgrade, unless the target table is empty. This is to protect the database and application from locking behavior during index creation that could cause an outage. To review index creation SQL separately for each service targeted for upgrade, you can perform several steps:

Emit a sql file by referencing your target version liquibase changelog file and reference database using Liquibase utilities.
Repeat step 1 for each microservice you plan to upgrade.

The end result be a migrate.sql file that represents the delta between the reference and the target version. Moreover, it should only contain the relevant index creation statements. This SQL will then be useful to apply manually against your environment(s) during a window that best fits your needs.