Alembic: Using Alembic for Single Database with Multiple Schemas

[mohamednajiullah]

Describe your question
I'm trying to use alembic to manage database migrations for a postgres database that contains multiple schemas with each schema being used by a different micro-service.

I realised that I have to use a different table for each micro-service otherwise one micro-service's migration starts deleting other micro-service tables. So I configured each micro-service alembic_version table to be in it's own schema to avoid clashes.

The problem that I'm facing is that every migration now recreates all the existing tables of the schema. I tried to debug it and one of the solutions was to set include_schemas=True. But this makes alembic scan all other schemas and try to delete all the tables present in them

I'm not sure if I explained the issue well. I can more details as required

Useful links

• The documentation website
• The Cookbook wiki
• The SQLAlchemy documentation website
• Stack Overflow tag
• Alembic Google group
• Gitter chat

Have a nice day!

[zzzeek]

you have to decide up front how these schemas are versioned.

in your application, do you have explicit table models written out that each point to just one of the schemas? this would use a single alembic_version table and include schemas.

or, is each schema a copy of one "master" schema, and your application has just one model that is copied to all of them? this would be multitenancy, and youd' need to alter your env.py to loop through all the schemas individually. there would be an alembic_version table in each. this is a little tricky but I can show you how.

or, you have multiple applciations, each application has table models for just one, or some, of those schemas? in this case you would also have an alembic_version table in each schema and each application would only look at once schema at a time, if some of those apps use more than one schema you'd need an include_object hook to use the right schemas.

[mohamednajiullah]

I can give you an example.

I have a user service that has user details like first name, last name etc. I intend to store the data for the user service in a user_schema with a table named user_details.

I also have an authentication service that has login details like login_id etc. I intend to store the data for the authentication service in a auth_schema with a table named login_details.

Each table has its own structure. Therefore I have tried to keep the alembic_version table for each service in it's own schema.

While running migration, alembic looks at that schema alone which is good. However it also tries to recreate the table for every migration even if the table already exists. How do I stop alembic from recreating the existing and migrate only the changes like adding a new column etc..

[zzzeek]

While running migration, alembic looks at that schema alone which is good. However it also tries to recreate the table for every migration even if the table already exists. How do I stop alembic from recreating the existing and migrate only the changes like adding a new column etc..

alembic doesn't do that if it's set up correctly, so we are still not there yet.

so you have:

1. one application

2. many schemas with different tables

3. you'd like alembic_version in each schema

OK so how would you like to run alembic for each of these separate schemas? are you running alembic with a single alembic.ini but using the "--name" option ? How is your env.py set up? how do you connect to the database, and then do you have an explicit "schema" name in each of your mapped class / Table objects ?

Basically to do what you're doing, a few things need to be done correctly up front and I would need to see how you're doing it. if you want to run alembic in one remote schema at a time, and that is not the default schema of the database connection,you would definitely need an include_object hook, so that each time alembic runs, it knows which schema to look at.

this would probably be easier to configure if you ran alembic exactly once for the whole application and for all schemas, you would set include_schemas=True and assuming you have table metadata for every table it would do the right thing.

[mohamednajiullah]

Hello @zzzeek ,

I cannot run alembic once for my entire application as the application consists of many independently deployable services. There is a database migration step in the deployment setup which makes the necessary db changes as part of a new deployment.

Each service has it's own migrations folder with separate alembic.inis. As for the env.py, I've not changed the default file much except add the version_table_schema in the following block

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=process_revision_directives,
            version_table_schema='user_schema',
            **current_app.extensions['migrate'].configure_args
        )

        with context.begin_transaction():
            context.run_migrations()

I went through the include_object documentation before. I'm struggling to understand how to set it up so that it does not recreate the tables for every migration script.

Thank you for your help!

[zzzeek]

OK so you have entirely separate alembic environments each with its own env.py correct?

OK last question do your metadata models have a "schema" in them? that is, they look like:

class MyModel(Base):
   # ...

   __table_args__ = {"schema": "user_schema"}

the include_object we are looking for here looks like this:

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and object.schema != "user_schema":
        return False
   else:
        return True

context.configure(
    # ...
    include_schemas=True,
    include_object = include_object
)

within the autogenerate process (I'm assuming that's what we're talking about when you say "recreate the tables for every migration script."), you have complete control over what objects are considered by using that hook.

[mohamednajiullah]

OK last question do your metadata models have a "schema" in them?

Yes @zzzeek . Let me try this out. Thank you so much!!

[mohamednajiullah]

It works.

Thank you so much @zzzeek!!

[alikazai]

thanks @zzzeek that fixed my issue