Automated database migration with liquibase

[MichaelArnauts]

It took a while to figure this out, so I might as well help some others who also encounter this issue.

I wasn’t aware that Camunda 7 didn’t auto-upgrade it’s database schema. I assumed it did since it also automatically created a schema on initial startup. It all went fine from Camunda 7.18 to 7.20, but in 7.21 we got an errors about a missing column.

There are docs on how to migrate the database yourself, but it isn’t automated.

We are running Camunda Platform Run, and did the following changes to automate database migrations.

• Add liquibase and the mysql-connector to the Docker image.
• Add a script that is ran during startup that does the following things:
  • Check if the 7.16.0 tag is applied (this is the lowest version with support for liquibase). This is to check if Liquibase has already been initialized.
  • If it is not initialized, we fetch the current database schema from ACT_GE_SCHEMA_LOG and run the liquibase changelog-sync-to-tag command to create the tables and indicate what our current schema is.
  • Run liquibase update to update the db schema to the latest version.

Dockerfile

FROM camunda/camunda-bpm-platform:run-7.21.0

# Add liquibase for the database schema migration
USER root
RUN mkdir /opt/liquibase \
    && wget https://github.com/liquibase/liquibase/releases/download/v4.27.0/liquibase-4.27.0.tar.gz -O /tmp/liquibase.tar.gz \
    && tar -xzf /tmp/liquibase.tar.gz -C /opt/liquibase \
    && rm /tmp/liquibase.tar.gz \
    && wget https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.3.0/mysql-connector-j-8.3.0.jar -O /opt/liquibase/internal/lib/mysql-connector-java-8.3.0.jar
COPY --chown=camunda:camunda liquibase.sh /camunda/liquibase.sh
USER camunda

# Patch /camunda/camunda.sh to run the database migration before starting the server
RUN sed -i '/exec/ i \/camunda/liquibase.sh' /camunda/camunda.sh

CMD ["./camunda.sh", "--rest", "--webapps", "--production"]

liquibase.sh

#!/usr/bin/env bash

export LIQUIBASE_SEARCH_PATH="/camunda/configuration/sql/liquibase/"
export LIQUIBASE_COMMAND_URL="$DB_URL"
export LIQUIBASE_COMMAND_USERNAME="$DB_USERNAME"
export LIQUIBASE_COMMAND_PASSWORD="$DB_PASSWORD"
export LIQUIBASE_COMMAND_CHANGELOG_FILE="camunda-changelog.xml"
export LIQUIBASE_SHOW_BANNER="false"

LIQUIBASE=/opt/liquibase/liquibase

# Run liquibase directly if arguments are provided
if [ $# -gt 0 ]; then
    exec $LIQUIBASE "$@"
fi

# Check if the base tag exists
$LIQUIBASE tag-exists --tag="7.16.0" 2>&1 | grep "does NOT exist"
if [ $? -eq 0 ]; then
    # The base tag doesn't exist, this means that liquibase isn't initialized yet

    # Fetch the actual schema version from the database
    SCHEMA_VERSION=$($LIQUIBASE execute-sql --sql="SELECT VERSION_ FROM ACT_GE_SCHEMA_LOG ORDER BY ID_ DESC LIMIT 1" | sed -n '3p' | cut -d ' |' -f1)
    if [ -z "$SCHEMA_VERSION" ]; then
        echo "No schema version found. This is normal on the first startup since Camunda still needs to create the database schema."
        exit 0
    fi

    # Mark the actual schema version as the actual tag
    echo "Syncing liquibase schema version to $SCHEMA_VERSION"
    $LIQUIBASE changelog-sync-to-tag --tag="$SCHEMA_VERSION"
fi

# Run database update
$LIQUIBASE --loglevel=INFO update

Feel free to comment if you have a better solution, or if I’m missing something obvious.

[ad_sahota]

Good work. Your solution is pretty good. Couple of observations

1. I don’t think you need to download DB jars. They are shipped with liquibase.
2. cut -d ' |' throws error for me on Ubuntu. So changed a bit.
3. Added a workaround to test the DB connectivity first. liquibase community edition does not has test=connection capability
   I took inspiration from your work and here is my script. It is work in progress and will share link to github repo once I finalize this.

#!/usr/bin/env bash

export LIQUIBASE_SEARCH_PATH="/camunda/configuration/sql/liquibase/"
export LIQUIBASE_COMMAND_URL="$DB_URL"
export LIQUIBASE_COMMAND_USERNAME="$DB_USERNAME"
export LIQUIBASE_COMMAND_PASSWORD="$DB_PASSWORD"
export LIQUIBASE_COMMAND_CHANGELOG_FILE="camunda-changelog.xml"
export LIQUIBASE_SHOW_BANNER="false"

LIQUIBASE=/opt/liquibase/liquibase

# Test DB connection
if [[ $DB_DRIVER == *"postgres"* ]]; then
  $LIQUIBASE execute-sql --sql="SELECT 1" 2>&1 | grep "ERROR"
  if [ $? -eq 0 ]; then
  echo "ERROR connecting DB from liquibase.Proceeding without DB migration."
  exit 0
  fi
elif [[ $DB_DRIVER == *"OracleDriver"* ]]; then
  $LIQUIBASE execute-sql --sql="SELECT 1 FROM DUAL" 2>&1 | grep "ERROR"
  if [ $? -eq 0 ]; then
  echo "ERROR connecting DB from liquibase.Proceeding without DB migration."
  exit 0
  fi
elif [[ $DB_DRIVER == *"org.h2.Driver"* ]]; then
  $LIQUIBASE execute-sql --sql="SELECT 1" 2>&1 | grep "ERROR"
  if [ $? -eq 0 ]; then
  echo "ERROR connecting DB from liquibase.Proceeding without DB migration."
  exit 0
  fi
fi

# Base tag query
$LIQUIBASE tag-exists --tag="7.16.0" 2>&1 | grep "does NOT exist"
if [ $? -eq 0 ]; then
    # The base tag does not exist in database and DB is up.
    # Fetch the actual schema version from the database.
    echo "DB Connected, checking schema version."
    if [[ $DB_DRIVER == *"postgres"* ]]; then
        SCHEMA_VERSION=$($LIQUIBASE execute-sql --sql="SELECT VERSION_ FROM ACT_GE_SCHEMA_LOG ORDER BY ID_ DESC LIMIT 1" | sed -n '3p' | cut -d '|' -f1 | tr -d ' ')
    elif [[ $DB_DRIVER == *"OracleDriver"* ]]; then
        SCHEMA_VERSION=$($LIQUIBASE execute-sql --sql="SELECT VERSION_ FROM ACT_GE_SCHEMA_LOG ORDER BY ID_ DESC FETCH FIRST 1 ROW ONLY" | sed -n '3p' | cut -d '|' -f1 | tr -d ' ')
    elif [[ $DB_DRIVER == *"org.h2.Driver"* ]]; then
        SCHEMA_VERSION=$($LIQUIBASE execute-sql --sql="SELECT VERSION_ FROM ACT_GE_SCHEMA_LOG ORDER BY ID_ DESC LIMIT 1" | sed -n '3p' | cut -d '|' -f1 | tr -d ' ')
    fi

    echo "Current Camunda SCHEMA_VERSION is $SCHEMA_VERSION"
    if [ -z "$SCHEMA_VERSION" ]; then
        echo "No schema version found in DB.Not migrating via liquibase.Camunda will create the database schema."
        exit 0
    fi
    # Mark the actual schema version as the actual tag
    echo "Syncing liquibase schema version to $SCHEMA_VERSION"
    $LIQUIBASE changelog-sync-to-tag --tag="$SCHEMA_VERSION"

fi
# Run database update
echo "liquibase db migration started"
$LIQUIBASE --loglevel=INFO update