Python SQLAlchemy Declarative Models: Tables, Columns, and Relationships

SQLAlchemy declarative models let you define a Python class and a database table together. This guide covers the core syntax for mapping tables and columns, choosing column types, adding constraints and indexes, and setting up relationships.

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__.

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, Numeric, 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.

Define back_populates on both sides when you have two relationships that represent the same pair of tables, so SQLAlchemy keeps the linked objects in sync. If you define only one side, the relationship is one-directional; if you define both sides but omit back_populates, in-memory changes on one side may not be reflected on the other until objects are refreshed.

Naming Conventions and Reserved Words

Column and table names become SQL identifiers, so choose names that are unambiguous in both Python and SQL. If a database column needs a different name from the Python attribute, pass the database column name as the first argument to Column:

class Order(Base):
    __tablename__ = "orders"

    id = Column(Integer, primary_key=True)
    status = Column("order_status", String(50))

If you cannot use a name because it is a Python keyword, use a valid Python attribute name such as class_ and provide the SQL column name explicitly. This keeps the Python attribute readable and the SQL identifier explicit.

For constraints and indexes, consider a naming convention on the metadata. Set the naming convention when you create your Base, before defining models:

from sqlalchemy import MetaData
from sqlalchemy.orm import declarative_base

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 useful when you run migrations. Without it, SQLAlchemy generates names that may differ between databases, making schema diffs noisy.

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.