Flask Cheatsheet

Database (SQLAlchemy)

Use this Flask reference while you build software engineering projects, review code for technical interview prep, or polish examples for a software engineer resume.

Flask-SQLAlchemy Setup

pip install flask-sqlalchemy
pip install psycopg2-binary    # PostgreSQL driver
pip install pymysql            # MySQL/MariaDB driver

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
# app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://user:pw@host:5432/dbname"
# app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://user:pw@host/dbname"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False  # suppress warning

db = SQLAlchemy(app)

Application Factory Pattern

# extensions.py
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

# __init__.py
from .extensions import db

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

Defining Models

from datetime import datetime, timezone
from .extensions import db

class User(db.Model):
    __tablename__ = "users"

    id         = db.Column(db.Integer, primary_key=True)
    username   = db.Column(db.String(80), unique=True, nullable=False)
    email      = db.Column(db.String(120), unique=True, nullable=False)
    password   = db.Column(db.String(256), nullable=False)
    role       = db.Column(db.String(20), default="user")
    is_active  = db.Column(db.Boolean, default=True, nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    bio        = db.Column(db.Text, nullable=True)

    # Relationships
    posts = db.relationship("Post", back_populates="author",
                            lazy="select", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<User {self.username}>"

    def to_dict(self):
        return {"id": self.id, "username": self.username, "email": self.email}

class Post(db.Model):
    __tablename__ = "posts"

    id         = db.Column(db.Integer, primary_key=True)
    title      = db.Column(db.String(200), nullable=False)
    body       = db.Column(db.Text, nullable=False)
    published  = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    author_id  = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)

    author = db.relationship("User", back_populates="posts")
    tags   = db.relationship("Tag", secondary="post_tags", back_populates="posts")

Column Types

SQLAlchemy Type	Python Type	SQL Type
`db.Integer`	`int`	INTEGER
`db.BigInteger`	`int`	BIGINT
`db.SmallInteger`	`int`	SMALLINT
`db.Float`	`float`	FLOAT
`db.Numeric(10,2)`	`Decimal`	NUMERIC
`db.String(n)`	`str`	VARCHAR(n)
`db.Text`	`str`	TEXT
`db.Boolean`	`bool`	BOOLEAN
`db.Date`	`date`	DATE
`db.DateTime`	`datetime`	DATETIME
`db.Time`	`time`	TIME
`db.Interval`	`timedelta`	INTERVAL
`db.LargeBinary`	`bytes`	BLOB/BYTEA
`db.JSON`	`dict/list`	JSON/JSONB
`db.Enum("a","b")`	`str`	ENUM

Column Options

db.Column(
    db.String(100),
    primary_key=True,
    nullable=False,
    unique=True,
    index=True,
    default="value",            # Python-side default
    server_default="value",     # DB-side DEFAULT
    onupdate=lambda: datetime.now(timezone.utc),  # auto-update on UPDATE
    comment="Human description",
)

Relationships

# One-to-many
posts = db.relationship("Post", back_populates="author", lazy="select")

# Many-to-one (FK side)
author = db.relationship("User", back_populates="posts")

# Many-to-many via secondary table
post_tags = db.Table(
    "post_tags",
    db.Column("post_id", db.ForeignKey("posts.id"), primary_key=True),
    db.Column("tag_id",  db.ForeignKey("tags.id"),  primary_key=True),
)

class Tag(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(50), unique=True)
    posts = db.relationship("Post", secondary=post_tags, back_populates="tags")

# Self-referential (e.g., categories)
class Category(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    name      = db.Column(db.String(50))
    parent_id = db.Column(db.Integer, db.ForeignKey("category.id"), nullable=True)
    children  = db.relationship("Category", backref=db.backref("parent", remote_side="Category.id"))

Lazy Loading Options

`lazy=`	Behavior
`"select"`	Separate SELECT when attribute accessed (default)
`"joined"`	JOIN in same query
`"subquery"`	Subquery in same query
`"dynamic"`	Returns a query object (deprecated in SQLAlchemy 2.0)
`True`	Alias for `"select"`
`False`	Alias for `"joined"`

Creating Tables

with app.app_context():
    db.create_all()     # create all tables not yet in DB
    db.drop_all()       # drop all tables (destructive!)

Use Flask-Migrate for production migrations (see below).

CRUD Operations

Create

user = User(username="alice", email="alice@example.com", password=hashed)
db.session.add(user)
db.session.commit()           # persists to DB
user.id                       # now populated

# Bulk insert
db.session.add_all([User(...), User(...)])
db.session.commit()

Read (Querying)

SQLAlchemy 2.0-style (preferred with Flask-SQLAlchemy 3+):

from sqlalchemy import select, or_, and_, desc, func

# Get by primary key
user = db.session.get(User, 1)              # None if not found
user = db.session.get_one(User, 1)          # raises NoResultFound

# Select all
users = db.session.execute(select(User)).scalars().all()

# Filter
stmt = select(User).where(User.email == "alice@example.com")
user = db.session.execute(stmt).scalar_one_or_none()

# Multiple filters (AND)
stmt = select(Post).where(Post.published == True, Post.author_id == 1)

# OR
stmt = select(User).where(or_(User.role == "admin", User.role == "mod"))

# ORDER, LIMIT, OFFSET
stmt = select(Post).order_by(desc(Post.created_at)).limit(10).offset(20)

# COUNT
count = db.session.execute(select(func.count(User.id))).scalar()

# Scalar values
stmt = select(func.sum(Order.total)).where(Order.user_id == 1)
total = db.session.execute(stmt).scalar()

# JOINs
stmt = (
    select(Post, User)
    .join(User, Post.author_id == User.id)
    .where(Post.published == True)
)
rows = db.session.execute(stmt).all()    # list of (Post, User) tuples

Legacy query API (still works, but SQLAlchemy 2.0 style is preferred):

User.query.get(1)
User.query.filter_by(email="alice@example.com").first()
User.query.filter(User.username.like("%ali%")).all()
Post.query.order_by(Post.created_at.desc()).paginate(page=1, per_page=20)

Update

user = db.session.get(User, 1)
user.email = "newemail@example.com"
db.session.commit()

# Bulk update (SQLAlchemy 2.0)
from sqlalchemy import update
stmt = update(User).where(User.role == "guest").values(is_active=False)
db.session.execute(stmt)
db.session.commit()

Delete

user = db.session.get(User, 1)
db.session.delete(user)
db.session.commit()

# Bulk delete
from sqlalchemy import delete
db.session.execute(delete(Post).where(Post.published == False))
db.session.commit()

Session Management

db.session.add(obj)          # track new object
db.session.add_all([...])    # track multiple
db.session.delete(obj)       # mark for deletion
db.session.commit()          # flush + commit transaction
db.session.rollback()        # undo changes since last commit
db.session.flush()           # send SQL to DB without committing (gets IDs)
db.session.expunge(obj)      # remove from session tracking
db.session.refresh(obj)      # reload from DB
db.session.close()           # close session

# Scoped session teardown (automatic with Flask-SQLAlchemy)
@app.teardown_appcontext
def shutdown_session(exception=None):
    db.session.remove()

Pagination

pagination = db.paginate(
    select(Post).order_by(Post.created_at.desc()),
    page=request.args.get("page", 1, type=int),
    per_page=20,
    error_out=False,       # return empty page instead of 404
    max_per_page=100,
)

pagination.items          # list of Post objects on this page
pagination.total          # total row count
pagination.pages          # total number of pages
pagination.page           # current page number
pagination.has_next       # bool
pagination.has_prev       # bool
pagination.next_num       # next page number
pagination.prev_num       # previous page number
pagination.iter_pages()   # iterator of page numbers for a nav widget

Flask-Migrate (Alembic Migrations)

pip install flask-migrate

from flask_migrate import Migrate
migrate = Migrate(app, db)

flask db init          # create migrations/ directory (once)
flask db migrate -m "add users table"   # auto-generate migration
flask db upgrade       # apply pending migrations
flask db downgrade     # revert last migration
flask db history       # show migration history
flask db current       # current revision
flask db stamp head    # mark DB as up to date without running migrations

Always review auto-generated migration files before running upgrade in production — Alembic cannot detect all changes (e.g., column renames).

Indexes and Constraints

class Post(db.Model):
    __tablename__ = "posts"
    __table_args__ = (
        db.Index("ix_posts_author_created", "author_id", "created_at"),
        db.UniqueConstraint("author_id", "slug", name="uq_author_slug"),
        db.CheckConstraint("length(title) > 0", name="ck_title_nonempty"),
    )

    id        = db.Column(db.Integer, primary_key=True)
    title     = db.Column(db.String(200), nullable=False, index=True)
    slug      = db.Column(db.String(200), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

Events

from sqlalchemy import event

@event.listens_for(User, "before_insert")
def set_defaults(mapper, connection, target):
    target.username = target.username.lower()

@event.listens_for(User, "after_delete")
def log_deletion(mapper, connection, target):
    print(f"Deleted user {target.id}")

Connection Pooling

app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_size": 10,
    "pool_recycle": 3600,    # recycle connections after 1 hour
    "pool_pre_ping": True,   # test connection before use (handles dropped connections)
    "max_overflow": 20,
}