We are migrating a big application from Flask to FastAPI. Currently sqlalchemy is used for the database models. In the future we probably want to switch to sqlmodel but in order to keep migrating effort to a minimum we want to support both at the same time.

I managed to somewhat support both at the same time but updating my alembic configuration but the issue is that relationships are not working. I can only use SQLModel and SQLAlchemy if they do not share any relationships right now which is effectively useless for me.

This is my current file structure:

.
└── models/
    ├── __init__.py  --> Define declarative base object 'Base' for SQLAlchemy tables
    ├── users.py  --> SQLAlchemy table
    ├── books.py  --> SQLAlchemy table
    ├── tags.py  --> SQLAlchemy table
    ├── books_tags.py  --> Many-2-Many association table between 'books' and 'tags'
    └── stats.py  --> SQLModel table

This is working due to the fact that the stats table does not have any relationships with the other tables.

However I want to change the books table from sqlalchemy to sqlmodel while keeping the relationships intact .

My .models/__init__.py file:

from sqlalchemy.orm import declarative_base
from sqlalchemy import MetaData

# Create a common MetaData object
common_metadata = MetaData()

# Use the common MetaData object for SQLAlchemy Base
Base = declarative_base(metadata=common_metadata)

from .users import User
from .books import Book
from .tags import Tag

The books table with sqlalchemy looks like this:

from sqlalchemy import Column, String, Integer, Date, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import UUID as pgUUID
from sqlalchemy.orm import relationship
from datetime import datetime
import uuid as uid
from . import Base
from .books_tags import book_tag_association  # many-to-many association table


class Book(Base):
    __tablename__ = "books"
    uuid = Column(pgUUID(as_uuid=True), primary_key=True, default=uid.uuid4, nullable=False)
    title = Column(String, nullable=False)
    author = Column(String, nullable=False)
    publisher = Column(String, nullable=False)
    published_date = Column(Date, nullable=False)
    page_count = Column(Integer, nullable=False)
    language = Column(String, nullable=False)
    user_uuid = Column(pgUUID(as_uuid=True), ForeignKey("users.uuid"), nullable=True)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    user = relationship("User", back_populates="books", lazy="selectin")
    tags = relationship("Tag", secondary=book_tag_association, back_populates="books", lazy="selectin")

The 'User' table is basically the same, for reference here is also the book_tag_association table:

from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.dialects.postgresql import UUID as pgUUID
from models import Base 

# Association table for many-to-many relationship
book_tag_association = Table(
    'book_tag_association', Base.metadata,
    Column('book_uuid', pgUUID(as_uuid=True), ForeignKey('books.uuid', ondelete='CASCADE'), primary_key=True),
    Column('tag_uuid', pgUUID(as_uuid=True), ForeignKey('tags.uuid', ondelete='CASCADE'), primary_key=True)
)

Up to this point everything is working just fine. But if I want to change the books table from sqlalchemy to sqlmodel like this the relationships won't work:

from sqlmodel import SQLModel, Field, Column, Relationship
from datetime import datetime, date
from typing import Optional, TYPE_CHECKING
import sqlalchemy.dialects.postgresql as pg
import uuid as uid

if TYPE_CHECKING:  # This is needed to prevent circular imports
    from .users import User


class Book(SQLModel, table=True):
    __tablename__ = "books"
    uuid: uid.UUID = Field(
        sa_column=Column(pg.UUID, nullable=False,
                         primary_key=True, default=uid.uuid4)
    )
    title: str
    author: str
    publisher: str
    published_date: date
    page_count: int
    language: str
    user_uuid: Optional[uid.UUID] = Field(
        default=None, foreign_key="users.uuid")
    created_at: datetime = Field(sa_column=Column(
        pg.TIMESTAMP, default=datetime.now))
    update_at: datetime = Field(sa_column=Column(
        pg.TIMESTAMP, default=datetime.now))
    user: Optional["User"] = Relationship(
        back_populates="book", sa_relationship_kwargs={"lazy": "selectin"})
    tags: List["Tag"] = Relationship(back_populates="books", link_model=book_tag_association)

The main issue is that my book_tag_association table is not able to resolve books.uuid. The main problem in my opinion is that the SQLModel tables are not part of the declarative base object Base. Therefore all the relationships between sqlalchemy and sqlmodel can not be resolved.

How can I make sure that the SQLModel tables are "known" within the declarative Base in order to use relationships with each other?