> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrate CockroachDB Schemas with Flyway

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page guides 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](https://flywaydb.org/documentation/).

## Watch the demo

## Before You Begin

Before you begin, do the following:

1. <InternalLink path="install-cockroachdb">Install CockroachDB</InternalLink> and <InternalLink path="secure-a-cluster">start a secure cluster</InternalLink>.
2. Download the latest version of the [Flyway command-line tool](https://flywaydb.org/documentation/commandline/#download-and-installation). CockroachDB v21.1 and later are fully compatible with Flyway versions 7.1.0 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:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ tar -xvf flyway-commandline-6.4.2-macosx-x64.tar.gz
   ```

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ cd flyway-6.4.2
   ```

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

   ```conf theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ...
   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 <InternalLink path="secure-a-cluster">started the secure cluster</InternalLink>. Also, the user that you specify (e.g., `max`) must also have <InternalLink path="grant">admin privileges</InternalLink> on the database whose schema you want to change (e.g., `bank`).
</Note>

## 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](https://flywaydb.org/documentation/migrations#naming). For example:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ touch sql/V1__Add_accounts_table.sql
   ```

2. Edit the `.sql` file, adding a <InternalLink path="create-table">`CREATE TABLE IF NOT EXISTS`</InternalLink> statement for the table that you want to create, and a simple <InternalLink path="insert">`INSERT`</InternalLink> statement to initialize the table with some data. For example:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   /* 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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ ./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 <InternalLink path="int">integer</InternalLink> (in this case, `id`) to an auto-generated <InternalLink path="uuid">UUID</InternalLink>, to follow some <InternalLink path="performance-best-practices-overview#unique-id-best-practices">CockroachDB best practices</InternalLink>. 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](https://flywaydb.org/documentation/migrations#naming), to specify a new migration version. For example:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ 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:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   /* 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:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ ./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:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ ./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](https://flywaydb.org/documentation/migrations#transactions). When used with CockroachDB, Flyway does *not* wrap schema migrations in transactions. <InternalLink path="transactions">Transaction boundaries</InternalLink> are instead handled by CockroachDB.

### Transaction retries

When multiple, concurrent transactions or statements are issued to a single CockroachDB cluster, <InternalLink path="performance-best-practices-overview#transaction-contention">transaction contention</InternalLink> can cause schema migrations to fail. In the event of transaction contention, CockroachDB returns a <InternalLink path="common-errors#restart-transaction">`40001 SQLSTATE` (i.e., a serialization failure)</InternalLink>, and Flyway automatically retries the migration. For more information about client-side transaction retries in CockroachDB, see <InternalLink path="transactions#transaction-retries">Transaction Retries</InternalLink>.

### Transactional schema changes

Support for <InternalLink path="online-schema-changes">transactional schema changes</InternalLink> 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 <InternalLink path="alter-table">`ALTER TABLE`</InternalLink> 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](https://github.com/flyway/flyway/issues), including the following details about the environment where you encountered the issue:

* CockroachDB version (<InternalLink path="cockroach-version">`cockroach version`</InternalLink>)
* Flyway version
* Operating system
* Steps to reproduce the behavior

## See Also

* [Flyway documentation](https://flywaydb.org/documentation/)
* [Flyway issue tracker](https://github.com/flyway/flyway/issues)
* <InternalLink path="connection-parameters">Client connection parameters</InternalLink>
* <InternalLink path="third-party-database-tools">Third-Party Database Tools</InternalLink>
* <InternalLink path="learn-cockroachdb-sql">Learn CockroachDB SQL</InternalLink>