SQLALchemy 2.0rc1 multi tenant migration (PostgreSQL schemas)

[mgurg]

Hi,
I'm using SQLALchemy with Alembic in the multitenant FastApi app. I would like to migrate to SQLAlchemy 2.0 but I have strange behaviour in migrations, where the only change is SQLAlchemy version: 1.4 -> 2.0.0rc1

The code is the same in both cases:

• 1.4 : https://github.com/mgurg/fastapi_docker/blob/sqlalchemy-14/requirements.txt
• 2.0.0rc1 : https://github.com/mgurg/fastapi_docker/blob/sqlalchemy-20/requirements.txt

In 2.0.0rc1 everything goes fine, but at the end I have ROLLBACK instead COMMIT, see attached files. Any ideas? I don't see any warnings or errors during code execution. Name of test PG schema: fake_tenant_company_for_test_00000000000000000000000000000000

SQ2.txt
SQ14.txt

[CaselIT]

Hi,

There is some information that's missing here:

• how is alembic called
• what's the alembic env.py?
• what's the version of alembic?

I think this is more an alembic discussion, I can move it once the provided information is given if the issue turns out to be in alembic

[mgurg]

You are 100% right, duplication is caused by those two handlers. With a single one everything looks fine.

SQ2_with_fix_correct_logging.txt

[CaselIT]

thanks for testing!

confirm that works and we will update the recipe on the site

@zzzeek what's the recipe to update?

[zzzeek]

that would be this one: https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases

two approaches are:

1. add a connection.commit() call, and a comment "SQLAlchemy 2.0 only"
2. run the schema name change inside of a "with connection.begin()" block.

at the moment number one seems a little more clear to me what's going on.

[CaselIT]

I honestly like 2 better, since it's clear that we are committing the search path change

[CaselIT]

Created with https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/4355

I went with 1 since it's easier to mention that it's need on v2 only

[CaselIT]

moved to alembic