FastAPI SQLite Tutorial: A CRUD API With SQLModel (Tested)

Ninad Pathak

October 7, 2026

A FastAPI app that keeps its data in a Python list loses all of it on restart. SQLite keeps it in a single file, with no database server to run. In this article I’ll walk you through wiring SQLite into FastAPI with SQLModel, the way FastAPI’s own tutorial does, then testing every endpoint with curl.

TLDR

Install fastapi[standard] and sqlmodel.
Create the engine with create_engine("sqlite:///shop.db", connect_args={"check_same_thread": False}).
Give each request its own Session through a dependency that yields it.
Create the tables once at startup with SQLModel.metadata.create_all(engine), from a lifespan function.

Run it with uvicorn main:app or fastapi dev main.py.

What is SQLModel and what will we build?

FastAPI works with any database library. Its own tutorial uses SQLModel, which “is built on top of SQLAlchemy and Pydantic” and was made by FastAPI’s author (FastAPI: SQL databases). A single class defines both the database table and the JSON the API accepts and returns.

To show you the whole flow, I will build a small products API that stores its data in shop.db. It has an endpoint for each CRUD operation.

Prerequisites

Requirement	Version tested
Python	3.13.14 (SQLite 3.53.4 inside Python’s `sqlite3`)
FastAPI	0.142.2, installed as `fastapi[standard]` for uvicorn and the `fastapi` command
SQLModel	0.0.48

fastapi[standard]
sqlmodel

Install them in a virtual environment with pip install -r requirements.txt.

Building the products API

The code is one file, main.py, shown in full after the steps.

Step 1: Define the models

ProductBase holds the fields a client sends. Product adds the id and table=True, which makes it a database table. ProductUpdate has every field optional, for partial updates. Field(gt=0) rejects prices of zero or less before anything reaches SQLite.

Step 2: Create the engine

sqlite:///shop.db is a relative path, so the file is created in the folder you start the server from. FastAPI’s tutorial explains the check_same_thread setting: “Using check_same_thread=False allows FastAPI to use the same SQLite database in different threads. This is necessary as one single request could use more than one thread (for example in dependencies).”

Step 3: Give each request a session

get_session() opens a Session, yields it to the endpoint and closes it afterwards. SessionDep is a shorthand type, so each endpoint only declares session: SessionDep.

Step 4: Create the tables at startup

The lifespan function runs SQLModel.metadata.create_all(engine) once when the app starts. It creates any missing tables and leaves existing ones alone.

Step 5: Write the endpoints

Each endpoint works through the session. Writes call session.add() and then session.commit(). Reads use session.get() for one row by id, or select() for a list. A missing id raises HTTPException(status_code=404).

The complete main.py:

from contextlib import asynccontextmanager
from typing import Annotated

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


class ProductBase(SQLModel):
    name: str = Field(index=True)
    price: float = Field(gt=0)
    description: str | None = None


class Product(ProductBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class ProductUpdate(SQLModel):
    name: str | None = None
    price: float | None = Field(default=None, gt=0)
    description: str | None = None


engine = create_engine("sqlite:///shop.db", connect_args={"check_same_thread": False})


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


SessionDep = Annotated[Session, Depends(get_session)]


@asynccontextmanager
async def lifespan(app: FastAPI):
    SQLModel.metadata.create_all(engine)
    yield


app = FastAPI(lifespan=lifespan)


@app.post("/products/", status_code=201)
def create_product(product: ProductBase, session: SessionDep) -> Product:
    db_product = Product.model_validate(product)
    session.add(db_product)
    session.commit()
    session.refresh(db_product)
    return db_product


@app.get("/products/")
def list_products(session: SessionDep, offset: int = 0, limit: int = 100) -> list[Product]:
    return session.exec(select(Product).offset(offset).limit(limit)).all()


@app.get("/products/{product_id}")
def get_product(product_id: int, session: SessionDep) -> Product:
    product = session.get(Product, product_id)
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    return product


@app.patch("/products/{product_id}")
def update_product(product_id: int, changes: ProductUpdate, session: SessionDep) -> Product:
    product = session.get(Product, product_id)
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    product.sqlmodel_update(changes.model_dump(exclude_unset=True))
    session.add(product)
    session.commit()
    session.refresh(product)
    return product


@app.delete("/products/{product_id}", status_code=204)
def delete_product(product_id: int, session: SessionDep) -> None:
    product = session.get(Product, product_id)
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    session.delete(product)
    session.commit()

Running it

Start the server from the folder that holds main.py. These are its startup lines:

$ uvicorn main:app --port 8765
Started server process [51030]
Application startup complete.
Uvicorn running on http://127.0.0.1:8765 (Press CTRL+C to quit)

Then call it from a second terminal. Create two products:

$ curl -s -X POST http://127.0.0.1:8765/products/ -H 'Content-Type: application/json' -d '{"name": "Basketball", "price": 29.99, "description": "Outdoor ball"}'
{"price":29.99,"id":1,"name":"Basketball","description":"Outdoor ball"}
$ curl -s -X POST http://127.0.0.1:8765/products/ -H 'Content-Type: application/json' -d '{"name": "Football", "price": 19.99}'
{"price":19.99,"id":2,"name":"Football","description":null}

List them, and fetch one by id. An id that doesn’t exist returns 404:

$ curl -s http://127.0.0.1:8765/products/
[{"price":29.99,"id":1,"name":"Basketball","description":"Outdoor ball"},{"price":19.99,"id":2,"name":"Football","description":null}]

$ curl -s http://127.0.0.1:8765/products/1
{"price":29.99,"id":1,"name":"Basketball","description":"Outdoor ball"}
$ curl -s -w ' %{http_code}' http://127.0.0.1:8765/products/99
{"detail":"Product not found"} 404

Update one field with PATCH. Fields you don’t send stay as they were:

$ curl -s -X PATCH http://127.0.0.1:8765/products/2 -H 'Content-Type: application/json' -d '{"price": 24.5}'
{"price":24.5,"id":2,"name":"Football","description":null}

A price of 0 fails validation with 422, and nothing is written:

$ curl -s -w ' %{http_code}' -X POST http://127.0.0.1:8765/products/ -H 'Content-Type: application/json' -d '{"name": "Free ball", "price": 0}'
{"detail":[{"type":"greater_than","loc":["body","price"],"msg":"Input should be greater than 0","input":0,"ctx":{"gt":0.0}}]} 422

Delete returns 204 with an empty body:

$ curl -s -o /dev/null -w '%{http_code}' -X DELETE http://127.0.0.1:8765/products/1
204
$ curl -s http://127.0.0.1:8765/products/
[{"price":24.5,"id":2,"name":"Football","description":null}]

Meanwhile, the server’s terminal logged each request:

127.0.0.1:62461 - "GET /docs HTTP/1.1" 200 OK
127.0.0.1:62462 - "POST /products/ HTTP/1.1" 201 Created
127.0.0.1:62463 - "POST /products/ HTTP/1.1" 201 Created
127.0.0.1:62464 - "GET /products/ HTTP/1.1" 200 OK
127.0.0.1:62465 - "GET /products/1 HTTP/1.1" 200 OK
127.0.0.1:62466 - "GET /products/99 HTTP/1.1" 404 Not Found
127.0.0.1:62467 - "PATCH /products/2 HTTP/1.1" 200 OK
127.0.0.1:62468 - "POST /products/ HTTP/1.1" 422 Unprocessable Content
127.0.0.1:62469 - "DELETE /products/1 HTTP/1.1" 204 No Content
127.0.0.1:62470 - "GET /products/ HTTP/1.1" 200 OK

Look inside the database

shop.db is an ordinary SQLite file. SQLModel named the table product and added an index for name:

$ sqlite3 -header -column shop.db '.schema product' 'SELECT * FROM product;'
CREATE TABLE product (
	name VARCHAR NOT NULL,
	price FLOAT NOT NULL,
	description VARCHAR,
	id INTEGER NOT NULL,
	PRIMARY KEY (id)
);
CREATE INDEX ix_product_name ON product (name);
name      price  description  id
--------  -----  -----------  --
Football  24.5                2

Common problems

sqlite3.OperationalError: database is locked: another process is writing to shop.db, or a session was left open. Keep one session per request as in Step 3. See database is locked.

SQLite objects created in a thread can only be used in that same thread: the engine was created without check_same_thread: False.
An empty database after a restart: the server started from a different folder, so sqlite:///shop.db pointed at a new file. Use an absolute path, which has four slashes on Linux and macOS (sqlite:////srv/app/shop.db).

None of these came up in the run above.

Conclusion

FastAPI and SQLite fit together with very little code. You need one engine with check_same_thread: False and one session per request, and SQLModel classes describe the tables. That’s enough for prototypes and small apps on one server. If several servers need to write to the same data, move to a client/server database such as PostgreSQL. See SQLite vs PostgreSQL.