Introduction to SeaQuery

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/lib.rs
• Cargo.toml
• changelog/1.0.0-rc.34.md
• changelog/1.0.0-rc.32.md
• sea-query-derive/Cargo.toml

SeaQuery is a dynamic SQL query builder for Rust, designed to facilitate the construction of complex SQL statements using an ergonomic API. It serves as an abstract syntax tree (AST) for SQL, supporting multiple database backends—specifically MySQL, Postgres, and SQLite—through a unified interface. SeaQuery is written in 100% safe Rust and serves as the foundational layer for the SeaORM project.

The library allows developers to build expressions, queries (SELECT, INSERT, UPDATE, DELETE), and schema definitions (TABLE, INDEX, FOREIGN KEY) dynamically at runtime. It solves common pain points such as manual parameter binding and backend-specific syntax variations, providing tools like raw_query! for ergonomic raw SQL and automatic $N sequencing for Postgres.

Sources: README.md:17-31, src/lib.rs:20-33

Core Components and Architecture

SeaQuery's architecture is built around several key traits and modules that handle the lifecycle of a SQL statement from definition to string serialization.

Primary Traits and Structures

Component	Description
Iden	A trait for identifiers (tables, columns, functions). Can be derived via macros.
Expr	The building block for constructing SQL expressions used in WHERE, JOIN, and SELECT clauses.
Query	The entry point for DML (Data Manipulation Language) statements like Select, Insert, Update, and Delete.
Table	The entry point for DDL (Data Definition Language) statements involving table creation and modification.
QueryBuilder	A backend-specific trait (e.g., MysqlQueryBuilder) that translates the AST into a SQL string.

Sources: README.md:129-224, src/lib.rs:319-381

Data Flow for Query Generation

The following diagram illustrates how a user-defined query is transformed into a database-ready string and parameter set.

The diagram shows the branching logic between generating a human-readable string for debugging versus a prepared statement for secure execution.
Sources: README.md:231-250, src/lib.rs:411-430

Query Construction

Expressions and Conditions

SeaQuery uses Expr for individual logic units and Cond for grouping them. The library respects operator precedence, reducing the need for manual parenthetical clutter.

Query::select()
    .column("id")
    .from("glyph")
    .cond_where(
        Cond::any()
            .add(Cond::all().add(Expr::col("a").is_null()))
            .add(Expr::col("b").is_in([3, 4]))
    )

Sources: README.md:88-109, src/lib.rs:163-188

Parameter Binding Logic

SeaQuery automates the sequencing of parameters to prevent "off by one" errors, particularly in backends like Postgres that use $1, $2 syntax.

The sequence diagram shows how parameters are collected and placeholders are generated sequentially during the build phase.
Sources: README.md:52-82, src/lib.rs:114-152

Schema and DDL Operations

SeaQuery supports extensive schema management, including table creation, indexing, and foreign key constraints.

Supported Schema Operations

• Table Management: Create (with if_not_exists), Alter (Add/Drop column), Rename, Drop, and Truncate.
• Indexing: Create and Drop indexes, including partial index support (though MySQL may reject these).
• Constraints: Foreign Key creation and dropping with support for actions like Cascade or SetNull.
• Partitioning: Support for PostgreSQL and MySQL table partitioning (Range, List, Hash).

Sources: README.md:416-630, changelog/1.0.0-rc.34.md:103-120

DDL Feature Support Table

Feature	MySQL	Postgres	SQLite
TRUNCATE TABLE	Supported	Supported	Not Supported
RENAME TABLE	RENAME TABLE	ALTER TABLE RENAME	ALTER TABLE RENAME
AUTO_INCREMENT	AUTO_INCREMENT	GENERATED BY DEFAULT AS IDENTITY	AUTOINCREMENT
DROP CONSTRAINT	DROP CONSTRAINT	DROP CONSTRAINT	Limited Support

Sources: README.md:460-560, src/lib.rs:563-630, changelog/1.0.0-rc.32.md:85-102

Advanced Features

raw_query! Macro

Introduced in version 1.0, the raw_query! macro provides an escape hatch for complex queries while maintaining parameter binding safety. It supports:

• Named parameters: {a}
• Vector/Array expansion: {..d}
• Nested field access: {c.id}

Sources: README.md:129-178, src/lib.rs:210-258

Backend Specific Logic

SeaQuery handles nuances between backends, such as PostgreSQL unsigned integer mapping. Since PostgreSQL lacks native unsigned types, SeaQuery maps them to wider signed types (e.g., Unsigned becomes bigint).

Sources: changelog/1.0.0-rc.34.md:15-30

Explanations and Optimization

The library supports EXPLAIN statements for all three major backends to assist in query performance analysis.

ExplainStatement::new()
    .analyze()
    .format(ExplainFormat::Json)
    .statement(Query::select().from(Table::Table))

Sources: changelog/1.0.0-rc.32.md:5-48

Summary

SeaQuery provides a robust, type-safe abstraction over SQL for Rust developers. By treating SQL as a dynamic AST, it enables the creation of complex, runtime-generated queries that are backend-agnostic until the final serialization step. Its integration with macros like Iden and raw_query! balances safety with developer productivity, making it a critical component of the SeaQL ecosystem.

Sources: README.md:17-25, src/lib.rs:20-28, Cargo.toml:1-25