Modeling Basics#

Declarative base classes configure primary keys, sentinel columns, and optional created_at and updated_at audit timestamps across your SQLAlchemy models.

Choosing a Base Class#

Select a base class matching your primary key strategy. Each keyed *Base class has a corresponding *AuditBase variant that adds created_at and updated_at UTC columns (DefaultBase omits preconfigured primary key and audit columns):

Base Class

Description

UUIDv7Base / UUIDv7AuditBase

Time-ordered UUIDv7 primary keys with sequential B-tree index locality.

UUIDBase / UUIDAuditBase

Random UUIDv4 primary keys.

BigIntBase / BigIntAuditBase

Auto-incrementing 64-bit integer primary keys (mapped to INTEGER on SQLite).

IdentityBase / IdentityAuditBase

SQL standard GENERATED BY DEFAULT AS IDENTITY integer primary keys.

NanoIDBase / NanoIDAuditBase

Short, URL-friendly string IDs.

DefaultBase

Unopinionated declarative base for custom or composite primary keys.

Define a model with a 64-bit integer primary key and automatic created_at and updated_at columns:

import datetime
from typing import Optional
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntAuditBase


class BasicBlogPost(BigIntAuditBase):
    __tablename__ = "basic_blog_post"

    title: Mapped[str] = mapped_column(index=True)
    content: Mapped[str]
    published: Mapped[bool] = mapped_column(default=False)
    published_at: Mapped[Optional[datetime.datetime]] = mapped_column(default=None)
import datetime
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntAuditBase


class BasicBlogPost(BigIntAuditBase):
    __tablename__ = "basic_blog_post"

    title: Mapped[str] = mapped_column(index=True)
    content: Mapped[str]
    published: Mapped[bool] = mapped_column(default=False)
    published_at: Mapped[datetime.datetime | None] = mapped_column(default=None)

Tip

Base classes and mixins assign column sort orders so id precedes model-specific columns and audit timestamps appear last in generated DDL.

Many-to-Many Relationships#

Model many-to-many associations with a secondary Table bound to orm_registry.metadata, and mix in SlugKey to add an indexed slug column:

from sqlalchemy import Column, ForeignKey, Table
from sqlalchemy.orm import Mapped, mapped_column, relationship
from advanced_alchemy.base import BigIntAuditBase, orm_registry
from advanced_alchemy.mixins import SlugKey

blog_post_topic = Table(
    "blog_post_topic",
    orm_registry.metadata,
    Column("post_id", ForeignKey("tagged_blog_post.id", ondelete="CASCADE"), primary_key=True),
    Column("topic_id", ForeignKey("blog_topic.id", ondelete="CASCADE"), primary_key=True),
)


class TaggedBlogPost(BigIntAuditBase):
    __tablename__ = "tagged_blog_post"

    title: Mapped[str] = mapped_column(index=True)
    topics: Mapped[list["BlogTopic"]] = relationship(
        secondary=blog_post_topic,
        back_populates="posts",
        lazy="selectin",
    )


class BlogTopic(BigIntAuditBase, SlugKey):
    __tablename__ = "blog_topic"

    name: Mapped[str] = mapped_column(unique=True, index=True)
    posts: Mapped[list["TaggedBlogPost"]] = relationship(
        secondary=blog_post_topic,
        back_populates="topics",
        lazy="selectin",
    )

Preventing Duplicate Rows#

Mix in UniqueMixin and implement unique_hash and unique_filter to return existing session or database instances instead of inserting duplicate rows:

from typing import Optional
from collections.abc import Hashable
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql.elements import ColumnElement
from advanced_alchemy.base import BigIntAuditBase
from advanced_alchemy.mixins import SlugKey, UniqueMixin
from advanced_alchemy.utils.text import slugify


class UniqueTopic(BigIntAuditBase, SlugKey, UniqueMixin):
    __tablename__ = "unique_topic"

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

    @classmethod
    def unique_hash(cls, name: str, slug: Optional[str] = None) -> Hashable:
        del slug
        return slugify(name)

    @classmethod
    def unique_filter(cls, name: str, slug: Optional[str] = None) -> ColumnElement[bool]:
        del slug
        return cls.slug == slugify(name)
from collections.abc import Hashable
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql.elements import ColumnElement
from advanced_alchemy.base import BigIntAuditBase
from advanced_alchemy.mixins import SlugKey, UniqueMixin
from advanced_alchemy.utils.text import slugify


class UniqueTopic(BigIntAuditBase, SlugKey, UniqueMixin):
    __tablename__ = "unique_topic"

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

    @classmethod
    def unique_hash(cls, name: str, slug: str | None = None) -> Hashable:
        del slug
        return slugify(name)

    @classmethod
    def unique_filter(cls, name: str, slug: str | None = None) -> ColumnElement[bool]:
        del slug
        return cls.slug == slugify(name)

Custom Keys and Dataclass Models#

Combine DefaultBase with AuditColumns for custom or composite primary keys, or pair it with SQLAlchemy’s MappedAsDataclass for dataclass model semantics:

from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import DefaultBase
from advanced_alchemy.mixins import AuditColumns


class ImportedProduct(DefaultBase, AuditColumns):
    __tablename__ = "imported_product"

    id: Mapped[str] = mapped_column(primary_key=True)
    name: Mapped[str]
from typing import Optional
from sqlalchemy.orm import Mapped, MappedAsDataclass, mapped_column
from advanced_alchemy.base import DefaultBase


class DataclassAuthor(MappedAsDataclass, DefaultBase):
    __tablename__ = "dataclass_author"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    name: Mapped[str]
    bio: Mapped[Optional[str]] = mapped_column(default=None)
from sqlalchemy.orm import Mapped, MappedAsDataclass, mapped_column
from advanced_alchemy.base import DefaultBase


class DataclassAuthor(MappedAsDataclass, DefaultBase):
    __tablename__ = "dataclass_author"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    name: Mapped[str]
    bio: Mapped[str | None] = mapped_column(default=None)

See Declarative Base and Mixins for the full list of base classes and mixins.