SQLAlchemy 2.0 Textbook Ch. 3 — Schema and the Type System

Chapter 3: declaring schema in SQLAlchemy 2.0. Mapped columns, types, defaults, indexes, constraints, table_args, with notes on cross-DB differences.

Declarative base

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

Subclass for all your models.

Mapped columns

from sqlalchemy.orm import Mapped, mapped_column
from datetime import datetime

class User(Base):
    __tablename__ = "users"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True, nullable=False)
    name: Mapped[str | None] = mapped_column(String(120))
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(default=datetime.utcnow, onupdate=datetime.utcnow)

The Python type maps to a DB type:

int → INTEGER (or BIGINT if you specify).
str → TEXT or VARCHAR.
float → FLOAT.
bool → BOOLEAN.
datetime → TIMESTAMP.
date → DATE.
bytes → BYTEA / BLOB.
Decimal → NUMERIC.
UUID → UUID (Postgres) / CHAR(32) elsewhere.

Explicit types

When the inferred type isn’t what you want:

from sqlalchemy import String, BigInteger, Text, Numeric

class Order(Base):
    __tablename__ = "orders"
    
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    short_code: Mapped[str] = mapped_column(String(8))
    description: Mapped[str] = mapped_column(Text)
    amount: Mapped[Decimal] = mapped_column(Numeric(10, 2))

String(N) for VARCHAR(N); Text for unlimited text.

For Postgres: TEXT vs VARCHAR have similar performance — TEXT is fine.

For MySQL: VARCHAR up to limits; TEXT for longer.

Nullability

email: Mapped[str]                                 # NOT NULL
email: Mapped[str | None]                          # NULL allowed
email: Mapped[str | None] = mapped_column(default=None)  # explicit

Type tells the story. Mapped[str] = NOT NULL; Mapped[str | None] = nullable.

Primary keys

id: Mapped[int] = mapped_column(primary_key=True)

Composite primary key:

class OrderItem(Base):
    order_id: Mapped[int] = mapped_column(primary_key=True)
    line: Mapped[int] = mapped_column(primary_key=True)

UUIDs:

import uuid
from sqlalchemy import Uuid

class Doc(Base):
    id: Mapped[uuid.UUID] = mapped_column(Uuid(as_uuid=True), primary_key=True, default=uuid.uuid4)

For Postgres: native UUID type. For MySQL/SQLite: stored as binary or string.

Defaults

Server-side default:

from sqlalchemy import func

created_at: Mapped[datetime] = mapped_column(server_default=func.now())

server_default is the default at the DB level (DEFAULT NOW() in DDL).

Python-side default:

created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

default runs in Python at insert time.

For consistency across writers: prefer server_default. For complex defaults: Python.

On update

updated_at: Mapped[datetime] = mapped_column(default=datetime.utcnow, onupdate=datetime.utcnow)

Sets updated_at to now on every UPDATE. Python-side; runs whenever ORM updates.

For server-side, you’d need a trigger (DB-specific).

Indexes

email: Mapped[str] = mapped_column(unique=True, index=True)

Or explicit:

from sqlalchemy import Index

class User(Base):
    __tablename__ = "users"
    email: Mapped[str]
    
    __table_args__ = (
        Index("ix_users_email", "email"),
        Index("ix_users_email_active", "email", "active"),
    )

For Postgres-specific (partial indexes, GIN, etc.): see the Postgres-focused textbook .

Unique constraints

class Membership(Base):
    __tablename__ = "memberships"
    
    user_id: Mapped[int]
    org_id: Mapped[int]
    
    __table_args__ = (
        UniqueConstraint("user_id", "org_id", name="uq_user_org"),
    )

Check constraints

from sqlalchemy import CheckConstraint

class Order(Base):
    __tablename__ = "orders"
    
    amount: Mapped[Decimal]
    
    __table_args__ = (
        CheckConstraint("amount > 0", name="ck_amount_positive"),
    )

DB-level constraint. Cross-DB compatible.

Foreign keys

from sqlalchemy import ForeignKey

class Post(Base):
    __tablename__ = "posts"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))

ondelete: CASCADE, SET NULL, RESTRICT, NO ACTION.

For MySQL InnoDB: enforced. SQLite: optional (need PRAGMA foreign_keys=ON).

Computed columns (generated columns)

from sqlalchemy import Computed

class Product(Base):
    __tablename__ = "products"
    
    name: Mapped[str]
    name_normalized: Mapped[str] = mapped_column(Computed("lower(name)"))

Postgres / MySQL: native. SQLite: 3.31+. Stored or virtual depending on flags.

Enums

import enum
from sqlalchemy import Enum as SAEnum

class Role(str, enum.Enum):
    USER = "user"
    ADMIN = "admin"

class User(Base):
    role: Mapped[Role] = mapped_column(SAEnum(Role))

Postgres: native ENUM type. MySQL: native ENUM. SQLite: stored as string.

For schema-evolution flexibility: use VARCHAR + CHECK constraint instead of native ENUM.

JSON / JSONB

from sqlalchemy import JSON

class Event(Base):
    payload: Mapped[dict] = mapped_column(JSON)

For Postgres-specific JSONB: use from sqlalchemy.dialects.postgresql import JSONB. See the Postgres textbook .

table_args and order

class User(Base):
    __tablename__ = "users"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str]
    
    __table_args__ = (
        UniqueConstraint("email"),
        Index("ix_users_active_email", "active", "email"),
        {"comment": "Application users"},
    )

__table_args__ is a tuple. Ends with a dict for table-level kwargs.

Schemas (namespaces)

class Audit(Base):
    __tablename__ = "events"
    __table_args__ = {"schema": "audit"}

Postgres: schema is a real namespace. MySQL: schemas == databases.

Mixins

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(server_default=func.now(), onupdate=datetime.utcnow)

class User(TimestampMixin, Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)

Reusable column groups.

Naming conventions

For consistent constraint names (helpful with Alembic):

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",
}

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)

Without this, autogenerated migrations may produce ambiguous names (e.g., ck_1).

See the Alembic textbook .

Custom types

from sqlalchemy.types import TypeDecorator, String

class LowercaseString(TypeDecorator):
    impl = String
    cache_ok = True
    
    def process_bind_param(self, value, dialect):
        return value.lower() if value else value

TypeDecorator wraps another type with conversion. Useful for normalization.

Self-referential

class Category(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"))

For tree structures. Subsequent chapter (Relationships) covers traversal.

Cross-DB type considerations

Type	Postgres	MySQL	SQLite
String(N)	VARCHAR(N)	VARCHAR(N)	TEXT
Text	TEXT	TEXT (or LONGTEXT)	TEXT
Integer	INTEGER	INTEGER	INTEGER
BigInteger	BIGINT	BIGINT	INTEGER
Boolean	BOOLEAN	TINYINT	INTEGER
DateTime	TIMESTAMP	DATETIME	TEXT
JSON	JSON / JSONB	JSON (5.7+)	TEXT
Numeric(p,s)	NUMERIC	DECIMAL	NUMERIC
UUID	UUID native	CHAR(36)	TEXT

For DB-agnostic code: stick to types in sqlalchemy (not dialect-specific) where possible. The library translates.

Common mistakes

1. Mapped[str] for nullable

If column is NULL-able, use Mapped[str | None]. Otherwise inserts of NULL fail at the ORM level.

2. Forgetting indexes on FKs

Foreign keys without indexes → slow joins / cascade deletes. Add index=True on the FK column.

3. Datetime without tz

Mapped[datetime] defaults to TIMESTAMP WITHOUT TIME ZONE. For tz-aware:

from sqlalchemy import DateTime
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

4. String(255) by reflex

VARCHAR(255) was a MySQL-specific concern. Postgres TEXT is fine. Pick limits based on actual constraints.

5. Naming ambiguity

Index("ix_email", "email") — if you have many email columns, conflicts. Use naming conventions.

What’s next

Chapter 4: Sessions and Unit of Work.

Read this next

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .

Declarative base#

Mapped columns#

Explicit types#

Nullability#

Primary keys#

Defaults#

On update#

Indexes#

Unique constraints#

Check constraints#

Foreign keys#

Computed columns (generated columns)#

Enums#

JSON / JSONB#

table_args and order#

Schemas (namespaces)#

Mixins#

Naming conventions#

Custom types#

Self-referential#

Cross-DB type considerations#

Common mistakes#

1. Mapped[str] for nullable#

2. Forgetting indexes on FKs#

3. Datetime without tz#

4. String(255) by reflex#

5. Naming ambiguity#

What’s next#

Read this next#