Sharing class registry from declarative base to SQLModel to enable relationships

[jossefaz]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlalchemy.ext.declarative import declarative_base
from sqlmodel import SQLModel

engine_ = sqlalchemy.create_engine(MYSQL_URL)
Base = declarative_base()
SQLModel.metadata = Base.metadata

#declare all
class OldModel(Base):
    __tablname__ = "oldmodel"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

class NewModel(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    new_value: str = Field(default=None, sa_column=Column(Text))

#create all
SQLModel.metadata.create_all(engine)

Description

I am using declarative_base().
I also use SQLModel and I bind the metadata objects in order to share the same metadata between SA declarative base and SQLModel.
Then I have some classes that inherits from Base and others that inherist from SQLModel (This is mainly because SQLModel is a pretty new feature and only our new db models use it. Older models continue to use Base as parent class).

Everything worked fine so far.
My problem occurred when I try to add a relationship that refer to an OldModel column within the NewModel

class OldModel(Base):
    __tablname__ = "oldmodel"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

class NewModel(SQLModel, table=True):
    __tablename__ = 'newmodel'
    id: Optional[int] = Field(default=None, primary_key=True)
    new_value: str = Field(default=None, sa_column=Column(Text))
    oldmodel_id: Optional[int] = Field(default=None, foreign_key="oldmodel.id")
    oldmodel: Optional["OldModel"] = Relationship(sa_relationship=relationship("OldModel", lazy='joined'))

Then I came to this error :

../venv/lib/python3.9/site-packages/sqlalchemy/orm/state.py:474: in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
../venv/lib/python3.9/site-packages/sqlalchemy/event/attr.py:343: in __call__
    fn(*args, **kw)
../venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:3569: in _event_on_init
    instrumenting_mapper._check_configure()
../venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:1874: in _check_configure
    _configure_registries({self.registry}, cascade=True)
../venv/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py:3384: in _configure_registries
    _do_configure_registries(registries, cascade)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

registries = set(), cascade = True

    @util.preload_module("sqlalchemy.orm.decl_api")
    def _do_configure_registries(registries, cascade):
    
        registry = util.preloaded.orm_decl_api.registry
    
        orig = set(registries)
    
        for reg in registry._recurse_with_dependencies(registries):
            has_skip = False
    
            for mapper in reg._mappers_to_configure():
                run_configure = None
                for fn in mapper.dispatch.before_mapper_configured:
                    run_configure = fn(mapper, mapper.class_)
                    if run_configure is EXT_SKIP:
                        has_skip = True
                        break
                if run_configure is EXT_SKIP:
                    continue
    
                if getattr(mapper, "_configure_failed", False):
                    e = sa_exc.InvalidRequestError(
                        "One or more mappers failed to initialize - "
                        "can't proceed with initialization of other "
                        "mappers. Triggering mapper: '%s'. "
                        "Original exception was: %s"
                        % (mapper, mapper._configure_failed)
                    )
                    e._configure_failed = mapper._configure_failed
>                   raise e
E                   sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class NewModel->newmodel'. Original exception was: When initializing mapper mapped class NewModel->newmodel, expression 'OldModel' failed to locate a name ('OldModel'). If this is a class name, consider adding this relationship() to the <class 'models.mymodel.NewModel'> class after both dependent classes have been defined.

It seems that Base and SQLModel are not sharing the same class registry. I thought that binding the metadata to the same object would be enough but not. How can I "force" sharing class registry (if this is the issue...)?
I am stuck. Any help would be blessed !

Operating System

Linux

Operating System Details

Ubuntu 21.10

SQLModel Version

0.0.6

Python Version

3.9

Additional Context

No response

[byrman]

It isn't due to the __tablname__ typo, is it?

[byrman]

According to the docs:

from sqlalchemy.orm import declarative_base

Base = declarative_base()

Is equivalent to:

from sqlalchemy.orm import registry

mapper_registry = registry()
Base = mapper_registry.generate_base()

So you could make them share a registry by doing this:

from sqlmodel.main import default_registry

Base = default_registry.generate_base()

But that seems low-level fiddling.

[YuriiMotov]

The approach suggested by @byrman seems to work well.

Runnable code example in the details:

from typing import Optional

import sqlalchemy
from sqlalchemy.orm import relationship
from sqlmodel import Column, Field, Relationship, SQLModel, Text
from sqlmodel.main import default_registry

Base = default_registry.generate_base()

engine = sqlalchemy.create_engine("sqlite:///", echo=True)

# declare all
class OldModel(Base):
    __tablename__ = "oldmodel"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)

class NewModel(SQLModel, table=True):
    __tablename__ = "newmodel"
    id: Optional[int] = Field(default=None, primary_key=True)
    new_value: str = Field(default=None, sa_column=Column(Text))
    oldmodel_id: Optional[int] = Field(default=None, foreign_key="oldmodel.id")
    oldmodel: Optional["OldModel"] = Relationship(
        sa_relationship=relationship("OldModel", lazy="joined")
    )

# create all
SQLModel.metadata.create_all(engine)

Output:

CREATE TABLE oldmodel (
        id INTEGER NOT NULL, 
        PRIMARY KEY (id)
)

CREATE TABLE newmodel (
        id INTEGER NOT NULL, 
        new_value TEXT, 
        oldmodel_id INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(oldmodel_id) REFERENCES oldmodel (id)
)

[aristeu13]

I had a similar problem, I was trying to use DeclarativeMeta and mapper_registry at the same time.

The code above allowed the problem, but add some changes to be able to generate tablename as well.

from sqlalchemy.orm import declared_attr, registry
from sqlalchemy.orm.decl_api import DeclarativeMeta


from app.utils import camel_case_to_snake_case

mapper_registry = registry()


class Base(metaclass=DeclarativeMeta):
    __abstract__ = True

    registry = mapper_registry
    metadata = mapper_registry.metadata

    @declared_attr
    def __tablename__(cls) -> str:  # pylint: disable=no-self-argument
        return camel_case_to_snake_case(cls.__name__)