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 aprofile
table. - Added a GitHub workflow with one job called
migrate-database
that uses Flyway to run all migrations in themigrations
directory against a Supabase database.
Top comments (1)
Great article! thx.