supabase/migrations

supabase/migrations is a consolidation of SQL migrations from:

supabase/postgres
supabase/supabase
supabase/cli
supabase/infrastructure (internal)

aiming to provide a single source of truth for migrations on the platform that can be depended upon by those components. For more information on goals see the RFC

How it was Created

Migrations were pulled (in order) from:

For compatibility with hosted projects, we include migrate.sh that executes migrations in the same order as ami build:

Run all db/init-scripts with postgres superuser role.
Run all db/migrations with supabase_admin superuser role.
Finalize role passwords with /etc/postgres.schema.sql if present.

Additionally, supabase/postgres image contains several migration scripts to configure default extensions. These are run first by docker entrypoint and included in ami by ansible.

Guidelines

Migrations are append only. Never edit existing migrations once they are on master.
Migrations in migrations/db/migrations have to be idempotent.
Self contained components (gotrue, storage, realtime) may contain their own migrations.
Self hosted Supabase users should update role passwords separately after running all migrations.
Prod release is done by publishing a new GitHub release on master branch.

Requirements

Usage

Add a Migration

# Start the database server
docker-compose up

# create a new migration
dbmate new '<some message>'

Then, populate the migration at ./db/migrations/xxxxxxxxx_<some_message> and make sure it execute sucessfully with

dbmate up

Adding a migration with docker-compose

dbmate can optionally be run locally using docker:

# Start the database server
docker-compose up

# create a new migration
docker-compose run --rm dbmate new '<some message>'

Then, populate the migration at ./db/migrations/xxxxxxxxx_<some_message> and make sure it execute sucessfully with

docker-compose run --rm dbmate up

Testing

Migrations are tested in CI to ensure they do not raise an exception against previously released supabase/postgres docker images. The full version matrix is at test.yml in the supabase-version variable.