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

# Flyway Database Migration

> Run Flyway database migrations using the Minimus Flyway image

[Flyway](https://flywaydb.org/) is an open-source database migration tool that tracks, versions, and applies schema changes using plain SQL scripts. This guide walks through running a Flyway migration against a PostgreSQL database using the [Minimus Flyway image](https://images.minimus.io/gallery/images/flyway/lines/latest).

## Flyway migration conventions

Flyway uses the naming convention `V{version}__{description}.sql`:

* Version number sets the execution order
* Double underscore separates the version number from the description.

Up until Flyway version 11, there was auto-scanning of the `sql/` directory. Starting with Flyway version 12 an explicit location must be configured instead.

Each migration runs exactly once. Flyway tracks applied versions in a `flyway_schema_history` table it creates in your database, and skips script versions that are already recorded there.

## Run your first migration

<Steps>
  <Step title="Start a PostgreSQL container">
    Start a PostgreSQL container on a shared Docker network to perform your testing on. Flyway will later use the database name, user, and password set here to connect to PostgreSQL:

    ```bash theme={null}
    docker network create flyway-net
    docker run -d \
      --name db \
      --network flyway-net \
      -e POSTGRES_DB=demo \
      -e POSTGRES_USER=demo \
      -e POSTGRES_PASSWORD=demo \
      reg.mini.dev/postgres:18
    ```

    <Note>
      This example does not mount a volume so data will be lost when the container is removed. For a persistent setup, add `-v pgdata:/var/lib/postgresql/data` to the `docker run` command.
    </Note>
  </Step>

  <Step title="Create a working directory">
    Create a working directory with a `sql` subdirectory. Next we will configure Flyway to look for migration scripts there:

    ```bash theme={null}
    mkdir -p flyway-demo/sql
    ```
  </Step>

  <Step title="Configure the database connection">
    Create `flyway-demo/flyway.conf` with the connection details matching the container above:

    ```text flyway-demo/flyway.conf theme={null}
    flyway.url=jdbc:postgresql://db:5432/demo
    flyway.user=demo
    flyway.password=demo
    flyway.locations=filesystem:/flyway/sql
    ```

    JDBC (Java Database Connectivity) is Java's standard API for database connections. The JDBC URL specifies the driver, host, port, and database name.
  </Step>

  <Step title="Create a migration script">
    Create the first migration script at `flyway-demo/sql/V1__Create_orders_table.sql`:

    ```sql flyway-demo/sql/V1__Create_orders_table.sql theme={null}
    CREATE TABLE orders (
        id        SERIAL PRIMARY KEY,
        reference VARCHAR(50)  NOT NULL,
        status    VARCHAR(20)  NOT NULL DEFAULT 'pending',
        created_at TIMESTAMP   NOT NULL DEFAULT NOW()
    );
    ```
  </Step>

  <Step title="Run the migration">
    Run the migration using Flyway:

    <CodeGroup>
      ```bash Run migration with Flyway theme={null}
      docker run --rm \
        --network flyway-net \
        -v $(pwd)/flyway-demo/sql:/flyway/sql \
        -v $(pwd)/flyway-demo/flyway.conf:/flyway/conf/flyway.conf \
        reg.mini.dev/flyway migrate
      ```

      ```shellscript Expected output theme={null}
      Flyway OSS Edition 12.x.x by Redgate
      Database: jdbc:postgresql://db:5432/demo (PostgreSQL 18.x)
      Schema history table "public"."flyway_schema_history" does not exist yet
      Successfully validated 1 migration (execution time 00:00.068s)
      Creating Schema History table "public"."flyway_schema_history" ...
      Current version of schema "public": << Empty Schema >>
      Migrating schema "public" to version "1 - Create orders table"
      Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.024s)
      ```
    </CodeGroup>
  </Step>
</Steps>

## Fix a PostgreSQL collation version mismatch with Flyway

When a PostgreSQL image is upgraded to a version built with a newer glibc collation library, Postgres may log a collation version mismatch warning. Flyway wraps the fix commands in a versioned migration for a tracked, auditable record that runs exactly once per environment.

<Info>
  See [Collation version mismatch in Postgres](/troubleshooting/troubleshooting-images#collation-version-mismatch-in-postgres) for background on the warning and what causes it.
</Info>

### Overview

The fix requires two commands handled separately:

1. `ALTER DATABASE demo REFRESH COLLATION VERSION` updates the collation version in PostgreSQL's internal metadata to match the OS. This clears the warning but does not fix index ordering issues.
2. `REINDEX DATABASE demo` rebuilds the indexes to match the new collation, completing the fix. \
   \
   `REINDEX DATABASE` is not compatible with transactions so it cannot be put in a regular Flyway migration file. Instead, it needs to run separately, either in a different migration marked as non-transactional or manually outside of Flyway entirely.

### Steps

<Steps>
  <Step title="Create the migration script">
    Save the following to `flyway-demo/sql/V2__Fix_collation_version.sql`:

    ```sql theme={null}
    ALTER DATABASE demo REFRESH COLLATION VERSION;
    ```

    Number the version as appropriate for your environment (`V2` is just an example).
  </Step>

  <Step title="Run the Flyway migration">
    ```bash theme={null}
    docker run --rm \
      --network flyway-net \
      -v $(pwd)/flyway-demo/sql:/flyway/sql \
      -v $(pwd)/flyway-demo/flyway.conf:/flyway/conf/flyway.conf \
      reg.mini.dev/flyway migrate
    ```
  </Step>

  <Step title="Reindex the database manually">
    Run `REINDEX DATABASE` manually during a maintenance window using the `psql` command-line client. First, open a psql session:

    ```bash theme={null}
    psql -h localhost -U demo -d demo
    ```

    Once connected, run:

    ```sql theme={null}
    REINDEX DATABASE demo;
    ```

    <Warning>
      `REINDEX DATABASE` locks the database. Plan a maintenance window before running this command in production.
    </Warning>
  </Step>
</Steps>

## JDK compatibility

The Minimus Flyway image is built on JDK 21. JDK 21 is an LTS release and is suitable for production deployments.

<Note>
  JDK 21 sets the compatibility floor for this image. Ensure your application runtime targets JDK 21 or is compatible with it. If your app is built for an older JDK (e.g. JDK 11 or 17), it may not be compatible with this image.
</Note>