MySQL, Postgres, and SQLite Handlers

Relevant source files

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

• src/lib.rs
• src/backend/mysql/query.rs
• src/backend/mysql/table.rs
• src/backend/mysql/index.rs
• src/backend/table_builder.rs
• src/query/explain.rs
• README.md

SeaQuery provides a unified interface for constructing dynamic SQL queries across three primary database backends: MySQL, PostgreSQL, and SQLite. These handlers function as backend-specific implementations of core traits, translating abstract syntax trees (AST) into database-specific SQL strings and parameter bindings.

The project aligns behavior across these backends where appropriate but also accommodates dialect-specific features such as MySQL's ON DUPLICATE KEY UPDATE or PostgreSQL's ON CONFLICT syntax.
Sources: README.md:16-20, src/lib.rs:25-30

Backend Architecture

The architecture relies on trait implementations to handle the translation from SeaQuery's internal representation to raw SQL. Each backend implements specific builder traits like QueryBuilder, SchemaBuilder, TableBuilder, and IndexBuilder.

Core Trait Hierarchy

The following diagram illustrates how backend-specific structures (e.g., MysqlQueryBuilder) implement the generic builder interfaces.

MySQL Handler Implementation

The MySQL handler manages specific syntax requirements such as backtick quoting for identifiers and the ON DUPLICATE KEY clause for upserts.

Query Statement Building

MySQL's QueryBuilder implementation handles dialect-specific functions and keywords. For example, it uses RAND for random functions and () for default inserts.
Sources: src/backend/mysql/query.rs:276-285

Feature	MySQL Implementation Details
Identifier Quoting	Uses backticks (`)
Random Function	RAND
Upsert Logic	ON DUPLICATE KEY UPDATE
Join Support	Lacks FULL OUTER JOIN (panics if attempted)
Distinct	Supports ALL, DISTINCT, and DISTINCTROW
Sources: src/backend/mysql/query.rs:13-20, src/backend/mysql/query.rs:200-205, src/backend/mysql/query.rs:276-278, src/backend/mysql/query.rs:245-250

Schema and Table Management

The MySQL table builder translates generic ColumnType enums into MySQL data types like TINYINT, VARCHAR, and ENUM.

// Example of MySQL column type mapping
ColumnType::TinyInteger | ColumnType::TinyUnsigned => sql.write_str("tinyint"),
ColumnType::Boolean => sql.write_str("bool"),
ColumnType::String(length) => {
    sql.write_str("varchar(").unwrap();
    write_int(sql, *length);
    sql.write_char(')')
}

Sources: src/backend/mysql/table.rs:46-55, src/backend/mysql/table.rs:98

Index Construction

MySQL indexes can be defined during table creation or as standalone statements. The handler supports BTREE, HASH, and FULLTEXT index types.
Sources: src/backend/mysql/index.rs:77-90

PostgreSQL and SQLite Variations

While sharing a common interface, the handlers diverge in their handling of parameter placeholders and advanced features.

Parameter Binding

• PostgreSQL: Uses numbered placeholders ($1, $2, $3).
• MySQL: Uses question marks (?).
• SQLite: Uses question marks (?).

Explain Statement Support

The ExplainStatement is implemented across backends with varying options:

• PostgreSQL: Supports ANALYZE, VERBOSE, COSTS, and multiple formats (TEXT, XML, JSON, YAML).
• MySQL: Supports ANALYZE, FORMAT (JSON, TREE, TRADITIONAL), and target specifications for specific columns or connection IDs.
• SQLite: Supports EXPLAIN QUERY PLAN.
  Sources: src/query/explain.rs:15-45, src/query/explain.rs:188-210

Type Mapping Summary

SeaQuery Type	MySQL	PostgreSQL (rc.34+)
Unsigned	INT UNSIGNED	bigint
SmallUnsigned	SMALLINT UNSIGNED	integer
TinyUnsigned	TINYINT UNSIGNED	smallint
Uuid	BINARY(16)	uuid
Json	JSON	jsonb
Sources: src/backend/mysql/table.rs:52-110, changelog/1.0.0-rc.34.md:16-25

Conclusion

The MySQL, Postgres, and SQLite handlers in SeaQuery provide the necessary translation layer to ensure that a single dynamic query AST can be executed across different database engines. By implementing specific builder traits, each handler manages the nuances of SQL dialects, type systems, and parameter sequencing required by the respective database drivers.