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 |
|---|---|
|
Time-ordered UUIDv7 primary keys with sequential B-tree index locality. |
|
Random UUIDv4 primary keys. |
|
Auto-incrementing 64-bit integer primary keys (mapped to |
|
SQL standard |
|
Short, URL-friendly string IDs. |
|
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.