Issues

Issue #119 new

thought experiments in managing stored procedures/user defined functions

selenamarie
created an issue

Managing Stored Procedures (SPs) and/or User Defined Functions (UDFs) is challenging. I'm coming from Postgres-land, so I'm going to stick with saying "UDFs" for the rest of the issue.

Most people I know put each UDF in a file, and then upgrades are managed fairly easily with something like alembic. Downgrades, on the other hand, are very difficult to figure out.

Some ideas that were thrown around that might help solve this problem include:

I'm most interested in solving the downgrade issue, regardless of other use cases.

Comments (8)

  1. Marc Abramowitz

    This is something I'm interested in too. I want to manage MSSQL stored procedures.

    Ideas: 1. Do it outside of Alembic, having one file per proc and a small SQLAlchemy-based tool that can compare the files to the databas procs and possibly apply them as well. 2. Same as 1 but leverage Alembic - e.g.: write Alembic migrations that have proc definition in them or that read them from an external flat file. Leaning towards latter because DBAs can review pure SQL without Python mixed in.

    As Selena said, going forward is easy; backwards is harder. If the proc definitions are in the migrations then there is a lot of duplication because each downgrade has the same proc as the previous migration's upgrade. I wonder if I can express that migration N's downgrade should always just do migration N - 1 's upgrade? Another way to go is that each upgrade and downgrade references a file with a particular version of the proc. Then they can share and not duplicate. Would have a file system full of every version of every stored proc which is not taking advantage of the source code control system. But that's how migrations are anyway. Maybe it feels worse with procs because they're more like code. Maybe I should get over my aesthetic concern here.

    Those are a few thoughts. Anyone have preferences or other ideas?

  2. Mike Bayer repo owner

    also see #173 which I think is mostly the same thing as here. I'm writing a new recipe in the docs section that adds some hypothetical systems for this.

  3. Log in to comment