How to use pydantic and sqlalchemy models with relationship in tiangolo FastAPI

Explanation of the problem

I am currently attempting to pass pydantic models to sqlalchemy models as specified in the sql-databases documentation. While this is successful for single models, it is failing when trying to handle relationships. I am expecting to receive an object that nests several other objects in my endpoint.

I am wondering if there is a way to accomplish this using the BaseModel.dict() method as outlined in the documentation, or if I need to map my pydantic model to my sqlalchemy model in order to achieve the desired outcome.

Below is an example of my current implementation, including the relevant imports and classes, as well as the specific endpoint that is causing issues:

from typing import Optional, List

from fastapi import Depends, FastAPI, HTTPException

from pydantic import BaseModel

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, sessionmaker, relationship
from sqlalchemy import ForeignKey, Column, Integer, String

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(BaseModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(BaseModel):
    id: Optional[int]
    name: str

    children: List[ChildSchema] = []

    class Config:
        orm_mode = True

SQLALCHEMY_DATABASE_URL = "sqlite:///./test_app.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base.metadata.create_all(bind=engine)

api = FastAPI()

def get_db():
    try:
        db = SessionLocal()
        yield db
    finally:
        db.close()

@api.post("/parents/", response_model=ParentSchema)
def post_parent(parent: ParentSchema, db: Session = Depends(get_db)):
    db_parent = Parent(**parent.dict())
    db.add(db_parent)
    db.commit()
    db.refresh(db_parent)
    return ParentSchema.from_orm(db_parent)

Troubleshooting with the Lightrun Developer Observability Platform

Getting a sense of what’s actually happening inside a live application is a frustrating experience, one that relies mostly on querying and observing whatever logs were written during development.
Lightrun is a Developer Observability Platform, allowing developers to add telemetry to live applications in real-time, on-demand, and right from the IDE.

• Instantly add logs to, set metrics in, and take snapshots of live applications
• Insights delivered straight to your IDE or CLI
• Works where you do: dev, QA, staging, CI/CD, and production

Start for free today

Problem solution for How to use pydantic and sqlalchemy models with relationship in tiangolo FastAPI

from pydantic import BaseModel, OrmModel
from sqlalchemy import Column, Integer, String

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(OrmModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(OrmModel):
    id: Optional[int]
    name: str
    children: List[ChildSchema] = None
    class Config:
        orm_mode = True

Other popular problems with FastAPI

from pydantic import BaseModel, OrmModel
from sqlalchemy import Column, Integer, String

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), index=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    parent_id = Column('parent_id', Integer(), ForeignKey('parents.id'), nullable=False)

    parent = relationship("Parent", back_populates="children")

class ChildSchema(OrmModel):
    id: Optional[int]
    name: str

    class Config:
        orm_mode = True

class ParentSchema(OrmModel):
    id: Optional[int]
    name: str
    children: List[ChildSchema] = None
    class Config:
        orm_mode = True

You can use the ParentSchema and ChildSchema to handle the nested relationships correctly, when you pass the ParentSchema to the ORM Pydantic will automatically create the relationship between the parent and children.

Problem: Handling File Uploads

Another common issue when working with FastAPI is handling file uploads. The default behavior of FastAPI is to receive files as bytes, but in some cases, you may need to receive files as file objects.

Solution:

One solution to this issue is to use the open function to open the file in binary mode and pass the file object to the endpoint.

from fastapi import FastAPI, File, UploadFile

app = FastAPI()

@app.post("/uploadfile/")
async def create_upload_file(file: bytes = File(...)):
    return {"filename": file.filename}

@app.post("/uploadfileobj/")
async def create_upload_file(file: UploadFile = File(...)):
    return {"filename": file.filename}

the first example will receive files as bytes and the second one will receive files as file objects.

Problem: Handling CORS

Another common issue when working with FastAPI is handling CORS(Cross-Origin Resource Sharing). By default, FastAPI does not handle CORS and you need to install an additional library or to add the headers manually.

Solution:

One solution to this issue is to use the FastAPI-CORS library that can be installed via pip and then use it to configure the CORS settings.

from fastapi import FastAPI
from fastapi_cors import FastAPICors

app = FastAPI()

cors = FastAPICors()
cors.add(
    app,
    routes=["/items/", "/items/{item_id}"],
    allow_origin="*",
    allow_methods=["*"],
    allow_headers=["*"],
)

This will add the necessary headers to handle CORS and allow requests from any origin, methods, and headers for the routes /items/ and /items/{item_id}.

A brief introduction to FastAPI

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.6+ based on standard Python type hints. It is built on top of Starlette for the web parts and Pydantic for the data parts. It is designed to be easy to use and to provide high performance by using async/await and asynchronous generators.

FastAPI also provides built-in support for OAuth2 authentication and OpenAPI documentation. It allows for the use of dependency injection and includes a convenient way to handle request and response data using Pydantic models. This allows for easy input validation, serialization and deserialization, and automatic documentation generation. Additionally, it supports many features such as WebSocket, GraphQL, background tasks, and more. It makes it easy to build robust and scalable APIs quickly and efficiently.

Most popular use cases for FastAPI

1. Building RESTful APIs: FastAPI allows developers to easily build and expose RESTful APIs. With the use of Pydantic models, input validation, serialization and deserialization, and automatic documentation generation are made easy. The following code block illustrates a simple example of a RESTful API endpoint that accepts a request payload, validates it and returns a response:

   from fastapi import FastAPI, Body
   from pydantic import BaseModel
   
   app = FastAPI()
   
   class Item(BaseModel):
       name: str
       description: str
   
   @app.post("/items/")
   def create_item(item: Item = Body(..., embed=True)):
       return {"item": item}
   

2. Real-time communication: FastAPI has built-in support for WebSockets, which allows for real-time communication between the server and the clients. This makes it possible to use FastAPI to build real-time applications such as chat systems, real-time notifications, and more.
3. Handling large data sets: FastAPI allows developers to handle large data sets efficiently by using asynchronous generators. This allows for the streaming of large data sets without the need to load the entire dataset into memory. This feature is particularly useful when working with large files or when processing data streams.