How to use two different metadata properly to connect to two databases?

[murata100]

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 typing import Optional

from sqlmodel import Field, SQLModel
import sqlalchemy

metadata1 = sqlalchemy.MetaData()
metadata2 = sqlalchemy.MetaData()

# all_metadata = sqlmodel.SQLModel.metadata

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    __table_args__ = (
        metadata1,  # This setting has no effect !! :(
    )

class Boss(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    age: Optional[int] = None
    __table_args__ = (
        metadata2,  # This setting has no effect !! :(
    )

engine1 = sqlalchemy.create_engine("database 1")
engine2 = sqlalchemy.create_engine("database 2")

metadata1.create_all(engine1)
metadata2.create_all(engine2)

## in alembic's env.py
# target_metadata = {
#    'engine1': mymodel.metadata1,
#    'engine2': mymodel.metadata2
#}

Description

• I want to use two databases that have different table groups.
• SQLModel provides only one metadata (sqlmodel.SQLModel.metadata)

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10.0

Additional Context

No response

[byrman]

This seems to work, although I did not test is thoroughly:

class SQLModel1(SQLModel):
    metadata = MetaData()


class SQLModel2(SQLModel):
    metadata = MetaData()


...


SQLModel1.metadata.create_all(engine1)
SQLModel2.metadata.create_all(engine2)

[murata100]

Thank you @byrman

I saw the definition of the SQLModel class

metadata is a class variable
metadata: ClassVar[MetaData]

Is the only way to handle multiple metadata is to extend the SQLModel class?

class SQLModel(BaseModel, metaclass=SQLModelMetaclass, registry=default_registry):
    # SQLAlchemy needs to set weakref(s), Pydantic will set the other slots values
    __slots__ = ("__weakref__",)
    __tablename__: ClassVar[Union[str, Callable[..., str]]]
    __sqlmodel_relationships__: ClassVar[Dict[str, RelationshipProperty]]  # type: ignore
    __name__: ClassVar[str]
    metadata: ClassVar[MetaData]
...

https://github.com/tiangolo/sqlmodel/blob/8d1b6f079adad47cc242710f6cb1790a8ad8fbd2/sqlmodel/main.py#L478

[byrman]

Is the only way to handle multiple metadata is to extend the SQLModel class?

In my experience there are often more solutions to a problem. I don't even know if extending the class is the right way to do it: I only tested table creation and select. Let us know how it works out for you.

[byrman]

By the way, create_all accepts a list of tables, so you can control which tables are created where by passing different lists to this method. That might also be something for you to try.

[murata100]

For now, I use it with alembic.
I don't use create_all in production.

env.py

from myapp import mymodel
target_metadata = {
      'engine1': mymodel.metadata1,
      'engine2': mymodel.metadata2
}

Probably, There is no problem to use it with alembic.

But, I'm a little hesitant to use sqlmodel in production..

[gsouveton]

I'm facing the same problem. But additionally I would like to work with transactions.
SQLAlchemy allows to do this :

Session = sessionmaker(binds={
    SomeMappedClass: create_engine('postgresql://engine1'),
    SomeDeclarativeBase: create_engine('postgresql://engine2'),
    some_mapper: create_engine('postgresql://engine3'),
    some_table: create_engine('postgresql://engine4'),
    })

Which allows to use an unique Session to process transactions.
I saw that the SQLModel Session class basically wraps the SQLAlchemy's one.
Is it possible to do something like this with only one session for several engines and data models split among these engines as SQLAlchemy allows ?

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})
session = Session()
# .... work with accounts and users
session.commit()

[cctdev]

Did anyone else ever figure this out? I'm looking to do something similiar (2 different fastapi endpoints using sqlmodel each tied to a different database) but am struggling to find any documentation that would support doing this.

[vn7n24fzkq]

I finally found a simple way to create tables in different databases.

import uuid as uuid_pkg
from typing import Optional

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


class DB1Base(SQLModel, registry=registry()):
    pass
    
class DB2Base(SQLModel, registry=registry()):
    pass

class DB1Table(DB1Base, table=True):
    uuid: Optional[uuid_pkg.UUID] = Field(default_factory=uuid_pkg.uuid4, primary_key=True)
    name: str = Field(nullable=False, index=True, unique=True)

class DB2Table(DB2Base, table=True):
    uuid: Optional[uuid_pkg.UUID] = Field(default_factory=uuid_pkg.uuid4, primary_key=True)
    name: str = Field(nullable=False, index=True, unique=True)

db1_engine = create_engine("postgresql://postgres@localhost:5432/db1")
db2_engine = create_engine("postgresql://postgres@localhost:5432/db2")

DB1Base.metadata.create_all(db1_engine)
DB2Base.metadata.create_all(db2_engine)

[gsouveton]

What a timing @vn7n24fzkq ! I spent the last few days on this and just came to the same solution !

Here is the explanation : SQLModel uses by default a single registry mapper, which means all classes names are gathered in a single namespace, causing name collisions if several databases contain classes with the same name.
Which causes errors like this one :

"sqlalchemy.exc.InvalidRequestError: Multiple classes found for path "xxx" in the registry of this declarative base. Please use a fully module-qualified path."

Specifying a new instance of registry when creating a SQLModel subclass seems the right way to solve this issue.

[jooh]

I finally found a simple way to create tables in different databases.

import uuid as uuid_pkg
from typing import Optional

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


class DB1Base(SQLModel, registry=registry()):
    pass
    
class DB2Base(SQLModel, registry=registry()):
    pass

class DB1Table(DB1Base, table=True):
    uuid: Optional[uuid_pkg.UUID] = Field(default_factory=uuid_pkg.uuid4, primary_key=True)
    name: str = Field(nullable=False, index=True, unique=True)

class DB2Table(DB2Base, table=True):
    uuid: Optional[uuid_pkg.UUID] = Field(default_factory=uuid_pkg.uuid4, primary_key=True)
    name: str = Field(nullable=False, index=True, unique=True)

db1_engine = create_engine("postgresql://postgres@localhost:5432/db1")
db2_engine = create_engine("postgresql://postgres@localhost:5432/db2")

DB1Base.metadata.create_all(db1_engine)
DB2Base.metadata.create_all(db2_engine)

This works!

If like me you were struggling to combine this with using inheritance to extend models the solution is to use mixins to patch in columns as in #70

[tanaga9]

I want a description of the recommended method in the official document.

[msftcangoblowm]

class DB1Base(SQLModel, registry=registry()):

WRONG!! x10^256 examples of also wrong. Don't mess up the registry

On the right track but forgot something critical. Applying
database objects naming convention.

^^^ quoted that so can copy+paste it and get reading ^^^

sqlalchemy docs screams to do this

alembic docs has a whole section dedicated to this topic

So what about SQLModel docs or issues? .... (crickets)

Every example ever lacks applying naming convention to the registry metadata!

from sqlalchemy import MetaData
from sqlalchemy.orm import registry

# copy+paste from the sqlalchemy docs. One case don't wanna be creative.
naming_convention_no_schema = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_`%(constraint_name)s`",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata = MetaData(
        naming_convention=naming_convention_no_schema,
        schema=schema,
)

reg = registry(metadata=metadata)

class DB1Base(SQLModel, registry=reg):

Why not to -- job security!

Can only imagine that folks wanting to give every single database object a name manually would do that as a form of job security. If that is the case then carry on.

For those of us who don't want to micro manage the micro manage of micro managers, would highly suggest not manually explicitly naming each and every database object. In each and every database. Not ever, but almost nearly ever.

For example, what if the guy in charge of random database object names gets a girlfriend? It could happen and what a disaster that would be. For him, for her, and for all the people who have to try to track down why a foreign key or unique constraint doesn't exist when it's clear as day in the code that it should be there.

Might have exaggerated, there is no agency of random database object names. But that won't stop the guy in charge of random database object names. Numerically, he can snap his fingers and poof, that agency will exist. Don't underestimate the power and unflinching resolve of the guy in charge of random database object names

schema

schema is str | None. For sharding and tenants you want it to be None.

A bit of trivia, for sqlite, the default schema is "main". For postgresql, it's "public".

The take away

Every single SQLModel Base ever neglects, when creating a registry, to apply database objects naming convention. This includes the SQLModel source code.

In Issues, when writing SQLModel Base(s), have mercy on those who are less knowledgeable by applying the registry correctly.