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
An active SingleStoreDB cluster.
Install the Teams Edition of Flyway command-line.
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.
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 );
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.
Create another
.sql
file, say V2_Add_column.sql, and add the following SQL statement:ALTER TABLE example ADD COLUMN Trn DATE;
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.