Alembic operations with multiple schemas

Question

NOTE: This is a question I have already found the answer for, but wanted to share it so it could help other people facing the same problem.

I was trying to perform some alembic operations in my multi-schema postgresql database such as .add_column or .alter_table (although it will be the same question for .create_table or .drop_table). For example: op.add_column('table_name', 'new_column_name')

However, I was getting the same error saying basically that the table name could not be found. This, as far as I understand it, is caused because alembic is not recognizing the schema and is searching for that table in the public schema. Then, I tried to specify the schema in the table_name as 'schema_name.table_name' but had no luck.

I came across similar questions Perform alembic upgrade in multiple schemas or Alembic support for multiple Postgres schemas, but didn't find a satisfactory answer.

score 2 · Accepted Answer · answered Jun 06 '20 at 09:44

2

After searching for it into the alembic documentation, I found that there is actually an schema argument for the different operations. For example:

op.add_column('table_name', 'column_name', schema='schema_name')

answered Jun 06 '20 at 09:44

Roberto Corral

41
4

score 2 · Answer 2 · answered Jan 21 '22 at 17:49

Alembic will automatically pick up the schema from a table if it is already defined in a declarative SQLAlchemy model.

For example, with the following setup:

# models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SomeClass(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))
    __table_args__ = {"schema": "my_schema"}

# alembic/env.py

from models import Base

target_metadata = Base.metadata

[...]

Running:

alembic revision --autogenerate -m "test"

Would result in a default migration script with a schema specified:

def upgrade_my_db():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('some_table',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='my_schema'
    )
    # ### end Alembic commands ###

Alembic operations with multiple schemas

2 Answers2