Use Flyway to integrate with OceanBase Database for version management

Flyway is an open-source database version control tool that tracks database state by creating a history table, enabling automated migrations. This topic explains how to integrate Flyway with OceanBase Database to automate schema changes and enable version tracking.

Prerequisites

You have deployed OceanBase Database and created a user tenant. For more information about how to create a user tenant, see Create a tenant.

Procedure

Step 1: Obtain the database connection information

Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection string. 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. If you connect to OceanBase Database through OceanBase Database Proxy (ODP), the IP address of the ODP is used. If you connect to OceanBase Database directly, the IP address of the OBServer node is used.

• $port: the port for connecting to OceanBase Database. If you connect to OceanBase Database through ODP, the default port is 2883, which can be customized during ODP deployment. If you connect to OceanBase Database directly, the default port is 2881, which can be customized during OceanBase Database deployment.

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

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the account for connecting to the tenant. The common format for ODP connection is username@tenant name#cluster name or cluster name:tenant name:username. The format for direct connection is username@tenant name.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Step 2: Integration configuration

MySQL-compatible tenant

We recommend that you use flyway-core 10.0.0 or later for a MySQL compatible tenant of OceanBase Database. In addition, you need to install the mysql-connector-java and flyway-database-oceanbase components.

For earlier versions of Flyway, refer to the following table:

1. Configure Maven dependencies.

   Add the following dependencies to the pom.xml file of your project:

   <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.

   In the resources directory of the Maven project, create a db/migration folder and add the following two SQL files to the folder:

   • 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. Write a Java test class.

   Create a Java class named FlywayTest to use Flyway for database migration. Modify the database connection information in the code based on the connection string obtained from the prerequisites.

   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 name of the database to be accessed.
   • username: the name of the database account.
   • password: the password of the account.

4. Run the test.

   After you run FlywayTest, you can see the person table and the inserted data in OceanBase Database.

Oracle-compatible tenant

For an Oracle compatible tenant of OceanBase Database, flyway-core 10.9.0 or later is required. In addition, you need to install the oceanbase-client, flyway-mysql, and flyway-database-oceanbase components.

1. Configure Maven dependencies.

   Add the following dependencies to the pom.xml file of your project:

   <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.

   In the resources directory of the Maven project, create a db/migration folder and add the following two SQL files to the folder:

   • 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. Write a Java test class.

   Create a Java class named FlywayTest to use Flyway for database migration. Modify the database connection information in the code based on the connection string obtained from the prerequisites.

   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 name of the database account.
   • password: the password of the account.

4. Run the test.

   After you run FlywayTest, you can see the person table and the inserted data in OceanBase Database.