Using Alembic with multiple tenants, each being different version, but same DB

[EgorLu]

Hi team, first of all, thanks for the great product.

I know that Alembic isn't designed for my use case, and generally the use case seems to be rare, but I wonder if there's a logical approach to such an issue.

Use case:
I have multiple tenants interacting with the same database. Each tenant automatically attempts to apply the migrations.
I've resolved the race condition issue by implementing a semaphore in the database.

The problem arises when each tenant expects a different schema from the same DB. This mostly can happen when there's a long running instance of an outdated version of the client code, and a new instance is running with a newer code that has a new revision.

Approach:
I was thinking about creating a log/history table that will have the DAG relationships between each revision mapped chronologically. Thus an older version may be aware of the fact that it's outdated and only try to update to the latest available revision.
This is a naive approach, considering that the new changes are backwards compatible.

Other approaches that I though about were over complicated and it seems like changing the architecture/environment would be a better solution.

[zzzeek]

if these tenants are sharing the same tables, then in order to support tenants on older versions of the code that refers to older versions of the schema, you have to implement a gradual versioning approach to your whole application where you never drop any columns or tables until all tenants have been migrated. application code that is newer also needs to be able to apply data to the "old" columns at runtime if there is data between these tenants that needs to be shared.

overall this is certainly a very major undertaking and it will severely limit how easily you can make schema changes to your database and to your application, as both will always need to be operating against a past version of the schema and a future one at the same time. Nevertheless this approach is done in the real world including for some Openstack projects like Openstack Nova, to suit the approach of being able to upgrade the software on multiple nodes while the whole thing keeps running. this is not quite the same thing as "tenants" though.

if OTOH your multitenant approach involves each tenant using a different sub-schema, like a PostgreSQL schema, then you'd version the schemas separately.

The usual approach for a true multi-"tenant" system with remote users accessing some common source of data is to not grant direct database access, and to instead provide RESTful API access with versioning. that way you just keep providing api/v1, api/v2, etc. and you can easily migrate your schema without any risk introduced by the multi-tenant aspect of it.

[EgorLu]

Thanks for the quick reply!

I was actually thinking about the REST API approach, to build a service that will aggregate the calls and will be version aware.
I'll have a look at how the guys at Nova did it, might give some insights.

On a similar topic, is there a plan to implement a lock into Alembic to avoid race conditions?

[zzzeek]

we wouldn't put that in Alembic as it is highly system-dependent but we would have some approaches in our "cookbook" section if there were something that's straightforward to illustrate. usually people want a lock because the application itself has multiple processes that each want to auto-update the database on startup.

[EgorLu]

I could prepare a PR with my simple solution that works on MySQL, PostgreSQL and probably all other relative databases.

[CaselIT]

if you could, that would be really appreciated!