Skip to main content

SingleStoreDB v7.8

Migrate Schema with Flyway

You can integrate Flyway into your SingleStoreDB cluster to manage schema changes in your database using the natively integrated SingleStore driver. You can also use Flyway to migrate schemas. Native SingleStore support is only available in the Teams Edition of Flyway.

Integration with SingleStoreDB is supported in Flyway version 9.8.2+.

Prerequisites

Connect Flyway to SingleStoreDB

To integrate Flyway into your SingleStoreDB 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:

    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:

    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:

    ALTER TABLE example ADD COLUMN Trn DATE;

  4. Run the migrate command:

    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

In this section: