Migrations

We do most changes to the database using Supabase Studio, which provides a nice UI for creating migrations. Additionally you can use the sql editor in Supabase to run arbitrary sql commands. In order to create a migration file with the changes you made in the sql editor, you can use the following command:

./scripts/supabase/create_migration.sh <descriptive_name_of_changes>

Note: When you created a migration file from your local database and then run the supabase migration up command, supabase may complain that the table already exists. In this case, you can mark the migration as applied by running the following command:

supabase migration repair <migration_timestamp> --status applied --local

Do not forget to also update the types by running:

./scripts/supabase/generate_types.sh

Special Case 1: Views

Unfortunately, Supabase currently ignores the security invoker of functions and views. Therefore, we need to manually add that to the migration file in order for the views to be protected. The security invoker has the effect that the same RLS policies that are applied to the tables are also applied to the view.

You need to add the following code to the migration file:

create or replace view "public"."view_name"
with(security_invoker = true)
as SELECT e.created_at,

Special Case 2: Changes to other schemas than public

Changes to other schemas than public are currently not supported. If you need to make changes to other schemas, you will need to use the manual SQL Migrations. You can generate a migration file with the following command:

supabase migration new descriptive_name_of_changes