Supabase#

Production#

1. Never modify the schema in the production environment. Always go through the migration process described below. This ensures the local Supabase DB and production stay in-sync
2. Data manipulation is allowed for operational purposes

Creating Migrations#

We have two sources of truth for the Suapbase DB schema:

1. Manually maintained schema files in supabase/schema/*.sql
2. Auto-generated migrations in supabase/migrations/*.sql

Manually Maintained Schema Files#

We manually maintain schema files for two main reasons:

1. Make it easy to search and see what the current schema is in production
2. Make it easy for reviewers to understand what changes you are trying to make and help verify the auto-generated migration files. It's very hard to review migrations without knowing what the targeted schema is.

Modifying the Database Schema#

psql#

You can use the psql client to run SQL against the local database.

psql postgresql://postgres:postgres@localhost:54322/postgres

I like to workshop my changes or migration statements in supabase/schema then copy them into the psql client

Supabase Studio Editor#

You can use the Supabase UI to make schema changes as well. To open, the Studio Editor go to http://localhost:54323/

open http://localhost:54323

This makes it easy to change the schema and add new data, but the changes are more opaque than the psql client. Remember to always update the manually maintained schema files so the reviewer can help verify the schema changes.

Generate Migration Files#

1. To preview the current migration or diff between production and your local Supabase schema, run

supabase db diff

2. To generate a migration file with the current diff, run

supabase db diff -f name_of_migration_file

3. To verify the current migration, run

supabase db reset

This will run all the migrations, including the recently generated ones. Verify the migrations succeed and the resulting database schema looks correct.

Generate Schemas from a Migration#

• After generating a migration, run make generate-schemas
• This will:
  • Update the supabase/schema/ directory with the current schemas
  • Update the auto-generated python types (via SQLC) for the backend
  • Update the auto-generated typescript types for the frontend app

Note: Supabase CLI Versioning

• You might see unexpected changes if your Supabsase CLI is not up to date
• To troubleshoot run brew upgrade supabase, restart your local Supabase instance, and run the script again

Bump Migration Version#

Sometimes another PR with a migration has been merged and you need to bump the version to get the latest migrations. To do this, run the following command:

# from the root of the project
make bump-migration name=my_migration_name

Local Development Setup#

1. Prerequisites#

• Docker: link

• Supabase CLI:

  ⧉

  brew install supabase/tap/supabase
  

• sqlc

  ⧉

  brew install sqlc
  

2. Login and Link#

• Navigate to the root directory of the Dosu repository:
  ⧉

  cd /path/to/dosu/
  

• Login to Supabase:
  ⧉

  supabase login
  # Follow the on-screen instructions to add your access token.
  

• Link to the staging account (leave the password blank):
  ⧉

  supabase link --project-ref newxxbpwcphdvfoydmjn
  

3. Environment Configuration#

• Start the Supabase docker containers
  ⧉

  supabase start
  

Troubleshooting#

The local Supabase env vars typically stay the same, but sometimes they change. If they do, run

supabase status

to get the latest values and update the development-local pulumi ESC environment with them.

4. More info#

More information on local development in supabase can be found in their documentation here.

5. Supabase UI#

• Visit the provided Studio URL to interact with Supabase's UI targeting
  Docker containers.

Testing#

If you are making complex schema changes, like new RLS or database triggers, it's recommended to add pgTap tests.

All tests live under supabase/test.

Read more about pgTap testing in the Supabase docs

You can run all tests locally with

supabase test db

or one by one with

supabase test db supabase/tests/database/{name_of_test}.test.sql