Migrate Schema with Flyway

Prerequisites

• An active SingleStore cluster.

• Install the Teams Edition of Flyway command-line.

Connect Flyway to SingleStore

To integrate Flyway into your SingleStore cluster, you need to update the connection parameters for your cluster and the Flyway license key in the Flyway configuration file (flyway.conf). This file is located in the /<flyway-installation>/conf/ directory.

Here's a sample configuration:

flyway.url=jdbc:singlestore://svchost:3306/dbTest
flyway.user=root
flyway.password=pa55w0rd!
flyway.schemas=dbTest
flyway.licenseKey=<flywaylicenseKey>

Refer to connection string parameters for more information on the supported parameters.

Example

Flyway migrations are defined in .sql files located in the /<flyway-installation>/sql/ directory. Flyway runs the SQL statements defined in these .sql files using the migrate command. The filename must comply with the naming conventions.

The following example shows how to perform a SQL-based migration using Flyway.

1. Create a .sql file in the /<flyway-installation>/sql/ directory, say V1_Create_table_example.sql, and add the following SQL statement:

   SQL

   Copy

   CREATE TABLE example (
       ID INT NOT NULL,
       Name VARCHAR(100) NOT NULL
   );

2. Run the migrate command in the /<flyway-installation> directory to apply the changes:

   Shell

   Copy

   flyway migrate

   Successfully validated 1 migration (execution time 00:00.020s) 
   Creating Schema History table `dbTest`.`flyway_schema_history` ...
   Current version of schema `dbTest`: << Empty Schema >> 
   Migrating schema `dbTest` to version "1 - Create table example" 

   The database is now migrated to version 1.

3. Create another .sql file, say V2_Add_column.sql, and add the following SQL statement:

   SQL

   Copy

   ALTER TABLE example ADD COLUMN Trn DATE;

4. Run the migrate command:

   Shell

   Copy

   flyway migrate

   Successfully validated 2 migrations (execution time 00:00.023s) 
   Current version of schema `dbTest`: 1 
   Migrating schema `dbTest` to version "2 - Add column" 
   Successfully applied 1 migration to schema `dbTest`, now at version v2 

   This command validates and runs both the migrations, and the database is now migrated to version 2.

References

• Flyway Documentation

• Flyway Migrations