2

I am using flask + sqlalchemy + alembic + postgresql, and am trying to find a simple architecture to solve the following problem.

I have a simple database structure, lets say two tables :

-- Users

-- UserItems

My users are in several different domains. I would like to have several of this database structure. For that I have database schemas in SQL. I created the matching class structure using sqlalchemy decalrative_base, and end up with a MetaData object that isn't tied to a specific schema.

Now I need to be able to run alembic's upgrade operation on each schema that I create (dynamically). I can override the target_metadata that alembic uses, but that will disturb the current running processes.

I thought about cloning the metadata, creating a "Like metadata1, but with schema XYZ" and feeding that to alembic, but I didn't find a way to do this.

Is the correct approach to clone the metadata and modify the schema of all the tables? Is there a simple way to do this?

Is there a different way to apply an alembic operation on multiple schemas in the same database?

Thanks

Noam
  • 1,881
  • 1
  • 12
  • 20

1 Answers1

1

What we ended up doing was use sqlalchemy's event mechanism to catch the queries before they are executed and add a prefix to alter the schema :

def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    schema_name = <Logic to resolve schema name>
    statement = "SET search_path TO '%s'; %s" % (schema_name, statement)
    return statement, parameters
    ......
    (later in the code)
    listen(Engine, 'before_cursor_execute', before_cursor_execute, retval=True)

This way, we can run alembic's migrate several time, making sure the schema_name is resolved correctly each time, and everything works smoothly

Noam
  • 1,881
  • 1
  • 12
  • 20