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;

set check_function_bodies = off;

set check_function_bodies = off;

set check_function_bodies = off; was set. Makes general sense to set this, but then it isn't turned back to

on

on

on

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$;

$function$;

$function$;

$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

*.sql

*.sql

*.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.

DanielOP•9/16/25, 2:08 PM

Uploading the base sql and the migration files in their current format. Again, thank you for any help.

schemas.sql404B

accounts.sql794B

20250915133322_create_accounts_table.sql685B

20250916134632_auto_add_account_function.sql429B

Ben H•9/16/25, 6:26 PM

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

DanielOP•9/17/25, 4:09 AM

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.

20250917040528_auto_add_account_function.sql115B

DanielOP•9/17/25, 4:31 AM

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.

DDaniel Quick and interesting note... So I deleted both of the recent migration files, ...

Fieryduck82579•9/17/25, 9:20 AM

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.

Fieryduck82579•9/17/25, 9:20 AM

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

FFieryduck82579 This isn't a declarative schemas-specific issue. I don't use the former, and I o...

DanielOP•9/18/25, 4:30 AM

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

schema

is modifying the execution to include the triggers?

DDaniel What are you trying to do when you encounter this issue? Based on that the code...

Fieryduck82579•9/18/25, 4:45 AM

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 resetsupabase 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.

FFieryduck82579 I was specifically referring to this issue: > the trigger was placed at the top...

DanielOP•9/18/25, 4:50 AM

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?

DDaniel Thanks. I'm curious for my own education, if you aren't using declarative db sc...

Fieryduck82579•9/18/25, 5:15 AM

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 migramigra 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.

DanielOP•9/18/25, 5:26 AM

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.

DDaniel Thank you. I'm more asking what flow you find yourself automatically creating m...

Fieryduck82579•9/18/25, 5:41 AM

Oh sorry, I misunderstood you.

bunx supabase db diff --local | bunx supabase migration new <filename>

bunx supabase db diff --local | bunx supabase migration new <filename>

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

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.

Declarative DB - Trigger isn't written into migration file

Similar Threads

Similar Threads

Similar Threads