Skip to content

Latest commit

 

History

History
115 lines (79 loc) · 4.09 KB

ManageDBMigrations.md

File metadata and controls

115 lines (79 loc) · 4.09 KB
Raw

How to manage database migrations

We use Liquibase to manage the Database Schema over time. The integration is done with Maven Liquibase Plugin and you can issue commands with the mvn liquibase:<command> in the db-migrations directory.

Following the best practices document we have main changelog file at db-migrations/src/main/resources/db/changelog.yaml. This file has include statement(s) in the correct order to the migrations directory where are the changelog files by major release. The master file at src/main/resources/db/migrations/changelog-master.yaml has all the change sets for the initial schema creation.

Standard migratiom

  1. Setting up the connection. The Liquibase plug-in settings are expected to be stored in db-migrations/src/main/resources/liquibase.properties. If the file does not exists copy it from the template provided at db-migrations/src/main/resources/liquibase.properties.template and update the following properties.

    verbose = true
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/comms_router_core
username = {USERNAME}
password = {PASSWORD}

  2. Update the database to the latest migration.

  3. Check and update the changeLogFile property in db-migrations/src/main/resources/liquibase.properties. Required by liquibase:update.

    changeLogFile = src/main/resources/db/changelog.yaml

  4. Update liquidbase by running the following command in the db-migrations directory.

    mvn liquibase:update

Create diff with changes

Generate changeSet(s) with the difference between two given databases. In this example we will create an additional database names comms_router_dev.

Note: Prerequisite for this task is to have an identical database with the new changes.

  1. Create new database.

    CREATE DATABASE `comms_router_dev` CHARACTER SET `utf8` COLLATE `utf8_general_ci`;

  2. Create new Super Admin.

    CREATE USER IF NOT EXISTS 'comms_router'@'localhost' IDENTIFIED BY 'comms_router_password';
GRANT ALL ON `comms_router_dev`.* TO 'comms_router'@'localhost';

  3. Check and update reference arguments in db-migrations/src/main/resources/liquibase.properties. Required by liquibase:diff.

    referenceDriver=com.mysql.jdbc.Driver
referenceUrl=jdbc:mysql://localhost:3306/comms_router_core
referenceUsername=comms_router
referencePassword=comms_password

  4. Add the diffChangeLogFile property in db-migrations/src/main/resources/liquibase.properties where the changeSet(s) will be saved.

    diffChangeLogFile=src/main/resources/db/migrations/changelog-next.yaml

  5. Edit the url property in db-migrations/src/main/resources/liquibase.properties by updating the standard connection settings to point to the dev database instance.

    url=jdbc:mysql://localhost:3306/comms_router_dev

  6. Update liquidbase by running the following command.

    mvn liquibase:update

Generate ChangeLog

  1. Add the outputChangeLogFile property in db-migrations/src/main/resources/liquibase.properties to where the schema from the current database will be imported from. Required by liquibase:generateChangeLog.

    outputChangeLogFile	 = src/main/resources/db/changelog.yaml

  2. Update liquidbase by running the following command.

    mvn liquibase:generateChangeLog