Manage schema versions of OceanBase Cloud databases with Liquibase

Liquibase is an open-source version control tool that supports version tracking, updates, and rollbacks of database schemas.

Prerequisites

• Download and install Liquibase.
• Download the MySQL JDBC driver from the official website and obtain the mysql-connector-j-x.x.x.jar file.

Procedure

1. Place the downloaded mysql-connector-j-x.x.x.jar into the lib directory of Liquibase.

2. Create a liquibase.properties configuration file and write the following content:

   url:  jdbc:mysql://x.x.x.x:3306/test
   username:  username
   password:  *****
   classpath: lib/mysql-connector-j-8.3.0.jar
   

3. Execute the following Liquibase command to generate a snapshot of the current database schema. Note that the actual parameters may differ depending on the version, please refer to the official documentation.

   ./liquibase --changeLogFile=mydatabase_changelog.xml generateChangeLog
   

4. Create a new table by creating a dbchangelog.xml file and writing the following content:

   <?xml version="1.0" encoding="UTF-8"?>
   <databaseChangeLog
       xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:pro="http://www.liquibase.org/xml/ns/pro"
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd
           http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.5.xsd">
   
       <changeSet author="BobR" id="myIDNumber123">
           <createTable tableName="actor">
               <column autoIncrement="true" name="id" type="INTEGER">
                   <constraints nullable="false" primaryKey="true" primaryKeyName="actor_pkey"/>
               </column>
               <column name="firstname" type="VARCHAR(255)"/>
               <column name="lastname" type="VARCHAR(255)"/>
               <column name="twitter" type="VARCHAR(15)"/>
           </createTable>
       </changeSet>
   
   </databaseChangeLog>
   

5. The above configuration describes a change to create the actor table. Now, execute the change.

   ./liquibase update --changeLogFile=dbchangelog.xml
   

6. After successful changes, you can also attempt to rollback to the previous version.

   ./liquibase rollbackCount 1 --changelog-file=dbchangelog.xml