Model Patterns

SQLAlchemy Model Patterns

Production patterns for defining models, relationships, and constraints with SQLAlchemy 2.0+.

Flask-SQLAlchemy Model Definition

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timezone

db = SQLAlchemy()

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

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    # Relationships
    posts = db.relationship('Post', backref='author', lazy=True)

Standalone SQLAlchemy 2.0 (Mapped Annotations)

from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'

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

    posts: Mapped[list["Post"]] = relationship(back_populates="author")

Column Types

Constraints

# Unique constraint
email = db.Column(db.String(120), unique=True)

# Not null
name = db.Column(db.String(100), nullable=False)

# Default value
status = db.Column(db.String(20), default='new')

# Dynamic default (use lambda for mutable defaults)
created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

# Check constraint
age = db.Column(db.Integer, db.CheckConstraint('age >= 0'))

# Composite unique constraint
__table_args__ = (db.UniqueConstraint('user_id', 'date', 'procedure'),)

# Index
__table_args__ = (db.Index('idx_user_date', 'user_id', 'date'),)

Relationships

One-to-Many

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    clients = db.relationship('Client', backref='user', lazy=True)

class Client(db.Model):
    __tablename__ = 'clients'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

One-to-Many with Cascade Delete

class Client(db.Model):
    appointments = db.relationship(
        'Appointment', backref='client', lazy=True,
        cascade='all, delete-orphan'
    )

When the client is deleted, all their appointments are automatically deleted.

Many-to-Many

# Association table
tags_posts = db.Table('tags_posts',
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'))
)

class Post(db.Model):
    tags = db.relationship('Tag', secondary=tags_posts, backref='posts')

Self-Referential (Hierarchical)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    children = db.relationship('Category', backref=db.backref('parent', remote_side=[id]))

Computed Properties

Use @property for values derived from other tables:

class Client(db.Model):
    @property
    def visit_count(self):
        return Appointment.query.filter_by(
            client_id=self.id, status='done'
        ).count()

Model Mixins

Share common columns across models:

class TimestampMixin:
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime, datetime.now(timezone.utc))

class User(TimestampMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    # inherits created_at and updated_at

Enum Columns

import enum

class StatusEnum(enum.Enum):
    NEW = 'new'
    REGULAR = 'regular'
    VIP = 'vip'

class Client(db.Model):
    status = db.Column(db.Enum(StatusEnum), default=StatusEnum.NEW)

Or use string-based approach for simpler cases:

status = db.Column(db.String(20), default='new')  # new/regular/vip