SQLAlchemy SQLite Tutorial: Models, CRUD and Foreign Keys (Tested)

SQLAlchemy lets you write session.add(post) instead of an INSERT. On SQLite there’s a catch: foreign key enforcement is off by default and SQLAlchemy leaves it that way, so a post can point at a user who doesn’t exist. In this article I’ll walk you through SQLAlchemy with SQLite from create_engine() to working CRUD, with foreign keys enforced the way the SQLAlchemy docs recommend.

TLDR

• Install with pip install sqlalchemy. The SQLite driver is Python’s built-in sqlite3, so there’s nothing else to install.
• Connect with create_engine("sqlite:///app.db") for a file next to your script, or four slashes for an absolute path on macOS and Linux.
• Define models with DeclarativeBase and Mapped[...], then run Base.metadata.create_all(engine).
• Turn on foreign keys with a connect event that runs PRAGMA foreign_keys=ON on every connection.
• create_all() never changes an existing table. Add new columns with ALTER TABLE or a migration tool.

What is SQLAlchemy, and what will we build?

SQLAlchemy is a Python SQL toolkit and object-relational mapper (ORM): you describe tables as Python classes and query them with Python expressions, and SQLAlchemy writes the SQL. It talks to SQLite through its built-in SQLite dialect (SQLAlchemy: SQLite).

To show you the full cycle, I will build a tiny blog with users and posts, where each post belongs to a user.

Prerequisites

Requirement	Version used here
Python	3.13.14
SQLAlchemy	2.1.3
SQLite	3.53.4 (the library Python uses)

Python 3.13.14 | SQLAlchemy 2.1.3 | SQLite 3.53.4

Building a blog database with SQLAlchemy and SQLite

Each step is a separate script in the same folder, working on blog.db.

Step 1: Install SQLAlchemy

$ pip install sqlalchemy
Successfully installed sqlalchemy-2.1.3 typing-extensions-4.16.0

Step 2: Define the models and the engine

Save this as models.py. The event listener is the SQLAlchemy docs’ recipe for SQLite foreign keys, which must be switched on “on all connections before use”:

from sqlalchemy import ForeignKey, String, create_engine, event
from sqlalchemy.engine import Engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    # From the SQLAlchemy SQLite docs: enforce foreign keys on every connection.
    ac = dbapi_connection.autocommit
    dbapi_connection.autocommit = True
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()
    dbapi_connection.autocommit = ac


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50), unique=True)
    email: Mapped[str | None]
    posts: Mapped[list["Post"]] = relationship(back_populates="author", cascade="all, delete-orphan")

    def __repr__(self):
        return f"User(id={self.id}, name={self.name!r})"


class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped[User] = relationship(back_populates="posts")


engine = create_engine("sqlite:///blog.db")

The URL sqlite:///blog.db (three slashes) means a file relative to the current folder. On macOS and Linux, an absolute path needs four: sqlite:////home/me/blog.db. On Windows it’s three slashes and the drive: sqlite:///C:\path\to\blog.db (SQLAlchemy: engine URLs).

Step 3: Create the tables

echo=True prints the SQL SQLAlchemy sends to SQLite:

from sqlalchemy import create_engine
from models import Base

engine = create_engine("sqlite:///blog.db", echo=True)
Base.metadata.create_all(engine)

$ python create.py
BEGIN (implicit)
CREATE TABLE users (
	id INTEGER NOT NULL,
	name VARCHAR(50) NOT NULL,
	email VARCHAR,
	PRIMARY KEY (id),
	UNIQUE (name)
)
CREATE TABLE posts (
	id INTEGER NOT NULL,
	title VARCHAR NOT NULL,
	user_id INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY(user_id) REFERENCES users (id)
)
COMMIT

Step 4: Create, read, update and delete

from sqlalchemy import delete, select, update
from sqlalchemy.orm import Session
from models import Post, User, engine

with Session(engine) as session:
    # Create
    ada = User(name="ada", email="ada@example.com")
    ada.posts = [Post(title="Notes on engines"), Post(title="SQLite in Python")]
    session.add_all([ada, User(name="linus")])
    session.commit()

    # Read
    print(session.scalars(select(User).order_by(User.id)).all())
    print(session.scalar(select(User).where(User.name == "ada")).posts[1].title)

    # Update
    session.execute(update(User).where(User.name == "linus").values(email="linus@example.com"))
    session.commit()
    print(session.scalar(select(User.email).where(User.name == "linus")))

    # Delete (the cascade removes ada's posts too)
    session.execute(delete(Post).where(Post.title.like("%engines%")))
    session.delete(session.scalar(select(User).where(User.name == "ada")))
    session.commit()
    print(session.scalar(select(User.name)), session.query(Post).count(), "posts left")

$ python crud.py
[User(id=1, name='ada'), User(id=2, name='linus')]
SQLite in Python
linus@example.com
linus 0 posts left

What to notice:

• ada.posts = [...] saves the posts with the user, and fills in user_id for you.
• session.scalars(select(...)) returns model objects, and session.scalar() returns the first one.
• cascade="all, delete-orphan" on the relationship deleted ada’s remaining post with her.

Step 5: Check that foreign keys are enforced

Without the event listener, SQLite accepts a post for a user that doesn’t exist:

# No event listener: foreign keys stay off, so an orphan row goes in.
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///blog.db")
with engine.begin() as conn:
    print("foreign_keys =", conn.execute(text("PRAGMA foreign_keys")).scalar())
    conn.execute(text("INSERT INTO posts (title, user_id) VALUES ('Orphan', 999)"))
    print(conn.execute(text("SELECT title, user_id FROM posts WHERE user_id = 999")).all())
    conn.execute(text("DELETE FROM posts WHERE user_id = 999"))  # clean up for the next step

$ python fk_off.py
foreign_keys = 0
[('Orphan', 999)]

With models.py imported, the listener is active and the same insert fails:

from sqlalchemy import text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session
from models import Post, engine

with Session(engine) as session:
    print("foreign_keys =", session.execute(text("PRAGMA foreign_keys")).scalar())
    session.add(Post(title="Orphan", user_id=999))
    try:
        session.commit()
    except IntegrityError as e:
        print("IntegrityError:", e.orig)

$ python fk_check.py
foreign_keys = 1
IntegrityError: FOREIGN KEY constraint failed

Running it: SQLite-specific features

These scripts run against the same blog.db after the steps above.

Upsert with on_conflict_do_update

SQLAlchemy’s SQLite dialect has its own insert() with on_conflict_do_update(), which becomes SQLite’s INSERT ... ON CONFLICT DO UPDATE:

from sqlalchemy.dialects.sqlite import insert
from sqlalchemy.orm import Session
from models import User, engine

stmt = insert(User).values(name="linus", email="torvalds@example.com")
stmt = stmt.on_conflict_do_update(index_elements=[User.name], set_={"email": stmt.excluded.email})
with Session(engine) as session:
    session.execute(stmt)
    session.commit()
    print(session.get(User, 2).email)

$ python upsert.py
torvalds@example.com

See SQLite upsert for the SQL behind it.

Run raw SQL with text()

from sqlalchemy import text
from models import engine

with engine.connect() as conn:
    rows = conn.execute(text("SELECT name, email FROM users WHERE name = :name"), {"name": "linus"})
    print(rows.all())

$ python raw_sql.py
[('linus', 'torvalds@example.com')]

Add a column to an existing table

create_all() creates missing tables, but it doesn’t alter tables that already exist. A new model column is ignored until you add it yourself:

# Adding a column to the model does NOT change an existing table: create_all() skips tables that exist.
from sqlalchemy import Column, Integer, inspect, text
from models import Base, User, engine

User.__table__.append_column(Column("karma", Integer, default=0))
Base.metadata.create_all(engine)
print([c["name"] for c in inspect(engine).get_columns("users")])
with engine.begin() as conn:
    conn.execute(text("ALTER TABLE users ADD COLUMN karma INTEGER DEFAULT 0"))
print([c["name"] for c in inspect(engine).get_columns("users")])

$ python new_column.py
['id', 'name', 'email']
['id', 'name', 'email', 'karma']

In a real project, a migration tool such as Alembic generates these ALTER TABLE statements. See SQLite ALTER TABLE ADD COLUMN.

Common problems

The first two errors came up while testing, each with its message first. The third wasn’t reproduced here.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file

The folder in the URL doesn’t exist, or the process can’t write there. A relative path is relative to where you start Python, not to the script:

from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///no/such/folder/app.db")
try:
    with engine.connect() as conn:
        conn.execute(text("SELECT 1"))
except Exception as e:
    print(type(e).__name__ + ":", str(e).splitlines()[0])

$ python bad_path.py
OperationalError: (sqlite3.OperationalError) unable to open database file

Use an absolute path (four slashes on macOS and Linux), or build it from Path(__file__).parent. See unable to open database file.

ERROR: No matching distribution found for sqlalchemy.dialects.sqlite

The SQLite dialect is part of SQLAlchemy, not a separate package:

$ pip install sqlalchemy.dialects.sqlite
ERROR: No matching distribution found for sqlalchemy.dialects.sqlite

database is locked

A second session tried to write while another held the lock, and it gave up. Keep sessions short and commit promptly. See database is locked. For async code, SQLAlchemy uses aiosqlite through the sqlite+aiosqlite:// URL.

Conclusion

SQLAlchemy and SQLite need almost no setup: one pip install and a sqlite:/// URL. Add the foreign-key listener from the start, and plan on Alembic or ALTER TABLE once your models change. For Flask apps, Flask SQLite shows Flask-SQLAlchemy on the same database.

Further reading

• SQLite CREATE TABLE
• SQLite INSERT
• SQLite SELECT
• SQLite UPDATE
• SQLite DELETE
• SQLite foreign keys
• aiosqlite
• Flask SQLite
• SQLAlchemy docs: SQLite dialect

Tested with SQLAlchemy 2.1.3 and Python 3.13.14 (SQLite 3.53.4) on 7 October 2026.