A Database Connection for Every Request
A get_db dependency opens a SQLite connection for each request, hands it to the route with yield, commits when the route succeeds, rolls back when it raises, and always closes it, so plain def routes ask for a connection with Depends instead of opening and closing their own.
Journeyman · FastAPI · REST API · Updated
After this lesson you can
- Write a get_db dependency with yield that opens a connection, commits on success, rolls back on an error and always closes it
- Give plain def routes a connection with Depends, and explain why one shared connection fails and why the routes stay def
Key takeaways
- A route that opens and closes its own connection repeats that work everywhere, and one that forgets to commit loses what it saved without any error.
- One connection shared by the whole app fails in FastAPI, because plain def routes run on a pool of worker threads, and a sqlite3 connection refuses a thread it wasn't made in.
- A dependency is a function FastAPI runs for a route and passes the result in. Depends(get_db) gives the route a connection.
- get_db opens a connection, yields it, commits on success, rolls back and raises again on an error, and closes it in finally. scope="function" finishes it before the answer goes out.
- Routes that use sqlite3 stay plain def, so FastAPI runs them on its worker threads while other requests carry on.
Every request to a database-backed API needs a connection, and every one of those connections has to be opened, finished properly and closed, thousands of times a day, even when a route fails halfway. Get it wrong and saved data quietly disappears, or the server fails under its first real traffic. This lesson gives each request its own connection in the way FastAPI projects do it.
The example is Rep Counter's workouts API from Keeping the Data Code in One Place, whose routes still call connect() and close() themselves.
Every route opening its own connection
Here's a route that saves a new workout, written the same way:
@app.post("/workouts", status_code=201)
def add_workout(new_workout: WorkoutIn):
conn = connect()
workout_id = workouts_data.add_workout(conn, new_workout.member, new_workout.day.isoformat())
workout = workouts_data.get_workout(conn, workout_id)
conn.close()
return workout
POST /workouts with {"member": "Ana", "day": "2026-11-17"} answers 201 Created:
{"id":5,"member":"Ana","day":"2026-11-17"}
Then GET /workouts/5 answers 404:
{"status":404,"title":"Not Found","detail":"There's no workout 5."}
The route never called conn.commit(). A change only stays in the file once it's committed, so closing the connection threw Ana's workout away, with no error anywhere. Every route that writes has to remember to commit, every route has to close, and five routes means five chances to forget.
Why not one connection for the whole app?
One connection, opened once at the top of main.py and used by every route, looks tidier. GET /workouts/1 then answers 500, and the server's terminal says why:
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 136338774888896 and this is thread id 136338691196608.
A thread is one line of work inside a program; a program can run several at once. Think of a café with several baristas: a slow order doesn't stop the next customer being served. FastAPI runs every plain def route on a thread pool, its team of baristas, so one slow request doesn't hold up the others. A sqlite3 connection refuses to be used by any thread but the one that made it, its check_same_thread rule, and the connection made at startup belongs to none of the baristas.
get_db: a connection for every request
FastAPI has a tool for "something every request needs": a dependency. It's a function FastAPI runs for a route, before the route itself, passing what it gives into the route's parameter. Here's Rep Counter's, in database.py:
import sqlite3
DATABASE_PATH = "rep_counter.db"
def get_db():
conn = sqlite3.connect(DATABASE_PATH, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
- The first lines open the file, make rows readable by name, and turn on foreign keys, which SQLite leaves off for every new connection (Related Tables and Foreign Keys).
yield connhands the connection to the route and pausesget_dbwhile the route runs. A function withyieldin it is a generator, and FastAPI calls such a dependency a dependency with yield.conn.commit()runs when the route finished without an error: everything the request wrote is saved, together.conn.rollback()runs when the route raised an error, a 404 or a crash halfway through: everything the request wrote is undone, so nothing is half-saved.raisethen passes the error on, so the error handlers still answer. A group of changes that is saved or undone as a whole is a transaction.finallyruns whatever happened, so the connection is always closed.check_same_thread=Falseturns the thread rule off. FastAPI may open a request's connection on one thread and run its route on another, and that's safe here, because each connection belongs to one request.
A route asks for it with Depends, read "depends on":
@app.post("/workouts", status_code=201)
def add_workout(new_workout: WorkoutIn, conn: sqlite3.Connection = Depends(get_db, scope="function")):
workout_id = workouts_data.add_workout(conn, new_workout.member, new_workout.day.isoformat())
return workouts_data.get_workout(conn, workout_id)
scope="function" tells FastAPI to finish get_db, commit included, before the answer is sent. Without it, the answer goes out first and the commit comes after, so a commit that failed would follow an answer that already said 201. Now POST /workouts answers 201 with Ana's workout, and GET /workouts/5 answers 200 OK:
{"id":5,"member":"Ana","day":"2026-11-17"}
Plain def routes, and one place for the file's name
The routes stay plain def. A sqlite3 call makes its thread wait for the disk, and in a def route that wait happens on one of FastAPI's worker threads while other requests carry on. Async Routes: When They Help, and When They Don't, in Module 5, explains async def and why it doesn't fit sqlite3.
DATABASE_PATH names the file in one place. Every route gets its connection through get_db, so swapping that one dependency points the whole API at another database, which is exactly what Testing with a Real, Throwaway Database does.

Check yourself
2 questions to try on the lesson page.
So what
A get_db dependency gives every request a connection that's opened, finished as a whole and closed the same way every time, so no route can forget a commit or leave a connection open.