# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
@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:

```json
{"id":5,"member":"Ana","day":"2026-11-17"}
```

Then `GET /workouts/5` answers 404:

```json
{"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:

```text
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`:

```python
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 conn`** hands the connection to the route and pauses `get_db` while the route runs. A function with `yield` in 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. `raise` then 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**.
- **`finally`** runs whatever happened, so the connection is always closed.
- **`check_same_thread=False`** turns 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":

```python
@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:

```json
{"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.

![One request's trip through get_db: the connection opens, yield hands it to the route, and then one of two paths: the route finishes, so commit saves its changes; or the route raises an error, so rollback undoes them and the error is raised again. Both paths end at close, and only then is the answer sent](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/11/7c6792f8ddaa485b832b401b324636b0.webp)

## 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.

## Key terms

- [Depends()](https://www.ask-codey.com/glossary/depends)
- [Dependency with yield](https://www.ask-codey.com/glossary/dependency-with-yield)
- [Dependency Injection in FastAPI](https://www.ask-codey.com/glossary/dependency-injection-in-fastapi)
- [Transaction](https://www.ask-codey.com/glossary/transaction)
- [ROLLBACK](https://www.ask-codey.com/glossary/rollback)

## Related

- [Keeping the Data Code in One Place](https://www.ask-codey.com/lessons/fastapi-data-module)
- [Testing with a Real, Throwaway Database](https://www.ask-codey.com/lessons/fastapi-data-testing-real-database)
- [Async Routes: When They Help, and When They Don't](https://www.ask-codey.com/lessons/fastapi-async-routes-when-they-help)

Source: https://www.ask-codey.com/lessons/fastapi-data-get-db-dependency
