[−][src]Macro sqlx::query

macro_rules! query {
    ($query:literal) => { ... };
    ($query:literal, $($args:expr),*$(,)?) => { ... };
}
This is supported on feature="macros" only.

Statically checked SQL query with println!() style syntax.

This expands to an instance of QueryAs that outputs an ad-hoc anonymous struct type, if the query has output columns, or () (unit) otherwise:

// let mut conn = <impl sqlx::Executor>;
let account = sqlx::query!("select (1) as id, 'Herp Derpinson' as name")
    .fetch_one(&mut conn)
    .await?;

// anonymous struct has `#[derive(Debug)]` for convenience
println!("{:?}", account);
println!("{}: {}", account.id, account.name);

Query Arguments

Like println!() and the other formatting macros, you can add bind parameters to your SQL and this macro will typecheck passed arguments and error on missing ones:

// let mut conn = <impl sqlx::Executor>;
let account = sqlx::query!(
        // just pretend "accounts" is a real table
        "select * from (select (1) as id, 'Herp Derpinson' as name) accounts where id = ?",
        1i32
    )
    .fetch_one(&mut conn)
    .await?;

println!("{:?}", account);
println!("{}: {}", account.id, account.name);

Bind parameters in the SQL string are specific to the database backend:

  • Postgres: $N where N is the 1-based positional argument index
  • MySQL: ? which matches arguments in order that it appears in the query

Nullability: Bind Parameters

For a given expected type T, both T and Option<T> are allowed (as well as either behind references). Option::None will be bound as NULL, so if binding a type behind Option be sure your query can support it.

Note, however, if binding in a where clause, that equality comparisons with NULL may not work as expected; instead you must use IS NOT NULL or IS NULL to check if a column is not null or is null, respectively. Note that IS [NOT] NULL cannot be bound as a parameter either; you must modify your query string instead.

Nullability: Output Columns

In most cases, the database engine can tell us whether or not a column may be NULL, and the query!() macro adjusts the field types of the returned struct accordingly.

For Postgres and SQLite, this only works for columns which come directly from actual tables, as the implementation will need to query the table metadata to find if a given column has a NOT NULL constraint. Columns that do not have a NOT NULL constraint or are the result of an expression are assumed to be nullable and so Option<T> is used instead of T.

For MySQL, the implementation looks at the NOT_NULL flag of the ColumnDefinition structure in COM_QUERY_OK: if it is set, T is used; if it is not set, Option<T> is used.

MySQL appears to be capable of determining the nullability of a result column even if it is the result of an expression, depending on if the expression may in any case result in NULL which then depends on the semantics of what functions are used. Consult the MySQL manual for the functions you are using to find the cases in which they return NULL.

To override the nullability of an output column, use query_as!.

Requirements

  • The DATABASE_URL environment variable must be set at build-time to point to a database server with the schema that the query string will be checked against. (All variants of query!() use dotenv so this can be in a .env file instead.)

  • The query must be a string literal or else it cannot be introspected (and thus cannot be dynamic or the result of another macro).

  • The QueryAs instance will be bound to the same database type as query!() was compiled against (e.g. you cannot build against a Postgres database and then run the query against a MySQL database).

    • The schema of the database URL (e.g. postgres:// or mysql://) will be used to determine the database type.

See Also