Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 50 ยท Databases

SQLAlchemy with FastAPI

Connect FastAPI to a database with SQLAlchemy 2.0: engine, session dependency with yield, ORM models versus Pydantic schemas, and async sessions.

An API that forgets everything when it restarts is a demo. To keep data you need a database, and in the Python world the standard tool for talking to relational databases is SQLAlchemy. FastAPI does not include a database layer, which is a feature: you choose the tool, and the dependency system from Dependencies gives you a clean way to hand each request its own database session.

This lesson uses SQLAlchemy 2.0 style with an in-memory SQLite database, so everything runs right here. You will meet the three core objects (engine, session, model), write a yield dependency that opens and closes a session per request, and learn why your ORM classes and your Pydantic schemas are two different things. The last section shows the async flavor, which needs a real driver and is therefore read-only. If SQL itself is new to you, the SQL tutorial is a good companion.

The three core objects

Engine, session, model

Engine Created once. Owns the connections.
Session One per request. Tracks your changes.
Model A Python class mapped to a table.

The engine lives for the whole app, the session for one request.

1 / 2
  • The engine knows how to connect (URL, driver, pool). You create it once at startup.
  • A session is a unit of work: you add and change objects, and commit() writes them in one transaction. Sessions are cheap and are not meant to be shared between requests.
  • A model is a class that inherits from a declarative base. In SQLAlchemy 2.0 you describe columns with type annotations: Mapped[int] and mapped_column(...).

A first model and a session

Before FastAPI enters, here is the SQLAlchemy part alone. SQLite in memory exists only for the life of a connection, so we tell the engine to reuse one connection with StaticPool; that is a trick for tests and demos, not for production databases.

Python Engine, model and session on their own

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line prints the SQL that select(...) builds, with a bound parameter instead of the number. SQLAlchemy never pastes values into SQL text, which is what protects you from SQL injection. create_all creates missing tables; it does not change existing ones, which is why real projects add migrations with Alembic.

A session per request

Now connect it to FastAPI. The shape is the same yield dependency you saw in Class and yield dependencies: open a session, hand it to the endpoint, and close it afterwards no matter what.

A second decision: the ORM model Book describes a table row, while the Pydantic schemas describe request and response JSON. Keep them separate. If you return the ORM class directly, you tie your public API to your table layout, and a new column such as internal_notes leaks into responses. Schemas also let the input differ from the output (clients do not send an id). The bridge is model_config = ConfigDict(from_attributes=True), which lets Pydantic read attributes from an ORM object instead of requiring a dictionary.

Python A FastAPI app with a session dependency

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Study the flow of create_book. The Pydantic model validates the JSON (the last request with an empty title never reaches the database). Book(**data.model_dump()) turns validated data into an ORM object, commit() saves it, and refresh() reloads it so the database-assigned id is available. The response_model=BookRead then converts the ORM object into JSON through from_attributes. The final line proves every request opened and closed its own session.

These endpoints are async def, but SQLAlchemy's classic Session is blocking: while a query runs, the event loop waits. For a small demo that is fine. In production you have two clean options: declare the endpoints with plain def (FastAPI then runs them in a worker thread, see async def vs def endpoints) or switch to SQLAlchemy's async API shown below. Do not mix a blocking session into async def code that serves heavy traffic. Pyodide has no threads, which is why the examples here keep async def.

Who commits?

A common design question is where commit() belongs. Calling it inside each endpoint, as above, is simple and explicit. Another style commits in the dependency after the yield and rolls back on error, so endpoints only add(). Whichever you pick, be consistent: a half-committed request is hard to debug. Rolling back is automatic when a session closes without a commit.

Async sessions (read-only)

SQLAlchemy 2.0 also offers an async API: create_async_engine, AsyncSession and await session.execute(...). It needs an async driver, such as asyncpg for PostgreSQL, aiomysql for MySQL or aiosqlite for SQLite, which this browser runtime does not provide, so the following code is for reading only:

Python
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/shop")
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_session():
    async with SessionLocal() as session:
        yield session

@app.get("/books/{book_id}", response_model=BookRead)
async def get_book(book_id: int, session: Annotated[AsyncSession, Depends(get_session)]):
    book = await session.get(Book, book_id)
    if book is None:
        raise HTTPException(status_code=404, detail="Book not found")
    return book

Almost everything you learned carries over: models, schemas, the yield dependency. The differences: every database call is awaited, expire_on_commit=False stops attributes from being reloaded lazily after a commit (a lazy load in async code raises an error), and relationships must be loaded explicitly, as you will see in CRUD, filtering and pagination.

Habits worth keeping

  • Create the engine once per process (a module-level object or in the lifespan from Lifespan events and app state), and a session per request.
  • Never keep a session in a global variable shared by requests.
  • Do not return ORM objects without a response_model; the schema is your contract.
  • Read the database URL from settings, not from source code (see Settings and configuration).
  • Let a migration tool own the schema once the project is more than a prototype.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How often should you create the SQLAlchemy engine, and how often a session?

    The engine owns the connection pool and is expensive, so it is shared. Sessions are cheap units of work that belong to one request.
  2. 2. What does ConfigDict(from_attributes=True) allow?

    Normally Pydantic validates dictionaries; from_attributes lets it build the schema from an object's attributes, which is how ORM rows become response JSON.
  3. 3. Why keep ORM models and Pydantic schemas separate?

    Separate classes stop internal columns from leaking and let create and read shapes differ (for example no id on input).
  4. 4. Why does the async example set expire_on_commit=False?

    After a commit SQLAlchemy normally expires attributes and reloads them on access, an implicit query that cannot run in async code without an await.

Summary

  • The engine is created once; a session per request comes from a yield dependency that always closes it.
  • SQLAlchemy 2.0 models use Mapped[...] and mapped_column; queries are built with select() and executed through the session.
  • ORM models describe tables, Pydantic schemas describe JSON; from_attributes=True connects them.
  • The classic Session blocks: use def endpoints or the async API in production.
  • Async SQLAlchemy needs an async driver and explicit loading; treat create_all as a prototype tool and use migrations later.

Next: SQLModel merges the model and the schema into one class.