[Python] How to support SQLAlchemy and SQLModel at the same time in FastAPI

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?

Continue reading...