How to Migrate Supabase Databases with Flyway & GitHub Actions

#supabase #postgres #github #flyway

Completely built on open-source technology, Supabase 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."

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.

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:

-- 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:

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:

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:

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 to access it:

-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.