Raw SQL Integration

Relevant source files

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

• sea-orm-macros/src/raw_sql.rs
• src/executor/execute.rs
• tests/raw_sql_tests.rs
• src/lib.rs
• sea-orm-sync/src/lib.rs
• CLAUDE.md

SeaORM provides a powerful mechanism for integrating raw SQL queries into the ORM workflow, designed to handle complex transactional queries that are difficult to express via standard fluent APIs. The core of this integration is the raw_sql! macro, which offers an ergonomic way to write SQL with type-safe parameter interpolation, array expansion, and protection against SQL injection.

This system allows developers to let SeaORM handle approximately 95% of transactional queries while providing a "safety valve" for the remaining 5% of highly complex cases. The integration supports both asynchronous and synchronous execution environments, ensuring consistency across different application types.
Sources: src/lib.rs:245-249, sea-orm-sync/src/lib.rs:245-249, CLAUDE.md:144-149

The raw_sql! Macro

The raw_sql! macro serves as the primary interface for raw SQL integration. It functions similarly to the standard format! macro but is specifically tailored for SQL execution. It supports backend-specific syntax (e.g., Sqlite, Postgres, MySql) and handles the conversion of Rust variables into SQL parameters.

Key Capabilities

• Nested Parameter Access: Access fields of Rust structs directly within the SQL string using {var.field} syntax.
• Operator Expansion: Use the .. operator (e.g., {..ids}) to expand Rust arrays or tuples into SQL-compatible comma-separated lists.
• Repeating Groups: Support for repeating logic within the SQL statement.
• Type Safety: Automatically maps Rust types to database values, mitigating the risk of SQL injection.

Sources: src/lib.rs:250-264, sea-orm-sync/src/lib.rs:250-264, CLAUDE.md:144-149

Macro Implementation Logic

The macro implementation in sea-orm-macros parses the provided tokens to identify the target database backend and the SQL template. It extracts expressions for interpolation and handles the transformation of the template into a format suitable for the database driver.

The diagram above illustrates the high-level flow of how the macro processes a raw SQL call into an executable statement.
Sources: sea-orm-macros/src/raw_sql.rs

Execution Workflows

Raw SQL can be used in various contexts, ranging from simple data retrieval to complex joins that map back to SeaORM entities or partial models.

Entity and Partial Model Integration

Raw SQL statements can be integrated into the standard Entity::find() workflow using the from_raw_sql method. This allows the result of a raw query to be automatically mapped into a SeaORM Model. Additionally, queries can be mapped to a FromQueryResult struct or a DerivePartialModel struct for specific field selection.

Sources: src/lib.rs:255-263, sea-orm-sync/src/lib.rs:490-505, CLAUDE.md:113-125

Raw Execution APIs

SeaORM provides direct execution methods for raw SQL statements when mapping to an entity is not required.

Method	Description
execute_raw	Executes a raw statement and returns an ExecResult.
query_one	Executes a query and returns exactly one row or an error.
query_all	Executes a query and returns all matching rows.
find_by_statement	Used with FromQueryResult to map custom SQL results to a struct.

Sources: src/executor/execute.rs, CLAUDE.md:167-175, src/lib.rs:505-515

Sequence Diagram: Raw SQL Query Flow

The following diagram shows the interaction between the application code, the SeaORM executor, and the database when performing a raw SQL query.

Comparison: Async vs. Sync

SeaORM supports both async/await patterns and synchronous execution. The API remains largely identical, allowing code portability between full-scale web servers and lightweight CLI tools.

// Async Example (Standard SeaORM)
let user: Option<user::Model> = user::Entity::find()
    .from_raw_sql(raw_sql!(Sqlite, "SELECT ..."))
    .one(db)
    .await?;

// Sync Example (sea-orm-sync)
let user: Option<user::Model> = user::Entity::find()
    .from_raw_sql(raw_sql!(Sqlite, "SELECT ..."))
    .one(db)?;

Sources: src/lib.rs:255-264, sea-orm-sync/src/lib.rs:255-264

Summary

Raw SQL Integration in SeaORM 2.0 provides an ergonomic and safe way to execute complex queries while maintaining compatibility with the ORM's entity mapping system. By utilizing the raw_sql! macro, developers gain the flexibility of handwritten SQL without sacrificing the type safety and parameter management provided by the library.
Sources: src/lib.rs:245-249, CLAUDE.md:144-149