Database Migration

We use Liquibase as a database migration tool to manage and version database schema changes. It allows us to create, modify, and track schema updates in a structured and consistent manner. By using Liquibase, we ensure seamless schema evolution across environments, maintain compatibility with the application, and enable collaborative development with clear version control for database changes.

The Liquibase migration scripts are organized under the tools/liquibase module within the tenant folder. All schema changes are stored in the changelog folder, while predefined data is maintained separately in the data folder.

To accelerate the development process, sample data for teams, workflows, and users is preloaded into the database, eliminating the need for developers to create it manually. This is achieved using two Liquibase contexts, dev and test, which insert the necessary data based on the active Spring profiles, dev or test, respectively.

Explanation of Liquibase Changesets for Different Phases

The following Liquibase changesets demonstrate how database updates are applied based on the active Spring profile (dev, test, or prod). If a changeset does not specify a context, it is applied in all phases by default.

Changeset for Development and Test Phases

This changeset is applied exclusively during the dev or test profiles, typically used in the development phase to preload sample data for testing and development purposes:

<changeSet author="flowinquiry"
		id="000:05-insert-default-user-authority-data" context="dev,test">
    <loadData
			file="config/liquibase/tenant/data/fw_user_authority_dev.csv"
			separator=";" tableName="fw_user_authority"
			usePreparedStatements="true">
			<column name="user_id" type="numeric" />
    </loadData>
    <loadData
			file="config/liquibase/tenant/data/fw_organization_dev.csv"
			tableName="fw_organization" usePreparedStatements="true"
			separator=";">
			<column name="id" type="NUMERIC" />
			<column name="name" type="STRING" />
			<column name="logo_url" type="STRING" />
			<column name="slogan" type="STRING" />
			<column name="description" type="STRING" />
		</loadData>
		<sql>SELECT setval('fw_organization_id_seq', (SELECT MAX(id) FROM
			fw_organization));</sql>
</changeSet>

Changeset for Production Phase

This changeset is applied exclusively during the prod profile, typically used in the production phase to load production-specific data:

<changeSet author="flowinquiry"
		id="000:05-insert-default-user-authority-data-prod" context="prod">
    <loadData
			file="config/liquibase/tenant/data/fw_user_authority_prod.csv"
			separator=";" tableName="fw_user_authority"
			usePreparedStatements="true">
			<column name="user_id" type="numeric" />
    </loadData>
</changeSet>

Changesets Without Context

If a changeset does not include a context attribute, it will be applied in all environments (development, test, and production) by default. This ensures that general database schema updates or universally required data are consistently applied regardless of the profile.