Usage

from the root of the supabase/postgres project, you can run the following commands:

Usage: nix run .#dbmate-tool -- [options]

Options:
  -v, --version [15|16|orioledb-17|all]  Specify the PostgreSQL version to use (required defaults to --version all)
  -p, --port PORT                    Specify the port number to use (default: 5435)
  -h, --help                         Show this help message

Description:
  Runs 'dbmate up' against a locally running the version of database you specify. Or 'all' to run against all versions.
  NOTE: To create a migration, you must run 'nix develop' and then 'dbmate new <migration_name>' to create a new migration file.

Examples:
  nix run .#dbmate-tool
  nix run .#dbmate-tool -- --version 15
  nix run .#dbmate-tool -- --version 16 --port 5433

This can also be run from a github "flake url" for example:

nix run github:supabase/postgres#dbmate-tool -- --version 15

or

nix run github:supabase/postgres/mybranch#dbmate-tool -- --version 15

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

First, start a local postgres server and apply the migrations

# Start the database server
nix run .#dbmate-tool -- --version 15 --flake-url "."

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

Then, execute the migration at ./db/migrations/xxxxxxxxx_<some_message> and make sure it runs 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.