Making FastAPI pydantic schemas faster

We all have used relationship definitions in FastAPI and wrote schemas to simulate SQL join tables action.

But if you are working with a bigger amount of data you quickly realise that it becomes very slow very quickly.

So we define our models something like this

class User(Base):
    __tablename__ = "users"

    user_id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=True)
    email = Column(String, nullable=False)

class Blog(Base):
    __tablename__ = "blogs"

    blog_id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=True)
    description = Column(String, nullable=False)
    creator_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)

    creator = relationship('users')

And for this we would define a response schema as such

class UserBase(BaseModel):
    user_id: int
    name: str
    email: str

class BlogOutput(BaseModel):
    blog_id: int
    title: str
    description: str
    creator: UserBase
    
    class Config:
        orm_mode: True

Now when we use BlogOutput as response model, we will get the simulated join table result between User and Blog.

Why do I say simulated? When I was trying this with a much bigger amount of data, I saw this getting really slow. Upon further investigation I learnt: FastAPI here isn’t really doing join tables, after it gets the list of creator_id, it again individually queries the database for the User which slows the entire process down.

How do we avoid this?

We make a very small tweak in our model description to avoid this problem. We define it something like this:

class User(Base):
    __tablename__ = "users"

    user_id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=True)
    email = Column(String, nullable=False)

class Blog(Base):
    __tablename__ = "blogs"

    blog_id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, nullable=True)
    description = Column(String, nullable=False)
    creator_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)

    creator = relationship('users', lazy="joined")

We add the lazy parameter as joined in our relationship defination. This will always fetch the rows from the User table when you are querying essentially doing a join table. This makes such queries exponentially faster.

This is not really a big change but somehow is so easily overlooked in documentation and has a huge impact.

Try it out for yourself!

Thanks for reading.