# Flyway

This page walks you through a series of simple database schema changes using Flyway, an open-source schema migration tool. For detailed information about using Flyway, see the Flyway documentation site.

## Watch the demo

## Before You Begin

Before you begin, do the following:

1. Install CockroachDB and start a secure cluster.
2. Download the latest version of the Flyway command-line tool. CockroachDB v20.1 is fully compatible with Flyway versions 6.4.2 and greater.

## Step 1. Configure Flyway connect to CockroachDB

1. Extract the Flyway TAR file that you downloaded, and change directories to the extracted `flyway-x.x.x` folder. For example:

       $ tar -xvf flyway-commandline-6.4.2-macosx-x64.tar.gz

       $ cd flyway-6.4.2

2. Edit the `flyway-x.x.x/conf/flyway.conf` configuration file to specify the correct connection parameters for your running, secure cluster. For example:

       ...
       flyway.url=jdbc:postgresql://localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.max.key&sslcert=certs/client.max.crt
       flyway.user=max
       flyway.password=roach
       ...

   Note:

   The SSL connection parameters in the connection URL must specify the full path to the certificates that you generated when you started the secure cluster. Also, the user that you specify (e.g., `max`) must also have admin privileges on the database whose schema you want to change (e.g., `bank`).

## Step 2. Create a schema migration

Flyway executes SQL statements defined in `.sql` files located in the `flyway-x.x.x/sql` subdirectory. The schema changes defined in these `.sql` files are known as migrations.

1. Create a `.sql` file with a name that follows the Flyway naming conventions. For example:

       $ touch sql/V1__Add_accounts_table.sql

2. Edit the `.sql` file, adding a `CREATE TABLE IF NOT EXISTS` statement for the table that you want to create, and a simple `INSERT` statement to initialize the table with some data. For example:

       /* Create accounts table */
       CREATE TABLE IF NOT EXISTS accounts (
         id INT PRIMARY KEY,
         balance INT
       );

       /* Add initial data to accounts table */
       INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250);

## Step 3. Execute a schema migration

To execute the migration, run the following command from the top of the `flyway-x.x.x` directory:

    $ ./flyway migrate

You should see output similar to the following:

    Database: jdbc:postgresql://localhost:26257/bank (PostgreSQL 9.5)
    Successfully validated 1 migration (execution time 00:00.011s)
    Creating Schema History table "bank"."flyway_schema_history" ...
    Current version of schema "bank": << Empty Schema >>
    Migrating schema "bank" to version 1 - Add accounts table [non-transactional]
    Successfully applied 1 migration to schema "bank" (execution time 00:00.081s)

The schema `"bank"` is now on version 1.

## Step 4. Add additional migrations

Suppose that you want to change the primary key of the `accounts` table from a simple, incrementing integer (in this case, `id`) to an auto-generated UUID, to follow some CockroachDB best practices. You can make these changes to the schema by creating and executing an additional migration:

1. Create a second `.sql` schema migration file, and name the file following the Flyway naming conventions, to specify a new migration version. For example:

       $ touch sql/V2__Alter_accounts_pk.sql

   This file will create a version 2 of the `"bank"` schema.

2. Edit the `V2__Alter_accounts_pk.sql` migration file, adding some SQL statements that will add a new column to the `accounts` table, and alter the table's primary key. For example:

       /* Add new UUID-typed column */
       ALTER TABLE accounts ADD COLUMN unique_id UUID NOT NULL DEFAULT gen_random_uuid();

       /* Change primary key */
       ALTER TABLE accounts ALTER PRIMARY KEY USING COLUMNS (unique_id);

3. Execute the migration by running the `flyway migrate` command from the top of the `flyway-x.x.x` directory:

       $ ./flyway migrate

   You should see output similar to the following:

       Flyway Community Edition 6.4.2 by Redgate
       Database: jdbc:postgresql://localhost:26257/bank (PostgreSQL 9.5)
       Successfully validated 2 migrations (execution time 00:00.016s)
       Current version of schema "bank": 1
       Migrating schema "bank" to version 2 - Alter accounts pk [non-transactional]
       DB: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
       Successfully applied 1 migration to schema "bank" (execution time 00:00.508s)

   The schema `"bank"` is now on version 2.

4. Check the complete and pending Flyway migrations with the `flyway info` command:

       $ ./flyway info

       Flyway Community Edition 6.4.2 by Redgate
       Database: jdbc:postgresql://localhost:26257/bank (PostgreSQL 9.5)
       Schema version: 2

       +-----------+---------+--------------------+------+---------------------+---------+
       | Category  | Version | Description        | Type | Installed On        | State   |
       +-----------+---------+--------------------+------+---------------------+---------+
       | Versioned | 1       | Add accounts table | SQL  | 2020-05-13 17:16:54 | Success |
       | Versioned | 2       | Alter accounts pk  | SQL  | 2020-05-14 13:27:27 | Success |
       +-----------+---------+--------------------+------+---------------------+---------+

## Flyway and Transactions

When used with most databases, Flyway wraps the statements in a migration within a single transaction. When used with CockroachDB, Flyway does not wrap schema migrations in transactions. Transaction boundaries are instead handled by CockroachDB.

### Transaction retries

When multiple, concurrent transactions or statements are issued to a single CockroachDB cluster, transaction contention can cause schema migrations to fail. In the event of transaction contention, CockroachDB returns a `40001 SQLSTATE` (i.e., a serialization failure), and Flyway automatically retries the migration. For more information about client-side transaction retries in CockroachDB, see Transaction Retries.

### Transactional schema changes

Support for transactional schema changes is limited in CockroachDB. As a result, if a migration with multiple schema changes fails at any point, the partial migration could leave the database schema in an incomplete state. If this happens, manual intervention will be required to determine the state of the schema, in addition to any possible fixes.

Note that this limitation also applies to single `ALTER TABLE` statements that include multiple schema changes (e.g., `ALTER TABLE ... ALTER COLUMN ... RENAME ..., ADD COLUMN ...`).

## Report Issues with Flyway and CockroachDB

If you run into problems, please file an issue on the Flyway issue tracker, including the following details about the environment where you encountered the issue:

- CockroachDB version (`cockroach version`)
- Flyway version
- Operating system
- Steps to reproduce the behavior

## See Also

- Flyway documentation
- Flyway issue tracker
- Client connection parameters
- Third-Party Database Tools
- Learn CockroachDB SQL