Performing automated migrations with Supabase

I love how easy it is to perform migrations with Supabase - this is something that could easily consume a lot of time as a solopreneur, but I'm able to easily perform many migrations per hour/day using their tooling. I'm finding that differences like this are what really count when I'm evaluating the feasibility of building and managing multiple tech products at the same time, as I'm looking to do under Positive Sum, my personal holding company.

Here is how it goes:

Step 1 - make the required changes to the locally hosted Supabase instance

Supabase has an out-of-the-box version that you can run locally with Docker. The dashboard interface is identical to the hosted version:

I can make these changes easily in the UI without having to write a single line of SQL, despite it being a Postgres database.

Step 2 - Run the migration command in the terminal

With the Supabase CLI, I can run the following command, which will perform a diff between the current local database, and a shadow database initialised, built up using the pre-existing migration scripts.

supabase db diff --use-migra -f <migration_name>

Where <migration_name> is a custom name you give to describe the migration, like a commit message.

Step 3 - Push to your development/production branch

A migration file is generated when running the above script. When I deploy to my development branch, a GitHub Action automatically picks this up and runs it against the development project I have with Supabase.

The GitHub action I'm using can be found here.

The outcome of this process is that the same changes make their way to the development database within a couple of minutes of making them locally, which saves a lot of time.

Pushing to production would be the same approach, although guarded by many more automated tests against development to ensure that all of the core flows are still operational.