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. > 1. 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. > 1. 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 > 1. 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-alembic+unsubscr...@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 > > <https://groups.google.com/d/msgid/sqlalchemy-alembic/d130316d-5974-4c98-abb1-e5af64a370c9n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/a557e105-6ef7-4d05-a930-f4e758ffde68%40www.fastmail.com.
