• A Supabase project up and running.
• Flyway installed on your local machine.
• Basic knowledge of SQL and command-line interface.

First, download and install Flyway CLI from the official Flyway website:

curl -LO https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/8.0.5/flyway-commandline-8.0.5-linux-x64.tar.gz
tar -xvf flyway-commandline-8.0.5-linux-x64.tar.gz
sudo ln -s `pwd`/flyway-8.0.5/flyway /usr/local/bin

To make sure it's installed correctly, run:

flyway -v

Create a new directory for Flyway configuration files and SQL scripts:

mkdir flyway_migrations
cd flyway_migrations

Inside this directory, create a flyway.conf file with the following content:

flyway.url=jdbc:postgresql://<your-supabase-instance-url>:5432/postgres
flyway.user=<your-supabase-db-user>
flyway.password=<your-supabase-db-password>
flyway.schemas=public
flyway.locations=filesystem:./sql

Replace <your-supabase-instance-url>, <your-supabase-db-user>, and <your-supabase-db-password> with the appropriate values from your Supabase project settings.

Create a subdirectory called sql within flyway_migrations:

mkdir sql

Write your SQL migration scripts inside this sql directory. Name the files following Flyway's naming convention: V1__create_table.sql, V2__add_column.sql, etc. Here's an example for V1__create_table.sql:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

Run the migrations using Flyway from the flyway_migrations directory:

flyway migrate

Check the output logs to make sure the migrations were successful. This command will apply all the new migration scripts in the sql directory to the Supabase database.

Log in to your Supabase dashboard, navigate to the SQL editor, and run:

SELECT * FROM flyway_schema_history;

This should show a table with the history of all the migrations that Flyway has applied, ensuring the records match your scripts.

To rollback the last applied migration, use:

flyway undo

However, Flyway Community Edition doesn't support automatic undo functionality. Instead, create specific rollback scripts using Flyway Pro or Enterprise Edition or execute the SQL manually:

DROP TABLE users;

Integrate Flyway migration with your CI/CD pipeline:

1. Add Flyway configurations to your pipeline script.
2. Run flyway migrate as part of the build/deployment process.

Example using GitHub Actions:

jobs:
  flyway:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Set up JDK 11
        uses: actions/setup-java@v1
        with:
          java-version: 11
      - name: Run Flyway Migrations
        run: flyway -configFiles=flyway_migrations/flyway.conf migrate

This setup will trigger database migrations automatically during deployment processes, ensuring your database schema is always up-to-date.