Learning FastAPI and SQLModel for API Development

Are you interested in learning FastAPI and SQLModel? I recently started a new job, and I needed to learn their tech stack. The company implements a microservices architecture, interacting with their databases through micro-apis built using FastAPI, a popular new python package for api development, using the SQLModel library as an ORM and input validator.

My Example Project

To familiarize myself with the new technologies, I decided to make a very simple api for a note-taking application. There were to be two tables in my development SQLite database: notes, which represents a note, and categories, which notes can optionally be entered into to help with organization. The schema for each of these was:

“notes” Table

“categories” Table

Note that in the schema listed above, category_id in the former table is a foreign key referencing id in the latter.

Project Setup

To build this project, I had to install FastAPI and SQLModel. Here is the generated requirements.txt file after installing these two packages:

anyio==4.3.0
certifi==2024.2.2
charset-normalizer==3.3.2
click==8.1.7
codecov==2.1.13
coverage==7.4.3
exceptiongroup==1.2.0
fastapi==0.110.0
greenlet==3.0.3
h11==0.14.0
httptools==0.6.1
idna==3.6
pydantic==1.10.14
PyYAML==6.0.1
requests==2.31.0
sniffio==1.3.1
SQLAlchemy==2.0.27
sqlmodel==0.0.16
starlette==0.36.3
typing_extensions==4.10.0
urllib3==1.26.15
uvicorn==0.27.1
uvloop==0.19.0
watchfiles==0.21.0
websockets==12.0

File Structure

This is the basic file structure of the notes app api I built:

notes_app
|->database
|  |->__init__.py
|  |->models.py
|->routers
|  |->__init__.py
|  |->note.py
|  |->category.py
|->__init__.py
|->main.py
|->requirements.txt

The database module contains the SQLModel models used to get interact with the database in the models.py folder. Meanwhile, the database connection and some helper methods are initialized in the __init__.py file.

The routers module, meanwhile, contains the logic for creating, reading, updating, and deleting records of each type. note.py handles the CRUD operations for records in the notes table, and category.py handles them for the categories table.

Most of the app resides within those two models. Apart from them, the main.py script serves as an entry point to run the api.

Database Setup

For the database of this application, everything is being done through the use of the SQLModel library. SQLModel makes it easy to interact with databases from python programs by combining the functionalities of an ORM (SQLAlchemy) and a Data Validation Schema (Pydantic). Because these tasks normally involve defining the same data schema twice, SQLModel is an intuitive library that allows developers to define their data schema once, and handles both tasks behind the scenes, while leaving access to the intricacies of both available to the developer when needed.

I set up all my code for interacting with my database in the database module of my application. In the __init__.py file, I establish a connection to the sqlite database, and have two helper functions: get_session(), which is used to get a database connection within my api route handlers, and create_db_and_tables(), which is used to create the two tables in my database.

from sqlmodel import SQLModel, create_engine, Session
from sqlalchemy.orm import sessionmaker

#establish database connection
sqlite_file_name = "notes.sqlite3"
sqlite_url = f"sqlite:///./{sqlite_file_name}"

connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)

#function to get the session in routes
def get_session():
    with Session(engine) as session:
        yield session

#database seeding function
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

#create db on direct run
if __name__ == '__main__':
    create_db_and_tables()

models.py contains the definitions for all my SQLModel models that are used for database interaction and data validation. In my app, I define base models with all the schema definitions for attributes. Then, for individual operations, like Read, for instance, I create a class that inherits from the base class, and extends and overrides the fields it should contain to fit the schema for reading an individual record from the database. Here is an example of this:

from typing import List, Optional
from sqlmodel import Field, Relationship, Session, SQLModel

#base category attrs
class CategoryBase(SQLModel):
    name: str = Field(index=True)
    description: str

    
#define relations
class Category(CategoryBase, table=True):
    __tablename__ = "categories"
    id: Optional[int] = Field(default=None, primary_key=True)
    notes: List["Note"] = Relationship(back_populates="category")

#extra validators for a read operation
class CategoryRead(CategoryBase):
    id: int

As you can see, CategoryBase defines two attributes, name and description. These are both string fields. Then, I create Category, which extends CategoryBase and adds an optional id field, and a relationship with the Note models, defined elsewhere. id here is optional so that Category can exist before the record is added to the database, since the database assigns the id.

CategoryRead makes the id attribute required. This is because this model serves as the input validator for the /api/cateogries/<id> route, which gets the information about a specific category from the database. The api needs to know the id of the category to get information on, so id should be required in this case.

In my api, there are numerous other SQLModel models that handle other types of api operations. But, these models do no good on their own. They need to be used in route handling logic to have perform their jobs of data validation and database interaction effectively.

Using Routers

One of the neat features of FastAPI is the ability to split the logic for interacting with similar resources off into their own file by making use of a router. Routers in FastAPI allow for the grouping of routes into their own object. These routers can be defined in their own file, then imported and tagged onto the app.

note.py contains an example of a router implementation:

from sqlmodel import Session, select
from fastapi import Depends, HTTPException, status, APIRouter, Response
from database import get_session
from database.models import *
from typing import List

#init the router
router = APIRouter()

# [...] get all records
@router.get("/", response_model=List[NoteRead]) #response_model specifies the return type of the function below
def read_notes(*, session: Session = Depends(get_session)):
    notes = session.exec(select(Note)).all()
    return notes

# [...] create record
@router.post('/', status_code=status.HTTP_201_CREATED)  #response_model specifies the return type of the function below
def create_note(*, session: Session = Depends(get_session), note: Note):
    session.add(note)
    session.commit()
    session.refresh(note)
    return note

# # [...] edit record
@router.patch('/{note_id}', response_model=NoteRead)
def update_note(*, note_id: int, note: NoteUpdate, session: Session = Depends(get_session)):
    db_note = session.get(Note, note_id)
    if not note:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail=f"No note with this id: {note_id} found")
    note_data = note.model_dump(exclude_unset=True)
    db_note.sqlmodel_update(note_data)
    session.add(db_note)
    session.commit()
    session.refresh(db_note)
    return db_note


# # [...] get single record
@router.get('/{note_id}', response_model=NoteReadWithCategory)  #response_model specifies the return type of the function below
def get_note(*, note_id: int, session: Session = Depends(get_session)):
    note = session.get(Note, note_id)
    if not note:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail=f"No note with this id: {note_id} found")
    return note

# # [...] delete record
@router.delete('/{note_id}', response_model=NoteRead)
def delete_note(note_id: str, session: Session = Depends(get_session)):
    note = session.get(Note, note_id)
    if not note:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail=f"No note with this id: {note_id} found")

    session.delete(note)
    session.commit()
    return note

As you can see, the method for creating routes and attaching them to the router is very similar to attaching routes to an app. Define a route handler with a route and a method, then specify a route handler function. Note that the route is a local route, the full route will be defined in app.py.

One interesting aspect of FastAPI is the response_model keyword argument. This specifies the SQLModel model that the handler will return to the user. For instance, let’s take a closer look at the get single record route:

# # [...] get single record
@router.get('/{note_id}', response_model=NoteReadWithCategory)  #response_model specifies the return type of the function below
def get_note(*, note_id: int, session: Session = Depends(get_session)):
    note = session.get(Note, note_id)
    if not note:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail=f"No note with this id: {note_id} found")
    return note

The response model type is NoteReadWithCategory. This model is defined in the models.py file.

#base note attrs
class NoteBase(SQLModel):
    title: str
    content: str
    priority: str
    published: bool
    category_id: Optional[int] = Field(default=None, foreign_key="categories.id")
    
#define relations
class Note(NoteBase, table=True):
    __tablename__ = "notes"
    id: Optional[int] = Field(default=None, primary_key=True)
    category: Optional[Category] = Relationship(back_populates="notes")

#extra validators for read operation
class NoteRead(NoteBase):
    id: int

#read a note with it's category attached
class NoteReadWithCategory(NoteRead):
    category: Optional['CategoryRead'] = None

Because NoteReadWithCategory inherits from top to bottom of the code block above, it specifies that the return type of the get get single record route as containing id, title, content, priority, published, category_id or null, and category or null.

Apart from that, the route handler is pretty unremarkable. It gets the Note by id, and returns it in the format specified by NoteReadWithCategory.

Tying it Together

After defining routers, all that was left was to create the main.py file, configure the app, add the routers, and start the app. My main.py file looked like this:

from .routers import note, category
from .database import create_db_and_tables
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

# create app
app = FastAPI()

# app config
origins = [
    "http://localhost:3000",
]

app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


# add routers to app
app.include_router(note.router, tags=["Notes"], prefix="/api/notes")
app.include_router(category.router, tags=["Categories"], prefix="/api/categories")


# healthcheck route
@app.get("/api/healthchecker")
def root():
    return {"message": "Welcome to FastAPI with SQLModel"}


# create tables on start
@app.on_event("startup")
def on_startup():
    create_db_and_tables()

This is mostly simple stuff, simply creating an app and passing it some configuration. Then, I add the routes from my routers using app.include_router, specifying a route prefix to prepend to the beginning of each route in the router. Finally, I defined a health checker route to be used to check the api status. Finally, I call the function I wrote in the database module to add all my tables to the database.

To run the app, I use a program called uvicorn, an ASGI server that works very nicely with FastAPI.

Conclusion

In building this project, I was able to learn the basics of FastAPI and SQLModel for my new job. FastAPI and SQLModel have some nice features. I especially like the automatic documentation generation and CRUD testing of FastAPI. SQLModel is a new and exciting library with a ton of potential. Hopefully, these notes and snippets have helped you in your journey of learning FastAPI and SQLModel, and how to use them in your projects.

Interested in more on SQLModel? Click here to read about how to make auto-updating timestamp fields on you models!