Build a Book Review API with FastAPI and SQLModel: A Beginner’s Guide

Welcome! If you know some Python basics but have never built a web API or worked with databases, this tutorial is for you. We’ll create a simple API where users can post reviews about books, read them, update them, and delete them, all stored in a SQLite database. We’ll use FastAPI (a modern Python web framework) and SQLModel (a library made by the creator of FastAPI that combines SQLAlchemy and Pydantic to make database work easy).

By the end, you’ll have a working API and a solid understanding of the core concepts.

What You’ll Need

  • Python 3.7 or later installed on your computer.
  • Basic Python knowledge (variables, functions, loops, classes).
  • A code editor (VS Code, PyCharm, or even Notepad).

No prior web or database experience required!

Step 1: Set Up the Project

First, create a new folder for your project and open it in your terminal / command prompt.

1.1 Create a Virtual Environment

A virtual environment keeps your project’s dependencies isolated.

Windows:

python -m venv venv
venv\Scripts\activate

macOS / Linux:

python3 -m venv venv
source venv/bin/activate

You’ll see (venv) appear in your terminal prompt—that means the environment is active.

1.2 Install Required Packages

pip install fastapi sqlmodel "uvicorn[standard]"
  • FastAPI: The web framework we’ll use to build the API.
  • SQLModel: Helps us define database tables as Python classes and interact with the database easily.
  • Uvicorn: A server that runs our FastAPI application.

1.3 Create the Main Python File

Create a new file named main.py in your project folder. We’ll write all our code there.

Step 2: What is an API and What is FastAPI?

Understanding APIs

API stands for Application Programming Interface. Think of it as a waiter in a restaurant:

  • You (the client) ask the waiter for something (like “I want a Kebab”).
  • The waiter goes to the kitchen (the server/database), gets what you need, and brings it back.
  • You don’t need to know how the kitchen works, just what to ask for and what you’ll get back.

In web terms, an API exposes endpoints (URLs like /reviews) that you can send requests to (GET to read data, POST to create new data, PATCH to update data, etc.). The API responds with data, usually in JSON format.

Why FastAPI?

FastAPI is great for beginners because:

  • It’s fast to write and run.
  • It has automatic interactive documentation (we’ll see this later!).
  • It uses Python type hints to validate data automatically, preventing many errors.

Step 3: What is SQLModel and How Do We Use a Database?

Databases for Beginners

A database is like an organized digital filing cabinet. Instead of storing data in text files, we use a database to store, search, and update information efficiently.

We’ll use SQLite, which is a simple file-based database. No separate server needed and Python can talk to it directly.

SQLModel: The Bridge

SQLModel lets us define the structure of our data using Python classes. For example, a BookReview class will have attributes like id, book_title, review_text, and rating. SQLModel automatically creates the corresponding database table and helps us read/write records.

Step 4: Write the Foundation – Imports and Database Setup

Open main.py and add the following:

from typing import Optional
from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select

# Create the FastAPI "app" – the core of our API
app = FastAPI()

# Set up the SQLite database engine
sqlite_file_name = "book_reviews.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)  # echo=True shows SQL commands in console (helpful for learning)

# This function creates all tables defined with SQLModel
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)
  • FastAPI() initializes our app.
  • create_engine() connects to the SQLite file book_reviews.db (it will be created automatically).
  • SQLModel.metadata.create_all(engine) creates the actual database tables from our Python models.

Step 5: Define the Book Review Model

We’ll create a Python class that represents a book review. This class will be both a database table definition and a data validation schema.

Add this to main.py:

class BookReview(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    book_title: str
    review_text: str
    rating: int = Field(ge=1, le=5)  # rating must be between 1 and 5

Explanation:

  • table=True tells SQLModel this class corresponds to a database table.
  • id: An auto-incrementing primary key (each review gets a unique ID). Optional[int] means it can be None when creating a new review (the database will assign it).
  • book_title, review_text: Strings.
  • rating: Integer with validation ge=1, le=5 (greater or equal 1, less or equal 5).

Step 6: Add a Startup Event to Create Tables

FastAPI can run code when the server starts. Let’s use that to create our database tables automatically.

Add this after the create_db_and_tables function:

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

Now whenever you run the server, the database file and table will be ready.

Step 7: Implement CRUD Endpoints

CRUD stands for Create, Read, Update, Delete – the four basic operations for persistent storage.

7.1 Create a Review – POST /reviews/

A POST request is used to send new data to the server.

@app.post("/reviews/", response_model=BookReview)
def create_review(review: BookReview):
    with Session(engine) as session:
        session.add(review)
        session.commit()
        session.refresh(review)
        return review

What’s happening?

  • @app.post("/reviews/") tells FastAPI that this function handles POST requests to the /reviews/ URL.
  • review: BookReview means FastAPI will automatically parse the incoming JSON body into a BookReview object.
  • We open a session (like a conversation with the database).
  • session.add(review) stages the new review for insertion.
  • session.commit() saves it permanently.
  • session.refresh(review) reloads the object from the DB to get the auto-generated id.
  • Finally, we return the created review (which will be sent back to the client as JSON).

7.2 Read All Reviews – GET /reviews/

A GET request retrieves data without changing it.

@app.get("/reviews/", response_model=list[BookReview])
def read_reviews():
    with Session(engine) as session:
        reviews = session.exec(select(BookReview)).all()
        return reviews
  • select(BookReview) creates a SQL “SELECT * FROM bookreview” query.
  • session.exec(...).all() executes it and returns a list of all reviews.

7.3 Read a Single Review – GET /reviews/{review_id}

We can get a specific review by its ID, which is provided in the URL.

@app.get("/reviews/{review_id}", response_model=BookReview)
def read_review(review_id: int):
    with Session(engine) as session:
        review = session.get(BookReview, review_id)
        if not review:
            raise HTTPException(status_code=404, detail="Review not found")
        return review
  • {review_id} in the URL is a path parameter. FastAPI captures it and passes it as the review_id argument.
  • session.get(BookReview, review_id) fetches the record with that primary key.
  • If no review exists, we raise an HTTPException with status code 404 (Not Found).

7.4 Update a Review – PATCH /reviews/{review_id}

A PATCH request updates part of an existing resource. We’ll allow updating any field (book title, review text, or rating).

First, we need a model that makes all fields optional (so the client can send only what they want to change). Add this class near your BookReview model:

class BookReviewUpdate(SQLModel):
    book_title: Optional[str] = None
    review_text: Optional[str] = None
    rating: Optional[int] = Field(default=None, ge=1, le=5)

Now the endpoint:

@app.patch("/reviews/{review_id}", response_model=BookReview)
def update_review(review_id: int, review_update: BookReviewUpdate):
    with Session(engine) as session:
        db_review = session.get(BookReview, review_id)
        if not db_review:
            raise HTTPException(status_code=404, detail="Review not found")

        # Get the data sent by client, ignoring unset values
        update_data = review_update.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_review, key, value)

        session.add(db_review)
        session.commit()
        session.refresh(db_review)
        return db_review
  • We retrieve the existing review from the DB.
  • review_update.dict(exclude_unset=True) gives a dictionary of only the fields the client actually sent.
  • We update the database object’s attributes using setattr.
  • We commit and refresh, then return the updated review.

7.5 Delete a Review – DELETE /reviews/{review_id}

A DELETE request removes a resource.

@app.delete("/reviews/{review_id}")
def delete_review(review_id: int):
    with Session(engine) as session:
        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}
  • Find the review, delete it, commit.
  • Return a simple confirmation message.

Step 8: Run the API and Explore the Interactive Docs

Your complete main.py should look like this. Save the file.

from typing import Optional
from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select

# ------------------ Models ------------------
class BookReview(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    book_title: str
    review_text: str
    rating: int = Field(ge=1, le=5)

class BookReviewUpdate(SQLModel):
    book_title: Optional[str] = None
    review_text: Optional[str] = None
    rating: Optional[int] = Field(default=None, ge=1, le=5)

# ------------------ Database Setup ------------------
sqlite_file_name = "book_reviews.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)

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

# ------------------ FastAPI App ------------------
app = FastAPI()

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

# ------------------ CRUD Endpoints ------------------
@app.post("/reviews/", response_model=BookReview)
def create_review(review: BookReview):
    with Session(engine) as session:
        session.add(review)
        session.commit()
        session.refresh(review)
        return review

@app.get("/reviews/", response_model=list[BookReview])
def read_reviews():
    with Session(engine) as session:
        reviews = session.exec(select(BookReview)).all()
        return reviews

@app.get("/reviews/{review_id}", response_model=BookReview)
def read_review(review_id: int):
    with Session(engine) as session:
        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=BookReview)
def update_review(review_id: int, review_update: BookReviewUpdate):
    with Session(engine) as session:
        db_review = session.get(BookReview, review_id)
        if not db_review:
            raise HTTPException(status_code=404, detail="Review not found")
        update_data = review_update.dict(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_review, key, value)
        session.add(db_review)
        session.commit()
        session.refresh(db_review)
        return db_review

@app.delete("/reviews/{review_id}")
def delete_review(review_id: int):
    with Session(engine) as session:
        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}

In your terminal (with the virtual environment active), run:

uvicorn main:app --reload
  • main:app means “look in main.py for the variable app“.
  • --reload makes the server restart automatically when you change code (great for development).

You’ll see output like:

INFO:     Uvicorn running on http://127.0.0.1:8000

Open your browser and go to:

  • Interactive API docs: http://127.0.0.1:8000/docs
  • Alternative docs: http://127.0.0.1:8000/redoc

You should see something like this:

FastAPI Tutorial 2026

FastAPI automatically generates this beautiful UI where you can test every endpoint right from your browser! Try:

  • POST /reviews/ with some JSON data.
  • GET /reviews/ to see all reviews.
  • PATCH /reviews/1 to update.
  • DELETE /reviews/1 to remove.

Conclusion

Congratulations! You’ve built a fully functional web API with a database backend—using only Python and two powerful libraries. You learned:

  • What an API is and how FastAPI helps build one.
  • How to define database tables with SQLModel.
  • How to perform CRUD operations with sessions.
  • How to test your API using automatically generated documentation.

From here, you can expand the project by adding more fields, connecting a frontend, or deploying it online. The fundamentals you learned today will serve you well in any web development journey.

Happy coding! 🚀