That was what we did in Java with Flyway and it’s stateless migrations. You could easily see the history of stores procedures and every time the app started up it made sure they were all updated and correct.

Worked great, WAY better than having them buried in other SQL files.

Funny. I was just having a conversation with my partner about this very issue and this was the approach I was thinking of. I’ve found keeping database functions, triggers, etc. painful as part of standard migrations—finding the most recent version is particularly annoying. They’re the kind of thing it’s best to treat like the rest of a project’s code—version controlled outside of migrations.

Isn't that what you'd do anyways?

How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control?

Or are you actually typing in triggers manually in the Postgres command line?

Rails migrations are checked in to version control, and then rails keeps track of which "migration" has been run. For tables this works fine, because you want to define them only once and if you need to change columns/add columns etc. you can create a new migration, which contains only the updates you want.

For triggers however you need to replace the trigger wholesale, so you end up with several versions of the trigger on your file system and need to check each migration file to see what the latest definition of the trigger is (for tables rails normally creates a separate file with the current state of the database after running a migration, so you have relatively easy access to the latest state of table definitions).

You can easily add functions, triggers, etc. as SQL in migrations. It’s just not a good idea when you realize you might want to change them. Putting them in migrations seems nice at first because it omits an extra step when upgrading your db. But then you have the SQL multiple times in your migrations, which makes finding the correct version annoying.

Not the OP but I did use triggers as suggested in the past. IME the issue is that DDL like triggers are transactional, so when you deploy them executing queries could be using a mix of old and new.

Granted, this is an issue for any system comprised of distributed components. Though I think the pain is more acute when it's the source of truth.

One way around is downtime or going all in on stored procedures for all work, or at least all writes.

The other wrinkle is that the trigger has to be rolled back when reverting code. And some shops are not diligent about maintaining down procedures. There is also the possiblity of rolling back migrations out of the order they were run, so you might not get back to a consistent state.

To keep things clean one could keep the trigger code in a file separate from any migrations and then create a migration that references the trigger source so that one doesn't have to go digging through the migration files later to read the trigger or update it.

I think some folks above may literally have meant they put the trigger code in the migration file itself. Or I'm way off base.

So there's a potential problem here.

Yes, you can technically just drop and replace all your stateless things (views, triggers, stored procedures, user defined functions).

But, if one of your migration steps happens to depend on a particular version of one of those things (it's not likely to be a trigger, but who knows?), then it can break.

I'd say - assuming you're doing your migration offline - then do it both ways. At a previous workplace I used a paid for tool called SQL Delta [https://www.sqldelta.com/]. It couldn't handle our complicated data migrations (at some point you need a human in the loop), but it was really useful for checking if there was anything off, and for synchronizing all of the views and so on at the end.