Build a Bookshelf REST API with FastAPI

Build a CRUD REST API with FastAPI, SQLModel, and SQLite.

Introduction

30 Second Summary

Every app that stores information needs a way to create, read, update, and delete it. The structured interface behind this is called a REST API. Whether it's a reading list, a recipe collection, or a product catalog, the pattern is the same.

In this project, you will build a personal bookshelf REST API using FastAPI and SQLModel. You'll define data models, write endpoints for full CRUD operations, persist data to a SQLite database, and explore auto-generated interactive documentation.

What You'll Build

You run a single command, open your browser, and land on a live interactive docs page where you can test every endpoint in real time. You add a book, try sneaking in a negative page count, and watch the API reject it instantly.

By the end of this project, you'll have:

  • A running API where you can POST a new book and watch FastAPI automatically reject invalid data with a clear error message before it ever touches the database.
  • Full CRUD endpoints that let you browse, filter by author, update individual fields, and delete books with proper HTTP status codes and error handling.
  • A persistent SQLite database powered by SQLModel, so your books survive server restarts and you can demo the API confidently.
  • Live Swagger UI documentation generated automatically from your Python type hints, with a "Try it out" button on every endpoint.
  • Secret Mission: Add pagination and sorting to your book list endpoint so it handles real-world data volumes.

Are there any prerequisites?

You should be comfortable with Python basics: functions, classes, and type hints. No prior experience with FastAPI, REST APIs, or databases is needed.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Python and Your FastAPI Project

Before you can build an API, you need the tools to write and run Python code.

In this step, you will install Python, set up a dedicated project folder with its own isolated environment, and get FastAPI running with a single endpoint you can hit in your browser.

In this step, get ready to:
  • Install Python 3.13 and verify it works.
  • Create a project folder with a virtual environment and install FastAPI and SQLModel.
  • Write and run your first FastAPI endpoint.
Install Python 3.13

FastAPI requires Python 3.10 or higher. This project uses Python 3.13, the latest stable release.

  • Open your browser and go to the Python downloads page for Windows.
  • Download the Python 3.13.14 Windows installer (64-bit).
  • Run the installer. On the first screen, check the box that says Add Python to PATH.
  • Click Install Now and wait for the installation to complete.

Why check Add Python to PATH?

Adding Python to your system PATH means you can run python from any terminal window. Without this, your computer would not know where to find the Python program when you type the command.

Now confirm the installation worked.

  • Switch back to VS Code.
  • Open a new terminal by pressing Ctrl+` (backtick key, usually above Tab).
  • Check your Python version by running this command:
python --version

✔️ I see version 3.13

Python is installed and ready to go. Move on to the next substep.

ⓧ I see an older version

Your system has an older Python that is taking priority on your PATH. Download and run the Python 3.13.14 installer from the Windows downloads page.

  • Make sure you check Add Python to PATH during installation.
  • Close your current VS Code terminal and open a fresh one with Ctrl+` so the updated PATH takes effect.
  • Run python --version again. You should now see 3.13.

ⓧ Command not found

Python is not installed yet. Download the Python 3.13.14 Windows installer (64-bit) from the official downloads page.

  • Run the installer and check Add Python to PATH on the first screen.
  • Click Install Now.
  • Close your VS Code terminal and open a fresh one with Ctrl+` so the new PATH loads.
  • Run python --version again. You should now see 3.13.
Create your project folder and install dependencies

A virtual environment keeps this project's packages separate from your system Python. This prevents version conflicts between different projects.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir bookshelf-api
cd bookshelf-api
  • Open this folder in VS Code. In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the bookshelf-api folder, and click Select Folder.
  • Open a new terminal in VS Code by pressing Ctrl+`.

Your terminal should now show that you are inside the bookshelf-api folder.

  • Create a virtual environment by running this command:
python -m venv venv
  • Activate the virtual environment by running:
venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt. This confirms the virtual environment is active.

Don't see (venv) in your prompt?

If you get an error about execution policies, PowerShell is blocking the activation script. Run this command first, then try activating again:

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

help me fix my virtual environment activation

  • Install FastAPI and SQLModel by running:
pip install "fastapi[standard]" sqlmodel

You will see pip downloading and installing several packages. This takes a minute or two.

What are these packages?

FastAPI is the web framework you will use to build your API. The [standard] extra installs the development server and other tools you need to run it.

SQLModel is a library that combines data validation with database access in a single model class. You will use it in a later step to persist books to a database.

  • Confirm FastAPI installed correctly by running:
fastapi --version

You should see a version number printed. This confirms the FastAPI CLI is ready to use.

Seeing command not found?

Make sure your virtual environment is active. You should see (venv) in your terminal prompt. If not, run venv\Scripts\activate again, then retry the command.

help me get the fastapi command working

Create and run your first endpoint

With everything installed, you can now write a minimal FastAPI app and see it respond in your browser.

  • Create a new file called main.py in your bookshelf-api folder. In VS Code, click the New File icon in the Explorer sidebar.
  • Name the file main.py and press Enter.
  • Add this code to main.py:
# Import the FastAPI framework
from fastapi import FastAPI

# Create the application instance
app = FastAPI()


# Define a route that responds to GET requests at /
@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}

What does this code do?

  • app = FastAPI() creates your API application. Every endpoint you build attaches to this object.
  • @app.get("/") is a decorator that tells FastAPI to call the function below whenever someone sends a GET request to the root URL.
  • The function returns a Python dictionary, and FastAPI automatically converts it to a JSON response.
  • Save main.py by pressing Ctrl+S.

Before you run the server, what do you think will happen when you visit the root URL in your browser?

  • Start the FastAPI development server by running this command in your terminal:
fastapi dev main.py

What does fastapi dev do?

This command starts a local development server that automatically detects your app object in main.py. It enables auto-reload, meaning the server restarts itself whenever you save changes to your code.

You should see output in your terminal showing the server started on http://127.0.0.1:8000.

  • Open your browser and navigate to http://127.0.0.1:8000.

You should see the JSON response: {"message": "Hello, Bookshelf!"}. Your first FastAPI endpoint is live.

Browser showing an error or nothing?

  • Check that your terminal still shows the server running. If you see an error there, read the message for a typo hint.
  • Make sure you saved main.py before running the server.
  • Confirm the URL is exactly http://127.0.0.1:8000 with no trailing slash or extra characters.

help me get my FastAPI server running

✔️ Awesome, I've got everything!

Great. Double check you have saved main.py and that your server is still running in the terminal.

ⓧ I'd like to double check the full code

Here is the complete main.py file you should have at this point:

from fastapi import FastAPI

app = FastAPI()


@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}

Your environment is ready and your first endpoint is responding. Next up, you will explore FastAPI's automatic interactive documentation and see why this framework makes API development so fast.

Explore the Auto-Generated API Docs

Your server is running and returning JSON at the root URL. Now it is time to discover the feature that makes FastAPI stand apart from other Python web frameworks.

FastAPI automatically generates interactive API documentation from your Python code. You do not write a single line of config to get it. In this step, you will explore those docs, add new endpoints, and watch the documentation update itself in real time.

In this step, get ready to:
  • Discover the live Swagger UI docs page and execute a request from your browser.
  • Add a second endpoint and watch the docs auto-update on save.
  • Add a path parameter endpoint and observe automatic type validation.
Discover the Swagger UI docs page

FastAPI ships with a built-in documentation page powered by Swagger UI. It reads your endpoint definitions and generates a visual interface where you can test every route without leaving the browser.

  • With your server still running from Step 1, open a new browser tab.
  • Navigate to http://127.0.0.1:8000/docs in the address bar.

You should see a page titled FastAPI with your GET / endpoint listed.

  • Click the GET / row to expand it.
  • Click the Try it out button on the right.
  • Click Execute.

You should see a response body of {"message": "Hello, Bookshelf!"} along with the response headers and a 200 status code.

What is Swagger UI?

Swagger UI is an open-source tool that renders OpenAPI specifications as interactive web pages. FastAPI generates the OpenAPI spec from your Python type hints and route decorators automatically.

This means your documentation is always in sync with your code. Change a function signature, save, and the docs page reflects it instantly.

Don't see the docs page?

Make sure your server is still running in the VS Code terminal. If you accidentally closed it, switch back to the terminal and run fastapi dev main.py again.

Double check the URL is exactly http://127.0.0.1:8000/docs with no trailing spaces or typos.

help me troubleshoot why my FastAPI docs page is not loading at /docs

Add a books endpoint and watch the docs update

The real magic of auto-generated docs shows up when you add new endpoints. You will add a route that returns a hardcoded list of books, save the file, and watch the docs page pick it up without restarting anything.

  • In main.py, add the following code below your existing read_root function:
@app.get("/books/")
def read_books_hardcoded():
    # Return a hardcoded list of books for now
    return [
        {"title": "1984", "author": "George Orwell"},
        {"title": "Brave New World", "author": "Aldous Huxley"},
    ]

What does this code do?

  • @app.get("/books/") registers a new GET endpoint at the /books/ path.
  • The function returns a plain Python list of dictionaries. FastAPI automatically converts it to a JSON array in the response.
  • Save main.py.
  • Check your terminal. You should see a message indicating the server detected the change and reloaded.
  • Switch back to your browser and refresh the /docs page.

You should now see two endpoints listed: GET / and GET /books/. Click Try it out on the new /books/ endpoint and execute it to confirm you see the two books in the response.

Only seeing the original endpoint?

Make sure you saved the file. The auto-reload only triggers on save.

Check that the new function is outside the read_root function, not indented inside it. Both functions should be at the same indentation level.

help me figure out why my new /books/ endpoint is not showing up in FastAPI docs

Add a path parameter endpoint and test type validation

Path parameters let you capture values from the URL itself. When you add a type hint to the parameter, FastAPI validates incoming requests against that type before your function even runs.

  • In main.py, add the following code below your read_books_hardcoded function:
@app.get("/books/{book_id}")
def read_book_by_id(book_id: int):
    # FastAPI validates that book_id is an integer from the URL path
    return {"book_id": book_id}

What does this code do?

  • {book_id} in the path tells FastAPI to capture that part of the URL as a variable.
  • book_id: int is a Python type hint. FastAPI uses it to automatically validate that the value is a valid integer.
  • If someone passes a non-integer value, FastAPI rejects the request before your function code executes.
  • Save main.py.
  • Refresh the /docs page in your browser.

You should now see three endpoints listed. First, test the happy path.

  • Click Try it out on the GET /books/{book_id} endpoint.
  • Enter 1 in the book_id field and click Execute.

You should see {"book_id": 1} in the response with a 200 status code.

Now for the interesting part. What do you think will happen if you pass the word hello instead of a number?

  • Clear the book_id field and type hello instead.
  • Click Execute.

FastAPI returns a 422 Validation Error response. The error message tells you the input should be a valid integer. You did not write any validation logic for this. The int type hint was enough.

Why does this matter?

In frameworks like Flask, you would need to manually check whether book_id is a valid integer and return an error yourself. FastAPI handles this automatically because it reads your Python type hints at startup and builds validation rules from them.

This pattern scales to complex request bodies with nested objects, optional fields, and custom constraints. You will see this in the next step.

Not seeing a 422 error?

Make sure you typed a non-numeric string like hello in the book_id field. If you typed a number like 0 or 999, that is still a valid integer so it will succeed.

Check that your function signature has the type hint book_id: int and not just book_id without a type.

help me understand why I am not getting a 422 validation error when passing a string to my FastAPI path parameter

✔️ Awesome, I've got everything!

Great. Double check you have saved main.py and that all three endpoints appear in your Swagger UI.

ⓧ I'd like to double check the full code

Here is the complete main.py at this point:

from fastapi import FastAPI

app = FastAPI()


@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}


@app.get("/books/")
def read_books_hardcoded():
    return [
        {"title": "1984", "author": "George Orwell"},
        {"title": "Brave New World", "author": "Aldous Huxley"},
    ]


@app.get("/books/{book_id}")
def read_book_by_id(book_id: int):
    return {"book_id": book_id}

You have seen FastAPI's auto-generated docs in action. Next up, you will define proper data models and build full CRUD endpoints so your bookshelf can actually store and manage books.

Define Data Models and Build CRUD Endpoints

Your bookshelf API currently returns hardcoded book data. That works for a demo, but a real API needs to accept data from clients, validate it automatically, and return structured responses.

In this step, you'll define Pydantic models that enforce the exact shape of every request and response. Then you'll build full CRUD endpoints (Create, Read, Update, Delete) backed by an in-memory list.

In this step, get ready to:
  • Define data models for books with automatic input validation.
  • Build Create, Read, Update, and Delete endpoints.
  • Test the full CRUD cycle and discover the limits of in-memory storage.
Define the book data models

Pydantic models define the exact shape of data your API accepts and returns. FastAPI uses these models to automatically reject requests that don't match the expected structure.

  • Select all the code in main.py and delete it.
  • Replace it with the following imports, models, and storage setup:
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel, Field
from typing import Optional

app = FastAPI()

# Book model - the full representation of a book
class Book(BaseModel):
    id: int
    title: str
    author: str
    year: int
    pages: int = Field(gt=0)

# BookCreate - what the client sends when adding a book (no id)
class BookCreate(BaseModel):
    title: str
    author: str
    year: int
    pages: int = Field(gt=0)

# BookUpdate - all fields optional for partial updates
class BookUpdate(BaseModel):
    title: Optional[str] = None
    author: Optional[str] = None
    year: Optional[int] = None
    pages: Optional[int] = Field(default=None, gt=0)

books: list[Book] = []  # In-memory storage
next_id: int = 1  # Auto-incrementing ID counter

What does this code do?

  • Book is the full representation of a book, including its id. This is what the API returns to clients.
  • BookCreate is what clients send when adding a new book. It excludes id because the server assigns that.
  • BookUpdate makes every field optional so clients can update just one field at a time without resending the whole object.
  • Field(gt=0) tells Pydantic to reject any pages value that isn't greater than zero.
  • Save main.py.
  • Check your terminal. The server should auto-reload and show INFO: Uvicorn running on http://127.0.0.1:8000 with no errors.

Seeing an import error?

  • Make sure your virtual environment is still activated. You should see (venv) at the start of your terminal prompt.
  • Check that you imported HTTPException from fastapi and BaseModel from pydantic on separate import lines.
  • Help me fix my import error in main.py
Add the create and list endpoints

Now you'll add the endpoints that actually create and retrieve books. Each endpoint declares a response_model so FastAPI knows exactly what shape to return.

  • Add the following code below your next_id line in main.py:
@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}

# Create a new book and assign it an auto-incremented ID
@app.post("/books/", response_model=Book, status_code=201)
def create_book(book: BookCreate):
    global next_id
    new_book = Book(id=next_id, **book.model_dump())
    books.append(new_book)
    next_id += 1
    return new_book

# Return all books in the collection
@app.get("/books/", response_model=list[Book])
def read_books():
    return books

What does this code do?

  • create_book accepts a BookCreate body, converts it to a full Book with an auto-assigned id, and appends it to the list.
  • response_model=Book tells FastAPI to validate the response shape and generate accurate docs.
  • status_code=201 returns HTTP 201 Created instead of the default 200, which is the correct code for resource creation.
  • model_dump() converts the Pydantic model to a dictionary so you can spread it into the Book constructor.
  • Save main.py.
  • Open http://127.0.0.1:8000/docs in your browser and refresh the page.
  • Click on the POST /books/ endpoint, then click Try it out.
  • Replace the example JSON body with this and click Execute:
{
  "title": "1984",
  "author": "George Orwell",
  "year": 1949,
  "pages": 328
}

You should see a 201 response with your book returned, now including an id of 1.

Seeing a 422 or 500 error?

  • A 422 means your JSON body doesn't match the BookCreate model. Check that all four fields (title, author, year, pages) are present and correctly typed.
  • A 500 error means something crashed in your code. Check the terminal for the traceback.
  • Help me debug my POST /books/ endpoint
Add the single-book lookup and delete endpoints

Your API can create and list books. Now you need endpoints to fetch a single book by its ID and to remove books from the collection.

  • Add the following code below your read_books function in main.py:
# Look up a single book by its ID
@app.get("/books/{book_id}", response_model=Book)
def read_book(book_id: int):
    for book in books:
        if book.id == book_id:
            return book
    raise HTTPException(status_code=404, detail="Book not found")

# Remove a book from the collection
@app.delete("/books/{book_id}")
def delete_book(book_id: int):
    for i, book in enumerate(books):
        if book.id == book_id:
            books.pop(i)
            return {"detail": "Book deleted"}
    raise HTTPException(status_code=404, detail="Book not found")

What does this code do?

  • read_book loops through the books list looking for a matching id. If none is found, it raises an HTTPException with status code 404.
  • delete_book uses enumerate() to track the index so it can pop the matching book from the list.
  • HTTPException is FastAPI's built-in way to return error responses with proper HTTP status codes.
  • Save main.py and refresh /docs in your browser.
  • Click on GET /books/{book_id}, click Try it out, enter 1 for book_id, and click Execute.
  • Try entering 999 for book_id. You should see a 404 response with "Book not found".

Getting a 404 for book ID 1?

  • If you restarted the server since you last created a book, your in-memory list is empty. POST a new book first, then try GET with ID 1 again.
  • Help me understand why my book lookup returns 404
Add the update endpoint and test the full cycle

The last CRUD operation is updating. A PATCH endpoint lets clients send only the fields they want to change, leaving everything else untouched.

  • Add the following code below your delete_book function in main.py:
# Update only the fields the client provides
@app.patch("/books/{book_id}", response_model=Book)
def update_book(book_id: int, book_update: BookUpdate):
    for i, book in enumerate(books):
        if book.id == book_id:
            update_data = book_update.model_dump(exclude_unset=True)
            updated_book = book.model_copy(update=update_data)
            books[i] = updated_book
            return updated_book
    raise HTTPException(status_code=404, detail="Book not found")

What does this code do?

  • model_dump(exclude_unset=True) returns only the fields the client actually sent, ignoring any fields left at their default None value.
  • model_copy(update=update_data) creates a new copy of the book with the specified fields overwritten. This is Pydantic's safe way to update models without mutating the original.
  • Save main.py and refresh /docs.
  • Test the full CRUD cycle. POST two books with different titles and authors.
  • GET /books/ to see both books listed.
  • PATCH /books/1 with a new title (send only {"title": "Animal Farm"}). Confirm only the title changed.
  • DELETE /books/2 and then GET /books/ to confirm only one book remains.

Now test the validation. What do you think happens if you try to create a book with a negative page count?

  • In Swagger UI, click POST /books/ and try submitting this body:
{
  "title": "Bad Book",
  "author": "Test Author",
  "year": 2024,
  "pages": -50
}

FastAPI returns a 422 Validation Error, rejecting the invalid data. You never wrote any validation logic. The Field(gt=0) constraint in your model handled it automatically.

Not seeing the 422 error?

  • Make sure you're sending a negative number for pages, not a string like "-50". JSON numbers have no quotes around them.
  • Check that your BookCreate model has pages: int = Field(gt=0) with the gt=0 constraint.
  • Help me understand why my validation isn't catching negative page counts

Your CRUD is fully working. But here is a question: where are your books actually stored right now?

  • Stop the server by pressing Ctrl+C in your terminal.
  • Restart the server by running:
fastapi dev main.py
  • Open /docs again and try GET /books/.

You get back an empty list. Every book you created is gone.

Why did my books disappear?

Your books lived in a Python list variable. That variable exists only in the server process's memory. When you stopped the server with Ctrl+C, the process died and the memory was freed.

This is the fundamental limitation of in-memory storage. The next step fixes this by adding a real database that persists data to a file on disk.

✔️ Awesome, I've got everything!

Great. Double check you've saved main.py and your server is running.

ⓧ I'd like to double check the full code

Your main.py should have these sections in order. Scroll up and confirm each one is present:

  • Imports from fastapi, pydantic, and typing.
  • app = FastAPI() initialization.
  • Three model classes: Book, BookCreate, BookUpdate.
  • books list and next_id counter.
  • read_root endpoint (GET /).
  • create_book endpoint (POST /books/).
  • read_books endpoint (GET /books/).
  • read_book endpoint (GET /books/{book_id}).
  • delete_book endpoint (DELETE /books/{book_id}).
  • update_book endpoint (PATCH /books/{book_id}).

Your API works perfectly while the server is running. Next up, you'll add a real database so your books survive restarts.

Persist Data with SQLModel and SQLite

In the last step, you built a full CRUD API and it worked perfectly. Then you restarted the server and your books vanished. That is the problem with storing data in a Python list.

In this step, you will replace the in-memory storage with a real SQLite database using SQLModel. SQLModel lets you define a single class that serves as both a Pydantic validation model and a database table. No more duplicating your data definitions.

In this step, get ready to:
  • Replace Pydantic models with SQLModel classes that double as database tables.
  • Connect to a SQLite database and create tables on startup.
  • Rewrite all CRUD endpoints to persist data through database sessions.
Replace the Pydantic models with SQLModel classes

SQLModel classes look almost identical to Pydantic models. The key difference is that one class (with table=True) maps directly to a database table.

  • Switch to the terminal where your server is running and press Ctrl+C to stop it.
  • Open main.py in VS Code, select all the content (Ctrl+A on Windows, Cmd+A on macOS), and delete it.
  • Paste the following imports and model definitions into the empty file:
from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select
from typing import Optional

# --- Models ---

# Shared fields that every book model uses
class BookBase(SQLModel):
    title: str
    author: str
    year: int
    pages: int = Field(gt=0)

# The actual database table (table=True maps this to a SQL table)
class Book(BookBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

# Used for incoming POST requests (no id needed)
class BookCreate(BookBase):
    pass

# Used for PATCH requests (all fields optional)
class BookUpdate(SQLModel):
    title: str | None = None
    author: str | None = None
    year: int | None = None
    pages: int | None = Field(default=None, gt=0)

# Controls what the API returns (includes id)
class BookPublic(BookBase):
    id: int

What do these models do?

  • BookBase holds the shared fields. Other models inherit from it so you only define title, author, year, and pages once.
  • Book has table=True, which tells SQLModel to create a real database table from this class. The id field is auto-generated by the database.
  • BookPublic is the response model. It controls what data the API sends back to the client, including the id.

Why this model hierarchy instead of one class?

Each model has a specific job. BookCreate ensures incoming data never includes an id (the database assigns it). BookUpdate makes every field optional so clients can send partial updates. BookPublic guarantees the response always includes the id.

This separation is the standard pattern in FastAPI codebases. It keeps validation tight and prevents accidental data leaks.

  • Save main.py.
  • Now add the database configuration below the models by pasting this code at the bottom of main.py:
# --- Database ---

# SQLite stores data in a local file called bookshelf.db
sqlite_url = "sqlite:///bookshelf.db"
# Required for SQLite with FastAPI's threaded request handling
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)

What does this database setup do?

  • sqlite_url points to a file called bookshelf.db in your project folder. SQLite stores the entire database in this single file.
  • check_same_thread: False is required because FastAPI handles requests on multiple threads, but SQLite normally restricts access to one thread.
  • create_db_and_tables() reads your model classes and creates the matching SQL tables if they do not already exist.
  • Save main.py.
  • Now add the application setup, startup event, root endpoint, and POST endpoint below the database section by pasting this code at the bottom of main.py:
# --- App ---

app = FastAPI()

# Create database tables when the server starts
@app.on_event("startup")
def on_startup():
    create_db_and_tables()

@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}

@app.post("/books/", response_model=BookPublic, status_code=201)
def create_book(book: BookCreate):
    with Session(engine) as session:
        # Convert the incoming data to a database model
        db_book = Book.model_validate(book)
        session.add(db_book)
        session.commit()
        session.refresh(db_book)
        return db_book

What does this code do?

  • @app.on_event("startup") runs create_db_and_tables() once when the server starts. This ensures the book table exists before any requests come in.
  • with Session(engine) as session opens a database connection for one request and automatically closes it when done.
  • Book.model_validate(book) converts the incoming BookCreate data into a Book table object that can be saved to the database.
  • session.add(), session.commit(), session.refresh() is the standard pattern: stage the new row, write it to disk, then reload it so the auto-generated id is available.
  • Save main.py.
  • Start the server by running this command in your terminal:
fastapi dev main.py
  • Open http://127.0.0.1:8000 in your browser. You should see {"message": "Hello, Bookshelf!"}.
  • Navigate to http://127.0.0.1:8000/docs and click Try it out on the POST /books/ endpoint. Replace the request body with this JSON and click Execute:
{
  "title": "1984",
  "author": "George Orwell",
  "year": 1949,
  "pages": 328
}
  • You should see a 201 response with your book data including an auto-generated id of 1.

Seeing an error when the server starts?

  • Check that you have from sqlmodel import Field, Session, SQLModel, create_engine, select at the top. A missing import causes NameError.
  • Make sure app = FastAPI() appears after the database section. FastAPI dev needs to find the app object.
  • Confirm your virtual environment is active. You should see (venv) in your terminal prompt.

Why isn't my FastAPI server starting after switching to SQLModel?

Add the read endpoints
  • Add the GET endpoint for listing all books by pasting this below the create_book function in main.py:
@app.get("/books/", response_model=list[BookPublic])
def read_books():
    with Session(engine) as session:
        books = session.exec(select(Book)).all()
        return books

What does this endpoint do?

session.exec(select(Book)).all() runs a SQL SELECT query that fetches every row from the book table and returns them as a Python list. The response_model=list[BookPublic] ensures each book in the response includes its id.

  • Save main.py and refresh http://127.0.0.1:8000/docs.
  • Click Try it out on GET /books/ and click Execute. You should see the book you created earlier in the response.
  • Add the GET endpoint for fetching a single book by ID. Paste this below the read_books function:
@app.get("/books/{book_id}", response_model=BookPublic)
def read_book(book_id: int):
    with Session(engine) as session:
        book = session.get(Book, book_id)
        if not book:
            raise HTTPException(status_code=404, detail="Book not found")
        return book

What does this endpoint do?

session.get(Book, book_id) fetches a single row by its primary key. If the ID does not exist, it returns None, and the endpoint raises a 404 error with a clear message.

  • Save main.py and refresh the docs page.
  • Test GET /books/1 in Swagger UI. You should see your book returned.
  • Try GET /books/999. You should see a 404 response with "Book not found".

GET /books/ returning an empty list?

  • If you restarted the server without creating a book first, the database might be empty. POST a new book via the docs page, then try GET again.
  • Check that select(Book) uses the Book table class (capital B), not BookPublic or BookBase.

Why is my GET /books/ endpoint returning an empty list?

Rewrite PATCH and DELETE, then test persistence
  • Add the PATCH endpoint for updating a book. Paste this below the read_book function:
@app.patch("/books/{book_id}", response_model=BookPublic)
def update_book(book_id: int, book_update: BookUpdate):
    with Session(engine) as session:
        book = session.get(Book, book_id)
        if not book:
            raise HTTPException(status_code=404, detail="Book not found")
        # Update only the fields the client sent
        book.sqlmodel_update(book_update.model_dump(exclude_unset=True))
        session.add(book)
        session.commit()
        session.refresh(book)
        return book

What does this endpoint do?

model_dump(exclude_unset=True) returns only the fields the client actually sent, ignoring anything left as None. Then sqlmodel_update() applies those changes to the database object in place. This is how you do true partial updates without overwriting fields the client did not mention.

  • Save main.py and test PATCH in the docs page. Update the title of book 1 by sending {"title": "Animal Farm"}. You should see the title change while author, year, and pages stay the same.
  • Add the DELETE endpoint. Paste this below the update_book function:
@app.delete("/books/{book_id}")
def delete_book(book_id: int):
    with Session(engine) as session:
        book = session.get(Book, book_id)
        if not book:
            raise HTTPException(status_code=404, detail="Book not found")
        session.delete(book)
        session.commit()
        return {"detail": "Book deleted"}

What does this endpoint do?

session.delete(book) removes the row from the database. After session.commit(), the book is permanently gone. The endpoint returns a confirmation message so the client knows the operation succeeded.

  • Save main.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved main.py and the server has reloaded without errors.

ⓧ I'd like to double check the full code

Scroll up through your main.py and confirm these sections are present in this order:

  • Imports (fastapi, sqlmodel, typing).
  • Models section: BookBase, Book, BookCreate, BookUpdate, BookPublic.
  • Database section: sqlite_url, connect_args, engine, create_db_and_tables().
  • App section: app = FastAPI(), startup event, root endpoint.
  • CRUD endpoints: create_book, read_books, read_book, update_book, delete_book.

Now for the moment of truth. Before you ran into the problem of books disappearing on restart. Do you think the data will survive this time?

  • In Swagger UI, POST a new book with this JSON:
{
  "title": "Brave New World",
  "author": "Aldous Huxley",
  "year": 1932,
  "pages": 311
}
  • Switch to your terminal and press Ctrl+C to stop the server.
  • Restart the server by running:
fastapi dev main.py
  • Go back to http://127.0.0.1:8000/docs and execute GET /books/.

Your book is still there. The data survived the restart because it is now stored in bookshelf.db, not in a Python variable that dies with the process.

Where is the database file?

Look in your project folder in the VS Code Explorer sidebar. You should see a new file called bookshelf.db. This single file contains your entire database. You can delete it anytime to start fresh.

Books still disappearing after restart?

  • Make sure you are using Session(engine) in your endpoints, not the old in-memory books list. If the list still exists in your file, delete it.
  • Confirm you have the @app.on_event("startup") function that calls create_db_and_tables(). Without it, the table never gets created.
  • Check that bookshelf.db exists in your project folder. If it is missing, the engine URL might be wrong.

Help me figure out why my books are not persisting after a server restart with SQLModel and SQLite.

Your bookshelf now remembers its books permanently. Next up, you will clean up the endpoint code with FastAPI's dependency injection system and add filtering so you can search books by author.

Add Dependency Injection and Query Filtering

In the last step, you gave your API a real database. But look at the code. Every single endpoint repeats the same with Session(engine) as session: boilerplate.

FastAPI's dependency injection system eliminates that duplication. You'll define one function that provides database sessions, and FastAPI calls it automatically for every request. This is the production pattern employers look for in interviews.

In this step, get ready to:
  • Create a reusable database session dependency.
  • Add author filtering to the book list endpoint.
  • Test the complete CRUD flow with filtering in Swagger UI.
Create a reusable session dependency

Right now, five endpoints each create their own database session. If you ever need to change how sessions work (add logging, swap databases), you'd have to update all five places.

A dependency function centralises that logic in one spot. FastAPI's Depends decorator tells each endpoint to call your function automatically.

  • In main.py, find the import lines at the top of the file:
from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select
from typing import Optional
  • Replace all three import lines with these updated imports:
from typing import Annotated

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

What changed in the imports?

  • Annotated lets you attach metadata (like a dependency) to a type hint.
  • Depends is FastAPI's dependency injection mechanism.
  • Query gives you control over query parameter validation.
  • The unused Optional import is removed since you'll use str | None syntax instead.
  • Find the create_db_and_tables function in the # --- Database --- section. Add the following code directly below it:
def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]

What does this code do?

get_session is a generator function (note the yield instead of return). It opens a session, hands it to your endpoint, then automatically closes it after the response is sent.

SessionDep is a type alias that bundles the type (Session) with the dependency (Depends(get_session)). Now any endpoint can get a managed session by declaring session: SessionDep as a parameter.

Why dependency injection instead of inline sessions?

Inline with Session(engine) works, but it ties every endpoint to a specific engine and session pattern. With dependency injection, you change get_session once and every endpoint benefits.

This also makes testing easier. In a test suite, you swap get_session with a function that uses a test database. No endpoint code changes at all.

Now you need to refactor each endpoint to use this new dependency instead of creating sessions inline.

  • Find the create_book function. Replace the entire function (from the @app.post decorator to the return statement) with this version:
@app.post("/books/", response_model=BookPublic, status_code=201)
def create_book(book: BookCreate, session: SessionDep):
    db_book = Book.model_validate(book)
    session.add(db_book)
    session.commit()
    session.refresh(db_book)
    return db_book

What changed?

The with Session(engine) as session: wrapper is gone. Instead, session: SessionDep appears as a function parameter. FastAPI sees the Depends metadata, calls get_session() automatically, and passes the result in.

The body is now flat. No indentation under a context manager, just clean business logic.

  • Save main.py and check your terminal. The server auto-reloads.
  • Switch to your browser and open http://127.0.0.1:8000/docs. Click Try it out on the POST /books/ endpoint and create a book to confirm the dependency works.

Seeing a server error?

If the server shows an import error, double check that Depends is in your from fastapi import line and that Annotated is imported from typing.

If POST returns a 500 error, make sure get_session uses yield (not return). A regular return closes the session before the endpoint can use it.

help me fix my FastAPI dependency injection error

  • Now replace read_book, update_book, and delete_book with these refactored versions that use SessionDep:
@app.get("/books/{book_id}", response_model=BookPublic)
def read_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    return book


@app.patch("/books/{book_id}", response_model=BookPublic)
def update_book(book_id: int, book_update: BookUpdate, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    book.sqlmodel_update(book_update.model_dump(exclude_unset=True))
    session.add(book)
    session.commit()
    session.refresh(book)
    return book


@app.delete("/books/{book_id}")
def delete_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    session.delete(book)
    session.commit()
    return {"detail": "Book deleted"}

What does this code do?

Each function now takes session: SessionDep as a parameter instead of creating its own session with a with block. The logic inside each function is identical to before.

Notice how update_book takes three parameters: the path ID, the request body, and the injected session. FastAPI figures out which is which from the type annotations.

  • Save main.py.
  • In Swagger UI, try the GET /books/{book_id} endpoint with an ID you know exists. Confirm you get the book back.

Getting a 500 Internal Server Error?

Check that every endpoint function signature has session: SessionDep and that you removed the entire with Session(engine) as session: wrapper (including the indentation). The body should now be at the function's top indentation level.

help me fix my refactored endpoints after adding dependency injection

Add author filtering to the book list

Your GET /books/ endpoint currently returns every book in the database. A real API lets users narrow results. You'll add an optional query parameter so users can filter by author name.

  • Find the current read_books function. Replace it entirely with this version:
@app.get("/books/", response_model=list[BookPublic])
def read_books(
    session: SessionDep,
    author: str | None = None,
):
    statement = select(Book)
    if author:
        statement = statement.where(Book.author == author)
    books = session.exec(statement).all()
    return books

What does this code do?

  • author: str | None = None declares an optional query parameter. When a client calls /books/?author=Orwell, FastAPI passes "Orwell" into this parameter.
  • The if author: guard means filtering only applies when the parameter is provided. Without it, all books are returned.
  • .where(Book.author == author) adds a SQL WHERE clause to the query. SQLModel translates this to WHERE author = 'Orwell' under the hood.
  • Save main.py and wait for the server to reload.
  • Refresh http://127.0.0.1:8000/docs in your browser. Expand the GET /books/ endpoint. You should see a new author parameter field in the docs.

Don't see the author parameter?

Make sure author: str | None = None is inside the function signature (between the parentheses), not in the function body. Also confirm you saved the file and the server reloaded successfully.

help me add an optional query parameter to my FastAPI endpoint

Test the complete flow in Swagger UI

Your API now uses dependency injection for sessions and supports filtering. Time to test the full flow and confirm everything works together.

  • In Swagger UI, use POST /books/ to create a book with author "Orwell". Create a second book with author "Tolkien".
  • Expand GET /books/ and click Try it out. Leave the author field empty and execute. Confirm you see all your books in the response.

Before you filter by author, what do you expect the response to look like if you type Orwell into the author field? How many books should come back?

  • Enter Orwell in the author field and execute.

You should see only the Orwell book in the response. The Tolkien book is filtered out.

  • Now enter Nobody in the author field and execute.

You get an empty list []. No crash, no error. The filter simply found no matches.

  • Try GET /books/{book_id} with an ID that does not exist (like 999). Confirm you get a 404 response with "Book not found".

Why do the status codes matter?

Your POST returns 201 (Created) instead of the default 200, signalling that a new resource was made. Your missing-book endpoints return 404 (Not Found) with a clear message.

These are HTTP conventions that frontend developers and API consumers rely on. Correct status codes make your API predictable and self-documenting.

✔️ Awesome, I've got everything!

Great. Double check you've saved main.py and the server reloaded without errors.

ⓧ I'd like to double check the full code

Your main.py should now look like this. Scroll through and compare each section with yours:

The import section and models:

from typing import Annotated

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


# --- Models ---

class BookBase(SQLModel):
    title: str
    author: str
    year: int
    pages: int = Field(gt=0)


class Book(BookBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class BookCreate(BookBase):
    pass


class BookUpdate(SQLModel):
    title: str | None = None
    author: str | None = None
    year: int | None = None
    pages: int | None = Field(default=None, gt=0)

The BookPublic model, database setup, and dependency:

class BookPublic(BookBase):
    id: int


# --- Database ---

sqlite_url = "sqlite:///bookshelf.db"
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)]

The app setup and first three endpoints:

# --- App ---

app = FastAPI()


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


@app.get("/")
def read_root():
    return {"message": "Hello, Bookshelf!"}


@app.post("/books/", response_model=BookPublic, status_code=201)
def create_book(book: BookCreate, session: SessionDep):
    db_book = Book.model_validate(book)
    session.add(db_book)
    session.commit()
    session.refresh(db_book)
    return db_book

The GET endpoints with author filtering:

@app.get("/books/", response_model=list[BookPublic])
def read_books(
    session: SessionDep,
    author: str | None = None,
):
    statement = select(Book)
    if author:
        statement = statement.where(Book.author == author)
    books = session.exec(statement).all()
    return books


@app.get("/books/{book_id}", response_model=BookPublic)
def read_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    return book

The PATCH and DELETE endpoints:

@app.patch("/books/{book_id}", response_model=BookPublic)
def update_book(book_id: int, book_update: BookUpdate, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    book.sqlmodel_update(book_update.model_dump(exclude_unset=True))
    session.add(book)
    session.commit()
    session.refresh(book)
    return book


@app.delete("/books/{book_id}")
def delete_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(status_code=404, detail="Book not found")
    session.delete(book)
    session.commit()
    return {"detail": "Book deleted"}

Your API now uses clean dependency injection and supports author filtering. Next up, you'll extend this further with pagination and sorting in the Secret Mission.

Secret mission

Add Pagination and Sorting

Real-world APIs never dump every record at once. In this bonus challenge, you will add offset/limit pagination and sortable columns to your GET /books/ endpoint, turning it into a production-ready list API that clients can page through and reorder on the fly.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The FastAPI development server (currently running in your terminal).
  • Your project folder on the Desktop (contains main.py, bookshelf.db, and the venv/ virtual environment).
  • Python 3.13 (installed on your system).

Keep everything running

No action needed. Choose this if you are still actively building or want to keep experimenting with your API.

  • Your FastAPI server continues running and serving requests at http://127.0.0.1:8000.
  • Your bookshelf.db file keeps all your book data intact.
  • Python 3.13 remains available for other projects.

Pause - I'll come back to this later

Stop the running server to free up your terminal and port, but keep all files so you can pick up where you left off.

  • In the terminal where the server is running, press Ctrl+C to stop the FastAPI dev server.
  • Deactivate the virtual environment by running:
deactivate
  • Your project folder and bookshelf.db remain on disk. When you return, reactivate the virtual environment and start the server again.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your code and database.

  • In the terminal where the server is running, press Ctrl+C to stop the FastAPI dev server.
  • Deactivate the virtual environment by running:
deactivate
  • Delete your project folder by running the command for your operating system.

macOS

cd ~/Desktop
rm -rf bookshelf-api

Windows

cd ~\Desktop
rmdir /s /q bookshelf-api
  • To uninstall Python 3.13, open Settings, select Apps, then Installed apps.
  • Find Python 3.13 in the list, click the three-dot menu icon, and select Uninstall.

Nice Work!

Nice Work!

Nice work! You just built a fully functional REST API from scratch using FastAPI and SQLModel. Your bookshelf API validates data, persists to a database, and serves live interactive documentation you can demo to anyone.

You learned how to:

  • Build a complete REST API with all CRUD operations using FastAPI, with automatic input validation powered by Pydantic and SQLModel data models.
  • Persist data to SQLite using SQLModel as both your ORM and your validation layer, eliminating the model duplication you would face with raw SQLAlchemy.
  • Implement dependency injection with Depends for clean, reusable database session management and added query filtering to demonstrate production-ready API patterns.
  • Secret Mission: Added pagination and sorting to your book list endpoint to handle production-scale data.

Ready to quiz yourself?