Database change management

Changes to both the database schema and data are managed via Liquibase. Please see the Liquibase documentation for a better understanding of the main concepts.

The primary changelog is located at database/changelog/changelog-root.yaml and includes the paths and files of other changelogs. The following table provides a mapping of schema objects to path.

Description	Changelog path	Object source path
Table	database/changelog/changelog-tables	Not applicable. Tables are defined in changelog files.
Function	database/changelog/changelog-functions	database/changelog/src/functions
Procedure	database/changelog/changelog-procedures	database/changelog/src/procedures
Trigger	database/changelog/changelog-triggers	database/changelog/src/triggers
Anonymous SQL	database/changelog/changelog-anonymous	Not applicable. The anonymous SQL or PLPGSQL are defined in changelog files.

Updates to datasets are also handled by changelog config files. These files are located in database/changelog/changelog-data.

How database changes are applied

The CircleCI build pipeline includes a job titled apply-database-changes. The steps of this job are as follows:

• Install dependencies (Cloud Foundry CLI, CF connect-to-service plugin, Java, Liquibase, and Postgresql clients)
• Login to Cloud Foundry using context variables stored in the environment context that is indicated in the workflow section of config.yml.
• Tunnel to the database via the connect-to-service plugin. This step specifies the app and service stored in the target environment context variables. This step uses a non-deterministic approach to determine completion. The tunnel is considered open when the output of the connect-to-service command is at least 12 lines. If the tunnel is not opened within approximately 60 seconds, the apply-database-changes job is halted.
• Set environment variables specific to Liquibase from the output of the connect-to-service command after the database tunnel is successfully opened. These environment variables are LIQUIBASE_COMMAND_USERNAME, LIQUIBASE_COMMAND_PASSWORD, and LIQUIBASE_COMMAND_URL.
• Backup the current state of the database to be uploaded to an S3 bucket just prior to job completion.
• Run the liquibase update command to compare the changesets in the databasechangelog table to what is in the current branch and apply any outstanding database schema or data updates. This step is contained in the .circleci/scripts/liquibase.sh shell script. The script monitors the output of the liquibase update command to determine if the command errored or resulted in 0 changesets applied. In the event that an error occurs or no database updates are applied, the apply-database-changes job halts.
• If the liquibase update command succeeds with applying one or more updates, all static data downloads are regenerated and transferred to an S3 bucket along with the database backup taken previously.

Guidance

Please consider the following suggestions when applying schema and updates to the database.

• Use Liquibase for all database changes, no matter how trivial.
• When possible, use a change type provided by Liquibase rather than writing raw SQL. For example, use the createTable change type when adding a new table.
• Create a new changeset for each schema change. (Data changes use the same changelog files but with a new id since these don't otherwise change).
• Use an anonymous changelog for data fixes.
• Scripts for stored procedures, functions, and triggers should attempt to drop the object before creation.