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", "mysql", "mariadb").
ddl str Verbatim CREATE INDEX … statement.
name str | None Optional name, for reporting.

dialect must be one the framework renders index DDL for: "postgresql", "sqlite", "mssql", "mysql", or "mariadb". The name is matched by exact string, so a typo ("postgres") or an unsupported dialect ("oracle") raises IncorrectUsageError at Domain.init(). Such a declaration would otherwise render no index on any dialect, so the error names it at the point you declare it rather than dropping it silently.

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 MySQL Memory Elasticsearch
Composite, unique, desc, naming ✅ ✅ ✅ ✅ unique enforced; rest advisory —
where (partial index) ✅ ✅ ⚠️ falls back ⚠️ falls back advisory —
include (covering columns) ✅ ⚠️ falls back ✅ ⚠️ falls back advisory —
Index.from_sql matched dialect only matched dialect only matched dialect only matched dialect only — —

The MySQL column above covers MariaDB too: one provider serves both, and SQLAlchemy names their dialects mysql and mariadb, so Index.from_sql matches on whichever the connection URI selects.

  • SQLAlchemy providers (PostgreSQL, SQLite, SQL Server, MySQL) 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.
  • MySQL additionally caps an indexed string: InnoDB allows 3072 bytes per index key, which is 768 utf8mb4 characters across the whole index, so a declaration whose string columns exceed that raises IncorrectUsageError naming the index and its fields. A composite index is the sum of its columns. See the MySQL provider page.
  • 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;
  • a RawIndex whose dialect is not one the framework renders DDL for (mariadb, mssql, mysql, postgresql, sqlite).

A RawIndex's DDL string stays opaque: only its dialect is checked. A name outside the rendered set (a typo like postgres, or a dialect with no renderer) would be silently dropped at every call site, so validation rejects it here instead.

Generating DDL artifacts

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