Migrations with User-Defined Functions
I'm working on a very data-intensive application where we use a lot of user-defined functions to keep things performant and consistent.
So far, every time we want to change one of these functions, we've defined a new custom migration with the full content of the function. This works, but it makes it very hard to see and review the changes in pull requests.
Does anyone have a good solution for this?
2 Replies
My current thinking is
1. store the user-defined functions in a directory like
udf/
2. write a script that compares the each function in the directory to the same definition in the database; if they differ, generate a new custom migration to update
3. apply migrations
There are some challenges with this approach:
* if a function's signature changes, we need to drop and re-create, not just create if not exists
* the database normalizes function contents, including changing casing and spacingDrizzle currently doesn't support user-defined functions, procedures or triggers; for now, you'll have to handle these manually or automatically with a custom implementation like the one you're detailing here.