Working with a Database

Until now our notes have lived in a Python list, which means they evaporate the instant you restart the server. That was fine for learning request handling, but no real app remembers its data by holding it in a variable. We need to persist — to write notes somewhere that survives a restart, a crash, a deploy. That somewhere is a database, and this phase is about teaching Flask to talk to one.

Here's the mental model to carry in before any code. 📝 Flask has no built-in database layer. None. Unlike Django, which ships its own ORM as part of the framework, Flask's core knows nothing about SQL, tables, or rows — and that's deliberate. Flask is a small core plus whatever you choose to bolt on. Want a database? You add an extension. This phase is the clearest, most concrete look you'll get at that philosophy: we'll watch "Flask = small core + chosen extensions" play out line by line, using the most popular choice, Flask-SQLAlchemy.

The extension philosophy, made concrete

📝 An ORM (Object-Relational Mapper) maps your objects to database rows and back. You work with a Note object in Python; the ORM figures out the INSERT, SELECT, and UPDATE statements that move it in and out of a table. If that idea is new, the Hibernate & JPA guide walks through the object-vs-table "impedance mismatch" in depth (it's Java, but the concept is identical), and what a database actually is covers the tables-rows-columns foundation underneath it all.

Python's premier ORM is SQLAlchemy — a standalone library that works with or without Flask. Flask-SQLAlchemy is a thin integration layer: it doesn't reinvent the ORM, it wires SQLAlchemy into Flask's app and request lifecycle so the two cooperate cleanly. That distinction matters. SQLAlchemy does the heavy lifting; Flask-SQLAlchemy just makes it feel native to Flask.

flowchart LR
  V[Your view function] --> FS[Flask-SQLAlchemy<br/>integration glue]
  FS --> SA[SQLAlchemy<br/>the actual ORM]
  SA --> DB[(Database<br/>SQLite file)]

What just happened: the diagram is the whole stack. Your view talks to Flask-SQLAlchemy, which is glue; the glue hands off to SQLAlchemy, the real ORM; SQLAlchemy generates SQL and runs it against the database. Flask itself isn't in this chain at all — it gained database powers purely by adding an extension, exactly the pattern the whole framework is built on.

First, install it (this pulls SQLAlchemy in as a dependency):

$ pip install Flask-SQLAlchemy

What just happened: one package, and your Flask app can now speak to a database. Nothing about Flask's core changed — you extended it from the outside, which is the entire point.

Setup: wiring the extension into the app

The extension needs two things: where the database lives, and a handle (db) you'll use everywhere to talk to it.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Where the database lives. SQLite = a single file on disk, perfect for learning.
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///notes.db"

# Create the extension and bind it to this app.
db = SQLAlchemy(app)

What just happened: the config line is a connection URI — sqlite:///notes.db means "use SQLite, stored in a file called notes.db next to the app." (Swap that string for a postgresql://... URI later and almost nothing else changes — that's the ORM earning its keep.) Then db = SQLAlchemy(app) creates the extension and hands it the app, so it can hook into Flask's lifecycle. Concretely, it sets up a database session per request — a fresh workspace that opens when a request arrives and closes when the response goes out. You don't manage that plumbing; the extension does, because it knows Flask's request cycle.

💡 That db object is your gateway to everything — defining models, querying, saving. By convention it lives at module level so the rest of your app can import it.

Defining a model

A model is a Python class that describes one table. Our notes app needs exactly one: a Note with an id, a title, and some content.

class Note(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    content = db.Column(db.Text, nullable=False)

    def __repr__(self):
        return f"<Note {self.id}: {self.title}>"

What just happened: by inheriting from db.Model, the Note class becomes a mapped entity — SQLAlchemy now knows this class corresponds to a table. Each db.Column is one column: id is an auto-incrementing integer primary key (the unique handle for each row), title is a short string capped at 120 characters, and content is Text for longer, unbounded writing. nullable=False means the database refuses to store a note missing that field. The __repr__ is just for readable debugging — it has no effect on the table.

That class implies a table. The CREATE TABLE SQLAlchemy generates for it looks like this:

CREATE TABLE note (
    id INTEGER NOT NULL PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    content TEXT NOT NULL
);

What just happened: this is the table your Note class describes, written in the database's own language. Notice the mapping is one-to-one: class → table (note), attribute → column, db.String(120) → VARCHAR(120), nullable=False → NOT NULL. You never write this SQL by hand; SQLAlchemy generates it from the model. To actually create the table:

with app.app_context():
    db.create_all()

What just happened: db.create_all() looks at every model you've defined and creates any tables that don't yet exist. It runs inside an app context because the extension needs to know which app's database to build against — a detail that becomes important once you have more than one app, which the next phase covers. Run this once and notes.db appears on disk with an empty note table waiting.

CRUD: the four things you do to data

CRUD is Create, Read, Update, Delete — the four operations every data-backed app performs. With the model in place, each one is a few lines, and the SQL stays invisible. Let's wire them into the note views from Phase 4, where notes used to go into a list.

Create — make an object, add it to the session, commit:

@app.route("/notes", methods=["POST"])
def create_note():
    note = Note(title=request.form["title"], content=request.form["content"])
    db.session.add(note)      # stage it in the session
    db.session.commit()       # write it to the database for real
    return redirect(url_for("list_notes"))

What just happened: you build a plain Note object from the submitted form data, then db.session.add(note) stages it — it's pending, not yet saved. db.session.commit() is the moment it actually hits the database (SQLAlchemy generates the INSERT and runs it). After commit, note.id is populated automatically with the new primary key. The redirect-after-POST pattern from Phase 4 still applies — commit, then send the browser to the list.

Read — fetch all notes, or one:

@app.route("/notes")
def list_notes():
    notes = Note.query.all()                     # SELECT * FROM note
    return render_template("notes.html", notes=notes)

@app.route("/notes/<int:note_id>")
def show_note(note_id):
    note = Note.query.get_or_404(note_id)        # fetch by PK, or 404
    return render_template("note.html", note=note)

What just happened: Note.query is your query entry point. .all() runs a SELECT and returns every row as a list of Note objects — you get objects back, not raw rows, which is the ORM's whole promise. .get_or_404(note_id) looks up a single note by primary key and, if none exists, raises a 404 for you instead of returning None and letting a later line crash. That helper is Flask-SQLAlchemy being thoughtful about the web: a missing record should be a clean 404, and it makes that the easy path. Need to filter on a non-key column? Note.query.filter_by(title="Groceries").all() runs a SELECT ... WHERE title = ?.

Update and Delete — both go through the session and finish with a commit:

# Update: change the object, then commit.
note = Note.query.get_or_404(note_id)
note.title = "Updated title"
db.session.commit()           # SQLAlchemy notices the change and runs UPDATE

# Delete: remove from the session, then commit.
db.session.delete(note)
db.session.commit()           # runs DELETE

What just happened: for an update you don't call a "save" method — you just mutate the object and commit. SQLAlchemy tracks which loaded objects changed and generates the UPDATE for exactly those. Delete is symmetric with add: db.session.delete(note) stages the removal, and commit() makes it real. The session is the common thread through all four operations.

The session, migrations, and the gotchas worth knowing now

📝 db.session is the unit of work. Think of it as a notepad of pending changes — adds, deletes, and modifications you've made to loaded objects. Nothing in that notepad touches the real database until you commit(). This is the single most important habit:

⚠️ No commit, no persistence. Forget db.session.commit() and your add quietly does nothing durable — the request ends, the session is discarded, and your note is gone. The mirror-image habit is db.session.rollback(): if something goes wrong mid-request, roll back to throw away the half-finished pending changes so the next request starts clean. Commit on success; roll back on error.

⚠️ create_all() only creates missing tables — it never alters existing ones. The day you add a created_at column to Note, create_all() will look at the already-existing note table, see it's there, and do nothing. Your new column won't appear and you'll be baffled. Schema changes to a live table are a real job, handled by Flask-Migrate (a Flask wrapper around Alembic, SQLAlchemy's migration tool). Migrations generate and version the ALTER TABLE steps so your schema can evolve safely. create_all() is a fine starting line; it is not how you change a schema later.

⚠️ The N+1 trap is still here. The moment your Note grows a relationship — say each note belongs to a notebook — looping over notes and touching note.notebook can fire one extra query per note. One query becomes N+1, and your list page slows to a crawl as data grows. It's the most common ORM performance bug across every language; the why is my query slow guide unpacks how to spot and fix it. Knowing it exists is half the battle.

💡 Step back and notice what you just did. Your notes app persists now — restart the server and they're still there. And you got there not by reaching into Flask's core, but by adding an extension and letting it integrate cleanly. You've now seen Flask's defining philosophy work end to end, with your own hands. That same pattern — small core, chosen extensions — is exactly what the next phase scales up.

Recap

1. Flask ships no ORM — unlike Django, you add one. This is the purest example of "Flask = small core + chosen extensions."
2. Flask-SQLAlchemy is integration glue over SQLAlchemy (Python's real ORM); it wires a per-request session into Flask's lifecycle so the ORM feels native.
3. A model is a class inheriting db.Model, with db.Column attributes mapping one-to-one to table columns; db.create_all() builds the tables.
4. CRUD flows through db.session: add + commit to create, Note.query.all() / get_or_404() / filter_by() to read, mutate-then-commit to update, delete + commit to remove.
5. ⚠️ No commit, no persistence — db.session holds pending changes until you commit(); rollback() on error. And create_all() never alters existing tables — schema changes need Flask-Migrate (Alembic).
6. ⚠️ The N+1 query trap applies the moment you add relationships — the same ORM gotcha you'd hit in any language.

Quick check

Three questions on the ideas that have to stick before Phase 6:

[
  {
    "q": "Why does Flask need Flask-SQLAlchemy at all, instead of having a database layer built in?",
    "choices": [
      "Flask's core ships no ORM by design — it's a small core, and you add database support as an extension",
      "Flask has a built-in ORM but it's deprecated, so you replace it with Flask-SQLAlchemy",
      "Flask-SQLAlchemy is required to run any Flask app, database or not",
      "Flask can only talk to SQLite, so the extension adds support for other databases"
    ],
    "answer": 0,
    "explain": "Unlike Django, Flask deliberately ships no ORM. It's a small core plus chosen extensions; Flask-SQLAlchemy is the extension you add to gain database support, integrating the standalone SQLAlchemy ORM into Flask's lifecycle."
  },
  {
    "q": "You call db.session.add(note) in a view but never call commit(). What happens to the note?",
    "choices": [
      "Nothing durable — the change stays pending in the session and is discarded when the request ends, so the note is not saved",
      "It is saved immediately, because add() writes to the database right away",
      "Flask raises an error at the end of the request forcing you to commit",
      "It is saved, but only to memory, and reloaded automatically next startup"
    ],
    "answer": 0,
    "explain": "db.session is the unit of work: add() only stages a pending change. Nothing hits the database until commit(). Without it, the session is discarded at request's end and the note is lost — commit on success, rollback on error."
  },
  {
    "q": "You add a new column to your Note model and run db.create_all() again. The column doesn't appear. Why?",
    "choices": [
      "create_all() only creates tables that don't already exist — it never alters an existing table; schema changes need a migration tool like Flask-Migrate",
      "You forgot to commit the session after create_all()",
      "create_all() can only be run once per database, ever",
      "SQLite doesn't support adding columns, so you must switch to PostgreSQL"
    ],
    "answer": 0,
    "explain": "create_all() builds only missing tables. The note table already exists, so it does nothing. Evolving an existing schema (adding columns, etc.) is handled by Flask-Migrate (Alembic), which generates versioned ALTER TABLE steps."
  }
]

← Phase 4: Forms & Request Data · Guide overview · Phase 6: Blueprints & the App Factory →