Use Flyway to implement version management in OceanBase Database

Flyway is an open-source database version control tool that tracks the state of the database through a history table, enabling automated migrations. This topic describes how to integrate Flyway with OceanBase Database to automate schema changes and maintain a version history.

Prerequisites

You have deployed OceanBase Database and created a user tenant.

Procedure

Step 1: Obtain the database connection information

Obtain the database connection string from the deployment personnel or administrator of OceanBase Database. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

• $database_name: the name of the database to be accessed.

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database.

• $user_name: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the account password.

Step 2: Integration configuration

MySQL-compatible mode

For the MySQL-compatible mode of OceanBase Database, we recommend using flyway-core 10.0.0 or later, along with mysql-connector-java and flyway-database-oceanbase components. For earlier Flyway versions, contact OceanBase technical support.

1. Configure Maven dependencies.

   Add the following dependencies to your project's pom.xml:

   <dependencies>
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-core</artifactId>
       <version>11.3.2</version>
   </dependency>
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.47</version>
   </dependency>
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-database-oceanbase</artifactId>
       <version>10.7.2</version>
   </dependency>
   </dependencies>
   

2. Create migration scripts.

   Under the resources directory of your Maven project, create a db/migration folder and add these SQL files:

   • File 1: V1_1__create_person_table.sql

     CREATE TABLE person (
         id INT NOT NULL,
         name VARCHAR(20) NOT NULL
     );
     

   • File 2: V2_1__add_person_table.sql

     INSERT INTO person (id, name)
     VALUES (1, 'apple');
     

3. Implement Java test class.

   Create a FlywayTest Java class for database migration. Modify the connection parameters according to your OceanBase Database connection string:

   public class FlywayTest {
       public static void main(String[] args) {
           String url = "jdbc:mysql://host:port/dbName";
           String user = "username";
           String password = "password";
           Flyway flyway = Flyway.configure()
                   .dataSource(url, user, password)
                   .load();
           flyway.baseline();
           flyway.migrate();
       }
   }
   

   Parameters:

   • host: the connection address of OceanBase Database.
   • port: the connection port of OceanBase Database.
   • dbName: the database name.
   • username: the account name.
   • password: the ccount password.

4. Execute the test.

   After running FlywayTest, verify the created person table and inserted data in your OceanBase database.

Oracle-compatible mode

For the Oracle-compatible mode, OceanBase Database requires flyway-core 10.9.0 or higher, along with oceanbase-client, flyway-mysql, and flyway-database-oceanbase components.

1. Configure Maven dependencies.

   Add these dependencies to your project's pom.xml:

   <dependencies>
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-core</artifactId>
       <version>10.9.0</version>
   </dependency>
   <dependency>
       <groupId>com.oceanbase</groupId>
       <artifactId>oceanbase-client</artifactId>
       <version>2.4.11</version>
   </dependency>
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-mysql</artifactId>
       <version>10.7.0</version>
   </dependency>
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-database-oceanbase</artifactId>
       <version>10.16.1</version>
       <scope>system</scope>
       <systemPath>/path/to/your/flyway-database-oceanbase-10.16.1.jar</systemPath>
   </dependency>
   </dependencies>
   

2. Create migration scripts.

   Under the resources directory, create a db/migration folder containing:

   • File 1: V1_1__create_person_table.sql

     CREATE TABLE person (
         id INT NOT NULL,
         name VARCHAR(20) NOT NULL
     );
     

   • File 2: V2_1__add_person_table.sql

     INSERT INTO person (id, name)
     VALUES (1, 'apple');
     

3. Implement Java test class.

   Create a FlywayTest class for database migration. Update the connection parameters as needed:

   public class FlywayTest {
       public static void main(String[] args) {
           String url = "jdbc:oceanbase://host:port/";
           String user = "username";
           String password = "password";
           Flyway flyway = Flyway.configure()
           .dataSource(url, user, password)
           .load();
           flyway.baseline();
           flyway.migrate();
       }
   }
   

   Parameters:

   • host: the connection address of OceanBase Database.
   • port: the connection port of OceanBase Database.
   • username: the account name.
   • password: the account password.

4. Execute the test.

   After running FlywayTest, verify the created person table and inserted data in your OceanBase database.