|

Build a Book Review API with FastAPI and SQLModel

You want to build a web API but have no idea where to start. This post walks you through everything from scratch. By the end, you will have a working Book Review API that stores data in a real database. You will be able to create, read, update, and delete book reviews through a browser.

No prior experience with web development required. You just need Python installed.

What Is an API?

An API is a program that other programs talk to. It lives on a server, listens for requests, and sends back responses.

Think of a restaurant. You sit at the table (your browser or app). A waiter takes your order (the API). The kitchen cooks the food (the database and business logic). The waiter brings your food back (the response).

In web APIs, those requests and responses travel over HTTP. HTTP is the same protocol your browser uses when you load a website. The difference is that instead of HTML for humans, an API sends back JSON. JSON is just structured text that looks like this:

{
  "id": 1,
  "title": "Dune",
  "rating": 5
}

Any app on any device can read JSON. That is why APIs are so useful.

What Is CRUD?

CRUD stands for Create, Read, Update, Delete. These are the four core operations any database-backed application needs:

  • Create (POST): add a new book review. POST is the HTTP method for sending new data to the server. The client sends a JSON body and the server saves it.
  • Read (GET): fetch one review or all reviews. GET is the HTTP method for requesting data. The server sends data back but changes nothing.
  • Update (PATCH): change a review you already saved. PATCH is the HTTP method for partial updates. The client sends only the fields that changed, not the whole record.
  • Delete (DELETE): remove a review. DELETE is the HTTP method for removing a specific record from the server.

Every API you will ever build maps to these four actions.

The Tools

FastAPI is a Python framework for building APIs. It reads Python type hints and uses them to validate requests, generate documentation, and handle serialization automatically. You write normal Python, and FastAPI handles the web layer.

SQLModel is a library for talking to databases using Python classes. It was built by the same author as FastAPI and works seamlessly with it. You define your data as a Python class, and SQLModel turns that into a database table.

SQLite is a database that lives in a single .db file on your computer. No server to set up, no config files. It is perfect for learning and small projects.

Set Up Your Project

First, create a folder for your project. Then create a virtual environment. A virtual environment is an isolated Python installation for your project so its dependencies do not interfere with other projects.

mkdir book-review-api
cd book-review-api
python -m venv venv

Now activate it:

# On Mac/Linux:
source venv/bin/activate

# On Windows:
venv\Scripts\activate

Install the dependencies:

pip install fastapi sqlmodel uvicorn
  • fastapi is the web framework
  • sqlmodel handles the database
  • uvicorn is the server that runs your FastAPI app

Create one file called main.py. Everything goes in this file.

Step 1: Define Your Data Models

Open main.py and start with the models. A model is a Python class that describes the shape of your data.

from typing import Annotated

from fastapi import Depends, FastAPI, HTTPException, Query
from sqlmodel import Field, Session, SQLModel, create_engine, select

This imports everything you need. Now define the models:

class BookReviewBase(SQLModel):
    title: str
    author: str
    rating: int = Field(ge=1, le=5)
    review: str | None = None


class BookReview(BookReviewBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class BookReviewPublic(BookReviewBase):
    id: int


class BookReviewCreate(BookReviewBase):
    pass


class BookReviewUpdate(SQLModel):
    title: str | None = None
    author: str | None = None
    rating: int | None = Field(default=None, ge=1, le=5)
    review: str | None = None

There are five classes here. Each one serves a different purpose. Here is what each one does:

BookReviewBase holds all the shared fields. Think of it as a blueprint:

  • title: the book title, a string
  • author: the book author, a string
  • rating: an integer from 1 to 5. The ge=1, le=5 means “greater than or equal to 1, less than or equal to 5.” FastAPI will reject any request that sends a rating outside that range.
  • review: optional text. The | None = None means the field is optional.

BookReview is the actual database table. The table=True argument tells SQLModel to create a real table in SQLite for this class. It adds an id field as the primary key. The id starts as None in Python but the database fills it in automatically when you save a new record.

BookReviewPublic is what you send back to clients. It extends the base and adds id: int. Declaring id as a plain int (not int | None) guarantees the client always gets a number back, never None.

BookReviewCreate is what clients send when creating a new review. It uses pass because it has the exact same fields as the base. You still define it as its own class so you can change it later without breaking the other models.

BookReviewUpdate is what clients send when updating a review. Every field is optional. This lets a client send just one field, like a new rating, without having to resend the entire record.

Step 2: Set Up the Database Engine

The engine is the connection to your SQLite database. Add this below the models:

sqlite_file_name = "reviews.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

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

create_engine takes a connection string. A connection string is like a home address for your database: it tells the engine what type of database to use and where to find it. sqlite:///reviews.db tells SQLModel to use a file called reviews.db in your current folder. SQLite creates that file automatically if it does not exist.

The check_same_thread: False argument lets FastAPI use the same database connection across different parts of a single request. Without this, SQLite raises an error because FastAPI processes parts of a request in different threads.

Step 3: Create Tables and a Session

A session is a temporary workspace. When FastAPI handles a request, it opens a session, does database work inside it, then closes it. Add these two functions:

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]

create_db_and_tables reads all your models with table=True and creates the matching tables in SQLite. You call this once at startup.

get_session opens a session, hands it to your route function using yield, then closes it cleanly after the response goes out. The with block ensures the session closes even if an error occurs.

SessionDep is a shorthand. Instead of writing session: Annotated[Session, Depends(get_session)] on every route, you write session: SessionDep. It means the same thing. FastAPI reads the Depends(get_session) and calls get_session automatically before running your route.

Step 4: Create the App and Run Table Setup on Startup

app = FastAPI()


@app.on_event("startup")
def on_startup():
    create_db_and_tables()

FastAPI() creates your application. The @app.on_event("startup") decorator tells FastAPI to run on_startup when the server starts. This creates the book_review table in reviews.db on first run.

Step 5: Create a Review (POST)

@app.post("/reviews/", response_model=BookReviewPublic)
def create_review(review: BookReviewCreate, session: SessionDep):
    db_review = BookReview.model_validate(review)
    session.add(db_review)
    session.commit()
    session.refresh(db_review)
    return db_review

The @app.post("/reviews/") decorator registers this function as the handler for POST requests to /reviews/.

response_model=BookReviewPublic tells FastAPI what shape the response should take. Even though db_review is a BookReview object (which has extra database internals), FastAPI filters it down to only the fields in BookReviewPublic before sending it to the client.

Inside the function:

BookReview.model_validate(review) converts the incoming BookReviewCreate data into a full BookReview database object.

session.add(db_review) stages the object for saving. Nothing writes to the database yet.

session.commit() writes the staged changes to the database. At this point, SQLite assigns the id.

session.refresh(db_review) reloads the object from the database so the id field gets populated in the Python object.

return db_review sends the review back to the client with its new id.

Step 6: Read All Reviews (GET)

@app.get("/reviews/", response_model=list[BookReviewPublic])
def read_reviews(
    session: SessionDep,
    offset: int = 0,
    limit: Annotated[int, Query(le=100)] = 10,
):
    reviews = session.exec(select(BookReview).offset(offset).limit(limit)).all()
    return reviews

select(BookReview) builds a SQL SELECT query for the book_review table. session.exec(...) runs it. .all() fetches every row as a list.

offset and limit are query parameters for pagination. A client calls /reviews/?offset=10&limit=5 to get reviews 11 through 15. The Query(le=100) constraint stops clients from requesting more than 100 at a time. Both have sensible defaults so a plain /reviews/ request works without any parameters.

Step 7: Read One Review (GET)

@app.get("/reviews/{review_id}", response_model=BookReviewPublic)
def read_review(review_id: int, session: SessionDep):
    review = session.get(BookReview, review_id)
    if not review:
        raise HTTPException(status_code=404, detail="Review not found")
    return review

The {review_id} in the path is a path parameter. FastAPI reads it from the URL, converts it to an int, and passes it to the function. A request to /reviews/3 sets review_id = 3.

session.get(BookReview, review_id) fetches the row with that id. If no row exists, it returns None.

raise HTTPException(status_code=404, detail="Review not found") sends a 404 response to the client with a clear error message. HTTP status code 404 means “not found.” FastAPI turns that into proper JSON automatically.

Step 8: Update a Review (PATCH)

@app.patch("/reviews/{review_id}", response_model=BookReviewPublic)
def update_review(review_id: int, review: BookReviewUpdate, session: SessionDep):
    review_db = session.get(BookReview, review_id)
    if not review_db:
        raise HTTPException(status_code=404, detail="Review not found")
    review_data = review.model_dump(exclude_unset=True)
    review_db.sqlmodel_update(review_data)
    session.add(review_db)
    session.commit()
    session.refresh(review_db)
    return review_db

PATCH is the HTTP method for partial updates. A client sends only the fields they want to change.

review.model_dump(exclude_unset=True) converts the incoming update data into a dictionary, but only includes fields the client actually sent. If the client only sent {"rating": 4}, this produces {"rating": 4}. Fields the client left out do not appear in the dictionary, so they do not overwrite existing data.

review_db.sqlmodel_update(review_data) applies those changes to the database object in memory.

Then you commit and refresh exactly as in the create route.

Step 9: Delete a Review (DELETE)

@app.delete("/reviews/{review_id}")
def delete_review(review_id: int, session: SessionDep):
    review = session.get(BookReview, review_id)
    if not review:
        raise HTTPException(status_code=404, detail="Review not found")
    session.delete(review)
    session.commit()
    return {"ok": True}

session.delete(review) marks the object for deletion. session.commit() removes it from the database. The function returns a simple JSON object confirming success.

The Complete File

Here is the entire main.py:

from typing import Annotated

from fastapi import Depends, FastAPI, HTTPException, Query
from sqlmodel import Field, Session, SQLModel, create_engine, select


class BookReviewBase(SQLModel):
    title: str
    author: str
    rating: int = Field(ge=1, le=5)
    review: str | None = None


class BookReview(BookReviewBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class BookReviewPublic(BookReviewBase):
    id: int


class BookReviewCreate(BookReviewBase):
    pass


class BookReviewUpdate(SQLModel):
    title: str | None = None
    author: str | None = None
    rating: int | None = Field(default=None, ge=1, le=5)
    review: str | None = None


sqlite_file_name = "reviews.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

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


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]

app = FastAPI()


@app.on_event("startup")
def on_startup():
    create_db_and_tables()


@app.post("/reviews/", response_model=BookReviewPublic)
def create_review(review: BookReviewCreate, session: SessionDep):
    db_review = BookReview.model_validate(review)
    session.add(db_review)
    session.commit()
    session.refresh(db_review)
    return db_review


@app.get("/reviews/", response_model=list[BookReviewPublic])
def read_reviews(
    session: SessionDep,
    offset: int = 0,
    limit: Annotated[int, Query(le=100)] = 10,
):
    reviews = session.exec(select(BookReview).offset(offset).limit(limit)).all()
    return reviews


@app.get("/reviews/{review_id}", response_model=BookReviewPublic)
def read_review(review_id: int, session: SessionDep):
    review = session.get(BookReview, review_id)
    if not review:
        raise HTTPException(status_code=404, detail="Review not found")
    return review


@app.patch("/reviews/{review_id}", response_model=BookReviewPublic)
def update_review(review_id: int, review: BookReviewUpdate, session: SessionDep):
    review_db = session.get(BookReview, review_id)
    if not review_db:
        raise HTTPException(status_code=404, detail="Review not found")
    review_data = review.model_dump(exclude_unset=True)
    review_db.sqlmodel_update(review_data)
    session.add(review_db)
    session.commit()
    session.refresh(review_db)
    return review_db


@app.delete("/reviews/{review_id}")
def delete_review(review_id: int, session: SessionDep):
    review = session.get(BookReview, review_id)
    if not review:
        raise HTTPException(status_code=404, detail="Review not found")
    session.delete(review)
    session.commit()
    return {"ok": True}

Run the API

fastapi dev main.py

You will see output like this:

INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)

Your API is live.

Test It in the Browser

Go to http://127.0.0.1:8000/docs in your browser. FastAPI generates an interactive documentation page from your code automatically. This page lists every route, shows the expected input and output shapes, and lets you send real requests from the browser.

Click “POST /reviews/”, then click “Try it out.” Paste this into the request body:

{
  "title": "Dune",
  "author": "Frank Herbert",
  "rating": 5,
  "review": "A masterpiece of world-building."
}

Click “Execute.” You get back something like:

{
  "title": "Dune",
  "author": "Frank Herbert",
  "rating": 5,
  "review": "A masterpiece of world-building.",
  "id": 1
}

The database assigned id: 1. Now try “GET /reviews/” and hit Execute. You get a list with that one review. Create a few more, then try the PATCH route to update one.

What Just Happened

You built a real API in about 70 lines of Python. Here is a summary of how all the pieces connect:

A client sends a POST request to /reviews/ with JSON in the body. FastAPI reads the BookReviewCreate type annotation and validates the incoming JSON against it. If the rating is 6 or the title is missing, FastAPI rejects the request before your code even runs.

Your route function receives a clean BookReviewCreate object. It converts that to a BookReview database object and hands it to the session. The session writes it to reviews.db. FastAPI then filters the response through BookReviewPublic and sends it back as JSON.

The same pattern repeats for every other route. The model classes handle the data contract. The session handles the database. FastAPI connects the two.

Where to Go Next

This API covers the fundamentals. From here, a few natural next steps:

Add authentication so not everyone can delete reviews. FastAPI has a full security tutorial covering OAuth2 and JWT tokens.

Move to PostgreSQL when you deploy. Swap sqlite:///reviews.db for a PostgreSQL connection string and everything else stays the same.

Split the code into multiple files as the project grows. FastAPI supports routers so you can put each group of routes in its own file.

Read the full SQLModel documentation at https://sqlmodel.tiangolo.com for more complex queries, relationships between tables, and migrations.

Similar Posts