SQLAlchemy的多租户 [英] Multi-tenancy with SQLAlchemy
问题描述
我有一个使用Pyramid/SQLAlchemy/Postgresql构建的Web应用程序，它允许用户管理一些数据，并且该数据对于不同的用户几乎是完全独立的.假设爱丽丝访问alice.domain.com并能够上传图片和文档，鲍勃访问bob.domain.com并能够上传图片和文档.爱丽丝从来没有见过鲍勃创建的任何东西，反之亦然(这是一个简化的示例，实际上在多个表中可能有很多数据，但是想法是相同的).
I've got a web-application which is built with Pyramid/SQLAlchemy/Postgresql and allows users to manage some data, and that data is almost completely independent for different users. Say, Alice visits alice.domain.com and is able to upload pictures and documents, and Bob visits bob.domain.com and is also able to upload pictures and documents. Alice never sees anything created by Bob and vice versa (this is a simplified example, there may be a lot of data in multiple tables really, but the idea is the same).
现在，在数据库后端组织数据的最直接的方法是使用单个数据库，其中每个表(pictures和documents)都有user_id字段，因此，基本上，获取所有Alice的图片，我可以做类似的事情
Now, the most straightforward option to organize the data in the DB backend is to use a single database, where each table (pictures and documents) has user_id field, so, basically, to get all Alice's pictures, I can do something like
user_id = _figure_out_user_id_from_domain_name(request)
pictures = session.query(Picture).filter(Picture.user_id==user_id).all()
这一切都很简单，但是有一些缺点
This is all easy and simple, however there are some disadvantages
- 我需要记住在进行查询时始终使用其他过滤条件，否则爱丽丝可能会看到鲍勃的照片；
- 如果用户很多，表可能会很大
- 在多台计算机之间拆分Web应用程序可能很棘手
因此，我认为以某种方式拆分每个用户的数据真的很好.我可以想到两种方法:
So I'm thinking it would be really nice to somehow split the data per-user. I can think of two approaches:
Have separate tables for Alice's and Bob's pictures and documents within the same database (Postgres' Schemas seems to be a correct approach to use in this case):
documents_alice
documents_bob
pictures_alice
pictures_bob
然后，使用一些黑魔法，根据当前请求的域将所有查询路由"到一个或另一个表:
and then, using some dark magic, "route" all queries to one or to the other table according to the current request's domain:
_use_dark_magic_to_configure_sqlalchemy('alice.domain.com')
pictures = session.query(Picture).all() # selects all Alice's pictures from "pictures_alice" table
...
_use_dark_magic_to_configure_sqlalchemy('bob.domain.com')
pictures = session.query(Picture).all() # selects all Bob's pictures from "pictures_bob" table
为每个用户使用单独的数据库:
Use a separate database for each user:
- database_alice
- pictures
- documents
- database_bob
- pictures
- documents
这似乎是最干净的解决方案，但我不确定多个数据库连接是否需要更多的RAM和其他资源，从而限制了可能的租户"数量.
which seems like the cleanest solution, but I'm not sure if multiple database connections would require much more RAM and other resources, limiting the number of possible "tenants".
所以，问题是，这一切有意义吗?如果是，我如何配置SQLAlchemy以在每个HTTP请求上动态修改表名(对于选项1)，或者维护到不同数据库的连接池，并为每个请求使用正确的连接(对于选项2)? >
So, the question is, does it all make sense? If yes, how do I configure SQLAlchemy to either modify the table names dynamically on each HTTP request (for option 1) or to maintain a pool of connections to different databases and use the correct connection for each request (for option 2)?
推荐答案
好，我最终在每个请求的开头都使用了金字塔的NewRequest事件来修改search_path:
Ok, I've ended up with modifying search_path in the beginning of every request, using Pyramid's NewRequest event:
from pyramid import events
def on_new_request(event):
schema_name = _figire_out_schema_name_from_request(event.request)
DBSession.execute("SET search_path TO %s" % schema_name)
def app(global_config, **settings):
""" This function returns a WSGI application.
It is usually called by the PasteDeploy framework during
``paster serve``.
"""
....
config.add_subscriber(on_new_request, events.NewRequest)
return config.make_wsgi_app()
效果很好，只要您将事务管理留给Pyramid(即不要手动提交/回滚事务，让Pyramid在请求结束时执行)-可以，因为手动提交事务不是无论如何都是好方法.
Works really well, as long as you leave transaction management to Pyramid (i.e. do not commit/roll-back transactions manually, letting Pyramid to do that at the end of request) - which is ok as committing transactions manually is not a good approach anyway.
这篇关于SQLAlchemy的多租户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
