On Thu, Mar 28, 2019 at 8:27 AM Xavier Bustamante Talavera <busta...@gmail.com> wrote: > > Hello, > > Thanks in advance for the help. > > I am using the ORM with SQLAlchemy in a flask app that is multi-tenant > (multi-client): I have several running Flask apps, one per client. Each flask > app connects to a different Postgres schema. I use one declarative base for > all clients, as they share the same ORM classes, and then every time flask > creates a new session it connects to the client schema. I create the tables > using the ORM in python, in a way based in this issue: > https://github.com/sqlalchemy/sqlalchemy/issues/3914 > > A new requirement is client-defined custom fields. So, our ORM classes will > have their regularly defined fields in sqlalchemy ORM manner in python, plus > some client-defined fields. The client-defined fields are regular types > without difficult stuff: like strings, numbers... As every client is in its > own schema, an example of approach could be creating those fields in the > table in the db directly, and then reflect them on the ORM at the beginning > of each session (as we need to know the schema).
I think this would work really poorly. Reflection is slow, mapping configuration is slow, tearing down of ORM models is slow and not optimized for any cases outside of test suites, all of this would add mulit-second latency to every request, and you would need to prevent all concurrency from each process as you are attempting to use the same ORM model in the process. options here include some kind of module-level trickery where each client gets their own SQLAlchemy model into a private module namespace in the process that is copied from the main one, which would be extremely difficult to get right, or to just give each client their own flask application process,which wont scale, depends on how many tenants you are talking about. > Simpler solutions would be just using hstore or JSON types, but I would be > loosing the goodies of SQLAlchemy / Postgres schemas and consistency. this is totally how I'd want to do it unless your clients are given access to program in SQL and SQLAlchemy. What is a real-world scenario where you have given a tenant three additional columns on one of the database tables and the tenants need to use that data ? what would that look like and what would you be doing that is any different from pulling those values from an hstore ? > > This could be done in different ways in SQLAlchemy, so I am asking you to > orient me to a solution, guide, tutorial, or advice to avoid known pitfalls > when doing this. For example some steps in how to build this reflection, if > you think it is a good solution. > > A future requirement will be creating client-defined tables that inherit from > our regular ORM ones. If for example, we have "Computer" ORM class, they > would want to define types of Computers, like "Desktop". These classes would > only be collections of client-defined fields. How would this impact the above > solution? again this sounds like your application just needs to have a Python package per client where each one has its own version of the ORM model. > > And finally, I will learn database migrations with Alembic, so any piece of > advice or link to manage this with Alembic would be appreciated. all the same, separate alembic migration directories per client. sorry > > This is for open-source software, so if we manage to do it we are happy to > write a blog post anywhere (we have a small blog site for example), if you > find it can be useful for others. > > Thank you for your time (and this amazing software)! :-) > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
