View/Function/Sproc/Matview proposal

Issue #434 closed
Steven Locke
created an issue

I have previously created a migration tool in c# utilizing dbUp which in addition to doing migrations maintained the state of views/functions/stored procedures (as well as eventually users etc). This allowed us to maintain views etc as if it were code and with each migration the views etc would be dropped and recreated at the state of the revision that is checked out. This had the advantage of treating your database objects (aside from tables where migration makes the most sense) as code that is part of the application. The database code was no longer "hidden" or shrouded in mystery. Now that I'm entrenched in python and use alembic and sqlalchemy which I love, I'd like to implement this functionality in this language. I've considered creating a separate app that wraps alembic to handle this however I'd like to know if there is any interest in having this functionality in the main application as an extension of sorts.

Comments (4)

  1. Michael Bayer repo owner

    Of course it would be nice for the library to have these features embedded but I'd want to take a very long road to get there, since these are much more intricate APIs that if we get them wrong, it's forever. Development as third party plugins to start with is the best option.

    There are already patterns available for all of these things however they are not put together into a single system that works out of the box for Alembic.

    For views, start with the view recipe: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

    for Alembic integration of views and stored procedures, there's an in-depth example of how to extend Alembic for this kind of thing here, which interestingly enough includes views and SPs: http://alembic.zzzcomputing.com/en/latest/cookbook.html#replaceable-objects

    The SQLAlchemy view recipe can be integrated with the alembic replaceable object recipe as well.

    What's not here is any means of defining the stored procedure as anything other than the literal SQL text of it. The situation is the same for triggers which are also defined by fairly idiosyncratic syntaxes. I think keeping these particular syntaxes as strings is the way to go since these are imperative syntaxes, not declarative like normal DML or SQL - trying to abstract something Oracle PL/SQL into Python would not really be feasible. So just the framework of CREATE/ DROP for sequences / triggers which is more or less what's described in the above recipes.

  2. Steven Locke reporter

    That pattern is slightly better to trace back changes to views/sprocs etc however I don't think it is ideal honestly. I was thinking of having an additional folder structure like the "versions" folder that would look like:

    Objects

    • views
    • functions
    • procedures

    Where in the appropriate folder each view has it's own definition file much like in a normal code project. Every time migrations are run (probably with additional flags/options etc) these views are recreated based on the definitions. I'm sure I could build something out within the pattern you've laid out, but from prior experience my company greatly benefitted from defining views as such because it allowed more meaningful pull requests and comparing git history for a view. I also believe that defining these objects as pure sql text is the ideal honestly. Supposing I were to start building out a third party plugin, do you have any examples for integrating a plugin into alembic? I believe for starters I will spin up a poc in a different repo and share the repo with you if you're interested.

  3. Michael Bayer repo owner

    however I don't think it is ideal honestly.

    that's why we dont add opinionated things like these as features :)

    I was thinking of having an additional folder structure like the "versions" folder that would look like:

    that's all convention which would be specific to your own application.

    Every time migrations are run (probably with additional flags/options etc) these views are recreated based on the definitions.

    this is the tricky part because I'm sure your views aren't just simple mirror images of the tables. you'd need to figure out what kind of heuristic you're looking for here whereby your view structure is maintained but relevant changes to the table (e.g. column add) are added to the view. To generalize this you'd almost certainly need to be using abstractions like select() objects, so that when a column is added to a table, the select() can be run against the new table to produce what the new view looks like, and that it compares as differently to the previous one.

    Supposing I were to start building out a third party plugin, do you have any examples for integrating a plugin into alembic?

    It depends on what hooks you are using. Most integration hooks are declarative, meaning, at the import level in your package, various hooks are called like Operations.register_operation(). The recipe at http://alembic.zzzcomputing.com/en/latest/cookbook.html#replaceable-objects illustrates a bunch of these hooks and the API for things as a whole (e.g. autogenerate, etc.) starts at http://alembic.zzzcomputing.com/en/latest/api/index.html.

  4. Michael Bayer repo owner

    there's no action to take within Alembic for this, this seems like a fairly ambitious plugin on top of Alembic and if extension hooks need to be built we can support that via separate issues.

  5. Log in to comment