Declarative DB - Trigger isn't written into migration file
Good morning! I'm using declarative database, and then creating the migration files following the steps in https://supabase.com/docs/guides/local-development/declarative-database-schemas. I was successful with initially setting up a table, and now I'm adding RLS and a trigger function on the table. RLS alteration and policy looks good in the migration script, but then I had some issues with the trigger function.
1) Before defining the function,
set check_function_bodies = off; was set. Makes general sense to set this, but then it isn't turned back to on in the migration script. Based on my limited research, this seems like something that should be reset - does Supabase do this silently, or does it have a different approach here?
2) The trigger wasn't written at all. The function looks good (Separately, the $$; at the end is replaced with $function$; with a new line before the semi-colon, but I assume this is WAI) --- but the trigger isn't written at all. https://supabase.com/docs/guides/local-development/declarative-database-schemas#known-caveats doesn't list anything about issues with triggers, so I don't know if this is a glitch. For the format of a migration file, I assume I paste the trigger as it's written in the *.sql, but no idea how alterations will be written in the future. Any advice?
Thank you for your help (or even just a "dunno.")!Declarative database schemas | Supabase Docs
Manage your database schemas in one place and generate versioned migrations.
12 Replies
Uploading the base sql and the migration files in their current format. Again, thank you for any help.
I could be wrong, as I'm trying to figure out how this works too. But I think what is happening in your case is that supabase db diff does not diff against the auth schema (which is where your trigger is). This is really confusing because the Supabase documentation (https://supabase.com/docs/reference/cli/supabase-db-dump) says otherwise: "by default all schemas in the target database are diffed". But, based on my testing, I think they must mean all schemas except for the Supabase managed schemas. Pretty confusing 🙃
Can you try with "supabase db diff --schema auth, app, public"?
I'd appreciate if someone more experienced could comment too 🙂
CLI Reference | Supabase Docs
CLI reference for the Supabase CLI
You nailed it. Additional migration file developed based on your command attached. Thank you so much! Are you aware of an issue filed for this? Otherwise, I'm happy to summarize your theory and point to this thread to see if Supabase wants to either fix the issue or update their documentation.
Thank you again - I never would have identified the workaround you provided.
Quick and interesting note... So I deleted both of the recent migration files, and then the command with the call out of the schemas - the trigger was placed at the top of the file, before the function was defined. When upped to my local supabase, immediately angry. There may be some general underlying issues with triggers generated from declarative schemas. Again, thank you so much for your help here.
This isn't a declarative schemas-specific issue. I don't use the former, and I occasionally have this issue too which has required manual editing.
Looking at the code, if no schema is explicitly listed, RLS and trigger changes should be included in the migration.
https://github.com/supabase/cli/blob/develop/internal/db/diff/templates/migra.ts#L66-L78
GitHub
cli/internal/db/diff/templates/migra.ts at develop · supabase/cli
Supabase CLI. Manage postgres migrations, run Supabase locally, deploy edge functions. Postgres backups. Generating types from your database schema. - supabase/cli
What are you trying to do when you encounter this issue? Based on that the code doesn't indicate the issue being encountered, do you think just the usage of the flag for
schema is modifying the execution to include the triggers?I was specifically referring to this issue:
the trigger was placed at the top of the file, before the function was defined.This happens sometimes when I create a new migration file.
When upped to my local supabase, immediately angry.Me too, when I run
supabase db reset to apply, and test the migration.
do you think just the usage of the flag for schema is modifying the execution to include the triggers?No, I've had this happen both with and without using the flag.
Thanks. I'm curious for my own education, if you aren't using declarative db schemas, why are you creating migration files through the supabase tooling?
It’s easy to fall into the common assumption that Supabase (or any other Postgres service) is just a Postgres database with some extra stuff. While that’s true to a point, there’s also a lot of service-specific detail that, unless you know exactly what you’re doing, can trip you up. For example, while I could have used the underlying
migra tool that Supabase tooling relies on, I would’ve had to think about things like managed schemas and other bits I’m not really familiar with. Therefore, I rely on the Supabase tooling to take care of it for me.Thank you. I'm more asking what flow you find yourself automatically creating migration files for if not for declarative db - above you said "This isn't a declarative schemas-specific issue. I don't use the former, and I occasionally have this issue too...", so I'm curious what you're doing if not automatically defining migration files for a declarative db schema.
Oh sorry, I misunderstood you.
I apply the changes directly to the database (since I develop locally), and then generate the migration.
While I see the appeal of using declarative database schemas ("DDS"), and I might eventually start using it, I am just too comfortable with using
psql to find all the information that I need (and that DDS might not provide (for now)).
The way I use migrations is (1) for easily setting up the database in a different machine, and (2) as a recovery point for when I am experimenting with database changes.