Fastapi: [QUESTION] Use multiple DB schemas in SQLAlchemy

[lfpcorreia]

First check

• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the FastAPI documentation, with the integrated search.
• [X] I already searched in Google "How to X in FastAPI" and didn't find any information.

Description

I need to use several schemas for a project. I'm using MySQL but any RDBMS should be compatible with this as long as SQLAlchemy supports it.

Currently I'm seeing two ways to do it:

From Flask-SQLAlchemy:

SQLALCHEMY_DATABASE_URI = 'postgres://localhost/main'
SQLALCHEMY_BINDS = {
    'users':        'mysqldb://localhost/users',
    'appmeta':      'sqlite:////path/to/appmeta.db'
}

from sqlalchemy import String, Integer, Column
from sqlalchemy.ext.declarative import declarative_base



class BindMetaMixin(object):
    def __init__(cls, name, bases, d):
        bind_key = (
            d.pop('__bind_key__', None)
            or getattr(cls, '__bind_key__', None)
        )

        super(BindMetaMixin, cls).__init__(name, bases, d)

        if bind_key is not None and getattr(cls, '__table__', None) is not None:
            cls.__table__.info['bind_key'] = bind_key


class DefaultMeta(BindMetaMixin):
    pass


Base = declarative_base(metaclass=DefaultMeta)


class User(Base):
    __bind_key__ = 'users'
    __tablename__ = "users"
    id = Column('id', Integer, primary_key=True)
    username = Column('username', String(255), unique=True)

from SQLAlchemy docs, Multi-Tenancy way

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

    __table_args__ = {'schema': 'per_user'}

```python
session = Session()
session.connection(execution_options={
"schema_translate_map": {"per_user": "account_one"}})

will query from the account_one.user table

session.query(User).get(5)

#### from SQLAlchemy docs, "raw/simpler way"

```python
class User(Base):
    __table_name__ = "users"
    __table_args__ = {"schema": "yet_another_schema"}
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100))

Additional context

Although all these ways work, I'm wondering what could be the "better" approach. Specifically which way will:

• Still support transactions (if the DB supports them of course)
• Would cause less strain (or none, for the the non-default schemas) when the session loads the metadata
• Would not mess with the connection pool

[sm-Fifteen]

Not really FastAPI-related, but binding is to work with database engines, which correspond with connections, not schemas.

Binding to schemas (with the ORM) is done by assigning schemas to your model classes, like so:

ModelBase = declarative_base(name='ModelBase')

class User_Model(ModelBase):
    __tablename__ = 'users'
    __table_args__ = {'schema': 'myapp'}

    id =                  Column(Integer, primary_key=True, autoincrement="auto")
    username =            Column(Text, unique=True, nullable = False)

[lfpcorreia]

Not really FastAPI-related, but binding is to work with database engines, which correspond with connections, not schemas.

Binding to schemas (with the ORM) is done by assigning schemas to your model classes, like so:

ModelBase = declarative_base(name='ModelBase')

class User_Model(ModelBase):
    __tablename__ = 'users'
    __table_args__ = {'schema': 'myapp'}

    id =                  Column(Integer, primary_key=True, autoincrement="auto")
    username =            Column(Text, unique=True, nullable = False)

thanks @sm-Fifteen ! Your are right, I've updated to reflect and better explain my issue.

the word bind came from the flask-sqlalchemy bind_key way of doing it.

[sm-Fifteen]

Well, that depends on what you're attempting to do. As the SQLA doc says (emphasis mine):

To support multi-tenancy applications that distribute common sets of tables into multiple schemas, the Connection.execution_options.schema_translate_map execution option may be used to repurpose a set of Table objects to render under different schema names without any changes.

If all you intend to do is have ORM model objects spread across multiple schemas, the "raw/simpler way" is all you need. Anything else would be overthinking it. There's no impact on connection pooling or transactions or metadata or anything of the sort because all this does is change what namespace will be rendered in front of the table name in SQL queries.

I'm not sure I understand what BindMetaMixin is supposed to do, here. If you look at the FastAPI doc for SQLAlchemy, you'll see you can just stick to regular SQLA interfaces (like declarative_base and such) and the most you'll have to do is create a context-manager dependency to manage session scope. There's no need for all the extra stuff Flask-SQLAlchemy does on top.

EDIT: Unless your question is about supporting multiple database connections, not schemas?

[lfpcorreia]

>

Well, that depends on what you're attempting to do. As the SQLA doc says (emphasis mine):

To support multi-tenancy applications that distribute common sets of tables into multiple schemas, the Connection.execution_options.schema_translate_map execution option may be used to repurpose a set of Table objects to render under different schema names without any changes.

If all you intend to do is have ORM model objects spread across multiple schemas, the "raw/simpler way" is all you need. Anything else would be overthinking it. There's no impact on connection pooling or transactions or metadata or anything of the sort because all this does is change what namespace will be rendered in front of the table name in SQL queries.

Thanks for the clear and to the point explanation/validation!

I'm not sure I understand what BindMetaMixin is supposed to do, here. If you look at the FastAPI doc for SQLAlchemy, you'll see you can just stick to regular SQLA interfaces (like declarative_base and such) and the most you'll have to do is create a context-manager dependency to manage session scope. There's no need for all the extra stuff Flask-SQLAlchemy does on top.

I also don't understand quite well what it tries to accomplish. But that's not a reason to disregard the work.. ;)

EDIT: Unless your question is about supporting multiple database _connections_, not schemas?

It isn't! I think we can close this issue.

[tiangolo]

Thanks for the help here @sm-Fifteen ! :cake:

Thanks @lfpcorreia for reporting back and closing the issue :+1:

[lfpcorreia]

You're welcome! Great work!