sea-query

SeaQuery is a Rust library for constructing dynamic SQL queries without hand-rolling string concatenation. Instead of assembling raw SQL text, you build expressions, queries, and schema definitions as an abstract syntax tree using an ergonomic, chainable API, and SeaQuery renders the correct dialect-specific SQL for MySQL, Postgres, or SQLite behind a common interface.

It is written in 100% safe Rust — every workspace crate carries #![forbid(unsafe_code)] — and is the foundation of SeaORM, the async dynamic ORM for Rust. First-party integration crates cover SQLx, the native postgres crate, and rusqlite, so the same query-building code can target whichever database driver a project already uses.

SeaQuery is designed to be lightweight: nearly all of its dependencies, from chrono and uuid support to Postgres array and range types, are opt-in feature flags rather than defaults. That makes it equally suitable as a low-level building block inside a larger ORM (as SeaORM uses it) or as a standalone query builder for teams who want typed, composable SQL construction without adopting a full ORM’s runtime and conventions.

What You Get

• Chainable builders for SELECT, INSERT, UPDATE, and DELETE statements, including joins, subqueries, CTEs, window functions, and UNION/aggregate queries
• Schema statement builders for CREATE/ALTER/DROP/RENAME/TRUNCATE TABLE and foreign key management, so DDL can be generated the same way as DML
• A common QueryBuilder trait implemented for MySQL, Postgres, and SQLite that renders each dialect’s correct quoting, placeholder style, and SQL syntax from one AST
• Typed Expr and Condition building blocks for WHERE/HAVING clauses, casting, custom functions, and aggregate expressions without string concatenation
• Optional derive macros (derive/attr features) for mapping Rust enums to Iden table/column identifiers
• Integration crates for SQLx, postgres, rusqlite, Diesel, and rbatis so generated queries can be executed directly against the driver already in use
• Opt-in feature flags for chrono, time, jiff, uuid, rust_decimal, bigdecimal, ipnetwork, mac_address, serde, and Postgres-specific array/interval/range/vector types

Common Use Cases

• Building dynamic queries whose WHERE clauses, joins, or selected columns vary at runtime based on user input or filters, where string concatenation would be error-prone
• Writing database-agnostic query logic that needs to run correctly against MySQL, Postgres, and SQLite from the same codebase
• Serving as the query-construction layer underneath a higher-level ORM or data-access layer (as it does inside SeaORM) rather than being used as a full ORM itself
• Generating and applying schema migrations (CREATE TABLE, ALTER TABLE, foreign keys) programmatically instead of hand-writing dialect-specific DDL
• Producing parameterized queries for execution through SQLx, rusqlite, or the native postgres crate while keeping SQL construction type-checked in Rust

Under The Hood

Architecture SeaQuery models SQL as an abstract syntax tree: statement types like SelectStatement, InsertStatement, UpdateStatement, and DeleteStatement (in src/query/) hold typed fields — selected expressions, joins, a ConditionHolder for WHERE/HAVING, ordering, and CTEs — built up through a chainable builder API. Rendering is separated from construction: each statement implements QueryStatementBuilder/QueryStatementWriter traits that a dialect-specific QueryBuilder (in src/backend/{mysql,postgres,sqlite}/) walks to produce the correct SQL string, quoting style, and parameter placeholders for that engine. Schema statements (src/table/, src/foreign_key/, src/index/) follow the same builder-then-render split, so DDL and DML share the same architectural pattern. The core abstraction — an AST decoupled from any single SQL dialect — is what lets one query definition render correctly against three different databases without per-backend query code.

Tech Stack SeaQuery is a Cargo workspace built around a minimal-by-default dependency graph: nearly every non-core dependency (chrono, time, jiff, uuid, rust_decimal, bigdecimal, ipnetwork, mac_address, serde, pgvector) is gated behind an opt-in Cargo feature rather than pulled in by default. Companion crates in the same workspace — sea-query-derive for identifier derive macros, and sea-query-sqlx, sea-query-postgres, sea-query-rusqlite, sea-query-diesel, sea-query-rbatis as separate published crates — bind the query AST to specific execution backends without coupling the core crate to any one driver. The project targets a pinned minimum Rust version and uses Cargo’s workspace mechanism to keep the derive macro and optional integrations independently versioned.

Code Quality The crate enforces #![forbid(unsafe_code)] and #![warn(clippy::nursery)] at the crate root, and denies missing Debug implementations, signaling a deliberately strict lint posture. Tests are extensive and dialect-specific — the tests/ directory contains thousands of lines split into mysql/, postgres/, and sqlite/ suites plus dedicated derive, error, and raw-SQL test files — and public API items carry embedded doctests (runnable assert_eq! examples in doc comments, as seen in src/query/select.rs) that are checked as part of the test suite. Error handling is explicit and typed via a small Error enum in src/error.rs rather than panics or silently discarded results. GitHub Actions CI runs the Rust and Diesel-integration test workflows on every change.

API Design The public API favors a fluent builder style — Query::select().column(...).from(...).and_where(...) — that mirrors how SQL reads, keeping boilerplate low for common query shapes while still exposing lower-level Expr and Condition primitives for complex cases. Naming is consistent across statement types (and_where, left_join, order_by read the same way whether building a SELECT or a schema statement), and the README documents every major construct — from basic expressions through window functions and schema DDL — with runnable, copy-pasteable examples for all three supported SQL dialects side by side.