Multi-Tenant Schema Support

Issue #244 closed
nickretallack
created an issue

I am trying to do something very similar to what's in this thread: https://groups.google.com/forum/#!topic/sqlalchemy-alembic/FA8wpmPJc7U

I'm having trouble getting this solution to work for me. Here's what I want to happen:

I have some tables explicitly in the "public" schema, and some tables implicitly in the "prototype" schema. That is, if schema is not set, it should be "prototype". There is only one public schema, but the prototype schema gets duplicated many times over with different names for different tetants. I always set the search_path to "{tenant_name}, public", where the tenant_name schema is a personal clone of the prototype schema.

Each clone of "prototype" should have its own alembic version table. When I change the schema, I'd like to generate a migration based on the prototype table, and then run it on all of prototype's clones.

Looping over the list of clones is easy enough. The hard part is getting alembic to generate the right diff. I wish it were as simple as setting search_path and then diffing only the tables that can be found that way, but alembic manages schemas more explicitly than that.

Also, I don't want the output to explicitly name the prototype schema in each line. It needs to default to whatever is the first item in my search path, the way postgres does.

In fact, if I could get alembic to run in a dumber mode where it didn't track schemas at all and just compared the list of visible tables and naively created any new ones however postgres saw fit, this would all work fine.

Comments (3)

  1. Michael Bayer repo owner

    the current approach for multiple alembic_version tables is multiple alembic environments. this can be set up within your env.py if you've worked out some scheme.

    As far as all the other things you're looking for it seems like you could hack around env.py mechanics to make it happen but I don't really know the specifics.

    This is kind of an open ended request here without much in the way of actionable specifics so mind taking it over to the mailing list ? Maybe someone has some advice, I don't have that much time to work out elaborate recipes right now.

  2. Log in to comment