Models and the Database with SQLAlchemy

Models

from datetime import datetime
from app.extensions import db

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(255), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    projects = db.relationship("Project", back_populates="owner", lazy="selectin")

class Project(db.Model):
    __tablename__ = "projects"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    slug = db.Column(db.String(220), unique=True, nullable=False, index=True)
    is_published = db.Column(db.Boolean, default=False, nullable=False)
    owner_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
    owner = db.relationship("User", back_populates="projects")

Migrations

flask --app run.py db init                 # once
flask --app run.py db migrate -m "users and projects"
flask --app run.py db upgrade

Review each generated file in migrations/versions/ before applying. Migrations are committed and run on every environment, including production deploys.

Querying (SQLAlchemy 2.0 style)

from sqlalchemy import select

project = db.session.get(Project, 42)                      # by primary key
project = db.first_or_404(select(Project).filter_by(slug=slug))

stmt = (select(Project)
        .where(Project.is_published.is_(True))
        .order_by(Project.id.desc())
        .limit(20))
projects = db.session.scalars(stmt).all()

page = db.paginate(select(Project).order_by(Project.id.desc()), page=2, per_page=20)

Creating, updating, deleting

project = Project(title="Booking app", slug="booking-app", owner=current_user)
db.session.add(project)
db.session.commit()

project.title = "Salon booking app"
db.session.commit()

db.session.delete(project)
db.session.commit()

Wrap multi-step changes in one commit; on error call db.session.rollback().

Avoiding N+1 queries

A loop over projects that touches project.owner runs one query per project unless relationships are loaded together. Use lazy="selectin" on the relationship or options(selectinload(Project.owner)) on the query, and watch the SQL log (SQLALCHEMY_ECHO = True) while developing.

Enums and choices

import enum
class Status(enum.Enum):
    DRAFT = "draft"
    PUBLISHED = "published"

status = db.Column(db.Enum(Status), default=Status.DRAFT, nullable=False)

SQLAlchemy stores the member name; always compare with the enum (Project.status == Status.PUBLISHED), never a lowercase string.

Exercise

Add Tag with a many-to-many relationship to Project via an association table. Generate and apply the migration, then write a query listing published projects with their tags without N+1 queries.

Check yourself

• Why review autogenerated migrations?
• How do you avoid N+1 queries?
• Why compare enum columns with enum members?