Defining Models & Column Constraints0%
Defining Models & Column Constraints
Beginner12 min read•Updated: Jul 11, 2026
Defining Models & Column Constraints in SQLAlchemy
An Object Relational Mapper (ORM) bridges the gap between object-oriented Python code and relational database tables. Instead of querying raw SQL tables, you define Python classes that subclass db.Model. Each class represents a database table, and each instance represents a row.
1. Declaring a Modern SQLAlchemy Model
In Flask-SQLAlchemy, models subclass db.Model. Attributes map to database columns via db.Column:
Python
from datetime import datetime, timezone
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
class Course(db.Model):
# Table name defaults to lowercase class name ('course'), but can be set explicitly:
__tablename__ = "courses"
# Primary Key
id = db.Column(db.Integer, primary_key=True)
# String & Text Columns with Constraints
title = db.Column(db.String(150), nullable=False, unique=True, index=True)
slug = db.Column(db.String(160), nullable=False, unique=True)
description = db.Column(db.Text, nullable=True)
# Numeric Types
price = db.Column(db.Numeric(precision=10, scale=2), nullable=False, default=0.00)
rating = db.Column(db.Float, default=5.0)
# Booleans & Integers
is_published = db.Column(db.BooleanField, default=False, nullable=False)
enrollment_count = db.Column(db.Integer, default=0)
# Timestamps
created_at = db.Column(
db.DateTime(timezone=True),
default=lambda: datetime.now(timezone.utc),
nullable=False
)
updated_at = db.Column(
db.DateTime(timezone=True),
default=lambda: datetime.now(timezone.utc),
onupdate=lambda: datetime.now(timezone.utc)
)
def __repr__(self):
return f"<Course id={self.id} title='{self.title}'>"
2. Common SQLAlchemy Column Data Types
| SQLAlchemy Type | SQL Equivalent | Description |
|---|---|---|
db.Integer | INTEGER | 32-bit signed integer |
db.BigInteger | BIGINT | 64-bit integer (useful for high-volume transactions) |
db.String(N) | VARCHAR(N) | Variable-length string with maximum length N |
db.Text | TEXT | Unbounded text string (articles, blog bodies) |
db.Boolean | BOOLEAN | True or False |
db.DateTime | TIMESTAMP | Python datetime.datetime object |
db.Date | DATE | Python datetime.date object |
db.Float | FLOAT | Floating-point real numbers |
db.Numeric(10,2) | NUMERIC / DECIMAL | Exact fixed-point numbers (financial currencies) |
3. Essential Column Constraints
primary_key=True: Establishes the unique identifier for table rows and creates an indexed primary key.nullable=False: Enforces a database-levelNOT NULLconstraint; insertingNoneraises anIntegrityError.unique=True: Enforces unique values across all rows (ideal for emails, usernames, slugs).index=True: Generates a B-tree database index for fast query lookup performance.default=value: Provides a default value when inserting a new record. Note: pass callables without parentheses (e.g.default=datetime.utcnow) so the function executes at insert time!
Interactive Quiz
Practice Quiz
Question 1
What base class must every Flask-SQLAlchemy model inherit from?
Question 2
Why is
db.Numeric preferred over db.Float for storing prices and financial currencies?Question 3
Why should a timestamp default be passed as a callable (
default=datetime.utcnow) rather than a called function (default=datetime.utcnow())?Question 4
What does setting
index=True on a column do in the underlying database?Question 5
What does the
__repr__ method do on a model class?Next Lesson
Database Relationships (One-to-Many & Many-to-Many)
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| Flask-SQLAlchemy Setup & Engine Configuration | Database Relationships (One-to-Many & Many-to-Many) |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.