Defining Database Models

Anatomy of a SQLAlchemy Model

Let's examine the basic structure of a SQLAlchemy model:

class User(db.Model):
    # Table name (optional, default is lowercase class name)
    __tablename__ = 'users'
    
    # Columns
    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)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    posts = db.relationship('Post', backref='author', lazy=True)
    
    # Methods
    def __init__(self, username, email):
        self.username = username
        self.email = email
    
    def __repr__(self):
        return f''
        
    # Custom methods
    def serialize(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

Key components of a model:

• Class definition: All models inherit from db.Model
• Table name: Defined by __tablename__ or derived from the class name
• Columns: Defined using db.Column with type and constraints
• Relationships: Defined using db.relationship to connect related models
• Constructor: The __init__ method for initializing model instances
• Representation: The __repr__ method for string representation
• Custom methods: Additional functionality specific to the model

Column Types and Constraints

When defining a column, you specify its data type and any constraints it should have:

Basic Column Definition

name = db.Column(db.String(50), nullable=False)

This creates a column named 'name' that:

• Stores string data up to 50 characters
• Cannot be NULL (must have a value)

Common Column Constraints

Example with Multiple Constraints

email = db.Column(db.String(120), 
                   unique=True, 
                   nullable=False, 
                   index=True,
                   comment='User email address')

This creates an email column that must be unique, cannot be NULL, is indexed for faster lookups, and includes a comment in the database schema.

Primary Keys and Composite Keys

Every table should have a primary key - a column or combination of columns that uniquely identifies each row.

Single-Column Primary Key

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # other columns...

This is the most common approach, using an auto-incrementing integer as the primary key.

UUID as Primary Key

import uuid

class User(db.Model):
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    # other columns...

Using UUIDs can be beneficial in distributed systems or when you want to hide sequential IDs.

Composite Primary Key

class Enrollment(db.Model):
    student_id = db.Column(db.Integer, db.ForeignKey('students.id'), primary_key=True)
    course_id = db.Column(db.Integer, db.ForeignKey('courses.id'), primary_key=True)
    enrollment_date = db.Column(db.Date)
    
    # Relationships
    student = db.relationship('Student', backref=db.backref('enrollments', lazy=True))
    course = db.relationship('Course', backref=db.backref('enrollments', lazy=True))

Here, the combination of student_id and course_id forms a composite primary key. This is common in many-to-many relationship tables.

Working with Default Values

Default values can be specified for columns, which will be used when a value isn't explicitly provided:

Static Default Values

active = db.Column(db.Boolean, default=True)
role = db.Column(db.String(20), default='user')

Dynamic Default Values with Functions

created_at = db.Column(db.DateTime, default=datetime.utcnow)
# Note: no parentheses after utcnow - we pass the function, not the result

Default with Lambda Function

token = db.Column(db.String(64), default=lambda: secrets.token_hex(32))

Server Default vs. Python Default

The default parameter is evaluated in Python when creating the object, while server_default is set at the database level:

from sqlalchemy.sql import func

# Python-side default
created_at = db.Column(db.DateTime, default=datetime.utcnow)

# Database-side default 
created_at = db.Column(db.DateTime, server_default=func.now())

Server defaults are useful when data might be inserted by other applications or directly via SQL.

Model Relationships

One of the most powerful features of an ORM is the ability to define relationships between models. These relationships mirror the foreign key relationships in the database but allow you to work with related objects directly.

One-to-Many Relationship

The most common relationship type is one-to-many, where one record in a table relates to multiple records in another table (e.g., one user has many posts).

# Parent model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    # The 'posts' attribute isn't an actual column, but a relationship
    posts = db.relationship('Post', backref='author', lazy=True)

# Child model
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    # Foreign key connecting to the User model
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

With this relationship defined:

• You can access a user's posts with user.posts
• You can access a post's author with post.author (via the backref)

Many-to-Many Relationship

Many-to-many relationships involve an association table that connects two entities (e.g., students can enroll in multiple courses, and courses can have multiple students).

# Association table (no model class needed for simple cases)
enrollments = db.Table('enrollments',
    db.Column('student_id', db.Integer, db.ForeignKey('student.id'), primary_key=True),
    db.Column('course_id', db.Integer, db.ForeignKey('course.id'), primary_key=True),
    db.Column('enrollment_date', db.Date, default=datetime.utcnow().date)
)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    courses = db.relationship('Course', secondary=enrollments, 
                              backref=db.backref('students', lazy='dynamic'))

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    # The relationship is defined in the Student model

With this relationship:

• You can access a student's courses with student.courses
• You can access a course's students with course.students

Association Object Pattern

For more complex many-to-many relationships (e.g., with additional data on the relationship), you can use the association object pattern:

class Enrollment(db.Model):
    student_id = db.Column(db.Integer, db.ForeignKey('student.id'), primary_key=True)
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'), primary_key=True)
    enrollment_date = db.Column(db.Date, default=datetime.utcnow().date)
    grade = db.Column(db.String(2))
    
    # Define relationships to both models
    student = db.relationship('Student', backref=db.backref('enrollments', lazy=True))
    course = db.relationship('Course', backref=db.backref('enrollments', lazy=True))

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    
    # Helper methods to work with courses through enrollments
    @property
    def courses(self):
        return [enrollment.course for enrollment in self.enrollments]
        
    def enroll(self, course, grade=None):
        enrollment = Enrollment(course=course, grade=grade)
        self.enrollments.append(enrollment)
        return enrollment

class Course(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)

One-to-One Relationship

A one-to-one relationship is a special case of one-to-many where the "many" side is constrained to have at most one related record.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    # uselist=False makes this a one-to-one relationship
    profile = db.relationship('Profile', backref='user', uselist=False, lazy=True)

class Profile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), unique=True, nullable=False)
    bio = db.Column(db.Text)
    location = db.Column(db.String(100))

With uselist=False, user.profile returns a single object rather than a list.

Lazy Loading and Relationship Options

The lazy parameter in relationship definitions controls how related data is loaded:

Examples of different loading options:

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

# Eager loading with joined
posts = db.relationship('Post', backref='author', lazy='joined')

# Dynamic loading for filtering
posts = db.relationship('Post', backref='author', lazy='dynamic')

# Usage with dynamic loading
# This returns posts as a query object, not a list
recent_posts = user.posts.filter(Post.created_at > one_week_ago).all()

Choosing the right loading strategy can have a significant impact on performance, especially when dealing with large datasets or complex relationships.

Backref vs. Back_populates

SQLAlchemy provides two ways to define bi-directional relationships: backref and back_populates.

Using backref

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', backref='author', lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

With backref, you define the relationship in one model, and SQLAlchemy automatically creates the reverse relationship.

Using back_populates

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', back_populates='author', lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    author = db.relationship('User', back_populates='posts')

With back_populates, you explicitly define the relationship in both models. This is more verbose but makes the relationships more explicit and can be easier to understand in complex models.

Advanced Column Features

Computed Columns with Hybrid Properties

Hybrid properties allow you to define attributes that work both at the instance level (Python) and at the class/query level (SQL).

from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    hourly_rate = db.Column(db.Numeric(10, 2), nullable=False)
    hours_worked = db.Column(db.Integer, nullable=False)
    
    @hybrid_property
    def full_name(self):
        return f"{self.first_name} {self.last_name}"
    
    @hybrid_property
    def salary(self):
        return self.hourly_rate * self.hours_worked
        
    @salary.expression
    def salary(cls):
        return cls.hourly_rate * cls.hours_worked
        
# Now you can do:
# employee.full_name  # Instance property
# Employee.query.filter(Employee.salary > 50000).all()  # Query-level expression

Check Constraints

You can add check constraints to ensure data meets certain conditions:

from sqlalchemy import CheckConstraint

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    
    # Ensure price is positive
    __table_args__ = (
        CheckConstraint('price > 0', name='check_positive_price'),
    )

Custom Column Types

SQLAlchemy allows you to create custom column types for specialized data:

import json
from sqlalchemy.types import TypeDecorator, TEXT

class JSONType(TypeDecorator):
    impl = TEXT
    
    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        return json.dumps(value)
        
    def process_result_value(self, value, dialect):
        if value is None:
            return None
        return json.loads(value)
        
class Settings(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    preferences = db.Column(JSONType, default={})

This example creates a custom JSON type that automatically converts between Python dictionaries and JSON strings.

Model Inheritance

SQLAlchemy supports several types of model inheritance patterns.

Single Table Inheritance

All classes in the hierarchy are stored in a single table with a discriminator column to identify the type.

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    type = db.Column(db.String(20))
    
    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'person'
    }

class Employee(Person):
    department = db.Column(db.String(50))
    salary = db.Column(db.Numeric(10, 2))
    
    __mapper_args__ = {
        'polymorphic_identity': 'employee'
    }

class Customer(Person):
    account_number = db.Column(db.String(10))
    loyalty_points = db.Column(db.Integer, default=0)
    
    __mapper_args__ = {
        'polymorphic_identity': 'customer'
    }

With this approach, a single 'person' table will store both employees and customers, with the 'type' column indicating which type each row represents.

Joined Table Inheritance

Each class has its own table, with the child tables having a foreign key to the parent table.

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    type = db.Column(db.String(20))
    
    __mapper_args__ = {
        'polymorphic_on': type,
        'polymorphic_identity': 'person'
    }

class Employee(Person):
    __tablename__ = 'employee'
    
    id = db.Column(db.Integer, db.ForeignKey('person.id'), primary_key=True)
    department = db.Column(db.String(50))
    salary = db.Column(db.Numeric(10, 2))
    
    __mapper_args__ = {
        'polymorphic_identity': 'employee'
    }

class Customer(Person):
    __tablename__ = 'customer'
    
    id = db.Column(db.Integer, db.ForeignKey('person.id'), primary_key=True)
    account_number = db.Column(db.String(10))
    loyalty_points = db.Column(db.Integer, default=0)
    
    __mapper_args__ = {
        'polymorphic_identity': 'customer'
    }

This approach creates three tables: 'person', 'employee', and 'customer', with foreign key relationships between them.

Abstract Base Classes

Abstract base classes provide common columns and methods but do not create a table themselves.

class TimestampMixin:
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class User(db.Model, TimestampMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    # Inherits created_at and updated_at from TimestampMixin

class Post(db.Model, TimestampMixin):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    # Inherits created_at and updated_at from TimestampMixin

This approach is useful for sharing common columns and behavior across unrelated models.

Model Events and Hooks

SQLAlchemy provides event hooks that allow you to run code at specific points in a model's lifecycle.

Using Event Listeners

from sqlalchemy import event

class User(db.Model):
    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_hash = db.Column(db.String(128))

# Run before a model is inserted
@event.listens_for(User, 'before_insert')
def lowercase_email(mapper, connection, target):
    if target.email:
        target.email = target.email.lower()

# Run when a model attribute changes
@event.listens_for(User.email, 'set')
def receive_user_email_set(target, value, oldvalue, initiator):
    if value and value != oldvalue:
        target.email = value.lower()

Common Events

• before_insert: Before a record is inserted
• after_insert: After a record is inserted
• before_update: Before a record is updated
• after_update: After a record is updated
• before_delete: Before a record is deleted
• after_delete: After a record is deleted

Event listeners are useful for tasks like:

• Data normalization (e.g., converting emails to lowercase)
• Setting timestamps automatically
• Validating data before saving
• Creating related records
• Auditing changes

Real-world Example: E-commerce Models

Let's explore a more comprehensive example - a set of models for a simple e-commerce application:

from datetime import datetime
from sqlalchemy.ext.hybrid import hybrid_property

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    first_name = db.Column(db.String(50))
    last_name = db.Column(db.String(50))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    addresses = db.relationship('Address', back_populates='user', lazy=True)
    orders = db.relationship('Order', back_populates='user', lazy=True)
    cart_items = db.relationship('CartItem', back_populates='user', lazy=True)
    
    @hybrid_property
    def full_name(self):
        if self.first_name and self.last_name:
            return f"{self.first_name} {self.last_name}"
        return self.username

class Address(db.Model):
    __tablename__ = 'addresses'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    street = db.Column(db.String(100), nullable=False)
    city = db.Column(db.String(50), nullable=False)
    state = db.Column(db.String(20), nullable=False)
    zip_code = db.Column(db.String(10), nullable=False)
    country = db.Column(db.String(50), nullable=False)
    is_default = db.Column(db.Boolean, default=False)
    
    # Relationships
    user = db.relationship('User', back_populates='addresses')
    orders = db.relationship('Order', back_populates='address', lazy=True)

class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
    description = db.Column(db.Text)
    parent_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=True)
    
    # Relationships
    products = db.relationship('Product', back_populates='category', lazy=True)
    children = db.relationship('Category', backref=db.backref('parent', remote_side=[id]))

class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    stock = db.Column(db.Integer, default=0, nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    category = db.relationship('Category', back_populates='products')
    cart_items = db.relationship('CartItem', back_populates='product', lazy=True)
    order_items = db.relationship('OrderItem', back_populates='product', lazy=True)
    
    # Ensure price is positive
    __table_args__ = (
        db.CheckConstraint('price > 0', name='check_positive_price'),
    )
    
    @hybrid_property
    def is_in_stock(self):
        return self.stock > 0

class CartItem(db.Model):
    __tablename__ = 'cart_items'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False, default=1)
    added_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    user = db.relationship('User', back_populates='cart_items')
    product = db.relationship('Product', back_populates='cart_items')
    
    # Ensure quantity is positive
    __table_args__ = (
        db.CheckConstraint('quantity > 0', name='check_positive_quantity'),
    )
    
    @hybrid_property
    def subtotal(self):
        return self.product.price * self.quantity

class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    address_id = db.Column(db.Integer, db.ForeignKey('addresses.id'), nullable=False)
    order_date = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    status = db.Column(db.String(20), default='pending', nullable=False)
    shipping_fee = db.Column(db.Numeric(10, 2), default=0.0, nullable=False)
    
    # Relationships
    user = db.relationship('User', back_populates='orders')
    address = db.relationship('Address', back_populates='orders')
    items = db.relationship('OrderItem', back_populates='order', lazy=True)
    
    @hybrid_property
    def subtotal(self):
        return sum(item.subtotal for item in self.items)
    
    @hybrid_property
    def total(self):
        return self.subtotal + self.shipping_fee

class OrderItem(db.Model):
    __tablename__ = 'order_items'
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    price = db.Column(db.Numeric(10, 2), nullable=False)  # Price at time of order
    
    # Relationships
    order = db.relationship('Order', back_populates='items')
    product = db.relationship('Product', back_populates='order_items')
    
    @hybrid_property
    def subtotal(self):
        return self.price * self.quantity
        
        
        

          This e-commerce model set demonstrates:
        
        
        

          
One-to-many relationships (User to Orders, Category to Products)
          
Many-to-many relationships with additional data (Products to Orders via OrderItems)
          
Self-referential relationships (Category hierarchy)
          
Check constraints for data integrity (positive prices and quantities)
          
Hybrid properties for computed values (full name, subtotals, totals)
          
Different types of columns (strings, numerics, booleans, datetimes)