Liquibase with MySQL database

MySQL is the world's one of the most popular open-source databases that support most of the application development requirements. MySQL ranks as the second-most-popular database, after Oracle Database.

MySQL database supports stored procedures, triggers, functions, views, cursors, ANSI-standard SQL, and more. Some of the largest and fastest-growing organizations such as Facebook, Twitter, Booking.com, and Verizon run high-volume Web sites, business-critical systems, and packaged software on MySQL.

Liquibase provides great support for MySQL Server, AWS Aurora – MySQL, AWS RDS – MySQL, Azure Database for MySQL version 8 and 5.7, and Google Cloud SQL – MySQL version 8.0.

For easy understanding, all examples are with a local installation of MySQL 8 with the below properties

url:jdbc:mysql://localhost:3306/AppleDb
username: root
password: rootroot

The latest MySQL 8 drivers jars can be downloaded from the JDBC driver JAR file or Maven download and placed in liquibase/internal/lib or liquibase/lib.

By default, Liquibase doesn't create a database schema, so either create schema before or createDatabaseIfNotExist=true attribute of MySQL

liquibase.properties

changeLogFile=changelog.mysql.sql
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot

ChangeLog files should have the name as the changelog.databaseType.sql, for example, changelog.h2.sql or  changelog.mysql.sql

changelog.mysql.sql

-- liquibase formatted sql

-- changeset wesome:1692352309572-1
CREATE TABLE apple (apple_id BIGINT NOT NULL, apple_name VARCHAR(255) NULL, CONSTRAINT PK_APPLE PRIMARY KEY (apple_id));

run command

liquibase status

All the required attributes are mentioned in liquibase.properties file, hence is no need to pass in CLI, the preferred way is to keep all required attributes in liquibase.properties only. or else the same command can also be run

liquibase status --username=root --password=rootroot --changelog-file=changelog.mysql.sql

Liquibase will check the connection with the MySQL database and if successful, the prompt will show a message like

changesets have not been applied to <your_jdbc_url> Liquibase command 'status' was executed successfully.

Always inspect and verify the SQL command before actually running it with update-sql command.

liquibase update-sql

The update-sql command will verify the SQL against the corresponding database.

Now update the database with the update command

liquibase update

Liquibase will show a conformation message

Liquibase: Update has been successful.
Liquibase command 'update' was executed successfully.

Liquibase will create a schema appleDB and 3 tables in it, apple table from apple-changelog.sql and DATABASECHANGELOG and DATABASECHANGELOGLOCK

Liquibase supports SQL, JSON, YAML, and XML as well, the same changelog file can be created in other formats as well.

Liquibase ChangeLog FIie in XML format

XML stands for Extensible Markup Language, it stores data in human-readable plain text format, the information can be represented as a tree structure, and most computers can process XML easily. It is based on W3C standards and is actually endorsed by software industry market leaders.

changelog.mysql.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
    <changeSet author="wesome (generated)" id="1692352246983-1">
        <createTable tableName="apple">
            <column name="apple_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="apple_name" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

liquibase.properties

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot

Liquibase ChangeLog FIie in JSON format

JSON stands for JavaScript Object Notation, it is easier to read and write than XML, which enhances readability. It also supports native data types such as numbers, booleans, and nulls, simplifying data representation and validation. JSON is quite fast as it consumes very less memory space. One drawback of JSON is that it doesn't supports comments

changelog.mysql.json

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "1692352264594-1",
        "author": "wesome (generated)",
        "changes": [
          {
            "createTable": {
              "columns": [
                {
                  "column": {
                    "constraints": {
                      "nullable": false,
                      "primaryKey": true
                    },
                    "name": "apple_id",
                    "type": "BIGINT"
                  }
                },
                {
                  "column": {
                    "name": "apple_name",
                    "type": "VARCHAR(255)"
                  }
                }
              ],
              "tableName": "apple"
            }
          }
        ]
      }
    }
  ]
}

liquibase.properties

changeLogFile=changelog.mysql.json
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot

Liquibase ChangeLog FIie in YAML format

YAML stands for yet another markup language, it is the most human-readable data serialization format. it's easier to learn than JSON because it’s written using natural language. YAML is a superset of JSON. It handles more types of data and has complex but still readable syntax.YAML supports comments, which are not supported by JSON.

changelog.mysql.yaml

databaseChangeLog:
  - changeSet:
      id: 1692352255676-1
      author: wesome (generated)
      changes:
        - createTable:
            columns:
              - column:
                  constraints:
                    nullable: false
                    primaryKey: true
                  name: apple_id
                  type: BIGINT
              - column:
                  name: apple_name
                  type: VARCHAR(255)
            tableName: apple

liquibase.properties

changeLogFile=changelog.mysql.yaml
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot