Why does adding a registry break tables being connected to the same MetaData instance?

[mikulas-mrva]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

# SQLAlchemy solution that works
from sqlalchemy.orm import registry
from sqlalchemy import Column, Integer, String


registry_one = registry()
BaseOne = registry_one.generate_base()


class ModelOneA(BaseOne):
    __tablename__ = "model_one_a"
    id = Column(Integer, primary_key=True)
    description = Column(String)


class ModelOneB(BaseOne):
    __tablename__ = "model_one_b"
    id = Column(Integer, primary_key=True)
    description = Column(String)


registry_two = registry()
BaseTwo = registry_two.generate_base()


class ModelTwoA(BaseTwo):
    __tablename__ = "model_two_a"
    id = Column(Integer, primary_key=True)
    description = Column(String)


class ModelTwoB(BaseTwo):
    __tablename__ = "model_two_b"
    id = Column(Integer, primary_key=True)
    description = Column(String)


if __name__ == "__main__":
    print(registry_one.metadata.tables)  # contains ModelOneA and ModelOneB as expected
    print(registry_two.metadata.tables)  # contains ModelTwoA and ModelTwoB as expected


# SQLModel solution that I hoped would do the same, but it doesn't work (the way I expected it to)
from sqlmodel import SQLModel, Field
from sqlalchemy.orm import registry


registry_one = registry()
BaseOne = registry_one.generate_base()


class ModelOneA(SQLModel, table=True, registry=registry_one):
    id: int | None = Field(primary_key=True)
    description: str


class ModelOneB(SQLModel, table=True, registry=registry_one):
    id: int | None = Field(primary_key=True)
    description: str


registry_two = registry()


class ModelTwoA(SQLModel, table=True, registry=registry_two):
    id: int | None = Field(primary_key=True)
    description: str


class ModelTwoB(SQLModel, table=True, registry=registry_two):
    id: int | None = Field(primary_key=True)
    description: str


if __name__ == "__main__":
    print(registry_one.metadata.tables)  # empty
    print(registry_two.metadata.tables)  # empty
    print(SQLModel.metadata.tables)  # empty

Description

I have an app that has tables in multiple databases, and I want to keep track of all of them. I started with SQLAlchemy, so I had one declarative base per database, and simply created the tables in all databases by running the following:

for database in get_all_databases():
    await conn.run_sync(database.base.metadata.create_all)  # database is a simple object that holds the declarative base and db name

Now I would like to transfer to SQLModel, but I didn't find a way to use declarative bases or directly insert metadata, so I am using the registry kwarg. The problem is that once I do that, the tables are nowhere to be found. Neither in the registry.metadata objects, nor in SQLModel.metadata. (see my example code)

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.18

Python Version

3.12

Additional Context

No response

[mikulas-mrva]

OK, so after being stuck for two days, of course I found a solution ten minutes after having posted a question.
This seems to work, but it's quite counterintuitive, and would probably deserve a mention in the docs.

from sqlmodel import SQLModel, Field
from sqlalchemy.orm import registry


registry_one = registry()


class BaseOne(SQLModel, registry=registry_one):
    ...


class ModelOneA(BaseOne, table=True):
    id: int | None = Field(primary_key=True)
    description: str


class ModelOneB(BaseOne, table=True):
    id: int | None = Field(primary_key=True)
    description: str


registry_two = registry()


class BaseTwo(SQLModel, registry=registry_two):
    ...


class ModelTwoA(BaseTwo, table=True):
    id: int | None = Field(primary_key=True)
    description: str


class ModelTwoB(BaseTwo, table=True):
    id: int | None = Field(primary_key=True)
    description: str


if __name__ == "__main__":
    print(registry_one.metadata.tables)  # contains ModelOneA and ModelOneB as expected
    print(registry_two.metadata.tables)  # contains ModelTwoA and ModelTwoB as expected
    print(SQLModel.metadata.tables)  # empty