How to handle Schema multi-tennancy with Python + Flask + sqlAlchemy + PostgreSQL

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.

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

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.

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.

We will proceed as following :

• A) Describe a hypothetical business use case where multi-tenancy architecture will be needed
• B) Architecture, strategy and steps
• C) Create and populate the database
• D) Connect the database to python
• E) Implement and expose a service per tenant
• F) Test via Postman
• G) Github code

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.

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

The scenario is :

We have a user table, the user logs in, we know for each user to which tenant it belongs, so we change the schema dynamically to the corresponding tenant

Strategy :

We can create the schemas and populate the database either directly from python with sqlAlchemy, or by sql script independently from python, and then plug python through the ORM sqlAlchemy to the database

To make a separation of concerns, we will not use python to create nor populate the database with, python will be used just to retrieve data from the database and expose them through a server as a service.

In the following, we will need 2 piece of software installed:

• The database PostgreSQL that hold our data and has the schema feature (not all databases have the notion of schema) that allow us to implement multi-tenancy
• Dbeaver is a powerful open source GUI to connect with a database through a user interface, that will allow us to make and see each change in our database

We will not show how to install Dbeaver or PostgreSQL because it is outside of the scope for this article, but for linux/ubuntu users, here is articles that shows how to install :

• Install PostgreSQL
• Install Dbeaver

At each step/change in the database we will take a screenshot and show how the result should look like

Our sql scripts can be directly executed through Dbeaver or through command line after having ssh with the database, or through a command line using a file, in this post the scripts will be executed through the GUI dbeaver but in the github repo, the sql script files will be furnished, with a command example that exec those scripts in the README.md

Steps :

On this article will try to follow this pattern :

• Explain what we are trying to do at each step
• Explain how we are doing it (we might either give main lines, or show completely how)
• Screenshot the result when it’s possible

C) Create and populate the database

As has been said earlier, we need the information of a user ordering an item, this translate to 3 tables; users table, items table then orders table which hold the relation between the user and the item he ordered

Also as has been said earlier, the user table will be in a shared schema, the shared schema is the schema public, the other tables will be duplicated in each schema per tenant.

For the purpose of this example we will have 2 tenants, client1, client2, so let’s start :

Connection with dbeaver:

Once PostgreSQL installed we will connect to it by dbeaver, so let’s open dbeaver :

You choose to make a connection, you select postgres, by default PostgreSQL is listening on the port 5432, hosted on localhost, the user is postgres the default password is either empty or postgres

Create the schemas for the database :

So for each table we need some basic infos

For table users we need :

(creation_timestamp, id, name, mail, organizaion_id)

For table items we need :

(creation_timestamp, id, description, price)

For table orders we need :

(creation_timestamp, id, user_id, item_id, quantity)

So open the sqlEditor in dbeaver and just copy and past the init.sql that you can find here then run the script, it should look like something like that :

As you can see we have a response status 200, we have created the schemas client1, client2 put in them the tables items & orders, and in the public schema we have the table users let’s populate our database

Populate the database :

To illustrate our business case, we will need 2 users, 2 items, 2 orders for each user, the same items will have a different price depending on the schema where they belong

Again you can populate the database, by running in the sqlEditor of debeaver the script populate.sql you can find here

D) Connect the database to python

So now we are starting to get serious, in order to connect python to the database we use the ORM (object relational mapper) sqlAlchemy that construct a layer of abstraction which ease the communication between python and the database

sqlAlchemy is a powerful tool for database manipulation, although the documentation has great margin of progression in terms of readibility and clarity

Config connection and init a session :

sqlAlchemy need some infos in order to establish connection with the database (ip host, password, databasename, user) once connected we create a sessionmaker, init a session, do our operations (queries) throught this object session, then commit the session (persist the operation in the database) as an example for the session initialisation see below :

import os
from sqlalchemy import create_engine, 
from sqlalchemy.orm import sessionmaker
NAME_DB = os.getenv('NAME_DB', 'postgres')
PORT_DB = os.getenv('PORT_DB', '5432')
PWD_DB = os.getenv('PWD_DB', 'postgres')
HOST_DB = os.getenv('HOST_DB', 'localhost')
USER_DB = os.getenv('USER_DB', 'postgres')
conString = "postgres://YourUserName:YourPassword@YourHost:5432/YourDatabase"
URI = f'postgresql+psycopg2://{USER_DB}:{PWD_DB}@{HOST_DB}:{PORT_DB}/{NAME_DB}'
engine = create_engine(URI)
session_maker = sessionmaker(bind=engine)
# Here we are initializing a new session
session = session_maker()

To see more about how to connect sqlAlchemy with database, check this section in the doc

Model mapping :

Once we have seen how to initialize our session we need to see how to do the python-database mapping, we can either, create separately the models, the tables and use a mapper or use the declarative base method where both of tables and models are defined and mapped together in one class, it’s the method we are going to use, as an example for table users, it will look like something like below :

from sqlalchemy import Column, DateTime, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class User(Base):

__tablename__ = 'users'
__table_args__ = {'schema': 'public'}

id = Column(String, primary_key=True)
creation_timestamp = Column(DateTime)
name = Column(String, nullable=False)
password = Column(String, nullable=False)
mail = Column(String, nullable=False)
organization_id = Column(String, nullable=False)

Notice here that we have added the facultatif attribute __table_args__ to indicate the schema that this model will be using, so now comes the question, ok for table user we know the schema is public but how can we for the other tables change dynamically their schema according to the tenant ?

Changing dynamically the schema

As seen upper in “Config connection and init a session” section when we work with sqlAlchemy ORM we init/open a session, we do our operations, then we commit the session to persiste data, this object session, in order to know how to communicate with the database, need to have been linked through a connectable object, this object could be a connection object or an Engine object (object that generate connectables).

So to change dynamically the schema for other tables, we should when ever we open a session indicate that we wants to map all the tables that are not public to a particular schema, sqlAlchemy fortunately gives us the possibility throught the method execution_options to configure a selection of schemas we want to be considered, check the doc here at section Translation of Schema Names

The code will be looking something like that :

engine = create_engine(URI, echo=True)
Base = declarative_base(metadata=metadata)
gen_session = sessionmaker()
SELECTED_SCHEMA = "client1"
# Here we map all the schema that are undifined (in our case that
# are different from the schema 'public' of table user) to the 
# SELECTED_SCHEMA 
connection = engine.connect().execution_options(
schema_translate_map={None: SELECTED_SCHEMA}
)
session = gen_session(bind=connection)

E) Implement and expose a service per tenant

Method to expose

In the class User, we are going to expose a method that return the user orders displays the corresponding items, their unit price, and what user has to pay, the code will be looking something like that :

from lib.models.base import BaseModel, db
from lib.models.item import Item
from lib.models.order import Order
class User(BaseModel):
__tablename__ = "users"
__table_args__ = {'schema': 'public'}
id = db.Column(db.Unicode(), primary_key=True)
name = db.Column(db.Unicode(), nullable=False)
mail = db.Column(db.Unicode(), nullable=False)
password = db.Column(db.Unicode(), nullable=False)
organization_id = db.Column(db.Unicode(), nullable=False)
@classmethod
def _get_user_priced_orders(cls):
  return cls.session.query(
User.name,
Item.description,
Item.price,
Order.quantity,
(Order.quantity * Item.price).label('Total')
).join(Order).join(Item)
def get_my_priced_orders(self):
  query = self._get_user_priced_orders()
  return query.filter(User.id == self.id).all()

Changing schema dynamically at each server request :

We have nearly finished, we have seen that we can change the schema targeted in the opening of a session so with a server app.

If we are able to open a session and target a schema before each request we won, and that exactly what flask allow us to do with the method before_request, it allow us to execute a function before request, and this function will be open a session and target a schema

Exposing the method throught a server :

Once the method is implemented we setup a quick flask server app with an entry point that will be waiting for a “user_id” and an “organization_id”

F) Test via postman

Postman usage :

So now we have setup the server, we will be using Postman, a tool that is used to test apis by sending request throught an ui and see the response, we will use it to test our localhost server

Expected result :

Remember the business case, depending on the user, the same item should display a different price, let’s see the results for client1, then client2

client1 :

client2 :

NB:

Here the client1 buys a Tesla for 5000 unit and Huawei for 2000 unit where client2 buys a Tesla for 1000 unit and a Huawei for 100 unit

G) Github code

And finally you can find the total github code for this tuto here