python - SQLAlchemy 的 Multi-Tenancy

Question

我有一个网络应用程序，它是用 Pyramid/SQLAlchemy/Postgresql 构建的，允许用户管理一些数据，而且这些数据对于不同的用户来说几乎是完全独立的。假设 Alice 访问 alice.domain.com 并能够上传图片和文件，Bob 访问 bob.domain.com 并且也能够上传图片和文件。 Alice 永远看不到 Bob 创建的任何东西，反之亦然(这是一个简化的示例，多个表中可能确实有很多数据，但思路是一样的)。

现在，在数据库后端组织数据最直接的选择是使用单个数据库，其中每个表(图片 和文档)都有 user_id 字段，所以，基本上，要获取 Alice 的所有照片，我可以做类似的事情

user_id = _figure_out_user_id_from_domain_name(request)
pictures = session.query(Picture).filter(Picture.user_id==user_id).all()

这一切都很简单，但是也有一些缺点

• 我需要记住在进行查询时始终使用额外的过滤条件，否则 Alice 可能会看到 Bob 的照片；
• 如果有很多用户，表可能会变得很大
• 在多台机器之间拆分 Web 应用程序可能很棘手

所以我认为以某种方式为每个用户拆分数据会非常好。我可以想到两种方法:

1. 在同一个数据库中为 Alice 和 Bob 的图片和文档创建单独的表(在这种情况下，Postgres 的 Schemas 似乎是正确的使用方法):

   documents_alice
   documents_bob
   pictures_alice
   pictures_bob
   

   然后，使用一些黑魔法，根据当前请求的域将所有查询“路由”到一个或另一个表:

   _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
   

2. 为每个用户使用单独的数据库:

   - database_alice
      - pictures
      - documents
   - database_bob
      - pictures
      - documents 
   

   这似乎是最干净的解决方案，但我不确定多个数据库连接是否需要更多的 RAM 和其他资源，从而限制可能的“租户”数量。

那么，问题是，这一切都有意义吗？如果是，我如何配置 SQLAlchemy 以在每个 HTTP 请求上动态修改表名(对于选项 1)或维护与不同数据库的连接池并为每个请求使用正确的连接(对于选项 2)？

Answer 1

在思考 jd 的回答后，我能够为 postgresql 9.2、sqlalchemy 0.8 和 flask 0.9 框架实现相同的结果:

from sqlalchemy import event
from sqlalchemy.pool import Pool
@event.listens_for(Pool, 'checkout')
def on_pool_checkout(dbapi_conn, connection_rec, connection_proxy):
    tenant_id = session.get('tenant_id')
    cursor = dbapi_conn.cursor()
    if tenant_id is None:
        cursor.execute("SET search_path TO public, shared;")
    else:
        cursor.execute("SET search_path TO t" + str(tenant_id) + ", shared;")
    dbapi_conn.commit()
    cursor.close()