On this page

SQLModel with FastAPI – Models, Engine, and Database Sessions

Published on August 10, 2026

Enrico Hochkoeppler Profile

By Enrico Hochkoeppler

Web Developer & TutoriaLibre Founder

SQLModel with FastAPI: Models, Engine, and Sessions

SQLModel is the library that connects FastAPI to a SQL database, built by the same author as FastAPI and designed specifically to eliminate the duplication that shows up when a project uses a separate ORM and a separate validation library side by side. A data model gets defined exactly once, and that same class is used both to create the database table and to validate the API's request and response data — no second, parallel definition to keep in sync with the first.

This article covers what SQLModel is, how it works under the hood, and how to wire it into a FastAPI application from the ground up.


What an ORM Solves​

A SQL database stores data in tables — rows and columns, much like a spreadsheet. Working with that data from Python normally means writing raw SQL strings, SELECT * FROM tasks WHERE id = 1 and similar. This works, but it's fragile: a SQL string carries no type checking and no autocomplete, and a typo in it only surfaces as an error once that specific line actually runs.

An ORM — Object-Relational Mapper — solves this by letting Python code work with database rows as ordinary Python objects instead of raw SQL text. A class gets defined once, and the ORM translates operations on instances of that class into SQL queries automatically. Instead of writing INSERT INTO tasks ... by hand, the equivalent operation becomes session.add(task).

SQLModel is an ORM, but one built by combining two tools most FastAPI projects already depend on separately: SQLAlchemy, the most widely used Python ORM, handling the actual database communication, and Pydantic, the validation library FastAPI already uses to parse and validate request and response data. Without SQLModel, a typical FastAPI project ends up maintaining two separate sets of classes — SQLAlchemy models for the database, and Pydantic schemas for the API — that describe the same data twice. SQLModel merges both into a single class definition.


What SQLModel Is​

SQLModel is a Python library that lets a single class act as both a database table definition and a Pydantic data model at the same time. The class is written once, with standard Python type annotations, and works in two contexts simultaneously: as a database model, where SQLModel generates the CREATE TABLE SQL and handles inserting, querying, updating, and deleting rows; and as a Pydantic model, where FastAPI uses that same class to parse incoming JSON, validate field types, and generate OpenAPI documentation automatically. The practical effect is less code, fewer places for the database shape and the API shape to drift apart from each other, and full editor support — autocomplete, type checking, inline error detection — across both roles at once.


SQLModel vs SQLAlchemy vs Pydantic​

FeatureSQLAlchemyPydanticSQLModelDatabase ORMYesNoYes (via SQLAlchemy)Data validationNoYesYes (via Pydantic)Type hintsPartialYesYesFastAPI integrationManualYesSeamlessOne model for all layersNoNoYesEditor autocompletePartialYesYes

SQLAlchemy is powerful but still requires a separate Pydantic schema for API validation on top of it. Pydantic handles validation well but has no database capability of its own. SQLModel combines both, which is why it's the default recommendation for FastAPI applications that need a SQL database behind them.


Installing SQLModel​


pip install sqlmodel

SQLModel installs SQLAlchemy and Pydantic as dependencies automatically — neither needs to be installed separately. The database used throughout this article is SQLite, a file-based database requiring no server, no separate installation, and no configuration beyond a filename — the right choice for learning and for small-to-medium applications. Moving to PostgreSQL or MySQL later requires changing only the database connection string, not the model code, as covered further down.


Three Core Concepts​

Working with SQLModel in FastAPI centers on three objects, and understanding what each one is responsible for before writing any code makes everything that follows easier to place.

The model is a Python class defining the shape of the data — its columns, their types, and their constraints — decorated with table=True to tell SQLModel to create an actual database table from it. The engine is the connection to the database itself, created once when the application starts and reused for every operation afterward — the persistent link between the running Python process and the database file. The session is a temporary workspace for one group of related database operations: it tracks new and modified objects and applies them to the database when session.commit() is called. In a FastAPI app, one session is created per request and discarded once that request finishes.


Defining a SQLModel Table Model​

Every table maps to one SQLModel class declared with table=True:

models/task.py:


from sqlmodel import SQLModel, Field

from typing import Optional



class Task(SQLModel, table=True):

    id: int | None = Field(default=None, primary_key=True)

    title: str

    description: str | None = Field(default=None)

    completed: bool = Field(default=False)

What each field declares​

class Task(SQLModel, table=True) inherits from SQLModel, and table=True is what distinguishes an actual database table from a plain data model used only for validation — without it, the class behaves as a pure Pydantic model with no database involvement at all.

id: int | None = Field(default=None, primary_key=True) is the primary key. int | None allows the field to hold either an integer or None, and the default is None because a newly created task doesn't have an ID yet — the database assigns one automatically the moment the row is inserted. primary_key=True is what tells SQLModel to generate the PRIMARY KEY constraint on this column.

title: str is a required string field with no default, meaning it must be provided when creating a task — SQLModel raises a validation error if it's missing.

description: str | None = Field(default=None) is an optional string; the str | None annotation tells both Pydantic and SQLAlchemy that the column is nullable, and default=None allows it to be omitted entirely when creating a task.

completed: bool = Field(default=False) defaults new tasks to incomplete.

int | None and str | None are Python 3.10+ union syntax, equivalent to Optional[int] and Optional[str] from the typing module — both work with SQLModel, but the union syntax is what the current official documentation uses, and it's the cleaner of the two.


The Dual Nature in Practice​

With table=True set, the class carries two sets of metadata simultaneously: SQLAlchemy metadata — column definitions, types, indexes, and constraints — used to generate the CREATE TABLE statement and construct SQL queries, and Pydantic metadata — field types, defaults, and validators — used to parse incoming JSON and serialize Python objects back into JSON for responses. FastAPI reads the Pydantic side when the model is used as a request body parameter; SQLAlchemy reads the SQLAlchemy side the moment an instance is added to a session. Neither requires a different method call to trigger:


# FastAPI reads the Pydantic metadata here — parses and validates the request body

@router.post("/tasks/", status_code=201)

def create_task(task: Task, session: Session = Depends(get_session)):

    # SQLAlchemy reads the SQLAlchemy metadata here — generates the INSERT SQL

    session.add(task)

    session.commit()

    session.refresh(task)

    return task

The same task object FastAPI populated directly from the request JSON is the exact object passed to the session — no conversion step, no separate mapping between an API schema and a database model.


Creating the Engine​

The engine is created once, at the module level, and shared across the entire application:

db.py:


from sqlmodel import SQLModel, Session, create_engine



DATABASE_URL = "sqlite:///database.db"



engine = create_engine(

    DATABASE_URL,

    connect_args={"check_same_thread": False},  # required for SQLite with FastAPI

    echo=False,  # set to True during development to see SQL queries in the terminal

)



def create_db_and_tables():

    SQLModel.metadata.create_all(engine)



def get_session():

    with Session(engine) as session:

        yield session

What each part does​

create_engine(DATABASE_URL, ...) creates the engine itself. In the connection string "sqlite:///database.db", sqlite:/// is the protocol and database.db is the filename SQLite creates in the project directory.

connect_args={"check_same_thread": False} is a SQLite-specific setting: SQLite's default behavior prevents the same connection from being used across multiple threads, but FastAPI can handle a single request across multiple threads, so this restriction has to be disabled explicitly. Thread safety is still maintained correctly regardless of this setting, since it's SQLAlchemy's session layer — not raw connection sharing — that manages that.

echo=False controls whether SQLAlchemy prints every SQL statement it executes to the terminal; setting it to True during development is genuinely useful for seeing exactly what queries are being generated, and setting it back to False before deploying avoids unnecessary log noise.

create_db_and_tables() calls SQLModel.metadata.create_all(engine), which reads every SQLModel table model that's been imported and issues CREATE TABLE IF NOT EXISTS for each one. This runs once at startup, never on individual requests.

get_session() is a generator function that creates a fresh Session for each request and closes it cleanly once that request is finished — the with Session(engine) as session: block ensures the session closes properly even if an exception happens partway through.


Wiring It Into FastAPI with lifespan​

The @app.on_event("startup") decorator that shows up in older material is deprecated and scheduled for removal in a future version of Starlette. The current, correct pattern is the lifespan context manager:

main.py:


from contextlib import asynccontextmanager

from fastapi import FastAPI

from db import create_db_and_tables, engine

from routers.tasks import router as tasks_router



@asynccontextmanager

async def lifespan(app: FastAPI):

    # Runs once when the application starts — creates tables if they don't exist

    create_db_and_tables()

    yield

    # Runs once when the application shuts down — clean up resources here if needed



app = FastAPI(lifespan=lifespan)

app.include_router(tasks_router)

Everything before yield runs at startup; everything after it runs at shutdown, keeping both halves of the lifecycle together in one readable function instead of split across two separate event handlers. create_db_and_tables() runs exactly once here, not on every request — it creates the database file and its tables the first time the app starts, and on every subsequent start, CREATE TABLE IF NOT EXISTS checks whether each table already exists and skips creating it if so.


The Session Dependency​

get_session() is a FastAPI dependency: a function FastAPI calls before the route handler runs, whose return value gets injected directly into that handler.


from fastapi import APIRouter, Depends

from sqlmodel import Session, select

from db import get_session

from models.task import Task



router = APIRouter(prefix="/tasks", tags=["Tasks"])



@router.post("/", status_code=201)

def create_task(task: Task, session: Session = Depends(get_session)):

    session.add(task)

    session.commit()

    session.refresh(task)

    return task



@router.get("/")

def get_tasks(session: Session = Depends(get_session)):

    return session.exec(select(Task)).all()

session: Session = Depends(get_session) tells FastAPI to call get_session() and pass the yielded Session object in as the session argument. Once the route handler returns, FastAPI closes that session automatically through the with block inside get_session(). One session belongs to one request — a session should never be shared across multiple requests or stored in a global variable, since each request needs to create its own, use it, and discard it, which is what prevents uncommitted data from one request leaking into another.


How add(), commit(), and refresh() Work​

These three calls appear in every write operation, and understanding each individually removes any guesswork around what's actually happening:

session.add(task) registers the object with the session. At this point it's tracked in memory only — nothing has reached the database yet.

session.commit() flushes every pending change to the database and commits the transaction; this is the moment the actual INSERT SQL runs. If something goes wrong — a constraint violation, a connection error — the transaction rolls back automatically.

session.refresh(task) re-reads the row from the database after the commit and updates the Python object with whatever values the database itself assigned. This step is what populates id — the database assigns that primary key integer during the INSERT, and without the refresh, task.id would still read as None even after a successful commit.


The Complete File Structure So Far​


project/

├── main.py       ← FastAPI app with lifespan

├── db.py         ← engine, create_db_and_tables(), get_session()

├── models/

│   └── task.py   ← Task SQLModel with table=True

└── routers/

    └── tasks.py  ← route handlers using Depends(get_session)

This separates database setup (db.py), data shape (models/), and route logic (routers/) into independent files, each with exactly one responsibility.


Verifying the Setup​


fastapi dev main.py

On the first run, SQLModel creates database.db in the project directory and issues CREATE TABLE task. Opening http://127.0.0.1:8000/docs shows the generated Swagger UI, with FastAPI reading the Pydantic metadata from Task to produce full documentation — field names, types, which fields are required — with no additional configuration written for it. Opening database.db directly with DB Browser for SQLite, a free GUI tool, allows browsing tables, rows, and running SQL queries against the file to confirm the data landed correctly.


What SQLite Is and When to Move On​

SQLite stores an entire database in a single file and requires no separate server process, making it the simplest possible setup — the right choice for learning, development, and small-to-medium applications running on a single server. It has two limitations that matter once an application grows past that: it doesn't support concurrent writes from multiple processes, which becomes relevant the moment an app runs with more than one Uvicorn worker, and it has no network access, meaning it must live on the same machine as the application connecting to it. When either limitation applies, moving to PostgreSQL requires changing only the connection string:


DATABASE_URL = "postgresql://user:password@localhost:5432/mydb"

Nothing else in the codebase needs to change — models, sessions, and queries all continue to work identically, since SQLModel and SQLAlchemy abstract away the database-specific SQL dialect underneath.


Common Mistakes to Avoid​

Using @app.on_event("startup") to call create_db_and_tables() relies on a decorator that's deprecated and scheduled for removal — the lifespan context manager covered earlier is the current correct approach. Forgetting session.refresh(task) after session.commit() leaves task.id as None in the response, since the database only assigns that value during the insert and nothing re-reads it back into the object without the refresh. Sharing a session across multiple requests, or storing one in a global variable or module-level state, causes data to leak between requests and isn't thread-safe. Leaving echo=True set in production logs every SQL statement to the terminal, which is valuable during development but adds log noise and overhead once deployed. Omitting table=True on a model silently turns it into a pure Pydantic model with no database table behind it at all — if a table isn't appearing in the database, this is the first thing worth checking.


Practice: Adding a created_at Field​


from datetime import datetime, timezone

from sqlmodel import SQLModel, Field



class Task(SQLModel, table=True):

    id: int | None = Field(default=None, primary_key=True)

    title: str

    description: str | None = Field(default=None)

    completed: bool = Field(default=False)

    created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))

default_factory accepts a callable, invoked automatically whenever a new instance is created without an explicit value for created_at. Using lambda: datetime.now(timezone.utc) rather than datetime.utcnow matters specifically: datetime.utcnow() has been deprecated since Python 3.12 and is scheduled for removal in Python 3.14, and it also produces a naive datetime with no timezone information attached. datetime.now(timezone.utc) produces a timezone-aware UTC datetime instead, which is the correct type for a timestamp in a production system — a naive datetime leaves any code reading it to guess which timezone it was recorded in.

After adding the field, restarting the server and checking /docs shows created_at now included in the Task schema, and any newly created task carries a real timestamp in its response.

Key Takeaways​

SQLModel closes the gap between a database model and an API schema by making one class serve both roles: table=True is what turns a class into an actual database table rather than a plain validation model, and every field's type hint drives both the column definition SQLAlchemy generates and the validation FastAPI applies to incoming requests. The engine is created once and shared for the life of the application, while a session belongs to exactly one request and is discarded once that request finishes — add(), commit(), and refresh() mark three distinct stages of a write, with refresh() specifically responsible for pulling database-assigned values like the primary key back into the Python object. The lifespan context manager has replaced the older startup-event decorator for running one-time setup like table creation, and switching from SQLite to PostgreSQL later requires touching only the connection string, not any model or query code.

Frequently Asked Questions​

What is SQLModel and what is it used for?

SQLModel is a Python library that lets a single class act as both a database table definition and a Pydantic data model. It was built by the creator of FastAPI and combines SQLAlchemy (for database access) with Pydantic (for data validation). You define your data model once and SQLModel uses it to create database tables, validate API request bodies, and serialize response data — eliminating the need to maintain separate ORM models and Pydantic schemas.

What is the difference between SQLModel, SQLAlchemy, and Pydantic?

SQLAlchemy is a powerful ORM that handles database communication but has no data validation capability. Pydantic handles data validation and serialization but has no database capability. SQLModel combines both: it uses SQLAlchemy under the hood for database operations and Pydantic under the hood for validation, so one class covers both concerns. In a FastAPI project without SQLModel, you would typically define separate SQLAlchemy models and Pydantic schemas for the same data.

What is an ORM and why is it useful?

An ORM (Object-Relational Mapper) lets you work with database rows as Python objects instead of writing raw SQL strings. You define a Python class with fields that map to database columns, and the ORM translates operations on those objects into SQL queries automatically. Instead of writing INSERT INTO tasks ..., you write session.add(task). This provides type safety, editor autocomplete, and errors that surface at development time rather than at runtime.

What does table=True do in a SQLModel class?

table=True tells SQLModel to treat the class as a database table definition. Without it, a class that inherits from SQLModel behaves as a pure Pydantic model — it validates data correctly but does not create a database table and cannot be used in database sessions. With table=True, SQLModel generates the CREATE TABLE SQL for the class and makes it available for all ORM operations through a session.

How do I define a primary key in SQLModel?

Use the Field function with primary_key=True and set the type to int | None with a default of None: id: int | None = Field(default=None, primary_key=True). The type is None by default because the database assigns the integer ID automatically during INSERT. Before the row is saved, the id field holds None. After session.commit() and session.refresh(), it holds the integer assigned by the database.

What is the difference between int | None and Optional[int] in SQLModel?

They are equivalent. int | None is the Python 3.10+ union syntax and Optional[int] is the older syntax from the typing module. Both tell SQLModel and Pydantic that the field can hold an integer or None. The union syntax is cleaner and is what the official SQLModel and FastAPI documentation now uses, but Optional[int] continues to work in all Python versions that FastAPI supports.

What is a database engine in SQLModel and how do I create one?

The engine is the persistent connection between your Python code and the database. It is created once when the application starts using create_engine(DATABASE_URL). For SQLite, the connection string is sqlite:///database.db, where database.db is the filename SQLite creates in the project directory. The engine is shared across the entire application — you create one and reuse it for every session. Never create a new engine per request.

What does connect_args={'check_same_thread': False} mean in SQLite?

By default, SQLite prevents the same connection from being used across multiple threads. FastAPI can process parts of a request across multiple threads, which triggers this restriction. Setting check_same_thread to False disables the restriction so SQLite connections can be used without thread errors. Thread safety is maintained correctly through SQLAlchemy's session management regardless of this setting.

What does create_db_and_tables() do in SQLModel?

It calls SQLModel.metadata.create_all(engine), which reads all imported SQLModel table models and issues CREATE TABLE IF NOT EXISTS statements for each one. This creates the database file and all tables on the first application start. On subsequent starts, it checks whether each table already exists and skips creation if it does. It should be called exactly once at startup, not on every request.

How do I create database tables automatically when a FastAPI application starts?

Use the lifespan context manager — the current recommended approach in FastAPI. Define an async function decorated with @asynccontextmanager that calls create_db_and_tables() before the yield, then pass it to FastAPI as the lifespan parameter: app = FastAPI(lifespan=lifespan). Everything before yield runs at startup and everything after yield runs at shutdown. The deprecated @app.on_event('startup') approach should not be used in new projects.

Why is @app.on_event('startup') deprecated in FastAPI?

@app.on_event('startup') and @app.on_event('shutdown') are deprecated in the current version of Starlette and will be removed in a future release. The replacement is the lifespan context manager, which keeps startup and shutdown logic together in a single function using a yield statement. This is more readable, ensures paired resources are always released, and avoids the need for global variables to share state between the two event handlers.

What is a session in SQLModel and how should I use it?

A session is a temporary workspace for a group of related database operations. It tracks new and modified objects and writes them to the database when you call session.commit(). In FastAPI, one session is created per request using the get_session() dependency and is closed automatically when the request is done. Sessions must never be shared across requests or stored in global variables — each request must use its own session to prevent data from one request leaking into another.

How do I inject a database session into a FastAPI route?

Define a get_session() generator function in db.py that yields a Session and declare it as a dependency in the route function: session: Session = Depends(get_session). FastAPI calls get_session() before the route handler runs, passes the yielded session as the session argument, and closes the session automatically when the handler returns. The correct implementation uses a with block: with Session(engine) as session: yield session — this guarantees cleanup even if an exception occurs.

What does session.add() do in SQLModel?

session.add(task) registers the object with the session and marks it as pending insertion. At this point the object is tracked in memory but nothing has been written to the database. The actual INSERT SQL runs when you call session.commit(). You must call session.add() before session.commit() for any new object you want to save.

What does session.refresh() do and why is it necessary after session.commit()?

session.refresh(task) re-reads the row from the database after a commit and updates the Python object with any database-assigned values. This is necessary because the database assigns the primary key integer during the INSERT, and without calling refresh(), the id field on the Python object remains None even after the commit. Returning the task object without refreshing it would send a response with id set to null instead of the real integer.

What is the get_session() dependency in FastAPI SQLModel projects?

get_session() is a FastAPI dependency — a generator function that creates a database Session for each incoming request and closes it cleanly afterward. It uses a with block around Session(engine) and yields the session inside the block. When the route handler finishes, FastAPI resumes the generator after the yield, which exits the with block and closes the session. This pattern guarantees cleanup even if the handler raises an exception.

What is SQLite and when should I switch to PostgreSQL?

SQLite is a file-based database that stores everything in a single file and requires no server process or separate installation. It is the correct choice for learning, development, and small-to-medium single-server applications. Switch to PostgreSQL when you need concurrent writes from multiple server processes or when the database must be on a separate machine from the application. In SQLModel, switching databases requires only changing the DATABASE_URL connection string — the models, sessions, and queries remain identical.

What does echo=True do in create_engine?

When echo=True is passed to create_engine, SQLAlchemy prints every SQL statement it executes to the terminal. This is useful during development for seeing exactly what queries are being generated — INSERT, SELECT, UPDATE, DELETE — and for debugging unexpected behaviour. Set echo=False in production to avoid log noise and minor performance overhead.

Why should I use datetime.now(timezone.utc) instead of datetime.utcnow() for timestamps?

datetime.utcnow() is deprecated since Python 3.12. It returns a naive datetime object with no timezone information, which means Python cannot distinguish it from a local time. datetime.now(timezone.utc) returns a timezone-aware datetime that is explicitly marked as UTC, which is the correct type for timestamps in production systems. When using default_factory in SQLModel Field, use lambda: datetime.now(timezone.utc) rather than passing datetime.utcnow directly.

How does SQLModel eliminate duplication between database models and API schemas?

In a project that uses SQLAlchemy and Pydantic separately, you define a SQLAlchemy class for database operations and a separate Pydantic class for request validation and response serialization. When the data shape changes, you must update both. SQLModel merges these into a single class: one class with table=True serves as both the ORM model for database sessions and the Pydantic model for FastAPI request parsing and response serialization. The same object that FastAPI populates from the request JSON body can be passed directly to session.add() without any conversion.

Found this helpful? Share the knowledge...

... or support this project to keep it alive!

Buy Me a Coffee

Previous

Database Integration

Next

Creating Models and Tables