Liquibase Demostration with Spring Boot Application

The following demonstration is a Spring Boot application which only exposes a GET endpoint /users/:id. To integrate Liquibase, add a dependency for it in pom.xml. ``` <dependency> <groupId>org.liquibase</groupId> <artifactId>liquibase-core</artifactId> </dependency> ``` The changelog files are located in resources/db/changelog/.  Liquibase supports SQL, XML, JSON and YAML format for storing changelog files. The 00-db.changelog-master.yaml includes the changelog files for the releases in the correct order. 00-db.changelog-master.yaml ```yml databaseChangeLog: - include: file: 01-db.changelog-yaml-example.yaml relativeToChangelogFile: true - include: file: 02-db.changelog-sql-example.sql relativeToChangelogFile: true - include: file: 03-db.changelog-json-example.json relativeToChangelogFile: true - include: file: 04-db.changelog-xml-example.xml relativeToChangelogFile: true ``` 01-db.changelog-yaml-example.yaml Here's an example of changelog in YAML format. It creates a table called user_details with four fields – id, username, first_name, last_name. A ChangeSet is identified by id and author. For best practice, keep only one change per ChangeSet. Pre-conditions can also be added here. ```yml databaseChangeLog: - changeSet: id: create-table-user author: liquibase-demo-service preConditions: - onFail: MARK_RAN not: tableExists: tableName: user_details changes: - createTable: columns: - column: autoIncrement: true constraints: nullable: false primaryKey: true primaryKeyName: user_pkey name: id type: BIGINT - column: constraints: nullable: false name: username type: TEXT - column: constraints: nullable: false name: first_name type: TEXT - column: name: last_name type: TEXT tableName: user_details ``` 02-db.changelog-sql-example.sql Here's an example of changelog in SQL format. It must start with the line –liquibase formatted sql to provide liquibase with metadata, followed by arbitrary SQL statements with a comment of the form : ``--changeset author:id attribute1:value1 attribute2:value2 [...]``. ```sql --liquibase formatted sql --changeset liquibase-demo-service:add-user-name-constraint ALTER TABLE user_details ADD CONSTRAINT user_details_username_key UNIQUE (username); ``` 03-db.changelog-json-example.json Here's an example of changelog in JSON format. ```json { "databaseChangeLog": [ { "changeSet": { "id": "add-last-name-constraint", "author": "liquibase-demo-service", "changes": [ { "addNotNullConstraint": { "columnName": "last_name", "constraintName": "user_last_name_key", "tableName": "user_details" } }] } } ] } ``` 04-db.changelog-xml-example.xml Here's an example of changelog in XML format. It utilizes loadUpdateData to loads and update from a CSV file into an existing table. ``` <?xml version="1.1" encoding="UTF-8" standalone="no"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd"> <changeSet author="liquibase-docs" id="loadUpdateData-example" context="!prod"> <loadUpdateData encoding="UTF-8" relativeToChangelogFile="true" file="../data/users.csv" onlyUpdate="false" primaryKey="id" quotchar="'" separator="," tableName="user_details"> </loadUpdateData> </changeSet> </databaseChangeLog> ``` By default, Liquibase is enabled. Those changelog files will be executed at Spring Boot startup. To disable it, add the following property in src\main\resources\application.yaml. ``` spring: liquibase: enabled: false ``` To deploy any changes in the changelog files that have not been deployed to database yet, run the update goal. ``` ./mvnw org.liquibase:liquibase-maven-plugin:update \ -Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \ -Dliquibase.changeLogFile=src/main/resources/db/changelog/00-db.changelog-master.yaml \ -Dliquibase.username=demouser \ -Dliquibase.password=demopassword ```  After running update goal, four tables have been created.  databasechangelog tracks which changeset have been run.  databasechangeloglock ensures there is only one Liquibase is running at one time as multiple instances being executed against the same database concurrently will lead to conflicts. The table user_details is defined in 01-db.changelog-yaml-example.yaml, with constraints defined in 02-db.changelog-sql-example.sql and 03-db.changelog-json-example.json. The data is loaded in 04-db.changelog-xml-example.xml.  user_details_id_seq is a sequence table for user_details.  To verify the result, start the Sprint Boot Application.  Get user with id 100000000  Tag can be used to mark the current database state for rollback in the future.  To create a tag, run the following goal ``` ./mvnw org.liquibase:liquibase-maven-plugin:tag \ -Dliquibase.tag=version1 \ -Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \ -Dliquibase.username=demouser \ -Dliquibase.password=demopassword ```  A tag version has been added.  There are three rollback modes – rollbackByTag, rollbackToDate and rollbackCount. · RollbackByTag reverts all changes made to the database after the specific tag. · RollbackToDate reverts all changes made to the database from the current date to the date and time you specify. · RollbackCount reverts the changes sequentially starting with the most recent changes. There are another modes such as rollbackOneChangeSet and rollbackOneUpdate but it requires Liquibase Pro. Take the first one as an example, supposing there is another changelog – adding middle_name in user_details. 05-add-middle-name.json Here we add the rollback statement to this changeset. ``` { "databaseChangeLog": [ { "changeSet": { "id": "add-middle-name", "author": "liquibase-demo-service", "changes":[{ "addColumn":{ "catalogName":"cat", "columns":[{ "column":{ "name":"middle_name", "type":"TEXT" } } ], "schemaName":"public", "tableName":"user_details" } } ], "rollback": [ { "sql": { "sql": "ALTER TABLE USER_DETAILS DROP COLUMN middle_name;" } } ] } } ]} ``` Deploy this changelog ``` ./mvnw org.liquibase:liquibase-maven-plugin:update \ -Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \ -Dliquibase.changeLogFile=src/main/resources/db/changelog/05-add-middle-name.json \ -Dliquibase.username=demouser \ -Dliquibase.password=demopassword ``` The column "middle_name" has been added.  The latest changelog is after the tag version1.  If we need to revert the changes after it, update 00-db.changelog-master.yaml to include the latest changelog. ``` - include: file: 05-add-middle-name.json relativeToChangelogFile: true ``` then run the following goal. ``` ./mvnw org.liquibase:liquibase-maven-plugin:tag \ -Dliquibase.tag=version1 \ -Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \ -Dliquibase.username=demouser \ -Dliquibase.password=demopassword ```  The record for 05-add-middle-name.json has been dropped.  The column middle_name has been dropped.  Similarly, we can use RollbackToDate or RollbackCount to revert the changes. RollbackToDate liquibase:rollback -Dliquibase.rollbackCount=1 RollbackCount liquibase:rollback "-Dliquibase.rollbackDate=Feb 03, 2021" To conclude, we can define changelogs in 4 possible formats and run the Maven Goals to deploy them to database in the pipeline. We can also create a tag to mark the current state and rollback if necessary.