Python SQLAlchemy Declarative Models: Tables and Columns

python sqlalchemy declarative models tables and columns requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When you define a SQLAlchemy declarative model, you describe both a Python class and a database table in one place. The class attributes become columns, and the table name is set with __tablename__. This article explains how to map tables and columns correctly, choose types, apply constraints, and set up relationships without common mistakes.

Defining a Declarative Model

Every declarative model inherits from a Base class created with declarative_base(). This base keeps track of the mapped classes and generates Table objects at import time.

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False, unique=True)
    name = Column(String(100), nullable=False)

The __tablename__ attribute sets the database table name. Each Column instance defines a column with its type and constraints. The class itself becomes the ORM handle to that table; instances represent rows.

Mapping Columns to Database Types

Choosing the correct column type matters because it determines how SQLAlchemy converts Python values to database values and back. The most common types are Integer, Float, String, Text, DateTime, Boolean, and Numeric.

from sqlalchemy import Column, Integer, String, DateTime, Boolean, Numeric, Text
from datetime import datetime

class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True)
    name = Column(String(200), nullable=False)
    description = Column(Text)
    price = Column(Numeric(10, 2), nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

String requires a length in most databases; Text does not. Numeric accepts precision and scale for exact decimal arithmetic. DateTime with default=datetime.utcnow sets the value at insert time, but the default is a Python callable, not a database default.

For database-side defaults, use server_default with a text() expression or a string literal. The distinction becomes important when other clients write to the same table.

Constraints and Indexes

Constraints enforce data integrity at the database level. A primary key is required for an ORM-mapped table unless you use a view or a special mapping. Common constraints are unique, nullable, and CheckConstraint.

from sqlalchemy import Column, Integer, String, CheckConstraint, Index

class Account(Base):
    __tablename__ = "accounts"
    __table_args__ = (
        CheckConstraint("balance >= 0", name="balance_non_negative"),
        Index("ix_accounts_email", "email"),
    )

    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False, unique=True)
    balance = Column(Numeric(12, 2), nullable=False)

__table_args__ accepts a tuple of constraints and indexes. A CheckConstraint adds a database-level check, which is safer than relying on application logic alone. Indexes speed up queries on the indexed columns but add write overhead.

When a column is unique=True, SQLAlchemy creates a unique constraint. For composite uniqueness, add a UniqueConstraint to __table_args__.

Relationships Between Models

Foreign keys link tables. In a declarative model, you define a ForeignKey on the column and then a relationship() on the class to expose the related objects.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = "addresses"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    street = Column(String(200))

    user = relationship("User", back_populates="addresses")

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False, unique=True)
    addresses = relationship("Address", back_populates="user")

The ForeignKey string uses the table name and column name, not the class name. The relationship() can reference the class by name as a string, which allows forward references. back_populates keeps both sides in sync when you append to user.addresses.

Without back_populates, SQLAlchemy may still work, but the relationship will be one-directional and can produce stale objects if you modify one side without the other.

Naming Conventions and Reserved Words

Column and table names become SQL identifiers. If a name is a reserved word in your database, such as order or group, you must quote it. SQLAlchemy can do this automatically if you set a naming convention on the metadata.

from sqlalchemy import MetaData

metadata = MetaData(
    naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(constraint_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s",
    }
)

Base = declarative_base(metadata=metadata)

This convention gives every constraint and index a deterministic name, which is essential when you run migrations. Without it, SQLAlchemy generates names that may differ between databases, making schema diffs noisy.

Avoid using Python reserved words as attribute names. If you must, use column_property or synonym, but the simplest fix is to choose a different attribute name and set the column name explicitly with Column("order", Integer).

Performance and Maintainability Considerations

Declarative models are convenient, but they can hide expensive query patterns. The default loading strategy for relationship() is lazy="select", which issues a separate SELECT for each related collection when accessed. This leads to the N+1 query problem.

# Inefficient: one query per user's addresses
users = session.query(User).all()
for user in users:
    print(user.addresses)

Use joinedload or selectinload to fetch related rows in one query.

from sqlalchemy.orm import selectinload

users = session.query(User).options(selectinload(User.addresses)).all()

selectinload issues a second query that loads all addresses for the selected users at once. joinedload uses a JOIN and can inflate row counts if you load multiple collections. Choose based on your data shape.

For maintainability, keep column definitions close to the business logic they represent. Avoid scattering model classes across many files without a clear import path. Use a single Base instance for the whole application so that create_all and migrations see all tables.

Migrations are where declarative models meet reality. Tools like Alembic compare the model metadata against the database schema. If you rely on implicit naming or forget server_default, migration scripts will contain unexpected changes. Define defaults explicitly and use a naming convention from the start.

Finally, remember that a declarative model is not just a table definition. It is also the class you use for query results. Adding methods to the class is normal, but avoid putting query logic that belongs in a service layer directly on the model. Keep the model focused on persistence and simple domain behavior.