Flask Models and Tables

A Flask-SQLAlchemy model is a Python class that represents a database table. Each instance of the class is one row in the table. Each class attribute decorated with db.Column becomes a column. This topic explores how to design models for real-world data.

Column Data Types

A Complete Product Model

from datetime import datetime

class Product(db.Model):
    __tablename__ = 'products'

    id          = db.Column(db.Integer, primary_key=True)
    name        = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text, nullable=True)
    price       = db.Column(db.Float, nullable=False)
    stock       = db.Column(db.Integer, default=0)
    is_active   = db.Column(db.Boolean, default=True)
    created_at  = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at  = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def __repr__(self):
        return f'<Product {self.name} ${self.price}>'

__tablename__ overrides the default table name (which SQLAlchemy derives from the class name). Without it, a class called Product maps to a table named product. Setting __tablename__ = 'products' makes the table name explicit.

The __repr__ Method

The __repr__ method controls what you see when you print a model object in the terminal. It makes debugging much easier:

Without __repr__:  <Product object at 0x7f1234>
With __repr__:     <Product Laptop $999.0>

Custom Table Name Convention

Class Name → Default Table Name → Custom __tablename__
User       → user               → 'users'
BlogPost   → blog_post          → 'blog_posts'
OrderItem  → order_item         → 'order_items'

Model with Multiple Constraints

class Employee(db.Model):
    __tablename__ = 'employees'

    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)
    email      = db.Column(db.String(120), unique=True, nullable=False, index=True)
    department = db.Column(db.String(50))
    salary     = db.Column(db.Float, nullable=False)
    hire_date  = db.Column(db.Date, nullable=False)

    @property
    def full_name(self):
        return f'{self.first_name} {self.last_name}'

The @property decorator creates a computed attribute. employee.full_name returns the concatenated name without storing it as a column — the database only stores first_name and last_name.

Adding Methods to Models

Models are regular Python classes. Add helper methods to keep database logic organized:

class Product(db.Model):
    # ... columns ...

    @classmethod
    def get_active(cls):
        return cls.query.filter_by(is_active=True).all()

    @classmethod
    def find_by_name(cls, name):
        return cls.query.filter(cls.name.ilike(f'%{name}%')).all()

    def apply_discount(self, percent):
        self.price = round(self.price * (1 - percent / 100), 2)
        return self

Class methods become reusable query shortcuts. Instance methods operate on a specific row.

Inspecting the Generated SQL

To see what SQL SQLAlchemy generates for a model, run in the Python shell:

from flask_sqlalchemy import SQLAlchemy
import sqlalchemy as sa

print(sa.schema.CreateTable(Product.__table__).compile(dialect=sa.dialects.sqlite.dialect()))

Summary

Flask-SQLAlchemy models map Python classes to database tables and class attributes to columns. Choose the right data type for each column. Use __tablename__ to set an explicit table name. Add @property attributes for computed values and class methods for reusable queries. Keeping database logic inside model methods makes your view functions short and your code testable.