Attribute Macro select

#[select]

The select procedural macro transforms a SQL query with named parameters into an asynchronous function that interacts with the database. It provides various features, including debugging options and support for multiple return types.

Syntax

use sqlx_template::select;

struct User {
    pub id: i32,
    pub active: bool,
}

#[select(
    sql = "SELECT * FROM users WHERE active = :active",
    db = "postgres",
    debug = 100
)]
pub async fn get_active_users(active: bool) -> Vec<User> {}

Attributes

• sql: Specifies the SQL query to be executed. This can be:

  • A raw SQL query as a string (e.g., sql = "SELECT * FROM user WHERE (name = :name and age = :age) OR name LIKE '%:name%').
  • A path to a file containing the SQL query (e.g., file = "path/to/query.sql").
  • The query directly as a string without the sql or file keyword.

  Constraints:

  • The query must contain a single SQL SELECT statement.
  • Named parameters (if exist) must be in the format :<param_name> and must correspond to the function’s parameters.

• debug: Controls the debug behavior of the macro. It can be:

  • An integer value. If not provided, the default is no debugging.
  • 0: Prints the query before execution.
  • Greater than 0: Prints the query and execution time if it exceeds the specified number of milliseconds.

Function Signature

The macro generates an asynchronous function with the following characteristics:

• The function signature remains unchanged (e.g., pub async fn <function_name>).
• The function parameters are preserved in their original order.
• An additional parameter for the database connection is required.

Return Types

The macro supports various return types based on the SQL query:

• Single Record:

  • T: Returns a single record, which must be present. If no record is found, an error is returned.
  • Option<T>: Returns a single record if present, or None if no record is found.

• Multiple Records:

  • Vec<T>: Returns all matching records as a vector.

• Asynchronous Stream:

  • Stream<T>: Returns an asynchronous stream of records.

• Paged Records:

  • Page<T>: Returns paginated results. Requires an additional parameter for pagination (e.g., impl Into<(i64, i32, bool)>). The function returns a tuple (Vec<T>, Option<i64>), where the vector contains the paginated records, and the optional value represents the total number of records if requested.

• Scalar Value:

  • Scalar<T>: Returns a single scalar value from the query.

• Affected Rows:

  • RowAffected: Returns the number of affected rows.

• Void:

  • : Returns nothing.

Example Usage

use sqlx_template::select;

struct UserInfo {
    pub name: String,
    pub age: i32,
}

#[select(
    sql = "
    SELECT *
    FROM user
    WHERE (name = :name and age = :age) OR name LIKE '%:name%'
",
    db = "postgres",
    debug = 100
)]
pub async fn query_user_info(name: &str, age: i32) -> Vec<UserInfo> {}