How to use SQLModel with more than 1 database?

Question

I have a FastAPI project using SQLModel as the orm. I would like to use multiple different databases in the same project. For example I would like one FastAPI endpoint to query 1 database and another FastAPI endpoint to query a completely different database. However I'm struggling to find any documentation on how to go about doing so. I'm assuming it involves a particular setup of the SQLModel classes/engines along with the metadata but I'm unsure. Any direction at all would be appreciated. Thanks.

Answer 1

Querying a database is done via a Session instance. That session object is instantiated by providing the database Engine object, which you typically get by calling the create_engine constructor. That constructor takes the database URI as its first argument. I see no problem just setting up two different engines like this:

engine_a = create_engine("sqlite:///a.db", echo=True)
engine_b = create_engine("sqlite:///b.db", echo=True)

If you want to have the same tables in both databases, you can just issue the create_all call for both engines:

SQLModel.metadata.create_all(engine_a)
SQLModel.metadata.create_all(engine_b)

If you actually want your databases to house different tables, you should probably take care in setting up your migration logic appropriately. I assume for a serious project, you'll be using something like alembic for that. But for a quick test, you can even issue individual CREATE TABLE statements depending on your needs. Say you want model A to map only to the database connected to via engine_a, then you could do this:

A.__table__.create(engine_a)

For querying you can use the session context just the same as always, but pass the correct engine to it:

with Session(engine_a) as session:
    ...

Here is a full working example:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine


class A(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    foo: str


class B(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    bar: str


if __name__ == '__main__':
    engine_a = create_engine("sqlite:///a.db", echo=True)
    engine_b = create_engine("sqlite:///b.db", echo=True)

    A.__table__.create(engine_a)
    B.__table__.create(engine_b)

    with Session(engine_a) as session:
        session.add(A(foo="abc"))
        session.commit()

    with Session(engine_b) as session:
        session.add(B(bar="xyz"))
        session.commit()

You can open the resulting a.db and b.db in something like sqlitebrowser to see that table A with its entry were actually created only in a.db, whereas table B with its entry are only in b.db.

Hope this helps.