How to set schema_translate_map in SQLAlchemy object in Flask app

Question

My app.py file

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres:////tmp/test.db'
db = SQLAlchemy(app) # refer https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#flask_sqlalchemy.SQLAlchemy

One of my model classes, where I imported db

from app import db
Base = declarative_base()

# User class
class User(db.Model, Base):
  id = db.Column(db.Integer, primary_key=True)
  username = db.Column(db.String(80), unique=True, nullable=False)
  email = db.Column(db.String(120), unique=True, nullable=False)

  def __repr__(self):
    return '<User %r>' % self.username

  def get_user_by_id(self, id):
    return self.query.get(id)

My database has the same set of tables in different schema (multi-tenancy) and there I need to select the schema as per the request initiated by a particular tenant on the fly by using before_request (grabbing tenant_id from subdomain URL).

I found Postgres provides selecting the schema name on fly by using schema_translate_map ref. https://docs.sqlalchemy.org/en/14/core/connections.html#translation-of-schema-names and that is under execution_options https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.execution_options

In my above code snippet where you see db = SQLAlchemy(app), as per official documentation, two parameters can be set in SQLAlchemy objct creation and they are - session_options and engine_options, but no execution_options ref. https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#flask_sqlalchemy.SQLAlchemy

But how do I set schema_translate_map setting when I am creating an object of SQLAlchemy

I tried this -

db = SQLAlchemy(app, 
  session_options={
    "autocommit": True, 
    "autoflush": False, 
    "schema_translate_map": {
      None: "public"
    }
  }
)

But obviously, it did not work, because schema_translate_map is under execution_options as mentioned here https://docs.sqlalchemy.org/en/14/core/connections.html#translation-of-schema-names

Anyone has an idea, how to set schema_translate_map at the time of creating SQLAlchemy object.

My goal is to set it dynamically for each request. I want to control it from this centralized place, rather than going in each model file and specifying it when I execute queries.

I am aware of doing this differently as suggested here https://stackoverflow.com/a/56490246/1560470 but my need is to set somewhere around db = SQLAlchemy(app) in app.py file only. Then after I import db in all my model classes (as shown above) and in those model classes, all queries execute under the selected schema.

score 5 · Accepted Answer · answered Feb 19 '22 at 23:10

I found a way to accomplish it. This is what needed

db = SQLAlchemy(app, 
  session_options={
    "autocommit": True, 
    "autoflush": False
  },
  engine_options={
    "execution_options":
      {
        "schema_translate_map": {
          None: "public",
          "abc": "xyz"
        }
      }
  }
)

How to set schema_translate_map in SQLAlchemy object in Flask app

1 Answers1