How to Migrate Supabase Databases with Flyway & GitHub Actions
Tags: supabase, postgres, github, flyway
Published: 2021-08-09 21:24:56

Completely built on open-source technology, [Supabase](https://supabase.io) is an upcoming alternative to Google's Firebase. One distinct difference between Supabase and Firebase is the way they persist and manage data. While Firebase uses Firestore, a NoSQL document database you can only configure via a web interface, Supabase uses a Postgres database under the hood.

When you set up a project, Supabase provides an in-built SQL console you can use to execute SQL statements on the project's database quickly. With a new project, it's tempting to whip up some `CREATE TABLE` statements and have at it.

And if you are experimenting with Supabase, feel free to use Supabase's SQL console. After all, it works quite well and is, hands down, the fastest way to get cooking. But if you are working on a project that will see the light of production, consider using a tool like Flyway to migrate your database schema more reliably.

In this post, we will set up a GitHub Action to execute Flyway migrations against a Supabase database whenever changes are pushed to a repository. This allows you to store your migrations alongside your code and always keep your database in sync with your business logic.

## Finding the Database Connection Info

You need a database host, port, username, password, and database name to run Flyway. In Supabase, you can find this information by clicking on "Settings" and then "Database."

![Sidebar menu of Supabase, showing the Settings menu open, with Database selected](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5yjs44rsstgnmhf2627v.png)

On the *Database Settings* page, you will find a box titled "Connection info" with all the information you need. For security reasons, the database password is not listed here. When you created the project, Supabase prompted you to enter it.

## Creating a Simple Flyway Migration

A Flyway migration is nothing more than an SQL file. Since migrations are used to version control your database schema, these files must adhere to certain naming conventions. Flyway has a great introduction to [naming SQL-based migrations](https://flywaydb.org/documentation/concepts/migrations.html#sql-based-migrations).

For our purposes, we will create a `profile` table with three columns: `user_id` referencing the ID of a registered user, `username`, and `email`.

Let's create a file called `V1__create_table_profile.sql` in a directory `migrations`. The `migrations` directory should be placed in the root directory of our repository:

```sql
-- migrations/V1__create_table_profile.sql
CREATE TABLE public.profile (
  user_id uuid REFERENCES auth.users NOT NULL PRIMARY KEY,
  username TEXT NULL,
  email TEXT NULL
);
```

If you want to change the location of these migrations, make sure to change the GitHub workflow configuration below accordingly.

## Writing a GitHub Action to Run Flyway

Whenever we push changes to the migrations in the `migrations` directory, we want a GitHub Action to run them against our Supabase database.

In our repository, create a file called `.github/workflows/migrate.yml` with the following content:

```yaml
name: 'Migrate database schema'

on:
  push:
    branches:
      - main

jobs:
  migrate-database:
    name: Run Flyway migrations
    runs-on: ubuntu-20.04
    env:
      SUPABASE_HOST: db.YOUR_SUPABASE_DATABASE_HOST.supabase.co
      SUPABASE_PORT: 5432
      SUPABASE_USER: postgres
      SUPABASE_DB: postgres
    steps:
      - uses: actions/checkout@v2
      - run: >-
          docker run --rm
          --volume ${{ github.workspace }}/migrations:/flyway/sql:ro
          flyway/flyway:7.12.1-alpine
          -url="jdbc:postgresql://${{ env.SUPABASE_HOST }}:${{ env.SUPABASE_PORT }}/${{ env.SUPABASE_DB }}?sslmode=require"
          -user="${{ env.SUPABASE_USER }}"
          -password="${{ secrets.SUPABASE_PASS }}"
          migrate
```

The first line defines the workflow's name:

```yaml
name: 'Migrate database schema'
```

Then, we define the event that will trigger this workflow. Here, the workflow will only run when someone pushes changes to `main`:

```yaml
on:
  push:
    branches:
      - main
```

The job `migrate-database` defines four environment variables: `SUPABASE_HOST`, `SUPABASE_PORT`, `SUPABASE_USER`, `SUPABASE_DB`. Make sure to change them according to the connection info you found on the *Database Settings* page in Supabase.

This job first checks the repository out and then executes a `docker` command. This command uses the `flyway:flyway` image to first mount the `migrations` directory to the container and then run the migrations against the database configured via the environment variables.

Since the database password is a secret, we are using an [encrypted secret](https://docs.github.com/en/actions/reference/encrypted-secrets) to access it:

```yaml
-password="${{ secrets.SUPABASE_PASS }}"
```

Make sure to create a secret `SUPABASE_PASS` containing the database password in your repository settings.

Commit everything and push your changes to your repository. Once the workflow has been executed, your database schema has been updated and you should see a new `profile` table in your database.

## Recap

Why should I use Flyway?

- Changes to your database schema become repeatable. This is useful when setting up new environments (either on supabase.io or locally).
- Your database schema evolves alongside your application.
- How your database looks like is co-located with the code interacting with it.

What we did:

- Retrieved the database connection info from the *Database Settings* page in the Supabase UI.
- Created a simple Flyway SQL migration called `migrations/V1__create_table_profile.sql`, which creates a `profile` table.
- Added a GitHub workflow with one job called `migrate-database` that uses Flyway to run all migrations in the `migrations` directory against a Supabase database.