Derive Macro SelectTemplate

Source

#[derive(SelectTemplate)]
{
    // Attributes available to this derive:
    #[table]
    #[debug_slow]
    #[tp_select_all]
    #[tp_select_one]
    #[tp_select_page]
    #[tp_select_stream]
    #[tp_select_count]
    #[tp_select_builder]
    #[db]
    #[tp_select_builder]
    #[auto]
}

Expand description

SelectTemplate is a derive macro designed to automatically generate record retrieval functions based on sqlx. This macro creates various query methods for the struct it is applied to, returning records from the database, assuming that the columns in the database correspond to the fields in the struct.

§Attributes

SelectTemplate accepts the following attributes:

table: Specifies the name of the table in the database (mandatory).
debug_slow: Configures debug logs for the executed query:
- If set to 0: Only logs the executed query.
- If set to a value greater than 0: Only logs the query if the execution time exceeds the configured value (in milliseconds).
- If not configured, no debug logs will be generated.
tp_select_all: Generates a function that returns all records as a Vec<T>. It has the following sub-attributes:
- by: List of columns for the WHERE condition, used as function input (can be empty).
- fn_name: The name of the generated function. If empty, the library will automatically generate a function name.
- order: Adds an ORDER BY clause based on the specified columns and order (supports asc|desc, default is asc).
- where: Additional WHERE clause with placeholder support (see Placeholder Mapping section below).
- debug_slow: Configures debug logs for the executed query.
tp_select_one: Similar to tp_select_all, but returns a single record as Option<T>.
tp_select_stream: Similar to tp_select_all, but returns an impl Stream<Item = T>.
tp_select_count: Similar to tp_select_all, but returns the count of records as i64.
tp_select_page: Similar to tp_select_all, but accepts pagination parameters and returns a tuple of all records and the total count.
db: Specifies the target database type (e.g., #[db("postgres")]).
tp_select_builder: Builder pattern configuration for SELECT operations with custom WHERE conditions.

The debug_slow attribute at the struct level has priority over the value in tp_select_*.

§Builder Pattern Support

The macro supports fluent builder patterns for query construction with custom WHERE conditions.

§Builder Attributes

tp_select_builder: Builder pattern configuration for SELECT operations
tp_update_builder: Builder pattern configuration for UPDATE operations
tp_delete_builder: Builder pattern configuration for DELETE operations

§Custom Condition Syntax

Custom conditions are defined using the following syntax:

#[tp_select_builder(
    method_name = "SQL_expression_with_placeholders"
)]

§Parameter Types

Auto-mapping: :field_name automatically maps to struct field types
Explicit types: :param$Type for custom parameter types
Multiple parameters: Single condition can accept multiple parameters

§Examples

#[tp_select_builder(
    with_email_domain = "email LIKE :domain$String",
    with_score_range = "score BETWEEN :min$i32 AND :max$i32",
    with_active_status = "active = :active"  // Auto-mapped to bool
)]

§Generated Methods

For each field, the builder generates:

§Field-based Methods

Equality: .field_name(value), .field_name_not(value)
Comparison: .field_name_gt(value), .field_name_gte(value), .field_name_lt(value), .field_name_lte(value)
String operations: .field_name_like(pattern), .field_name_start_with(prefix), .field_name_end_with(suffix)
Ordering: .order_by_field_asc(), .order_by_field_desc()

§Builder-specific Methods

§SELECT Builder

Query execution: .find_all(), .find_one(), .count(), .find_page(), .stream()
SQL generation: .build_sql()

§UPDATE Builder

SET clauses: .on_field_name(value) - specify which fields to update
WHERE clauses: .by_field_name(value) - specify which records to update
Execution: .execute() - returns number of affected rows

§DELETE Builder

WHERE clauses: .field_name(value) - specify which records to delete
Execution: .execute() - returns number of deleted rows

§Usage Examples

§SELECT Builder

let users = User::builder_select()
    .email("john@example.com")?           // Field-based condition
    .score_gte(&75)?                      // Generated comparison method
    .with_email_domain("%@company.com")?  // Custom condition
    .with_score_range(60, 90)?            // Custom condition with multiple params
    .order_by_score_desc()?               // Generated ORDER BY method
    .find_all(&pool)
    .await?;

§UPDATE Builder

let affected = User::builder_update()
    .on_email("newemail@example.com")?    // SET email = ?
    .on_active(&true)?                    // SET active = ?
    .by_id(&user_id)?                     // WHERE id = ?
    .with_high_score(80)?                 // Custom WHERE condition
    .execute(&pool)
    .await?;

§DELETE Builder

let deleted = User::builder_delete()
    .active(&false)?                      // WHERE active = false
    .with_old_accounts("2023-01-01")?     // Custom WHERE condition
    .execute(&pool)
    .await?;

§Validation

Table alias validation: Prevents use of table aliases (e.g., u.field) in custom conditions
Column validation: Ensures referenced columns exist in the struct
Type safety: Compile-time parameter type checking
SQL injection protection: Uses parameterized queries

§Performance Features

Compile-time optimization: SQL templates pre-generated at compile time
Minimal runtime overhead: Reduced format! calls and string allocations
Efficient parameter binding: Direct parameter binding without intermediate formatting

§Document generated by LLMs

§Placeholder Mapping

The where attribute supports advanced placeholder mapping with two main cases:

§Case 1: Column-Mapped Placeholders

When a placeholder (:name) appears in a comparison operation (=, !=, <, >, LIKE) and is mapped to a struct field, the parameter type is automatically inferred from the struct field:

use sqlx_template::SelectTemplate;

#[derive(SelectTemplate, sqlx::FromRow)]
#[table("users")]
#[db("postgres")]
#[tp_select_all(where = "name = :name and age > :age")]
pub struct User {
    pub name: String,  // :name parameter will be &str (String -> &str)
    pub age: i32,      // :age parameter will be &i32
}
// Generated: find_all(name: &str, age: &i32, conn: E) -> Result<Vec<User>, sqlx::Error>

§Case 2: Custom Type Placeholders

Use the format :name$Type to specify a custom parameter type:

use sqlx_template::SelectTemplate;

#[derive(SelectTemplate, sqlx::FromRow)]
#[table("users")]
#[db("postgres")]
#[tp_select_all(where = "score > :min_score$f64 and created_at > :since$chrono::DateTime<chrono::Utc>")]
pub struct User {
    pub score: f64,
    pub created_at: chrono::DateTime<chrono::Utc>,
}
// Generated: find_all(min_score: &f64, since: &chrono::DateTime<chrono::Utc>, conn: E) -> Result<Vec<User>, sqlx::Error>

§Mixed Usage

You can combine both approaches in the same WHERE clause:

use sqlx_template::SelectTemplate;

#[derive(SelectTemplate, sqlx::FromRow)]
#[table("users")]
#[db("postgres")]
#[tp_select_all(where = "name = :name and score > :min_score$f64")]
pub struct User {
    pub name: String,  // :name mapped to column
    pub score: f64,    // :min_score$f64 uses custom type
}
// Generated: find_all(name: &str, min_score: &f64, conn: E) -> Result<Vec<User>, sqlx::Error>

Note: Placeholders are only mapped when they appear in direct column comparisons. Placeholders in expressions like 2 * id > :value or JSON operations data -> :key are not mapped.

Additionally, the library will automatically generate the following default functions when SelectTemplate is derived:

find_all: Returns all records in the table.
count_all: Counts all records in the table.
find_page_all: Returns all records and the total count in the table based on pagination parameters.

§Example

use sqlx_template::SelectTemplate;
use sqlx::{FromRow, Pool};
use futures_util::StreamExt;

#[derive(SelectTemplate, FromRow)]
#[table("users")]
#[db("postgres")]
#[tp_select_one(by = "id", fn_name = "find_user_by_id")]
#[tp_select_one(by = "email", where = "active = :active")]
#[tp_select_all(by = "id, email", order = "id desc", where = "score > :min_score$f64")]
#[tp_select_count(by = "id, email")]
#[tp_select_page(by = "org", order = "id desc, org desc")]
#[tp_select_stream(order = "id desc")]
#[tp_select_builder(
    with_email_domain = "email LIKE :domain$String",
    with_score_range = "score BETWEEN :min$f64 AND :max$f64"
)]
#[debug_slow = 1000]
pub struct User {
    #[auto]
    pub id: i32,
    pub email: String,
    pub password: String,
    pub org: Option<i32>,
    pub active: bool,
    pub score: f64,
}

// Example usage:
// Find user by id
let user = User::find_user_by_id(&1, &pool).await?;
println!("Found user: {:?}", user);

// Find user by email
let user = User::find_one_by_email(&"user@example.com".to_string(), &pool).await?;

// Find all users with conditions
let users = User::find_all_by_id_and_email(&1, &"user@example.com".to_string(), &pool).await?;

// Count users
let user_count = User::count_by_id_and_email(&1, &"user@example.com".to_string(), &pool).await?;

// Find users with pagination
let page_request = (0i64, 10i32, true); // (offset, limit, count)
let (users, total_count) = User::find_page_by_org_order_by_id_desc_and_org_desc(&Some(1), page_request, &pool).await?;

// Stream users
let mut user_stream = User::stream_order_by_id_desc(&pool);
while let Some(Ok(user)) = user_stream.next().await {
    println!("Streamed user: {:?}", user);
}

// Builder pattern queries
let users = User::builder_select()
    .email("john@example.com")?
    .active(&true)?
    .with_email_domain("%@company.com")?
    .with_score_range(&60.0, &90.0)?
    .order_by_score_desc()?
    .find_all(&pool)
    .await?;

In the example above:

table is set to “users”, specifying the table to query from.
Various tp_select_* configurations generate different types of query functions.
Function names are automatically generated based on the by and order parameters.

§Note

This macro relies on sqlx, so you need to add sqlx to your [dependencies] in Cargo.toml and properly configure the database connection before using the generated query methods.

Derive Macro SelectTemplateCopy item path

§Attributes

§Builder Pattern Support

§Builder Attributes

§Custom Condition Syntax

§Parameter Types

§Examples

§Generated Methods

§Field-based Methods

§Builder-specific Methods

§SELECT Builder

§UPDATE Builder

§DELETE Builder

§Usage Examples

§SELECT Builder

§UPDATE Builder

§DELETE Builder

§Validation

§Performance Features

§Document generated by LLMs

§Placeholder Mapping

§Case 1: Column-Mapped Placeholders

§Case 2: Custom Type Placeholders

§Mixed Usage

§Example

§Note

Derive Macro SelectTemplate