Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 72 ยท Databases

Flask-SQLAlchemy Models

Define tables as Python classes with Flask-SQLAlchemy 3 and SQLAlchemy 2 style Mapped columns, create the schema, then add, query and roll back with db.session.

Writing raw SQL, as in the sqlite3 lesson, is direct but repetitive. An ORM (object-relational mapper) lets you describe each table as a Python class and each row as an object, then translates your Python into SQL. SQLAlchemy is Python's standard ORM, and Flask-SQLAlchemy is the small extension that wires it into Flask: it creates the engine from your config, gives you a request-aware db.session, and provides the db.Model base class.

This lesson uses the modern SQLAlchemy 2 style (Mapped[...], mapped_column() and select()), which is what current documentation recommends. You will define a model, create its table, add and read rows, and see why a failed write needs a rollback(). In the browser, Flask-SQLAlchemy installs with micropip and uses an in-memory SQLite database.

Setting up the extension

The setup has three parts: create a db object, configure the database URL, and connect them with init_app().

Python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///:memory:"
db.init_app(app)

SQLALCHEMY_DATABASE_URI is the connection string. sqlite:///:memory: is an in-memory SQLite database; a file would be sqlite:///app.db (stored in the instance folder), and a server database looks like postgresql://user:password@host/dbname. Creating db separately from app and calling init_app() later is what makes the application factory pattern possible.

Defining a model

A model is a class that inherits from db.Model. Each column is an annotated attribute: the type inside Mapped[...] says what Python type it holds, and mapped_column() adds database details such as length, uniqueness and defaults.

Some rules worth knowing:

  • Mapped[int] is NOT NULL; Mapped[int | None] is nullable.
  • mapped_column(primary_key=True) marks the primary key; an integer primary key auto-increments.
  • Flask-SQLAlchemy names the table from the class in snake_case: Book becomes book, BlogPost becomes blog_post. Set __tablename__ to override it.
flowchart LR
    A["class Book(db.Model)<br/>id, title, author, year"] -- "create_all()" --> B["table book<br/>id | title | author | year"]
    C["Book(title='Emma', ...)<br/>a Python object"] -- "add + commit" --> D["a row in book"]
    D -- "select(Book)" --> E["Book objects again"]

A class describes the table; an instance of the class is one row.

Python Models and create_all

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.

db.create_all() issues CREATE TABLE for every model that does not have a table yet. It never changes an existing table, so adding a column later needs a migration tool (see Migrations). Notice the SQL it generates: year is the only nullable column, and UNIQUE (isbn) comes from unique=True.

Flask-SQLAlchemy needs an application context to know which app's database to use, which is why the code uses with app.app_context():. Inside a view function the context already exists, so you can use db.session directly.

The session: add, flush, commit

You never write rows directly. You work through db.session, a unit of work that collects your changes and sends them in one transaction:

  1. db.session.add(obj) (or add_all([...])) marks an object as pending.
  2. A flush sends the pending SQL to the database inside the open transaction. It happens automatically before queries and at commit time, and it is when ids get assigned.
  3. db.session.commit() makes the transaction permanent.
  4. db.session.rollback() throws the uncommitted changes away.

You read with select() statements run through the session. db.session.scalars(select(Book)) returns model objects; db.session.get(Book, 2) fetches by primary key and returns None when there is no such row.

Python Add, query, update, delete and roll back

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.

Look at the first print: before the commit, emma.id and in_stock are both None. The primary key comes from the database and the Python-side default=True is applied when the row is inserted. The IntegrityError shows why rollback() matters: after a failed flush, the session refuses further work until you roll back.

Updating needs no special call: change an attribute on an object the session knows about, then commit(). SQLAlchemy notices the change and issues an UPDATE. Queries and CRUD Views builds complete pages on top of these operations.

Older tutorials show Book.query.all(), db.Column(db.String) and Model.query.get(1). That style still runs in Flask-SQLAlchemy 3.1 but is marked legacy. Prefer db.session.scalars(select(...)) and Mapped annotations in new code.

db.session inside views

In a request, db.session is scoped to the application context: every request gets its own session, and Flask-SQLAlchemy removes it when the request ends. Anything you added but did not commit is simply discarded. This is the same lifecycle as the g connection in the previous lesson, handled for you.

Python Using db.session in views

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 draft vanishes because nothing committed it, while the two real notes remain. Also see how create reads note.id after the commit: the object is refreshed from the database, so the new id is available.

Good habits

  • Keep one db = SQLAlchemy(...) object in its own module (for example extensions.py) so models and the app factory can both import it.
  • Return plain dictionaries (or use a serialization library, see Validation and Serialization) instead of trying to jsonify a model object.
  • Set String(n) lengths deliberately; SQLite ignores them, but PostgreSQL and MySQL enforce them.
  • Commit once per unit of work, not after every line, so a failure rolls back the whole operation.
  • You will see SQLALCHEMY_TRACK_MODIFICATIONS = False in older tutorials. It switched off a feature that tracked every object change and sent signals, at a memory cost. Flask-SQLAlchemy 3 already defaults it to False, so you can leave it out unless you really want those signals.
  • Never build SQL from strings with user input. select(Book).where(Book.title == user_text) is parameterized for you.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does Mapped[int | None] mean in a model?

    A plain Mapped[int] is NOT NULL. Adding | None makes the column nullable.
  2. 2. A model class is named BlogPost. What table name does Flask-SQLAlchemy generate?

    The class name is converted to snake_case and is not pluralized. Set __tablename__ for a different name.
  3. 3. You call db.session.add(book) in a view and forget db.session.commit(). What happens?

    Only a commit makes changes permanent. The session is removed at the end of the request.
  4. 4. After an IntegrityError during commit(), what must you do before using the session again?

    A failed flush leaves the transaction in an error state. rollback() resets it.
  5. 5. What does db.create_all() do to a table that already exists?

    It never alters existing tables, which is why schema changes need migrations.

Summary

  • Flask-SQLAlchemy connects SQLAlchemy to Flask: create db = SQLAlchemy(model_class=Base), set SQLALCHEMY_DATABASE_URI, call db.init_app(app).
  • Models inherit from db.Model and use Mapped[...] plus mapped_column(); table names default to snake_case of the class name.
  • db.create_all() creates missing tables inside an app context but never alters existing ones.
  • Work through db.session: add, commit, rollback, delete, get, and select() via scalars().
  • Ids and Python-side defaults appear after the flush or commit; failed writes need rollback().
  • Each request has its own session, and uncommitted changes are discarded at the end of the request.

Next: Queries and CRUD Views, building create, read, update and delete pages.