Version management with Flyway for OceanBase Cloud

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

Prerequisites

1. You have registered an OceanBase Cloud account and created an instance with a MySQL-compatible or Oracle-compatible tenant.

2. The Flyway server IP has been added to the OceanBase Cloud instance allowlist. For details, refer to Set allowlist groups.

3. You have obtained the OceanBase Cloud database connection string. For details, refer to:

   • Obtain connection string (MySQL compatible mode)
   • Obtain connection string (Oracle compatible mode)

Integration configuration

MySQL compatible mode

For the MySQL compatible mode in OceanBase Cloud, 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 Cloud 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 Cloud 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();
       }
   }
   

   Parameter Description:

   • host: OceanBase Cloud database connection endpoint
   • port: OceanBase Cloud database connection port
   • dbName: OceanBase Cloud database name
   • username: Database account name
   • password: Account password

4. Execute the test.

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

Oracle compatible mode

For the Oracle compatible mode, OceanBase Cloud 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();
       }
   }
   

   Parameter Description:

   • host: OceanBase Cloud database connection endpoint
   • port: OceanBase Cloud database connection port
   • username: Database account name
   • password: Account password

4. Execute the test.

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