No history yet

Modern Declarative Models

The New Declarative Base

In SQLAlchemy 2.0, defining database models is built around a modern, type-annotated approach. The foundation for this system is the DeclarativeBase class. Think of it as the central registry where all your table models are collected.

To get started, you create your own Base class that inherits from DeclarativeBase. All of your specific model classes, like User or Product, will then inherit from this custom Base.

from sqlalchemy.orm import DeclarativeBase

# All model classes will inherit from this Base
class Base(DeclarativeBase):
    pass

This setup is the cornerstone of the ORM's declarative system. It allows SQLAlchemy to discover all your models and understand how they relate to the database schema.

Defining a Table Model

With the Base class ready, you can define your first table. Each model is a Python class that inherits from Base. This class needs a special attribute, __tablename__, which is a string that specifies the actual table name in the database.

The real power of the new style comes from Python's type annotations. SQLAlchemy 2.0 uses the Mapped generic type to link a Python attribute to a database column. This makes your code more explicit and allows static type checkers like Mypy to understand your models.

from typing import Optional
from sqlalchemy import Integer, String
from sqlalchemy.orm import Mapped, mapped_column

# Assuming 'Base' is defined as above

class User(Base):
    __tablename__ = "user_account"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

Let's break down this User model.

Columns and Types

Each attribute in the User class that should map to a database column is defined with a Mapped type annotation. The Python type you use inside Mapped[...] tells SQLAlchemy what kind of data to expect.

  • id: Mapped[int]: This declares an id attribute that holds a Python integer. SQLAlchemy maps this to the INTEGER SQL type by default.
  • name: Mapped[str]: This declares a name attribute that holds a string. This maps to VARCHAR or a similar text type in SQL.
  • fullname: Mapped[Optional[str]]: Using Optional[str] (or str | None) signals that the fullname column can be NULL in the database. If a type isn't optional, the corresponding column will be created with a NOT NULL constraint.

While SQLAlchemy can infer the SQL type from the Python type in many cases (like int to INTEGER), you often need more control. This is where mapped_column() comes in.

The mapped_column() function lets you provide specific details about the database column, such as its exact SQL type, constraints, or default values.

In the User model, we use it to define the id and name columns more precisely:

  1. Primary Key: The id column is designated as the primary key by passing primary_key=True. This is essential for uniquely identifying each row in the table.
  2. String Length: The name column is explicitly given the type String(30). While Mapped[str] would have worked, String(30) is more specific. It tells the database to create a VARCHAR(30) column, which can be more efficient for storage and indexing than an unbounded text type.

The fullname column definition is simpler. Since it doesn't need any special constraints or a specific SQL type beyond the default for a string, we can omit mapped_column(). SQLAlchemy understands that Mapped[Optional[str]] implies a nullable string column.

Quiz Questions 1/5

What is the primary role of the DeclarativeBase class in SQLAlchemy 2.0?

Quiz Questions 2/5

Consider the following SQLAlchemy 2.0 model definition:

from typing import Optional
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Product(Base):
    __tablename__ = 'products'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    description: Mapped[Optional[str]]

Which statement about the resulting database table is true?