Custom Database Functions

Relevant source files

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

• README.md
• src/lib.rs
• src/backend/query_builder.rs
• src/backend/table_builder.rs
• tests/mysql/table.rs
• tests/postgres/table.rs

SeaQuery provides a robust framework for incorporating both built-in and custom database functions into dynamic SQL queries. By leveraging the Func and Expr modules, developers can construct complex functional expressions that are correctly serialized across different database backends like MySQL, Postgres, and SQLite.

The system allows for standard aggregate functions (e.g., SUM, COUNT) while providing an "escape hatch" via the Iden trait to define domain-specific or backend-specific functions not natively included in the library's core.

Sources: README.md:468-472, src/lib.rs:524-528

Core Components and Architecture

The architecture for custom functions relies on the interaction between the Func enum, the FunctionCall structure, and the QueryBuilder trait. Functions are treated as a type of expression (Expr::FunctionCall), allowing them to be nested within SELECT statements, JOIN conditions, and WHERE clauses.

Function Structure

A function call in SeaQuery consists of the function name (represented by the Func enum) and a set of arguments. Arguments are themselves Expr objects, enabling recursive complexity.

The FunctionCall struct manages the relationship between the function identifier, its arguments, and optional modifiers like DISTINCT or FILTER.
Sources: src/backend/query_builder.rs:658-670, src/backend/query_builder.rs:885-900

Implementation of Custom Functions

To implement a custom function, developers use the Iden trait to define the function's string representation. This identifier is then wrapped in Func::cust and passed to a FunctionCall.

Defining a Custom Function

A custom function is defined by implementing Iden on a struct or enum. The unquoted method (or the derive(Iden) macro) specifies the SQL name of the function.

struct MyFunction;

impl Iden for MyFunction {
    fn unquoted(&self) -> &str {
        "MY_FUNCTION"
    }
}

let query = Query::select()
    .expr(Func::cust(MyFunction).arg(Expr::val("hello")))
    .to_owned();

Sources: README.md:483-494, src/lib.rs:539-550

Function serialization Flow

The QueryBuilder trait handles the translation of the abstract function call into backend-specific SQL.

Built-in vs. Custom Functions

SeaQuery categorizes functions into built-in aggregates and custom extensions.

Function Type	Examples	serialization Logic
Aggregate	Max, Min, Sum, Avg, Count	Mapped to standard SQL keywords in prepare_function_name_common.
System	Cast, Abs, Coalesce, Lower, Upper	Handled by backend-specific keywords (e.g., IFNULL for MySQL).
Custom	MY_FUNCTION, PG_EXT_FUNC	Uses the string provided by the Iden implementation.

Sources: src/backend/query_builder.rs:885-913, README.md:468-475

Advanced Usage and Constraints

Function Modifiers

Function calls support modifiers, most notably DISTINCT, which is serialized within the argument list. Postgres-specific features like FILTER (WHERE ...) are also supported via the filter field in FunctionCall.
Sources: src/backend/query_builder.rs:915-938

Indexing with Functions

Functions can be used within table schema definitions, specifically for functional indexes (expression-based indexes).

Index::create()
    .name("idx-character-area")
    .table(Character::Table)
    .col(Expr::col(Character::SizeH).mul(Expr::col(Character::SizeW)))

Sources: tests/mysql/table.rs:242-246, tests/postgres/table.rs:537-541

Backend Specifics

Different backends may implement the same high-level Func using different SQL functions. For example, Func::CharLength is translated via the char_length_function() hook in the QueryBuilder, allowing for CHAR_LENGTH vs LENGTH variations.
Sources: src/backend/query_builder.rs:898, src/backend/query_builder.rs:1149-1151

Summary

Custom Database Functions in SeaQuery bridge the gap between a type-safe Rust API and the varied functional landscapes of MySQL, Postgres, and SQLite. By providing both a set of common aggregates and an extensible Iden-based system for custom functions, SeaQuery ensures that developers can utilize the full power of their database's functional capabilities while maintaining a structured AST.
Sources: src/lib.rs:23-26, src/backend/query_builder.rs:658-665