7.2.1 TerpTasks has a fatal flaw🔗

Look back at storage.py from the REST lecture—a dict and a counter, with a comment calling it “deliberately boring.” It has a property we’ve politely ignored all semester: restart the server and every task on campus vanishes. Deploy a new version? Data gone. Crash? Gone. Two uvicorn workers? Two disagreeing dictionaries.

What we need is a database: a program whose entire job is keeping data correct and durable while many clients read and write it concurrently. And we specifically want a relational database, because our data is shaped like relationships: users own tasks, tasks carry tags, tags apply to many tasks. Forty years of theory and engineering have been aimed at exactly this shape.

Why MySQL? Because it is the relational database you are most likely to be handed. It is the most widely deployed open source RDBMS in the world: it runs WordPress and most of the LAMP-era web, it is the default in a large fraction of company codebases, and every cloud sells a managed version of it (Amazon RDS and Aurora, Google Cloud SQL, Azure Database, PlanetScale). It installs in one command, it is fast, and the internship you take next summer will probably have one running.

And one honest caveat. MySQL earned an old reputation for laxness: it used to silently truncate oversized strings, silently accept invalid dates, and—the part that matters most for this lecture—silently ignore constraints it had happily parsed. MySQL 8 fixed nearly all of it: strict SQL mode is on by default, InnoDB is the default storage engine, utf8mb4 is the default character set, and CHECK constraints are finally enforced. But “fixed by default” is not “impossible to break,” and you will meet servers configured by someone else. So this lecture teaches MySQL the way you should use it: verify the four settings first, then trust the schema. That conditional is itself a lesson about databases.

7.2.2 The stack, and where today fits🔗

The REST lecture’s central promise—swapping storage changes nothing about the API—gets cashed today: app/main.py keeps its routes; storage.py gets replaced by real tables.

7.2.3 Intuition: a database is a spreadsheet with a spine🔗

A table looks like a spreadsheet: rows and columns. The difference is that a spreadsheet suggests and a database enforces. A spreadsheet lets you type “banana” in the due-date column, leave the owner blank, and paste the same user twice with two different emails. A relational database, given the right schema, makes those states unrepresentable—the write is rejected before it happens. Schema design is deciding, up front, which wrong states can never exist. (Recognize the theme: types in Pydantic, contracts in REST, guardrails in the (part "Elephant-Goldfish") lecture. A schema is guardrails for data.)

MySQL sharpens the point by giving you a way to get it wrong: a schema is only guardrails if the server is configured to enforce it. Which brings us to the four settings.

7.3.1 Getting a server🔗

The fastest zero-install-on-your-laptop option is Docker:

docker run --name terpmysql \
  -e MYSQL_ROOT_PASSWORD=pw \
  -e MYSQL_DATABASE=terptasks \
  -p 3306:3306 -d mysql:8.4

mysql -h 127.0.0.1 -u root -ppw terptasks     # the interactive client

Inside the client, SHOW TABLES; lists tables, DESCRIBE tasks; summarizes one, and SHOW CREATE TABLE tasks\G prints the exact DDL MySQL is enforcing—which is the command you will reach for every time reality and your intentions disagree.

7.3.2 The four settings that decide whether your schema is real🔗

Run this on any MySQL server before you trust it:

SELECT VERSION();                  -- want 8.0.16 or newer (CHECK enforcement)
SELECT @@default_storage_engine;   -- want InnoDB       (foreign keys)
SELECT @@character_set_database;   -- want utf8mb4      (all of Unicode)
SELECT @@sql_mode;                 -- want STRICT_TRANS_TABLES in the list

Read the right-hand column again: the failure mode is silence. MySQL does not refuse your FOREIGN KEY on a MyISAM table; it accepts the statement and enforces nothing. That is precisely the class of bug this lecture exists to prevent, so we will be explicit in every CREATE TABLE:

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Those two table options are the defaults in MySQL 8. Write them anyway. They cost one line and they document an intention that a future server’s configuration cannot quietly revoke.

7.5.1 Tables and column types🔗

Formal definition. A table is a named set of rows; every row has the same columns, and every column has a data type the database enforces on write.

Choosing types is your first design act. The MySQL types you’ll use constantly:

Three habits worth acquiring immediately.

Never store money in floating point. DECIMAL is exact; FLOAT and DOUBLE are not. This is not a MySQL quirk, it is binary floating point, and it is how you end up a cent short a million times.

Prefer VARCHAR(n) to TEXT for anything you will index or constrain. MySQL cannot put a TEXT column in an index without a prefix length—email TEXT UNIQUE fails outright with “BLOB/TEXT column used in key specification without a key length”—and before MySQL 8.0.13 a TEXT column could not have a DEFAULT at all. Pick a generous n and move on.

Store timestamps as UTC, and know which type you chose. This one needs its own subsection, because MySQL is genuinely different here.

7.5.2 MySQL’s timestamp trap🔗

PostgreSQL has TIMESTAMPTZ, a timezone-aware moment. MySQL has no equivalent type. It offers two imperfect options:

TIMESTAMP looks like the timezone-aware type you wanted, and it half is—but its conversion depends on a session setting, so the same row reads differently to two clients, and it hits the 2038 wall. A task app that lets someone schedule a reminder for their grandchild’s graduation would break on TIMESTAMP.

Course rule: use DATETIME(6), put UTC in it, always, and convert to a local zone only at the display edge. The discipline your CLAUDE.md has enforced all semester does not go away under MySQL—it moves up one layer, from the database to your Python. That is a real cost of MySQL over PostgreSQL, and it is worth naming rather than papering over.

7.5.3 Primary keys🔗

Formal definition. A primary key (PK) is a column (or set of columns) whose value uniquely identifies each row: unique and non-null, one per table.

In plain English: every row needs a name that nothing else has and that never changes.

Intuition—why not use the email as the PK? It’s unique, after all. Because PKs get copied everywhere: every task will carry its owner’s PK. If the PK is the email and a user renames (@terpmail → @umd), you must rewrite every copy. A meaningless integer (surrogate key) never needs to change, because it means nothing. Real-world attributes make good UNIQUE constraints and bad primary keys.

In MySQL, auto-assigned surrogate keys are spelled AUTO_INCREMENT:

CREATE TABLE users (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    email  VARCHAR(255) NOT NULL UNIQUE,  -- natural uniqueness: constraint, not PK
    name   VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two MySQL facts about AUTO_INCREMENT: a table may have exactly one such column and it must be indexed (in practice, the primary key); and the counter never goes backwards, so a rolled-back insert permanently burns an id. Gaps in your id sequence are normal and mean nothing. If you ever find yourself explaining a gap to a user, your ids have leaked into the product—which is an argument for surrogate keys being invisible, not for making them gapless.

7.5.4 Foreign keys: relationships with teeth🔗

Formal definition. A foreign key (FK) is a column constrained to contain only values that exist as a primary key in another (or the same) table. The database rejects any write that would break the reference—this is referential integrity.

CREATE TABLE tasks (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    owner_id  INT NOT NULL,
    title     VARCHAR(200) NOT NULL,
    due       DATE NOT NULL,
    done      BOOLEAN NOT NULL DEFAULT FALSE,

    -- MySQL requires a *separate* FOREIGN KEY clause (see the trap below).
    CONSTRAINT fk_tasks_owner FOREIGN KEY (owner_id)
        REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT ck_tasks_title_nonempty CHECK (CHAR_LENGTH(title) > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The MySQL trap you must know. In PostgreSQL you may write the reference inline on the column:

owner_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE   -- NOT ENFORCED

MySQL parses this and throws it away. The documentation is blunt about it: MySQL accepts REFERENCES only as part of a separate FOREIGN KEY specification. No error, no warning—you get a plain integer column and believe you have referential integrity. Copy a schema from a Postgres tutorial into MySQL and this is exactly how you lose it. Always write the table-level FOREIGN KEY clause, and always verify with SHOW CREATE TABLE.

Naming constraints (fk_tasks_owner, ck_tasks_title_nonempty) is not decoration either: MySQL needs the name to drop a constraint in a later migration, and a named constraint turns an error message from a hex blob into a sentence.

Worked example—integrity in action. Trace three writes against these two tables:

1. INSERT INTO tasks (owner_id, title, due) VALUES (7, ’Study’, ’2026-07-10’) with no user 7 → rejected: Cannot add or update a child row: a foreign key constraint fails. An orphan task cannot exist even for a millisecond.

2. DELETE FROM users WHERE id = 3 where user 3 has 12 tasks → allowed, and the 12 tasks vanish with them: that’s our ON DELETE CASCADE choice, made in the schema, not in application code that might forget.

3. INSERT INTO tasks (owner_id, title, due) VALUES (3, ”, ’2026-07-10’) → rejected by the CHECK: Check constraint ’ck_tasks_title_nonempty’ is violated. Empty titles are unrepresentable—on MySQL 8.0.16 or newer. On anything older, that third write succeeds silently, which is why the version check in A MySQL You Can Trust (and Type Into) is not optional.

The alternatives to CASCADE are choices too: RESTRICT (refuse to delete a user who still has tasks—this is InnoDB’s default, and NO ACTION is a synonym for it, not the deferred check the SQL standard describes) and SET NULL (tasks become ownerless—requires the column be nullable). Each is a policy about the world; the point is the database enforces whichever you declare.

One more InnoDB detail with a payoff later: InnoDB requires an index on every foreign key column and creates one for you if you didn’t. That is why “all tasks owned by user 3” is fast without you doing anything—the constraint you wrote for correctness bought you a lookup structure for free.

Misconception. “My app checks this already, so constraints are redundant.” Your app is one of many writers (today: the API. Next month: a cleanup script, an admin console, your teammate’s notebook, an AI agent). Constraints are the only check that binds all of them. Application checks are courtesy; constraints are law.

7.6.1 One-to-many (the workhorse)🔗

One user owns many tasks; each task has one owner. The FK lives on the “many” side (tasks.owner_id)—necessarily, because a column holds one value: a task can point at one user, while a user is pointed at by any number of tasks. When in doubt, say the sentence both directions (“a user has many tasks / a task has one user”) and put the FK on the side that says “one.”

7.6.2 One-to-one (the specialist)🔗

Each user has at most one profile. Structurally it’s a one-to-many strangled by a uniqueness constraint: put the FK on the child and make it unique—or, most idiomatically, make it the child’s PK:

CREATE TABLE user_profiles (
    user_id   INT NOT NULL PRIMARY KEY,
    bio       VARCHAR(1000) NOT NULL DEFAULT '',
    timezone  VARCHAR(64) NOT NULL DEFAULT 'America/New_York',

    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

user_id being both PK and FK makes a second profile for the same user unrepresentable. (Note bio is VARCHAR(1000), not TEXT: MySQL before 8.0.13 refuses a literal DEFAULT on a TEXT column, and we want the default to be part of the schema rather than a thing every writer must remember.)

When would you split a table in two like this? (a) The extra columns are optional and large (don’t haul a 4KB bio into every task-list query); (b) different access permissions; (c) genuinely optional subtypes. If none apply, 1-1 columns usually just belong in the parent table—this is the relationship type you should use least.

7.6.3 Many-to-many (the junction)🔗

A task has many tags; a tag marks many tasks. Now neither side can hold the FK—one column can’t store “tags 2, 5, and 9.” The temptation is a comma-string column (tags = ’school,urgent’), or, in modern MySQL, a JSON array column, which feels more sophisticated and is the same mistake in better clothes. Resist both; Normalization: Designing Out Redundancy shows why. The relational answer is a third table whose rows are the relationships:

CREATE TABLE tags (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE task_tags (
    task_id  INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (task_id, tag_id),     -- composite PK: each pairing once

    CONSTRAINT fk_tt_task FOREIGN KEY (task_id)
        REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_tt_tag  FOREIGN KEY (tag_id)
        REFERENCES tags(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Worked example. Task 42 is tagged “school” (tag 2) and “urgent” (tag 5); task 43 is also “urgent”:

Every question becomes a lookup in this table: tags of task 42?—rows with task_id=42, served by the composite PK, whose leftmost column is task_id. All urgent tasks?—rows with tag_id=5, served by the index InnoDB created for the tag_id foreign key (the composite PK cannot help here, because tag_id is not its leftmost column). Untag it?—delete one row; the tag itself and the task are untouched, which is exactly the deletion behavior our feature list demanded. The composite PK makes double-tagging unrepresentable.

Misconception. “Junction tables are a workaround.” They’re the honest representation: the relationship itself is a fact, and facts get rows. The proof: the moment you need to know when a tag was applied or who applied it, those become ordinary columns on task_tags—try adding applied_at to a comma-string, or constraining it inside a JSON array.

7.7.1 The pathology, live🔗

Here’s the schema the intern (yes, the REST lecture’s intern) proposed—one big table, “like the spreadsheet we already had”:

Each stored fact should live in exactly one place; here, “Alice’s name is Alice Wilson” is stored twice. That redundancy breeds three named anomalies:

• Update anomaly: Alice marries and changes their name. You must update every row they own; miss one and the database now asserts two contradictory facts. Which is true? No way to know—the design destroyed the answer.

• Insert anomaly: Carol signs up but has no tasks yet. Where does Carol exists go? You can’t record a user without inventing a fake task.

• Delete anomaly: Bob completes and deletes his only task—and Bob’s existence vanishes with row 3. Deleting one fact silently destroyed an unrelated one.

Formal definition (working version). Normalization is decomposing tables so that every fact is stored exactly once: each non-key column depends on the key (its whole table’s identity), and nothing else.

The classic ladder, informally: 1NF—every cell atomic (the tags column flunks this: ’school,urgent’ is two facts in one cell, unsearchable and unconstrainable). 2NF/3NF—no column depends on part of the key or on another non-key column (owner_name flunks: it depends on owner_email, not on the task). The mnemonic that compresses the whole ladder: every non-key column depends on the key, the whole key, and nothing but the key.

The modern version of the 1NF mistake. MySQL 8 has a real JSON column type, and it is genuinely useful—for payloads you never query into, like a webhook body you’re keeping for audit. It is not a license to store ["school","urgent"] in a column. Ask what you gave up: no foreign key to tags, so a typo’d "schol" is a new tag forever; no UNIQUE on tag names; renaming a tag means rewriting every document; and “untag this” becomes a read-modify-write race instead of one DELETE. A junction table gives you all four back. JSON is for data the database does not need to understand.

7.7.2 The fix is the schema we already built🔗

Normalize the intern’s table and—no coincidence—out fall the running example’s tables: users (name stored once), tasks (FK to owner), tags + task_tags (atomic, constrainable). Re-run the anomalies: rename Alice → one UPDATE users; Carol signs up → one INSERT INTO users, no fake task; Bob deletes his task → Bob remains. The redesign didn’t add capability—it removed the ability to be wrong.

How far to go? Course rule: normalize until every fact has one home; denormalize only later, deliberately, for a measured performance reason—and treat that as what it is, technical-debt with interest (a task_count column on users is a cache that can lie; every write path must now maintain it).

7.8.1 What an ORM is (and isn’t)🔗

Formal definition. An object-relational mapper translates between rows and objects: table ↔ class, row ↔ instance, column ↔ attribute, FK ↔ object reference. SQLAlchemy is Python’s standard; we use its modern 2.0 declarative style.

What it buys you: Python types instead of hand-assembled SQL strings, relationship traversal (task.owner.name), the unit-of-work session, and—crucial for us—the same code runs on MySQL in production and SQLite in tests. Notice what this means for the MySQL traps above: you write ForeignKey(...) and SQLAlchemy emits a proper table-level FOREIGN KEY clause, because the MySQL dialect knows the inline form is ignored. The ORM is, among other things, a repository of other people’s scar tissue.

What it doesn’t do: absolve you from knowing the schema underneath. The ORM emits SQL; when something is slow or surprising, you read the SQL. It’s a power tool, not a hiding place.

7.8.2 The models (the heart of the mini-project)🔗

# app/models.py -- the running-example schema, as SQLAlchemy 2.0 models
from __future__ import annotations

from datetime import date
from typing import Optional

from sqlalchemy import (CheckConstraint, Column, Date, ForeignKey, String,
                        Table)
from sqlalchemy.orm import (DeclarativeBase, Mapped, mapped_column,
                            relationship)

# MySQL needs these on every table; SQLite ignores them, so tests are
# unaffected and dev/prod stay one codebase.
MYSQL_OPTS = {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4"}


class Base(DeclarativeBase):
    """All models inherit from this; it collects table metadata,
    which is exactly what Alembic will compare against the live DB."""


# M-N junction: plain table, no class -- it carries no data of its own (yet).
task_tags = Table(
    "task_tags",
    Base.metadata,
    Column("task_id", ForeignKey("tasks.id", ondelete="CASCADE"),
           primary_key=True),
    Column("tag_id", ForeignKey("tags.id", ondelete="CASCADE"),
           primary_key=True),
    **MYSQL_OPTS,
)


class User(Base):
    __tablename__ = "users"
    __table_args__ = MYSQL_OPTS

    id: Mapped[int] = mapped_column(primary_key=True)   # -> INT AUTO_INCREMENT
    # Every String needs an explicit length: MySQL has no unbounded VARCHAR,
    # and SQLAlchemy raises CompileError rather than guess one for you.
    email: Mapped[str] = mapped_column(String(255), unique=True)
    name: Mapped[str] = mapped_column(String(100))

    # 1-N: one user, many tasks. delete-orphan mirrors ON DELETE CASCADE
    # at the ORM level: delete the user, the session deletes their tasks.
    tasks: Mapped[list[Task]] = relationship(
        back_populates="owner", cascade="all, delete-orphan")

    # 1-1: relationship + uselist=False; FK lives on the profile (child).
    profile: Mapped[Optional[UserProfile]] = relationship(
        back_populates="user", cascade="all, delete-orphan", uselist=False)


class UserProfile(Base):
    __tablename__ = "user_profiles"
    __table_args__ = MYSQL_OPTS

    # PK *is* the FK: one profile per user, unrepresentable otherwise.
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"), primary_key=True)
    # VARCHAR, not TEXT: MySQL < 8.0.13 forbids a DEFAULT on TEXT columns.
    bio: Mapped[str] = mapped_column(String(1000), default="",
                                     server_default="")
    timezone: Mapped[str] = mapped_column(String(64),
                                          default="America/New_York")

    user: Mapped[User] = relationship(back_populates="profile")


class Task(Base):
    __tablename__ = "tasks"
    __table_args__ = (
        # length() rather than MySQL's CHAR_LENGTH(): portable to SQLite,
        # and identical here since we only compare against zero.
        CheckConstraint("length(title) > 0", name="ck_tasks_title_nonempty"),
        MYSQL_OPTS,
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    owner_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"))   # FK on the many side
    title: Mapped[str] = mapped_column(String(200))
    due: Mapped[date] = mapped_column(Date)
    done: Mapped[bool] = mapped_column(default=False)  # -> TINYINT(1) on MySQL

    owner: Mapped[User] = relationship(back_populates="tasks")
    tags: Mapped[list[Tag]] = relationship(
        secondary=task_tags, back_populates="tasks")


class Tag(Base):
    __tablename__ = "tags"
    __table_args__ = MYSQL_OPTS

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50), unique=True)

    tasks: Mapped[list[Task]] = relationship(
        secondary=task_tags, back_populates="tags")

Read it against the design sections: every schema decision reappears as a keyword. FK on the many side → owner_id on Task; one-to-one → uselist=False plus PK-as-FK; many-to-many → secondary=task_tags; cascade policy → both in the FK (ondelete="CASCADE", for the database) and in the relationship (cascade="all, delete-orphan", for the session). Declaring it in both places keeps Python’s view and the database’s law in agreement.

The MySQL-specific lines, gathered in one place. Three things in that file exist only because the target is MySQL: MYSQL_OPTS on every table (engine and charset, per A MySQL You Can Trust (and Type Into)); an explicit length on every String—MySQL has no unbounded VARCHAR, so SQLAlchemy refuses to compile String without one, and that error is the single most common first-run failure when a Postgres project is pointed at MySQL; and bio being a bounded String so it can carry a server-side default. Everything else—the relationships, the cascades, the check constraint—is dialect-neutral. The ORM is where the two databases stop differing, which is most of why we use one.

7.8.3 Engine, session, and the unit of work🔗

Formal definition. The engine manages connections to one database URL. A session is a unit of work: it accumulates your changes (new objects, modified attributes, deletions) in memory and, on commit(), writes them in a single transaction—all or nothing.

# app/db.py
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# MySQL is the default: the constraints this project teaches (FK cascades,
# InnoDB, the CHECK) are the ones a real server actually enforces. Set
# DATABASE_URL to sqlite:///terptasks.db for a no-server fallback -- same
# models, same CRUD, same migrations either way. This default is the single
# source of truth; migrations/env.py imports it rather than repeating it.
DEFAULT_URL = "mysql+pymysql://root:pw@127.0.0.1:3306/terptasks?charset=utf8mb4"
DATABASE_URL = os.environ.get("DATABASE_URL", DEFAULT_URL)

engine = create_engine(
    DATABASE_URL,
    echo=False,          # echo=True prints every statement -- use it to learn
    pool_pre_ping=True,  # see below: MySQL hangs up on idle connections
    pool_recycle=3600,
)
SessionLocal = sessionmaker(bind=engine)

Why pool_pre_ping. MySQL closes any connection idle longer than wait_timeout—eight hours by default, often far less on managed instances. Your pool keeps handing out the corpse, and you get the most-Googled error in MySQL history: “MySQL server has gone away.” pool_pre_ping=True makes SQLAlchemy test a connection before lending it out and quietly replace dead ones. Two keyword arguments buy you an entire category of 3 a.m. pages.

Intuition. A session is a shopping cart, not a cash register. session.add(...) puts items in the cart; nothing hits the store until commit() (checkout); rollback() abandons the cart intact. This is why a half-failed request leaves no half-written data—the transaction either fully happens or fully doesn’t.

One MySQL-flavored footnote on transactions: InnoDB’s default isolation level is REPEATABLE READ (PostgreSQL’s is READ COMMITTED). Practically, that means a long-lived session keeps seeing the snapshot it started with—so if you open a session, sleep, and re-query expecting to see a teammate’s committed write, you won’t. Short sessions, one per request, dodge the whole issue, which is what the dependency in exercise E9 sets up.

7.8.4 CRUD, worked end-to-end🔗

# app/crud.py -- every function takes a Session: caller owns the transaction
from __future__ import annotations

from datetime import date

from sqlalchemy import select
from sqlalchemy.orm import Session

from .models import Tag, Task, User


def create_user(db: Session, email: str, name: str) -> User:
    user = User(email=email, name=name)
    db.add(user)                    # C: staged in the unit of work
    db.commit()                     # transaction: INSERT happens here
    db.refresh(user)                # pull back DB-assigned values (id)
    return user


def create_task(db: Session, owner: User, title: str, due: date,
                tag_names: list[str] = ()) -> Task:
    # get-or-create each tag: UNIQUE(name) makes duplicates impossible,
    # so we look before we leap.
    tags = []
    for name in tag_names:
        tag = db.scalar(select(Tag).where(Tag.name == name))
        tags.append(tag if tag else Tag(name=name))
    task = Task(owner=owner, title=title, due=due, tags=tags)
    db.add(task)                    # cascades: new tags staged with it
    db.commit()
    db.refresh(task)
    return task


def open_tasks_for(db: Session, email: str) -> list[Task]:
    # R: 2.0-style select; the join follows the FK we designed.
    stmt = (select(Task).join(Task.owner)
            .where(User.email == email, Task.done.is_(False))
            .order_by(Task.due))
    return list(db.scalars(stmt))


def complete_task(db: Session, task_id: int) -> Task | None:
    task = db.get(Task, task_id)    # R by primary key
    if task is None:
        return None
    task.done = True                # U: just assign; session tracks it
    db.commit()
    return task


def delete_user(db: Session, user_id: int) -> bool:
    user = db.get(User, user_id)
    if user is None:
        return False
    db.delete(user)                 # D: cascade takes tasks & profile too
    db.commit()
    return True

Trace create_task(db, alice, "Study 389A", date(2026,7,10), ["school","urgent"]):

1. Neither tag exists → two Tag objects created in Python only.

2. Task(owner=alice, tags=tags)—no ids anywhere yet; just object references.

3. db.add(task) stages task and (via relationship cascade) both tags.

4. db.commit() opens a transaction and emits, in dependency order: INSERT INTO tags ×2, INSERT INTO tasks, INSERT INTO task_tags ×2. Commit.

5. db.refresh(task) → task.id is now real. task.owner.name → ’Alice Wilson’, traversing the FK as an attribute.

Turn on echo=True and watch it happen; on MySQL you will see SQLAlchemy fetch each new id with LAST_INSERT_ID() rather than PostgreSQL’s RETURNING, which is a small, concrete picture of what a dialect is.

Note what you did not write: no SQL strings, no id bookkeeping, no join syntax for the traversal—and yet every one of those five statements is the design sections’ schema, verbatim. (One honest warning for later: looping over user.tasks and touching task.tags for each fires a query per task—the N+1 problem; SQLAlchemy’s selectinload fixes it. File the name away for the day a page gets slow.)

7.9.1 The problem migrations solve🔗

Week 1 you ship the schema above. Week 3, tasks need a priority column. You cannot DROP TABLE and recreate—production has real rows now. You need to evolve a live schema, and everyone’s copy of it (your laptop, your teammate’s, CI, production) must evolve identically and in order.

That’s Git’s problem, re-worn: uncontrolled change to a shared artifact. Alembic is the version control: each migration is a small script with an upgrade() (apply the change) and a downgrade() (undo it), scripts chain parent-to-child like commits, and the database itself remembers its current revision in a one-row table (alembic_version). “What schema is production on?” becomes as answerable as git log.

7.9.2 The MySQL rule that changes how you write migrations🔗

MySQL has no transactional DDL. Every CREATE, ALTER, or DROP causes an implicit commit: the statement before it is committed, the DDL runs, and it is committed too. You cannot wrap several schema changes in one transaction and roll the whole thing back.

Compare the two worlds concretely. A migration with three ALTER statements whose third one fails:

MySQL 8 did add atomic DDL, which is a real improvement but a narrower promise than it sounds: a single DDL statement won’t leave a half-created table behind. It says nothing about a migration made of several statements.

Three habits follow, and they are the reason this section exists:

1. One logical change per migration. Many small revisions, not one big one. A failure then lands on a revision boundary, where Alembic can tell you where you are.

2. Make each step idempotent-ish or trivially re-runnable, and write the downgrade() as if you will need it at speed.

3. Back up before migrating production, because “roll back the transaction” is not available to you.

7.9.3 Autogenerate, and why you still read the diff🔗

Alembic can diff your models against the live database and draft the migration for you:

alembic revision --autogenerate -m "add priority to tasks"

Because Base.metadata (from models.py) is the intended state and the database is the current state, Alembic compares and emits:

"""add priority to tasks"""
from alembic import op
import sqlalchemy as sa

revision = "8f2c41d9a1b3"          # this migration's id
down_revision = "c71a02e54d20"     # its parent -- migrations form a chain


def upgrade() -> None:
    op.add_column("tasks",
        sa.Column("priority", sa.Integer(), nullable=False,
                  server_default="1"))


def downgrade() -> None:
    op.drop_column("tasks", "priority")

You read this diff like a PR—autogenerate is a draft, not an oracle. It reliably catches added and removed tables and columns; it cannot see intent. Rename name → full_name and autogenerate proposes drop_column("name") + add_column("full_name")—your data in that column is destroyed. The correct migration is op.alter_column(...), and only a human knows that, because only a human knows it was a rename. (This is the mean-code-review discipline from the (part "Elephant-Goldfish") lecture, applied to schema diffs—and doubly important if an AI agent wrote the models change.)

The MySQL wrinkle on alter_column. MySQL’s ALTER TABLE ... CHANGE restates the entire column definition, so Alembic must know the parts you aren’t changing. On MySQL you must pass them:

def upgrade() -> None:
    op.alter_column("users", "name",
                    new_column_name="full_name",
                    existing_type=sa.String(100),     # required on MySQL
                    existing_nullable=False)          # required on MySQL

Omit existing_type and you get an outright error; omit existing_nullable and you can silently drop a NOT NULL you meant to keep. Autogenerate usually fills these in; hand-written migrations usually don’t, which is exactly when it bites.

One more habit visible in the earlier draft: the new column is NOT NULL—but existing rows have no value for it. The server_default="1" is what makes this migration runnable against a table with data. On a non-strict MySQL you might get away without it (MySQL would invent a zero); on a correctly configured strict server, the migration fails. The strict server is the one telling you the truth.

7.9.4 Upgrade, downgrade, and the workflow🔗

alembic upgrade head        # apply all pending migrations, in order
alembic current             # what revision is this DB on?
alembic downgrade -1        # undo the most recent one
alembic history             # the chain, like git log

Worked walkthrough (the mini-project’s own history):

The team workflow: models change and migration are one commit, reviewed together; teammates git pull && alembic upgrade head; CI runs migrations against a scratch MySQL container before tests. Schema drift—the classic “works on my machine” of the database world—becomes structurally impossible, for the same reason Git made “which copy of the code is real?” obsolete.

Misconception. “Base.metadata.create_all() is enough—why Alembic?” create_all creates missing tables; it never alters existing ones. It’s fine for a throwaway script or the very first day; the moment real data exists, evolution requires migrations. (Our tests use create_all—fresh scratch DB each time; our real database uses Alembic. Different jobs.)

7.10.1 Structure🔗

app/models.py, app/db.py, and app/crud.py appear in full in SQLAlchemy ORM: The Schema as Python.

7.10.2 requirements.txt🔗

That cryptography line is not optional padding. MySQL 8’s default authentication plugin is caching_sha2_password, and PyMySQL cannot complete that handshake without it. The error is “cryptography is required for sha256_password or caching_sha2_password”, it appears the first time you point the app at a real server, and it has cost more student hours than any concept in this lecture.

7.10.3 alembic.ini and migrations/env.py🔗

[alembic]
script_location = migrations
# URL is supplied by env.py from $DATABASE_URL -- never hardcode it here.

# migrations/env.py -- minimal, synchronous version
import os
import sys

from alembic import context
from sqlalchemy import create_engine

sys.path.append(os.path.dirname(os.path.dirname(__file__)))  # find app/

from app.db import DATABASE_URL as url  # noqa: E402  -- the one default
from app.models import Base   # noqa: E402  -- imports register every table

# The intended state: our models. Alembic diffs the live DB against this.
target_metadata = Base.metadata


def run_migrations_offline() -> None:
    """Emit SQL to stdout instead of a DB ('alembic upgrade head --sql')."""
    context.configure(url=url, target_metadata=target_metadata,
                      literal_binds=True)
    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    engine = create_engine(url, pool_pre_ping=True)
    with engine.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            # SQLite cannot ALTER much, so Alembic rebuilds tables instead.
            # MySQL alters natively -- this stays off there.
            render_as_batch=url.startswith("sqlite"),
        )
        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Two notes on that file. render_as_batch exists for SQLite, which is stricter about ALTER than MySQL—one line buys dev/test parity. And context.begin_transaction() is real on SQLite and largely ceremonial on MySQL, for the no-transactional-DDL reason above: do not let its presence convince you a failed MySQL migration rolled back.

7.10.4 migrations/versions/0001_initial_schema.py🔗

"""initial schema: users, profiles, tasks, tags, task_tags"""
from alembic import op
import sqlalchemy as sa

revision = "c71a02e54d20"
down_revision = None          # the root of the chain
branch_labels = None
depends_on = None

MYSQL = {"mysql_engine": "InnoDB", "mysql_charset": "utf8mb4"}


def upgrade() -> None:
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("email", sa.String(255), nullable=False, unique=True),
        sa.Column("name", sa.String(100), nullable=False),
        **MYSQL,
    )
    op.create_table(
        "tags",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("name", sa.String(50), nullable=False, unique=True),
        **MYSQL,
    )
    op.create_table(
        "user_profiles",
        sa.Column("user_id", sa.Integer(), primary_key=True),
        sa.Column("bio", sa.String(1000), nullable=False, server_default=""),
        sa.Column("timezone", sa.String(64), nullable=False,
                  server_default="America/New_York"),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"],
                                name="fk_profiles_user", ondelete="CASCADE"),
        **MYSQL,
    )
    op.create_table(
        "tasks",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("owner_id", sa.Integer(), nullable=False),
        sa.Column("title", sa.String(200), nullable=False),
        sa.Column("due", sa.Date(), nullable=False),
        sa.Column("done", sa.Boolean(), nullable=False, server_default="0"),
        sa.ForeignKeyConstraint(["owner_id"], ["users.id"],
                                name="fk_tasks_owner", ondelete="CASCADE"),
        sa.CheckConstraint("length(title) > 0",
                           name="ck_tasks_title_nonempty"),
        **MYSQL,
    )
    op.create_table(
        "task_tags",
        sa.Column("task_id", sa.Integer(), primary_key=True),
        sa.Column("tag_id", sa.Integer(), primary_key=True),
        sa.ForeignKeyConstraint(["task_id"], ["tasks.id"],
                                name="fk_tt_task", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["tag_id"], ["tags.id"],
                                name="fk_tt_tag", ondelete="CASCADE"),
        **MYSQL,
    )


def downgrade() -> None:
    # Reverse dependency order: children before parents. On MySQL this
    # order is mandatory -- InnoDB refuses to drop a table another table
    # still references.
    op.drop_table("task_tags")
    op.drop_table("tasks")
    op.drop_table("user_profiles")
    op.drop_table("tags")
    op.drop_table("users")

Every foreign key here is a named sa.ForeignKeyConstraint—table-level, never inline. That is the MySQL trap from Tables, Columns, Types, and Keys, defended against in the one file that actually creates your production schema, and the names are what a future op.drop_constraint will need.

7.10.5 migrations/versions/0002_add_task_priority.py🔗

The migration from Alembic: Version Control for Your Schema, verbatim—reviewed draft, server_default and all. (Keeping models and migrations in sync: models.py in the repo also carries priority on Task—added in the same commit as 0002, as the workflow demands.)

"""add priority to tasks"""
from alembic import op
import sqlalchemy as sa

revision = "8f2c41d9a1b3"
down_revision = "c71a02e54d20"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column("tasks",
        sa.Column("priority", sa.Integer(), nullable=False,
                  server_default="1"))


def downgrade() -> None:
    op.drop_column("tasks", "priority")

7.10.6 tests/test_schema.py🔗

"""Schema-promise tests: constraints, relationships, cascades.

Default: SQLite in-memory -- fast, fresh per test, no server needed.
Set TEST_DATABASE_URL to a MySQL URL to run the identical suite against
a real server; that is the whole point of the ORM layer.
"""
import os
from datetime import date

import pytest
from sqlalchemy import create_engine, event, select
from sqlalchemy.exc import IntegrityError, OperationalError
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool

from app import crud
from app.models import Base, Tag, Task, User

TEST_DATABASE_URL = os.environ.get("TEST_DATABASE_URL", "sqlite://")


@pytest.fixture()
def db():
    if TEST_DATABASE_URL.startswith("sqlite"):
        engine = create_engine(TEST_DATABASE_URL, poolclass=StaticPool,
                               connect_args={"check_same_thread": False})
        # SQLite ignores foreign keys unless asked. InnoDB always enforces
        # them -- this listener is what makes the two behave alike.
        event.listen(engine, "connect",
                     lambda c, _: c.execute("PRAGMA foreign_keys=ON"))
    else:
        engine = create_engine(TEST_DATABASE_URL, pool_pre_ping=True)

    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)   # scratch DB: create_all is fine here
    with Session(engine) as session:
        yield session
    Base.metadata.drop_all(engine)


def test_unique_email_is_enforced_by_the_database(db):
    crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    with pytest.raises(IntegrityError):
        crud.create_user(db, "alice@umd.edu", "Alice Clone")
    db.rollback()                     # failed transaction must be rolled back


def test_empty_title_is_rejected_by_the_check_constraint(db):
    # On MySQL this requires 8.0.16+. On an older server the CHECK is
    # parsed and ignored, and this test fails -- which is the point.
    #
    # Two exception types, one rejection: SQLite reports a failed CHECK as
    # IntegrityError, while MySQL raises error 3819, which PyMySQL classes
    # as OperationalError. The database said no either way -- but a test
    # that names only one of them passes on SQLite and fails on MySQL.
    alice = crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    with pytest.raises((IntegrityError, OperationalError)):
        crud.create_task(db, alice, "", date(2026, 7, 10))
    db.rollback()


def test_one_to_many_traversal_both_directions(db):
    alice = crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    task = crud.create_task(db, alice, "Study 389A", date(2026, 7, 10))
    assert task.owner.name == "Alice Wilson"          # many -> one
    assert [t.title for t in alice.tasks] == ["Study 389A"]   # one -> many


def test_many_to_many_shares_tags_without_duplication(db):
    alice = crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    t1 = crud.create_task(db, alice, "Study", date(2026, 7, 10),
                          tag_names=["school", "urgent"])
    t2 = crud.create_task(db, alice, "Books", date(2026, 7, 11),
                          tag_names=["school"])
    school = db.scalar(select(Tag).where(Tag.name == "school"))
    assert {t.title for t in school.tasks} == {"Study", "Books"}
    assert db.scalar(select(Tag).where(Tag.name == "urgent")) is not None
    assert len(db.scalars(select(Tag)).all()) == 2   # get-or-create: no dupes
    assert {tag.name for tag in t1.tags} == {"school", "urgent"}
    assert {tag.name for tag in t2.tags} == {"school"}


def test_deleting_user_cascades_tasks_but_never_tags(db):
    alice = crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    crud.create_task(db, alice, "Study", date(2026, 7, 10),
                     tag_names=["school"])
    assert crud.delete_user(db, alice.id) is True
    assert db.scalars(select(Task)).all() == []          # tasks went with them
    assert db.scalar(select(Tag).where(Tag.name == "school")) is not None
                                                          # shared tag survives


def test_complete_task_is_a_tracked_update(db):
    alice = crud.create_user(db, "alice@umd.edu", "Alice Wilson")
    task = crud.create_task(db, alice, "Study", date(2026, 7, 10))
    assert crud.complete_task(db, task.id).done is True
    assert crud.open_tasks_for(db, "alice@umd.edu") == []

7.10.7 Run it🔗

git clone <your-fork-url> terptasks-db && cd terptasks-db
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

pytest -q                                # 6 passed -- no server needed

The suite needs nothing running: TEST_DATABASE_URL defaults to in-memory SQLite. The application’s own default is MySQL, so bring a server up before migrating:

docker run --name terpmysql -e MYSQL_ROOT_PASSWORD=pw \
  -e MYSQL_DATABASE=terptasks -p 3306:3306 -d mysql:8.4

docker exec terpmysql mysqladmin ping -uroot -ppw   # wait for 'mysqld is alive'

alembic upgrade head                     # DATABASE_URL unset -> the MySQL default
alembic current                          # -> 8f2c41d9a1b3 (head)
alembic downgrade -1                     # drop priority; back to v1
alembic upgrade head                     # forward again

# Run the identical suite against the real server:
export TEST_DATABASE_URL='mysql+pymysql://root:pw@127.0.0.1:3306/terptasks?charset=utf8mb4'
pytest -q                                # same 6 tests, real database

# Verify what MySQL is actually enforcing -- not what you meant to write:
mysql -h 127.0.0.1 -u root -ppw terptasks -e 'SHOW CREATE TABLE tasks\G'

No server handy? One variable puts the whole thing on a file, with the same models and the same migrations:

export DATABASE_URL=sqlite:///terptasks.db
alembic upgrade head

That last command is the habit to keep. SHOW CREATE TABLE prints the schema MySQL will enforce tonight at 3 a.m.: the engine, the charset, the named foreign keys, the check constraint. If something you wrote isn’t in that output, MySQL silently dropped it, and you would rather learn that now.

7.11.1 Basic🔗

1. E1. For each, name the relationship type and state which table holds the FK (or that a junction is required): (a) student ↔ transcript; (b) course ↔ enrolled students; (c) department ↔ professors; (d) task ↔ subtasks (careful); (e) user ↔ “tasks they may view” in a sharing feature.

2. E2. The intern’s flat table stores owner_email, owner_name, and tags per task row. Give one concrete sequence of operations for each anomaly—update, insert, delete—using Alice, Bob, and Carol.

3. E3. Choose the MySQL type and constraints for each column, with one-line justifications: a UMD student ID; a tuition balance; an account creation moment; a task’s “percent complete”; an optional nickname; a 5,000-word note body that is never searched. For the timestamp, state which of DATETIME and TIMESTAMP you picked and what you gave up.

4. E4. Why is users.email a UNIQUE column rather than the primary key? What specifically goes wrong in our schema (name the tables) if it were the PK and a user changed emails?

5. E5. In test_deleting_user_cascades_tasks_but_never_tags, explain mechanically why the tag survives: which table’s rows are deleted by the cascade, and why doesn’t the cascade cross the junction to tags?

6. E6. The silent-failure hunt. A teammate hands you this DDL. It runs without error on their MySQL and enforces almost nothing. Name every guarantee that is silently missing, say why, and rewrite it correctly. Then prove your rewrite with SHOW CREATE TABLE and one rejected INSERT per constraint.

   CREATE TABLE notes (
       id       INT AUTO_INCREMENT PRIMARY KEY,
       owner_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
       title    TEXT NOT NULL UNIQUE,
       body     TEXT NOT NULL DEFAULT '',
       stars    INT NOT NULL CHECK (stars BETWEEN 0 AND 5)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

   (There are at least five distinct problems, and one of them stops the statement from running at all. Find that one first.)

7.11.2 Intermediate🔗

1. E7. Add comments to the mini-project: users comment on tasks; a comment has one author, one task, a body, and a timestamp. Decide the relationship types, write the model, the autogenerate-style migration (with correct downgrade()), and a cascade test: deleting a task removes its comments; deleting a comment touches nothing else. State and defend your timestamp column type.

2. E8. Add applied_at to the tag-task relationship. The junction must become an association object (a mapped class instead of the bare Table). Migrate without losing existing pairings, and write the test proving old pairs survived with a backfilled timestamp.

3. E9. Wire terptasks-db into the REST lecture’s terptasks-rest: replace its in-memory storage.py with a session-per-request dependency (Depends(get_db)) calling crud.py. The REST contract tests must pass unchanged—that’s the graded criterion, and the whole point.

4. E10. Deliberately break the rename rule: rename Task.title to Task.summary in models, run alembic revision –autogenerate, and paste the (destructive) draft it produces. Then write the correct migration with op.alter_column—including the existing_type and existing_nullable MySQL requires. Demonstrate on a database with three tasks that the draft loses the titles and yours doesn’t. Finally, delete existing_type from your version and report the exact error MySQL gives.

5. E11. Write the N+1 demonstration: seed 20 tasks with tags, turn on echo=True, and count queries for “print every task with its tags” naive vs. with selectinload(Task.tags). Report both counts and the SQL shapes.

7.11.3 Advanced🔗

1. E12. Design the schema for recurring tasks from the (part "Elephant-Goldfish") lecture’s design doc: a Recurrence entity (frequency, anchoring policy, timezone) with its relationship to Task. Defend 1-1-on-task vs. separate-table-1-N with the relationship-design criteria; write model + migration + a test for the lazy “spawn next instance on completion” write path in one transaction. Say how you store the recurrence’s timezone given that MySQL has no TIMESTAMPTZ.

2. E13. Schema review, EGM-style. Trade E7/E8 solutions with another student. Audit: constraint gaps (what wrong state is representable?), cascade traps, normalization violations, and the MySQL-specific silent failures from E6. Then run a Goldfish critic over your audit. Deliverable: findings ranked blocker/should-fix/nit, plus the critic’s additions.

3. E14. The migration squeeze. Production has schema v2 with 10,000 rows. The team decides priority should become an enum table (priorities(id, label)) instead of a bare integer. Write the three-step expand/backfill/contract migration sequence such that the app keeps working at every intermediate step (old code on new schema during deploy). Explain why one big migration cannot achieve this—and then explain the second, MySQL-specific reason the three-step version is mandatory here and merely advisable on PostgreSQL.

7.12.1 Key takeaways🔗

1. A schema makes wrong states unrepresentable. Types, PKs, FKs, UNIQUE, CHECK, and cascade rules are enforced guarantees binding every writer—app, script, agent, or intern.

2. On MySQL, that sentence has a precondition. InnoDB, utf8mb4, strict SQL mode, and version 8.0.16+. Get one wrong and MySQL accepts your constraint and enforces nothing. Check the four settings; write a separate table-level FOREIGN KEY clause because inline REFERENCES is parsed and discarded; verify with SHOW CREATE TABLE.

3. Surrogate integer PKs (AUTO_INCREMENT); natural uniqueness as UNIQUE constraints. PKs get copied everywhere, so they must never need to change.

4. Three relationship shapes, three placements: 1-N puts the FK on the many side; 1-1 is a unique (often PK) FK on the child; M-N requires a junction table—whose rows are facts, and can carry columns of their own.

5. Normalize until every fact has one home. The key, the whole key, and nothing but the key; anomalies (update/insert/delete) are the disease, decomposition is the cure, denormalization is deliberate debt—and a JSON column full of tags is the same 1NF mistake with better syntax.

6. The ORM is the schema as Python—relationship traversal, unit-of-work sessions (commit = all-or-nothing), same code on MySQL and SQLite. It emits SQL; it doesn’t excuse you from reading it.

7. Migrations are Git for schemas. Autogenerate drafts; humans review—renames and NOT NULL-on-existing-rows are where drafts destroy data. Downgrades reverse structure, not data. And because MySQL commits every DDL statement, a failed migration stops where it failed: keep them small, back up first.

8. The stack stays decoupled: the REST contract didn’t change when storage became real. That’s what all the layering was for.

7.12.2 Terminology🔗

7.12.3 Common mistakes🔗

• Email (or any mutable fact) as a primary key.

• Inline REFERENCES on a column—MySQL parses it and enforces nothing. Write the table-level FOREIGN KEY clause.

• ENGINE=MyISAM, or an old utf8 charset, or a MySQL older than 8.0.16: three different ways to get a schema that looks right and guarantees nothing.

• TEXT where you needed VARCHAR(n): it cannot be indexed without a prefix length, and (pre-8.0.13) cannot have a default.

• Comma-separated lists—or a JSON array—in a column instead of a junction table.

• Skipping FK constraints “because the app checks”—it isn’t the only writer.

• FLOAT for money; TIMESTAMP for a date past 2038; storing local time in a DATETIME and hoping.

• Forgetting db.rollback() after an IntegrityError, then wondering why the session is broken.

• Shipping autogenerate output unread (the rename-as-drop-column data loss).

• op.alter_column on MySQL without existing_type.

• NOT NULL column added without a server_default—migration fails on real data.

• Writing a five-statement migration and assuming a failure rolls it back. Not on MySQL.

• Using create_all() as a migration strategy past day one.

• Never testing downgrade() until the emergency that needs it.

7.12.4 Connections🔗

• Backward: this is the persistence layer under the FastAPI and (part "REST APIs") lectures (E9 literally plugs it in); constraint-thinking continues Pydantic’s make-invalid-unrepresentable philosophy; migrations echo the git lecture; denormalization is the technical-debt notes with rows.

• Forward: OWASP’s injection unit lands here (the ORM’s parameterized queries are your first defense—but only if you never build SQL strings by hand); the layered-architecture notes get their canonical example; and when an AI agent maintains your schema (Claude Code + the Elephant-Goldfish discipline), migration review is exactly where the human judgment concentrates.

• Sideways: everything here except the SQL dialect and four configuration settings transfers directly to PostgreSQL. That is worth noticing—the design is the durable part, the vendor is not.