Database Migrations

Table of Contents

Snippets #

Liquibase generation and first changeset #

Autogenerate changeset for existing db:

liquibase --driver=org.postgresql.Driver --changeLogFile=CS_0000-autogen.xml --url="jdbc:postgresql://192.168.1.100/DB_NAME"  --username=USERNAME --password=PASSWORD --defaultSchemaName=SCHEMA_NAME --includeSchema=true --schemas=SCHEMA_NAME generate-changelog

Needed to add this to the top of the auto-generated changeset:

    <changeSet author="Rishi Maharaj" id="76359fe6-9193-4d8c-a467-48ea88b538c7">
        <sql>
            CREATE SCHEMA IF NOT EXISTS SCHEMA_NAME;
            CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
            CREATE EXTENSION IF NOT EXISTS hstore;
        </sql>
    </changeSet>

Go To References #

Liquibase CLI commands - Reference for CLI commands which can be used to autogenerate a changelog for an existing database
Liquibase XML overview - Reference to better understand how to structure and use the XML format, which I prefer for SQL DBs
Liquibase JSON overview - Reference to better understand how to structure and use the JSON format which works well to create and define Mongodb schemas with JSON validation
Liquibase best practices - Nice info on file structure, environment management, planning for rolling forward and rolling back changes, and determining reference data (e.g. for a QA environment)

Videos #

It’s Time to Automate Your Database Schema Changes - Robert Reeves, CTO, Liquibase - Nice video explaining why database migration tools are necessary, with a live demo of Liquibase
Spring Boot Database Migrations with Flyway -Example | Java Techie - While researching db migration options, looked into Flyway (as it was brought up in a great explanation of Integration testing with Spring Boot @DataJpaTest)
Spring Data Mongodb Migrations using Mongock - Looked into Mongock for a Mongodb migration tool but decided against it to standardize with Liquibase
Liquibase with SpringBoot | Step by step tutorial for Beginners - Full course - Nice walkthrough for how to set up Liquibase from scratch