Skip to content

Indexes

Applies to: DDD · CQRS · Event Sourcing

Index declarations tell the persistence layer which indexes to create for an aggregate, entity, or projection. They are passed as the indexes= option on the @domain.aggregate / @domain.entity / @domain.projection decorators and are honored by every database adapter that can support them. (Projections, as read-optimized query models, are an especially natural place to declare indexes.)

from protean import Index, Q


@domain.aggregate(indexes=[
    Index("status", "priority", desc=("priority",)),
    Index("email", unique=True),
    Index("status", where=Q(status__in=["pending", "failed"]), name="ix_active"),
])
class Order:
    ...

For the design rationale (why indexes are decorator parameters rather than a class Meta: block, and the split between portable indexes on the aggregate and storage-specific tuning on the model) see ADR-0014.

Index

Index(*fields, name=None, unique=False, desc=(), where=None, include=())
Parameter Type Default Description
*fields str — Required. One or more field names, in index order. At least one.
name str | None None Explicit index name. Derived deterministically when omitted (see Naming).
unique bool False Enforce uniqueness across the indexed fields.
desc tuple[str, ...] () Subset of fields to index in descending order. Every entry must also appear in fields.
where Q | None None Partial-index predicate. Honored on PostgreSQL and SQLite; ignored with a warning elsewhere.
include tuple[str, ...] () Covering (non-key) columns. Honored on PostgreSQL and SQL Server; ignored with a warning elsewhere.

Index is a frozen value object. Field names are validated against the element's declared fields during Domain.init().

Naming

When name is omitted, the index name is derived as <prefix>_<table>_<fields>, where the prefix is uq for unique indexes and ix otherwise:

Declaration Derived name (table order)
Index("status", "priority") ix_order_status_priority
Index("email", unique=True) uq_order_email

Provide an explicit name= when you need a stable, well-known identifier (for example to reference it from a migration or a monitoring query).

Index.from_sql

Index.from_sql(dialect, ddl, name=None) -> RawIndex

An escape hatch for dialect-specific DDL the portable Index API cannot model (GIN/GiST/BRIN, expression indexes, dialect-only options). The schema generator and the SQLAlchemy adapter emit the verbatim ddl only when the configured dialect matches dialect.

@domain.aggregate(indexes=[
    Index.from_sql(
        "postgresql",
        "CREATE INDEX ix_order_data_gin ON order USING gin (data jsonb_path_ops)",
    ),
])
class Order:
    ...
Parameter Type Description
dialect str Dialect the DDL targets ("postgresql", "sqlite", "mssql").
ddl str Verbatim CREATE INDEX … statement.
name str | None Optional name, for reporting.

RawIndex is the return type of from_sql. You never construct it directly, always go through Index.from_sql.

Dialect support

The portable subset (composite, descending, unique, naming) is honored everywhere indexes apply. The opt-in features (where, include) are honored only where the dialect supports them, and otherwise degrade to a full index with a logged warning, declarations never fail because of an unsupported opt-in.

Feature PostgreSQL SQLite SQL Server Memory Elasticsearch
Composite, unique, desc, naming ✅ ✅ ✅ unique enforced; rest advisory —
where (partial index) ✅ ✅ ⚠️ falls back advisory —
include (covering columns) ✅ ⚠️ falls back ✅ advisory —
Index.from_sql matched dialect only matched dialect only matched dialect only — —
  • SQLAlchemy providers (PostgreSQL, SQLite, SQL Server) translate declarations into SQLAlchemy Index constructs at table-build time, emitted by create_all() / protean db setup. Unsupported where/include log a warning and fall back to a full index.
  • Memory validates declarations for shape and enforces unique indexes: a duplicate insert or update that violates a single-column or composite Index(..., unique=True) raises ValidationError (NULLs treated as distinct, matching PostgreSQL/SQLite). Non-unique indexes are advisory, so you can develop against the memory provider and switch to a SQL backend without code changes.
  • Elasticsearch does not map relational indexes; use ES field mappings or Index.from_sql where applicable.
  • Non-SQL backends (memory, Elasticsearch, and cache stores such as Redis for cache-backed projections) silently ignore non-unique index declarations. No warning is emitted (memory being the exception that enforces unique). Declarations stay valid (field references are still checked at Domain.init()) and take effect if the element is later persisted to a SQL backend. Warnings are emitted only by SQL providers, and only for an opt-in (where=/include=) a specific dialect cannot honor.

See the per-adapter pages under Database Providers for specifics.

Validation

Index declarations are validated during Domain.init() (after reference resolution), raising IncorrectUsageError on:

  • a field that is not declared on the element (in fields, desc, or include);
  • a desc entry not present in fields;
  • a list entry that is neither an Index nor a RawIndex.

RawIndex entries are opaque verbatim DDL and are not introspected.

Generating DDL artifacts

protean schema render --indexes renders declared indexes to per-dialect .sql files without touching a database. See protean schema render.