Expectation
Support Postgres Multitenancy using Schemas with Sqlalchemy and alembic.
Model
class User(Base):
__tablename__ = 'users'
__table_args__ = ({"schema": "test"})
id = Column(Integer, primary_key=True)
name = Column(String(80), unique=True, nullable=False)
def __repr__(self):
return '<User %r>' % self.name
We have two tenants tenant_1, tenant_2. Each tenant will have its own schema created in the same Postgres DB and one more schema to maintain shared tables between all tenants. Each tenant schema will have exact same tables.
So, the use case is any model that we have in models.py should be created in all tenant schemas. So the users table has to be created in both schemas tenant_1, tenant_2.
I am trying to use alembic to auto-generate migrations for all schemas for the same model.
alembic env.py
target_metadata = app.Base.metadata
...
def run_migrations_online() -> None:
""" Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
all_my_tenant_names = ["tenant_1", "tenant_2"]
for tenant_schema_name in all_my_tenant_names:
conn = connection.execution_options(schema_translate_map={"test": tenant_schema_name})
print("Migrating tenant schema %s" % tenant_schema_name)
context.configure(
connection=conn, target_metadata=target_metadata, include_schemas=True,
version_table_schema=target_metadata.schema,
)
with context.begin_transaction():
context.execute('SET search_path TO public')
context.run_migrations()
The expectation is migration file will have statements to create tenant_1.users, tenant_2.users tables. But it only has a statement to create test.users table.
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=80), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'),
schema='test'
)
op.drop_table('user', schema='tenant_2')
op.drop_table('alembic_version', schema='tenant_2')
op.drop_table('user', schema='tenant_1')
op.drop_table('alembic_version', schema='tenant_1')
# ### end Alembic commands ###
Can someone please help to accomplish this?
