How to migrate databases in Supabase

––– views

By Kenneth Cassel

Migrations in Supabase with Migra cover image

Migrations - Why we use migra

One of the main benefits of using Supabase is databases on the platform are powered by Postgres. Since Postgres itself is a mature and battle-tested database, there are a ton of options available for database migrations.

At Slip, we're using a tool called migra for database migrations. Essentially, it 'diffs' two databases and generates a script which can be applied to transform one database's shema into the target database's schema.

Like the Supabase team, we also have a preference for using diffing tools instead of manual migrations. They allow us to iterate quickly without the overhead of saving and managing our own SQL scripts, while still being confident that our schemas match across environments.

We explored a few options for migration tools and ended up using migra. It's fairly straight-forward to use and it's already dockerized — so you can use it inside of a CI/CD workflow easily.

There are few caveats to watch out for when using migra with Supabase projects and we're going to explore them in this tutorial!

Setup - Install Migra

migra is a Python program so you can either install it using Python's pip package manager, or use docker to pull down a ready-to-use image with migra.

We're going to use the docker image in this tutorial. If you don't already have docker installed on your machine, install it now.

Pull down the docker image

docker pull djrobstep/migra

docker pull djrobstep/migra

Optional: Create an alias
An optional step you can take here is creating an alias for migra so you don't have to type docker run djrobstep/migra migraevery time you want to use the tool.

alias migra="docker run djrobstep/migra migra"

alias migra="docker run djrobstep/migra migra"

Usage:
The most basic usage of migra takes the form of the following command.

The dburl_from should be the database you want new changes applied to. The dburl_target is the database whose schema you want to match.

migra [dburl_from] [dburl_target]

migra [dburl_from] [dburl_target]

Find your Supabase Connection Details

You'll need your postgres connection string from both the projects you're using.
You can find this in the Settings -> Database screen inside of Supabase

Supabase dashboard showing where to find a connection string for a db

Generate SQL Script from Schema Diff Using Migra

Let's imagine you have two Supabase projects/environments, a Dev project and a Staging project.

You just created a new feature called Tutorials, which allows users to post free tutorials to your site. It involved some database changes, including adding a new table and a new storage bucket (📝 we'll talk more about the bucket later in the tutorial).

Your new feature works locally but now you want to deploy it and do a run-through to find any edge cases when deployed to the Staging environment.

Luckily, you're using a migration tool like migra to manage your database schema changes. You won't have to manually record each change you've made, you can just diff your databases and generate a script!

You'll need to use the migra tool and pipe the generated sql commands to a file so you can inspect the changes.

Sometimes the script that migra generates will have drop statements. If it does, the command will fail unless you pass the --unsafe flag.

migra [stg_connection_string] [dev_connection_string] --unsafe  > stg_from_dev.sql

migra [stg_connection_string] [dev_connection_string] --unsafe  > stg_from_dev.sql

After running the command, you should end up with a file called stg_from_dev.sql which shows the diff between the two databases. It would look something like this:

create type "public"."tutorial_status" as enum ('published', 'draft');

create table "public"."tutorials" (
    "id" uuid not null default gen_random_uuid(),
    "author_id" uuid not null,
    "title" text not null default 'New Tutorial'::text,
    "slug" text,
    "description" text,
    "cover_image_url" text,
    "created_at" timestamp with time zone default now(),
    "updated_at" timestamp with time zone default now(),
    "status" tutorial_status default 'draft'::tutorial_status
);

alter table "public"."tutorials" enable row level security;

CREATE UNIQUE INDEX tutorials_pkey ON public.tutorials USING btree (id);

alter table "public"."tutorials" add constraint "tutorials_pkey" PRIMARY KEY using index "tutorials_pkey";


create type "public"."tutorial_status" as enum ('published', 'draft');

create policy "Users can do anything to their own tutorials"
on "public"."tutorials"
as permissive
for all
to public
using ((auth.uid() = author_id))
with check (auth.uid() = author_id);

create type "public"."tutorial_status" as enum ('published', 'draft');

create table "public"."tutorials" (
    "id" uuid not null default gen_random_uuid(),
    "author_id" uuid not null,
    "title" text not null default 'New Tutorial'::text,
    "slug" text,
    "description" text,
    "cover_image_url" text,
    "created_at" timestamp with time zone default now(),
    "updated_at" timestamp with time zone default now(),
    "status" tutorial_status default 'draft'::tutorial_status
);

alter table "public"."tutorials" enable row level security;

CREATE UNIQUE INDEX tutorials_pkey ON public.tutorials USING btree (id);

alter table "public"."tutorials" add constraint "tutorials_pkey" PRIMARY KEY using index "tutorials_pkey";


create type "public"."tutorial_status" as enum ('published', 'draft');

create policy "Users can do anything to their own tutorials"
on "public"."tutorials"
as permissive
for all
to public
using ((auth.uid() = author_id))
with check (auth.uid() = author_id);

Apply the migra generated SQL Script to the Staging Database

After generating a script, you should take some time to visually inspect and do a sanity check of the generated sql script, especially if you used the --unsafe flag.

If the script looks good, it's time to apply the changes to your database.

Copy the generated sql script and run it inside the Supabase SQL editor.

Congrats! 🎉 You just completed a database migration using migra for your Supabase project!

🚦 Wait up! - Migra doesn't migrate all Supabase data

In our scenario there is one more step we need to take so that our Staging environment is ready for the tutorial feature. We added a tutorials bucket, but it's not showing up in Staging!

Why is that? Doesn't migra generate an accurate schema diff across databases?
It does, but adding a new bucket doesn't affect our storage schema!

While migra is a great tool for performing database migrations, the underlying storage schema stays the same in Supabase when you add a new bucket. A new bucket doesn't represent a schema change, but a new entry inside of the storage.buckets table.

Running a quick SQL query select * from storage.buckets will show us that we've inserted a row into the storage.buckets table called tutorials. We didn't change the schema.

postgres=> select * from storage.buckets;
    id     |   name    | owner |          created_at           | updated_at | public
-----------+-----------+-------+-------------------------------+--------------------------
 courses   | courses   |       | 2021-09-30 01:38:43.379242+00 |            | t
 tutorials | tutorials |       | 2021-09-30 01:39:23.369674+00 |            | t

postgres=> select * from storage.buckets;
    id     |   name    | owner |          created_at           | updated_at | public
-----------+-----------+-------+-------------------------------+--------------------------
 courses   | courses   |       | 2021-09-30 01:38:43.379242+00 |            | t
 tutorials | tutorials |       | 2021-09-30 01:39:23.369674+00 |            | t

Add any storage or authentication changes manually

To complete our migration and get our Staging environment working, we'll need to manually add any storage or authentication changes to our Staging Supabase project.

In our case, all we added on the storage or authentication front was a new public bucket called tutorials, so we'll apply those changes to the Staging environment and we're good to go!

Next steps:

We went over a very basic migration scenario, but it was a manual process.

A great next step would be setting up migra migrations to happen automatically inside of a CI/CD workflow.

We're going to be exploring doing that ourselves using Github Actions for a more automated experience. We'll be sure to write about it and let you know how we ultimately set it up!

If you have any questions or feedback feel free to reach out to me on Twitter @KennethCassel!

Thanks for reading!

Like this content?
If you'd like to learn more about Supabase check out our upcoming course Advanced Supabase Techniques, where we build and teach you techniques we've learned for making development with Supabase faster!