Skip to main content

Defining Database Models with SQLAlchemy Declarative

A SQLAlchemy model is a Python class that represents a database table. Each model class inherits from the declarative base, maps to a single table, and uses class attributes to define columns. Defining models correctly ensures data integrity, query efficiency, and schema clarity.

Basic Model Structure​

Create a new file models.py and define a simple model:

from sqlalchemy import Column, Integer, String, DateTime, create_engine
from sqlalchemy.orm import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    # Primary key column
    id = Column(Integer, primary_key=True, autoincrement=True)
    
    # String columns with length constraints
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    
    # Optional column with default value
    created_at = Column(DateTime, default=datetime.utcnow)
    
    def __repr__(self):
        return f"<User(id={self.id}, username={self.username})>"

Every model requires __tablename__ (maps the class to a database table) and at least one column marked primary_key=True. The primary_key=True argument ensures the column uniquely identifies each row. The autoincrement=True flag (default for integer primary keys) automatically generates sequential IDs.

Column Types and Constraints​

SQLAlchemy provides data types for every common database type. Common types and their uses:

TypeExampleUse Case
IntegerColumn(Integer)Whole numbers (user IDs, counts)
String(n)Column(String(100))Text with max length n
TextColumn(Text)Unlimited text (descriptions, articles)
FloatColumn(Float)Decimal numbers (prices, ratings)
BooleanColumn(Boolean)True/False flags
DateTimeColumn(DateTime)Timestamps (created_at, updated_at)
DateColumn(Date)Calendar dates (birth_date)
JSONColumn(JSON)Nested objects (metadata, preferences)

Constraints enforce data rules at the database level, preventing invalid data from being inserted:

from sqlalchemy import Column, Integer, String, Float, Boolean, DateTime
from datetime import datetime

class Product(Base):
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True)
    
    # String with length; nullable=False enforces NOT NULL
    name = Column(String(200), nullable=False)
    
    # Float with check constraint (price >= 0)
    price = Column(Float, nullable=False)
    
    # Boolean with default value
    is_active = Column(Boolean, default=True)
    
    # DateTime with server-side default
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # Unique constraint prevents duplicate values
    sku = Column(String(50), unique=True, nullable=False)

The nullable=False argument makes a column required (NOT NULL in SQL). The unique=True argument ensures no two rows have the same value in that column. The default argument provides a Python-callable or static value used when inserting a new row without specifying that column.

Primary Keys and Indexing​

A primary key uniquely identifies each row and is the fastest column to search. SQLAlchemy creates an index on the primary key automatically. For tables where you want to search by a non-primary column frequently, add an explicit index:

from sqlalchemy import Column, Integer, String, Index

class Post(Base):
    __tablename__ = "posts"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    slug = Column(String(300), unique=True, nullable=False)
    
    # Explicit index for fast filtering
    author_id = Column(Integer, nullable=False)
    
    __table_args__ = (
        Index("idx_author_id", "author_id"),
        Index("idx_slug", "slug"),
    )

The __table_args__ tuple allows you to specify indexes, constraints, and other table-level settings. Indexes speed up WHERE clauses and JOIN conditions; create them on foreign keys and frequently filtered columns.

Default Values and Server-Side Defaults​

A default value is used when a row is inserted without specifying that column. Use Python callables for computed defaults:

from sqlalchemy import Column, Integer, String, DateTime, func
from datetime import datetime

class Comment(Base):
    __tablename__ = "comments"
    
    id = Column(Integer, primary_key=True)
    text = Column(String(1000), nullable=False)
    
    # Python callable (called at insert time)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # SQL function (executed by the database)
    updated_at = Column(DateTime, onupdate=func.now(), nullable=False)

The default=datetime.utcnow (without parentheses) tells SQLAlchemy to call datetime.utcnow() when a new Comment is created. The onupdate=func.now() uses a SQL function to set updated_at whenever the row is modified.

Table Relationships (Foreign Keys Overview)​

Models often reference other models using foreign keys. The foreign key column stores the primary key of another table, creating a relationship. We'll explore relationships in depth in the next article; here's a preview:

from sqlalchemy import Column, Integer, String, ForeignKey

class Author(Base):
    __tablename__ = "authors"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

class Book(Base):
    __tablename__ = "books"
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    
    # Foreign key: author_id references authors.id
    author_id = Column(Integer, ForeignKey("authors.id"), nullable=False)

The ForeignKey("authors.id") argument tells SQLAlchemy (and the database) that author_id references the id column in the authors table. This enforces referential integrity: you cannot insert a book with an author_id that doesn't exist.

Creating Tables in the Database​

Once your models are defined, create the actual tables using the declarative base:

from sqlalchemy import create_engine
from models import Base

engine = create_engine("sqlite:///myapp.db")
Base.metadata.create_all(engine)  # Creates all tables

Calling Base.metadata.create_all(engine) inspects all models that inherit from Base and creates corresponding tables in the database. If a table already exists, it is skipped; this is safe to call on every application startup.

Key Takeaways​

  • Models are classes that inherit from declarative_base() and map to database tables via __tablename__.
  • Every model requires a primary key (usually id = Column(Integer, primary_key=True)).
  • Constraints (nullable, unique, default, check) enforce data integrity at the database level.
  • Indexes on frequently searched or joined columns speed up queries.
  • Foreign keys reference other tables and enforce referential integrity.
  • Call Base.metadata.create_all(engine) to create all tables in the database.

Frequently Asked Questions​

Should I use String(50) or Text for long text fields?​

Use String(n) for fields with a known maximum length (usernames, emails, URLs) because the database enforces the limit. Use Text for variable-length content (article bodies, descriptions) with no enforced maximum. Text is not slower in modern databases.

What's the difference between default and onupdate?​

default sets a value when inserting a new row if no value is provided. onupdate sets a value whenever the row is updated (modified). Use onupdate=func.now() for automatic "last modified" timestamps.

Do I need to specify a primary key, or can I skip it?​

SQLAlchemy requires every model to have a primary key. If your table represents a junction table for many-to-many relationships, use a composite primary key of two foreign keys.

Can I use the same model name in different modules?​

Yes. Model names (like User) are local to your module. However, keep them unique site-wide for clarity. The actual table name is determined by __tablename__, so two User classes with different table names will create separate tables.

How do I handle datetime columns with timezones?​

Use DateTime(timezone=True) to store timezone-aware timestamps. Store datetimes in UTC and convert to local time in your application.

Further Reading​