How to specify PostgreSQL schema in SQLAlchemy column/foreign key mixin?

Question

Existing PostgreSQL database have tables organized in different "schemas" to split a large database (both for scaling and implementing fine-tuned security at server level). Similarly, the declarative_base table descriptions are organized in different files in a package - one file per schema:

package
    __init__.py
    tables_in_schema1.py
    tables_in_schema2.py

Metadata and engine object goes into each file from the top of the package as db.Model. For example, tables_in_schema1.py would have (ignoring the necessary ORM imports and then need for back references) table alpha:

from package import db

class TableAlpha(db.Model, object):
    __tablename__ = "alpha"
    __table_args__ = ({"schema": "schema1"})

    id_alpha = Column(INTEGER, Sequence("pk_alpha", 1, 1), primary_key=True)


class MixinAlphaRelation(object):

    @declared_attr
    def id_alpha(cls):
        return Column(INTEGER, ForeignKey("schema1.alpha.id_alpha"))

Now in tables_in_schema2.py, two tables are defined. One is a stand-alone table, called beta, and the other is a linking table for the one-to-many relationship between alpha and beta to called table rho:

from package import db
from package.tables_in_schema1 import MixinAlphaRelation

class TableBeta(db.Model, object):
    __tablename__ = "beta"
    __table_args__ = ({"schema": "schema2"})

    id_beta = Column(INTEGER, Sequence("pk_beta", 1, 1), primary_key=True)


class MixinBetaRelation(object):

    @declared_attr
    def id_beta(cls):
        return Column(INTEGER, ForeignKey("schema2.beta.id_beta"))


class TableRho(db.Model, MixinAlphaRelation, MixinBetaRelation):
    __tablename__ = "rho"
    __table_args__ = (
        UniqueConstraint("id_alpha", "id_beta", name="uq_rho_alpha-beta"),
        {"schema": "schema2"})

    id_row = Column(INTEGER, Sequence("pk_rho", 1, 1), primary_key=True)

The intended goal for the inheritance of table rho of both mixins is to produce a table consisting of three rows (and to reuse the mixin for other tables that are also referencing to either alpha or beta):

CREATE TABLE schema2.rho (
    id_row INTEGER PRIMARY KEY,
    id_alpha INTEGER REFERENCES schema1.alpha(id_alpha),
    id_beta INTEGER REFERENCES schema2.beta(id_beta)
    );

CREATE INDEX uq_rho_alpha-beta ON schema2.rho(id_alpha, id_beta);

However, when trying to recreate all these tables by calling db.create_all() SQLAlchemy will spew out an error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'rho.id_alpha' could not find table 'schema2.alpha' with which to generate a foreign key to target column 'id_alpha'

It appears that instead of locating table alpha being in schema1 as specified in the imported mixin, SQLAlchemy seems to be looking for it in schema2.

How can this be solved? Is there a way to pass/force the correct schema for the mixin? Thanks.

Answer 1

I finally found the error - a typo or extra underscore in table beta declaration: instead of the correct __tablename__ I had __table_name__. Without the __tablename__ class member specified, SQLAlchemy will create the table on the server using the class name as the table name (default behavior). In my case, it created table TableBeta instead of intended beta. That caused the foreign key mixin to not find the table.

The approach I took (detailed in my question) is the correct way of using mixins and specifying schema in SQLAlchemy for the use case (tables in different schemas and table class declarations in different model files). Schema name is specified in table declarations using the __table_args__ class member passed with a keyword dictionary {"schema": "schema_name"}. Schema name is qualified in mixin/column declarations in the form "schema_name.table_name.column_name".