Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Jonathan Vanasco]

On Friday, March 29, 2019 at 4:39:40 AM UTC-4, Xavier Bustamante Talavera 
wrote:
>
>
> @Ibrahima and @Jonathan, as I understand you are talking about something 
> like the Entity–Attribute–Value model 
> , 
> adapted to the multi-tenant case. In my case the data comes form an API in 
> JSON, so although there is not a special strong case towards this pattern, 
> I think I will be using a JSON type.
>

Yes! That's the name, I could not remember it.  I would definitely use JSON 
instead of EAV due to it's ease of use and overall performance.

FWIW, There are two main variations of the EAV pattern I've seen: 
normalizing the values into their own table & using a table that just has 
attribute id + value id (Ibrahima's suggestion, i think), and just doing a 
an attribute id + value table (what I was alluding to).  in my experience, 
the scale of multi tenant applications tends to make the fully normalized 
implementation incredibly slow, so i just don't bother with it anymore.

-- 
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.

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Ibrahima Gaye]

Thanks Xavier,
i did not know the pattern had a name :)  ( the Entity–Attribute–Value model

,)
Ibrahima GAYE




Le ven. 29 mars 2019 à 09:39, Xavier Bustamante Talavera 
a écrit :

> Hello everyone and thanks for your answers,
>
> 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 ?
>
>
> @Mike: To answer you the user-defined data would only be used for CRUD
> operations, no specific coding logic involved, so the only benefit of going
> to using extra fields in a table rather than a hstore / json type would be
> data and access consistency —probably not enough for the problems you
> mention this approach would take.
>
> As I understand then it is better to just define a Postgres JSON type as
> the custom field and provide our own schema validation.
>
> For the client-defined inheritance, as those tables are going to lack
> custom logic more than the field definitions, we will value other
> approaches not touching table definitions.
>
> On 29 Mar 2019, at 00:32, Ibrahima Gaye  wrote:
>
> Hi Jonathan,
> i would do it like this:
> - add in your global model  tables named attributs, attributs_value and
> values,
> - any table (let's call it XTable) that will eventually has need extra
> column per client will be linked to attributs_value via a table
> XTable_Attributs (For maximum flexibility).
> Hope that helps,
> Best regards
>
>
> @Ibrahima and @Jonathan, as I understand you are talking about something
> like the Entity–Attribute–Value model
> ,
> adapted to the multi-tenant case. In my case the data comes form an API in
> JSON, so although there is not a special strong case towards this pattern,
> I think I will be using a JSON type.
>
> Thank you again for your comments; it has been very enlightening!
>
>
>
> Ibrahima GAYE
>
>
>
> Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco  a
> écrit :
>
>>
>>
>> On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
>>>
>>>
>>> > 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.
>>>
>>
>> wile I would handle this as JSON data too, there is also a database
>> pattern for doing this in multi tenant applications where you use a table
>> to allocate and store the allowable keys for each tenant , and another
>> table to store the key values for the tenants objects. but i would do this
>> in JSON.
>>
>>
>>
>> --
>> 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.
>
> Best regards,
> Xavier Bustamante Talavera.
> Linkedin  | +34 634 541 887
>
> --
> 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 

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Xavier Bustamante Talavera]

Hello everyone and thanks for your answers,

>> 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 ?

@Mike: To answer you the user-defined data would only be used for CRUD 
operations, no specific coding logic involved, so the only benefit of going to 
using extra fields in a table rather than a hstore / json type would be data 
and access consistency —probably not enough for the problems you mention this 
approach would take.

As I understand then it is better to just define a Postgres JSON type as the 
custom field and provide our own schema validation.

For the client-defined inheritance, as those tables are going to lack custom 
logic more than the field definitions, we will value other approaches not 
touching table definitions.

> On 29 Mar 2019, at 00:32, Ibrahima Gaye  wrote:
> 
> Hi Jonathan,
> i would do it like this:
> - add in your global model  tables named attributs, attributs_value and 
> values,
> - any table (let's call it XTable) that will eventually has need extra column 
> per client will be linked to attributs_value via a table XTable_Attributs 
> (For maximum flexibility).
> Hope that helps,
> Best regards

@Ibrahima and @Jonathan, as I understand you are talking about something like 
the Entity–Attribute–Value model 
, 
adapted to the multi-tenant case. In my case the data comes form an API in 
JSON, so although there is not a special strong case towards this pattern, I 
think I will be using a JSON type.

Thank you again for your comments; it has been very enlightening!

> 
> 
> Ibrahima GAYE
> 
>  
> 
> 
> Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco  > a écrit :
> 
> 
> On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
> 
> > 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. 
> 
> wile I would handle this as JSON data too, there is also a database pattern 
> for doing this in multi tenant applications where you use a table to allocate 
> and store the allowable keys for each tenant , and another table to store the 
> key values for the tenants objects. but i would do this in JSON.
> 
> 
> 
> -- 
> 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 
> .
Best regards,
Xavier Bustamante Talavera.
Linkedin  | +34 634 541 887

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Ibrahima Gaye]

Hi Jonathan,
i would do it like this:
- add in your global model  tables named attributs, attributs_value and
values,
- any table (let's call it XTable) that will eventually has need extra
column per client will be linked to attributs_value via a table
XTable_Attributs (For maximum flexibility).
Hope that helps,
Best regards


Ibrahima GAYE



Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco  a
écrit :

>
>
> On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
>>
>>
>> > 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.
>>
>
> wile I would handle this as JSON data too, there is also a database
> pattern for doing this in multi tenant applications where you use a table
> to allocate and store the allowable keys for each tenant , and another
> table to store the key values for the tenants objects. but i would do this
> in JSON.
>
>
> --
> 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.

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Jonathan Vanasco]

On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote:
>
>
> > 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. 
>

wile I would handle this as JSON data too, there is also a database pattern 
for doing this in multi tenant applications where you use a table to 
allocate and store the allowable keys for each tenant , and another table 
to store the key values for the tenants objects. but i would do this in 
JSON.


-- 
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.

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

[Mike Bayer]

On Thu, Mar 28, 2019 at 8:27 AM Xavier Bustamante Talavera
 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