Managing multiple tenant "namespaces"

[Nikola Radovanovic]

Hi,

I have a bit unusual use-case in software I work on. 

There are three main "namespaces" (kinds of data): first one is "general" (not multi-tenant), where we keep some common security and settings data.

Second one is "client", where we keep clients (which are companies/organisations) and this one is multi-tenant, so multiple clients share same table layout and there are also relationships between "general" and "client" tables. 

Third one can be seen as a "cluster" where we keep data "clients" wants to share among each other. "Cluster" is multi-tenant, so all "clusters" share same table layout, and which differs somewhat with the one in "client". There might be relationships between "cluster" and "general", but most probably not with the "client".

Now, my questions are related to what would be the best way to create a migration management based on Alembic in this case?

1. I guess some custom migration script  based on Alembic API
2. where to keep migration related data table - one in "general" schema for "general" tables, then each "client" and "cluster" schema has its own
3. how to pass "clients" and "cluster" for which we want to perform migration? Shall we read them from DB itself, or pass as command line params (by default do for all)
4. I have not use Alembic API so far, so what would be the best place to start - apart official docs is there any recommended tutorial or something similar?

Also, as a side question, I guess separate declarative_base for "general", "client" and "cluster" would be a good thing, or shall we use one - common for all, but in which case we have to decide on thy fly which tables belongs to "clients" and which to "clusters" so we don't mix them.

Thank you in advance.

Kindest regards

[Mike Bayer]

On Wed, Apr 7, 2021, at 5:18 AM, Nikola Radovanovic wrote:

Hi,

I have a bit unusual use-case in software I work on. 

There are three main "namespaces" (kinds of data): first one is "general" (not multi-tenant), where we keep some common security and settings data.

Second one is "client", where we keep clients (which are companies/organisations) and this one is multi-tenant, so multiple clients share same table layout and there are also relationships between "general" and "client" tables. 

Third one can be seen as a "cluster" where we keep data "clients" wants to share among each other. "Cluster" is multi-tenant, so all "clusters" share same table layout, and which differs somewhat with the one in "client". There might be relationships between "cluster" and "general", but most probably not with the "client".

I'm going to assume "client" and "cluster" are just two multitenant systems that are independent of each other.   

also by "multiple clients share the same table layout" I assume you mean, each client has their own database that's theirs alone, where the tables inside of it look like the tables inside of another client's database.   

Now, my questions are related to what would be the best way to create a migration management based on Alembic in this case?

1. I guess some custom migration script  based on Alembic API
   

well you would have custom things to do in the env.py script to set up for the tenants.   i dont know how much more customization you would need other than connecting to the right databases.

2. where to keep migration related data table - one in "general" schema for "general" tables, then each "client" and "cluster" schema has its own
   

since you have to run migrations for the multitenant schemas many times,   each "namespace" has to have it's own alembic_version table.    so with separate versioning tables the first level of switching is using the --name parameter documented at https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-multiple-alembic-environments-from-one-ini-file  .   this means the three namespaces have their own set of revisions.

3. how to pass "clients" and "cluster" for which we want to perform migration? Shall we read them from DB itself, or pass as command line params (by default do for all)
   

the above recipe should handle this part

4. I have not use Alembic API so far, so what would be the best place to start - apart official docs is there any recommended tutorial or something similar?
   

so.....to do "multitenant" you also have to run the migrations for every database.     There's a recipe for doing this in terms of Postgresql here: https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases  but the general idea applies to multitenant setups in general, within env.py you need to apply the migrations to the "tenant" or "tenants" that you want.  

Also, as a side question, I guess separate declarative_base for "general", "client" and "cluster" would be a good thing, or shall we use one - common for all, but in which case we have to decide on thy fly which tables belongs to "clients" and which to "clusters" so we don't mix them.

the important part would be that they use separate MetaData collections.  you can share multiple MetaData among one base if you prefer using a pattern such as https://docs.sqlalchemy.org/en/14/orm/declarative_config.html#metadata  or the one that follows it in https://docs.sqlalchemy.org/en/14/orm/declarative_config.html#abstract .

Thank you in advance.

Kindest regards

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/d130316d-5974-4c98-abb1-e5af64a370c9n%40googlegroups.com.

[Nikola Radovanovic]

Hi,

we have one Postgres DB with multiple schemes:

1. general (one schema, contains users and some common and data shared between clients/clusters)
2. client schemes: c_client_1, c_client_2, c_client_3, etc. (all clients have same table layout, and have some access to general schema)
3. cluster schemes: cl_cluster_1, cl_cluster_2, cl_cluster_3, etc. (all clusters have same table layout, and have some access to general schema)

Basically, I want to find the best possible way to handle migrations - preferably using one env.py/alembic.ini. Each client/cluster should have its migration table in its own schema and general has its own migration table in general schema. Client and cluster will share base class with.

What I noticed is when using schema_translation_map, table layout is detected OK, but when I run migration next time, it does not detect changes in tables, but entire tables again. I guess it is due schema_translation_map is not used then.

Thank you in advance.

Regards

[Mike Bayer]

On Fri, Apr 16, 2021, at 7:15 AM, Nikola Radovanovic wrote:

Hi,

we have one Postgres DB with multiple schemes:

1. general (one schema, contains users and some common and data shared between clients/clusters)
   
2. client schemes: c_client_1, c_client_2, c_client_3, etc. (all clients have same table layout, and have some access to general schema)
   
3. cluster schemes: cl_cluster_1, cl_cluster_2, cl_cluster_3, etc. (all clusters have same table layout, and have some access to general schema)
   

Basically, I want to find the best possible way to handle migrations - preferably using one env.py/alembic.ini.

yes, use the recipe for passing --name, but you can have everything in one alembic.ini file and one env.py file as long as that file knows how to respond to the different environments

Each client/cluster should have its migration table in its own schema and general has its own migration table in general schema. Client and cluster will share base class with.

What I noticed is when using schema_translation_map, table layout is detected OK, but when I run migration next time, it does not detect changes in tables, but entire tables again. I guess it is due schema_translation_map is not used then.

schema_translate_map is not supported by migrations right now: https://github.com/sqlalchemy/alembic/issues/555

use the search_path recipe at https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases , this has been tested.

Thank you in advance.

Regards

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/ed08c030-f35a-49c7-9ee3-5b263a7177a9n%40googlegroups.com.

[Nikola Radovanovic]

Many thanks!

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/KRHod6Jbero/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/bf8aa229-cb51-431d-a599-969a5592a262%40www.fastmail.com.