如何使用python flask sqlalchemy postgres处理架构多租户

什么是多租户？ (What is multi-tenancy ?)

Briefly, multi-tenancy is an architecture that allows one instance of a software to serve multiple clients (called tenants), one of the advantages is cost saving. suppose you have licence for a software you use (MS sql server for example), would be nice if you can handle all your client in one instance, and paying for just one licence.

简而言之，多租户是一种允许软件的一个实例为多个客户端(称为租户)服务的体系结构，优点之一是节省了成本。 假设您拥有使用的软件的许可证(例如，MS sql server)，如果您可以在一个实例中处理所有客户端并仅支付一张许可证的费用，那就太好了。

您可能需要多租户？ (Where you might need multi-tenancy ?)

The basic use case, is when you are building a Saas app, you want to serve multiple clients (tenants), and keep at the same time their data isolated, there are many ways multi-tenant architectures are achieved, describing those ways is outside of the scope for this article, here we will have interest for the schema based multi-tenant architecture in PostgreSQL, you can find more details about types of architectures for multi-tenancy in this article here

一个基本的用例是，当您构建Saas应用程序时，您想要服务多个客户端(租户)，并同时保持其数据隔离，实现多租户架构的方法有很多，这些方法描述于外部范围为这篇文章的，在这里我们将在PostgreSQL架构为基础的多租户架构的兴趣，你可以在这篇文章中找到有关类型的多租户架构的更多细节在这里

我为什么要写这篇文章？ (Why am i writing this post ?)

I had to implement a micro-service for an existing multi-tenant schema database (PostgreSQL), i haven’t found enough ressources on the subject, i solved the problem for myself and decided to share how i did it in a tuto format.

我必须为现有的多租户模式数据库(PostgreSQL)实施微服务，我没有找到足够的资源来解决这个问题，我自己解决了这个问题，并决定分享我如何以tuto格式进行操作。

您将在这篇文章中学到什么？ (What you will learn in this post ?)

This article will be written in a ~ tuto format, it is hoped that by the end of this post, the reader (with minimum of pre-requisites of python/flask) will be able to implement a micro-service that handle multi-tenancy schema based in PostgreSQL, a github repo will be furnished by the end.

本文将以〜tuto格式编写，希望到本文结束时，读者(最少具有python / flask的前提条件)将能够实现处理多租户的微服务。基于PostgreSQL架构，最后将提供一个github仓库。

We will proceed as following :

我们将按照以下步骤进行：

• A) Describe a hypothetical business use case where multi-tenancy architecture will be needed

  A)描述一个需要多租户架构的假设业务用例

• B) Architecture, strategy and steps

  B)架构，策略和步骤

• C) Create and populate the database

  C)创建并填充数据库

• D) Connect the database to python

  D)将数据库连接到python

• E) Implement and expose a service per tenant

  E)为每个租户实施和公开服务

• F) Test via Postman

  F)通过邮递员测试

• G) Github code

  G) Github代码

A)假设的业务用例： (A) Hypothetical business use case :)

Let’s suppose we have a Saas app, and in one part of this Saas app we have a kind of an online store, in this online store, the user is allowed to order items, in our company even if the items are mainly the same, the politic of pricing of our items is tenant dependent, we are not really comfortable with having tables that persist clearly this price/item/tenant dependency, so we decided to separate our data per tenant so in this case, we will have a schema per user per tenant, and reference this dependency implicitly in schema’s data.

假设我们有一个Saas应用程序，并且在此Saas应用程序的一部分中，我们有一种在线商店，在这个在线商店中，即使项目基本相同，也允许用户在我们公司订购商品，由于我们的商品定价策略取决于租户，因此我们对于让表格清楚地保留此价格/项目/租户的依赖关系并不满意，因此我们决定将每个租户的数据分开，因此在这种情况下，我们将根据每个租户的用户，并在架构数据中隐式引用此依赖关系。

The database will be already populated, so we will just expose a service where a user can see his orders, including the item price unit, the total amount to pay

数据库将已经被填充，因此我们将公开一个服务，用户可以在其中查看其订单，包括商品价格单位，总付款额

B)架构，策略和步骤 (B) Architecture, strategy and steps)

建筑理念： (Architecture idea :)

The idea of the architecture is to have one table (user table) in a schema shared by all tenants, and all the other tables are privately organized per schema per tenant.

该体系结构的思想是在所有租户共享的模式中具有一个表(用户表)，而所有其他表都是按每个租户的模式进行私有组织的。

Each record of the shared table/schema (user table here) have a field (organization_id for example) that reference the schema that holds its data, this record serve as an entry point, to choose with which schema to work with.

共享表/模式(此处为用户表)的每个记录都有一个字段(例如，organization_id)，该字段引用保存其数据的模式，该记录用作入口点，用于选择要使用的模式。

The scenario is :