Index Management
Relevant source files
The following files were used as context for generating this wiki page:
Index Management in SeaQuery provides a fluent API for defining, creating, and dropping database indexes across different SQL backends, including MySQL, PostgreSQL, and SQLite. It allows developers to construct index-related DDL (Data Definition Language) statements as Abstract Syntax Trees (ASTs) that are then translated into backend-specific SQL strings.
The module supports a wide array of index features, such as unique constraints, primary keys, full-text searches, and partial indexes via WHERE clauses. By abstracting the syntax differences between databases—such as how PostgreSQL handles CONCURRENTLY or how MySQL uses USING HASH—SeaQuery ensures consistent schema management within a Rust environment.
Sources: src/lib.rs:567-610, src/index/create.rs:165-177
Index Creation Architecture
Index creation is centered around the IndexCreateStatement struct. This structure holds all necessary metadata, including the target table, column names, index types, and visibility options (e.g., IF NOT EXISTS).
Statement Structure and Components
The API uses a builder pattern to configure the index before it is serialized by a specific SchemaBuilder.
The diagram above illustrates the relationship between the statement definition and the backend-specific builders.
Sources: src/index/create.rs:165-177, src/backend/index_builder.rs:5-8
Key Methods for Construction
| Method | Description | Supported Backends |
|---|---|---|
if_not_exists() | Adds IF NOT EXISTS clause to the statement. | Postgres, SQLite |
primary() | Defines the index as a Primary Key. | All |
unique() | Defines the index as a Unique index. | All |
concurrently() | Creates index without locking the table. | Postgres |
full_text() | Sets index type to FullText (GIN in Postgres). | MySQL, Postgres |
include(col) | Includes non-key columns in the index. | Postgres |
cond_where(expr) | Creates a partial index using a filter. | All (MySQL emits error) |
Sources: src/index/create.rs:189-260, changelog/1.0.0-rc.34.md:83-85
Backend-Specific Implementations
The IndexBuilder trait defines how the AST is translated into SQL. While a default implementation exists, each backend provides specific logic to handle its unique syntax requirements.
MySQL Implementation
MySQL requires specific placement for index types and does not support standalone IF EXISTS for index dropping. It also uses the KEY keyword within table creation expressions.
The flow shows the sequence of SQL generation for MySQL indexes.
Sources: src/backend/mysql/index.rs:4-28, src/backend/mysql/index.rs:90-104
PostgreSQL Implementation
Postgres supports advanced features like CONCURRENTLY, INCLUDE columns, and NULLS NOT DISTINCT. It maps IndexType::FullText specifically to the GIN access method.
- Operator Classes: Postgres can specify operator classes for individual columns within the index columns list.
- Include Columns: Uses the
INCLUDE (...)syntax to store non-key data in index leaf nodes. - Concurrent Creation: Adds
CONCURRENTLYbetweenCREATEandINDEX.
Sources: src/backend/postgres/index.rs:42-70, src/backend/postgres/index.rs:105-115
Partial and Functional Indexes
SeaQuery supports indexes that are either limited to a subset of rows (Partial) or based on expressions rather than raw columns (Functional).
Functional Indexes
Functional indexes are created by passing expressions to the .col() method instead of simple identifiers.
// Functional Index Example
Index::create()
.name("idx-character-area")
.table(Character::Table)
.col(Expr::col(Character::SizeH).mul(Expr::col(Character::SizeW)))
.to_string(MysqlQueryBuilder);
// Result: CREATE INDEX `idx-character-area` ON `character` ((`size_h` * `size_w`))
Sources: src/index/create.rs:152-164, tests/mysql/index.rs:65-72
Partial Indexes
Partial indexes use the cond_where or and_where methods to apply a WHERE clause to the index.
- Postgres/SQLite: Supported natively.
- MySQL: Historically ignored filters; as of
1.0.0-rc.34, it now emits theWHEREclause, which will cause a MySQL syntax error to prevent silent data integrity issues.
Sources: src/index/create.rs:125-138, changelog/1.0.0-rc.34.md:83-85
Dropping Indexes
Index deletion is handled by the IndexDropStatement. Similar to creation, it supports conditional dropping and backend-specific scoping.
Index management in SeaQuery provides a robust abstraction layer, enabling developers to define complex indexing strategies that remain portable across database engines while still accessing backend-specific optimizations.