SQLModel Tutorial: Build a SQLite App

Working with SQLite rows from Python starts with mapping your application’s data to database tables. SQLModel maps class fields to database columns and connects your models to SQLite through an engine and sessions.

I’ll walk through building the models and database setup, then show how sessions handle database rows.

TL;DR

Build the table models, create a SQLite engine, and use short-lived sessions to commit database changes. The runnable sample below defines its models, creates tables, and demonstrates CRUD operations.

• Use table=True for a model that maps to a database table.
• Use create_engine(“sqlite:///tutorial.db”) for a file-backed SQLite database.
• Use Session with a context manager, then commit writes.

What Is SQLModel?

SQLModel combines Pydantic data models with SQLAlchemy’s object-relational mapping. A table model maps class fields to database columns, while an engine and session connect those models to SQLite. In this tutorial, defining that mapping is the first step before creating the database tables.

If you are new to SQLite from Python, start with the Python SQLite guide. This page focuses on SQLModel’s ORM layer rather than the standard-library driver.

The shared model layer is useful when input data and database rows have the same shape. SQLModel also supports plain data models that do not create tables, so an application can use different models for stored rows and external input. This tutorial keeps the example small and uses table models for both entities.

Editor support follows the declared Python types, and SQLModel’s official documentation describes compatibility with SQLAlchemy and Pydantic. The type hints make required fields and optional relationships visible while reading the model. They do not remove the need to validate business rules or database constraints.

How to Build a SQLModel SQLite App

Build the application by defining related models, creating the engine and tables, then using sessions for database operations. I ran the complete example with Python 3.14.7, SQLModel 0.0.48, and SQLAlchemy 2.1.8 in a clean workspace.

Install SQLModel in the Python environment that will run the program. The package installs SQLAlchemy and Pydantic as dependencies, so the example imports all three layers through SQLModel’s API.

python -m pip install sqlmodel

The command returned this package check in the isolated workspace:

Requirement already satisfied: sqlmodel in ./.venv/lib/python3.14/site-packages (0.0.48)

The installed versions were SQLModel 0.0.48 and SQLAlchemy 2.1.8. If Python reports No module named sqlmodel, the install command likely ran with a different interpreter than the one launching the script.

Step 1: Define the table models

A table model inherits from SQLModel and sets table=True. The optional integer primary keys start as None because SQLite assigns their values when rows are inserted. team_id is a foreign key to team.id, and Relationship provides the Python-side link between the two rows.

The Field declaration gives id a primary-key role. Without default=None, the model would expect an ID before insertion instead of letting the database assign it. A field such as name: str is required.

The optional team_id: int | None field allows a Hero row to exist without a Team. Its foreign-key declaration still makes SQLite store a link when a team is assigned.

The two Relationship declarations point back to each other through back_populates. The list on Team represents several heroes, and the optional Team field on Hero represents its single parent.

The foreign key stores the database relationship. The ORM relationship gives Python objects a convenient way to traverse it. SQLModel’s official tutorial uses this paired declaration for one-to-many relationships.

Step 2: Create an engine and tables

An engine manages database connections. The three-slash SQLite URL names a file relative to the current working directory. SQLModel’s metadata create_all method creates tables that are missing, which is useful for a new tutorial database.

For a file database, the URL begins with sqlite:/// followed by the path. A relative path keeps the file in the program’s working directory, while an absolute path makes its location explicit.

SQLite can also use an in-memory database, but that has different connection-lifetime behavior. I chose a file here because the tutorial should leave a database that remains after the process exits.

Define every table model before calling SQLModel’s create_all method, because SQLModel registers table metadata as classes are imported. This call creates the Team and Hero tables in the same database. It is idempotent for tables that already exist, but it does not apply alterations to those tables.

Use the engine once at application setup and pass it to each session. Creating a new engine for every query adds connection-management work without changing the model or the database. A session is the boundary for pending changes, and its context manager closes the session when the block ends.

When a write fails before commit, the transaction has not been saved. In a larger program, handle the exception and roll back the session before reusing it. This tutorial keeps each session short and lets errors surface, which makes the first database interactions visible instead of hiding them behind a service layer.

The file path also affects where the database appears. Running the same relative SQLite URL from a different working directory can create or open a different file. Use an absolute path or set the working directory in your application when the database location must stay fixed.

These choices keep the first example focused: one engine points at one file, and each session handles one unit of work. A production application can add configuration, migrations, and request-scoped session management after this basic flow is clear.

Save this as tutorial.py in a directory where you installed SQLModel, then run the file. The example writes a disposable local database called tutorial.db.

python tutorial.py

The script completed and printed these results:

Created hero 1: Rusty-Man
Selected Rusty-Man; team_id=1
Updated hero: Rusty-Man II
Deleted hero

Step 3: Add, select, update, and delete rows

A session tracks objects during a unit of work. Add objects and commit to persist them, query with a select expression, and commit edits or deletions before the session closes.

The first session adds a Hero with a Team object already attached. SQLAlchemy flushes the related rows in dependency order, so the team row is available when the hero’s foreign key is written. After commit, refresh reloads the generated primary key into the Python object.

The next session builds a typed query for Hero records and filters by name. The session execution method runs the statement, and the one-result accessor returns its matching row. If no row or more than one row matches, that accessor raises an exception, which makes the expectation explicit.

Changing an attribute on a session-loaded object marks it dirty. Adding it again is safe but not required for an object already attached to that session. The commit writes the change.

The final session deletes the Hero row and commits. The Team row remains because this example does not configure cascading deletion.

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select


class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    heroes: list["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")


engine = create_engine("sqlite:///tutorial.db", echo=False)
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    team = Team(name="Preventers")
    hero = Hero(name="Rusty-Man", secret_name="Tommy Sharp", team=team)
    session.add(hero)
    session.commit()
    session.refresh(hero)
    print(f"Created hero {hero.id}: {hero.name}")

with Session(engine) as session:
    hero = session.exec(select(Hero).where(Hero.name == "Rusty-Man")).one()
    print(f"Selected {hero.name}; team_id={hero.team_id}")
    hero.name = "Rusty-Man II"
    session.add(hero)
    session.commit()
    print(f"Updated hero: {hero.name}")

with Session(engine) as session:
    hero = session.exec(select(Hero).where(Hero.name == "Rusty-Man II")).one()
    session.delete(hero)
    session.commit()
    print("Deleted hero")

The program prints the ID that SQLite assigned and confirms each CRUD operation:

Created hero 1: Rusty-Man
Selected Rusty-Man; team_id=1
Updated hero: Rusty-Man II
Deleted hero

The selection uses Session.exec() with SQLModel’s typed select() expression. Each context manager closes its session, and each write is committed before the next one begins.

SQLite Edge Cases to Check

SQLite connection behavior and table creation have limits that matter once the example grows. I keep the basic file-backed engine settings unless the application has a demonstrated need for shared connections across threads.

What does check_same_thread=False change?

The Python SQLite driver normally checks that a connection is used by the thread that created it. SQLAlchemy documents this as a driver connection option.

FastAPI’s SQLite example passes the connect_args dictionary with check_same_thread set to False for its request-handling setup. That option removes the driver’s thread check, but it does not make concurrent writes safe by itself.

Keep it out of a single-threaded script unless your connection-sharing design requires it. A session should still belong to one request or unit of work, and concurrent access to a connection needs an intentional design.

This connection check shows where the driver option belongs. It opens a connection and runs a read-only query.

from sqlmodel import create_engine

engine = create_engine("sqlite:///tutorial.db", connect_args={"check_same_thread": False})
with engine.connect() as connection:
    print(connection.exec_driver_sql("SELECT 1").scalar_one())

1

The query proves that the engine connects with the option set. It does not test concurrent use, so do not treat it as proof that one connection can be shared safely among threads.

Developers often encounter the message “SQLite objects created in a thread can only be used in that same thread.” Treat that as a sign to inspect which thread owns the connection and how sessions are created, not as a reason to disable the check everywhere.

SQLAlchemy’s SQLite dialect describes connection pooling and thread checks as separate concerns. When a framework runs work on multiple threads, its documented example may pass a driver option to allow the connection to cross that boundary. A session should still belong to one request or unit of work, and concurrent access to the same connection needs an intentional design.

Does create_all update an existing table?

The create_all method creates missing tables, but does not compare a changed model to an existing table and migrate its schema. For a disposable tutorial file, recreate the database when you change the model.

For data you need to keep, use a migration tool such as Alembic and review each generated migration before applying it. That distinction prevents a model edit from being mistaken for a database migration. Keep the original file until the migration has been checked.

Situation	What to check	Next step
Module import fails	Interpreter and environment differ	Install SQLModel with the Python executable that runs the app
SQLite thread error	Which thread created and uses the connection	Scope sessions to a unit of work and follow the framework’s documented connection setup
New model field is absent	Table already existed before model change	Use a migration for retained data, or recreate disposable tutorial data

For an API built on these models, see the separate FastAPI and SQLite tutorial. It covers request handling, while this example stays focused on SQLModel and SQLite.

Conclusion: Keep the Session and Schema Separate

A SQLModel application needs a table model, an engine, and short-lived sessions that commit their writes. Use the create_all method for a new schema, but switch to migrations when existing data and schema changes need to coexist.

FAQ

These answers cover common setup and modeling questions from the SQLModel documentation.