[−][src]Macro sqlx::query

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

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

This expands to an instance of query::Map that outputs an ad-hoc anonymous struct type, if the query has at least one output column that is not Void, or () (unit) otherwise:

ⓘThis example is not tested
// 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);

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.

    • Or, sqlx-data.json must exist at the workspace root. See Offline Mode below.

  • The query must be a string literal, or concatenation of string literals using + (useful for queries generated by macro), 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.

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:

ⓘThis example is not tested
// 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/SQLite: ? 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.

In Postgres and MySQL you may also use IS [NOT] DISTINCT FROM to compare with a possibly NULL value. In MySQL IS NOT DISTINCT FROM can be shortened to <=>. In SQLite you can us IS or IS NOT. Note that operator precedence may be different.

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, 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.

For SQLite we perform a similar check to Postgres, looking for NOT NULL constraints on columns that come from tables. However, for SQLite we also can step through the output of EXPLAIN to identify columns that may or may not be NULL.

To override the nullability of an output column, see below.

Type Overrides: Bind Parameters (Postgres only)

For typechecking of bind parameters, casts using as are treated as overrides for the inferred types of bind parameters and no typechecking is emitted:

ⓘThis example is not tested
#[derive(sqlx::Type)]
#[sqlx(transparent)]
struct MyInt4(i32);

let my_int = MyInt4(1);

sqlx::query!("select $1::int4 as id", my_int as MyInt4)

In Rust 1.45 we can eliminate this redundancy by allowing casts using as _ or type ascription syntax, i.e. my_int: _ (which is unstable but can be stripped), but this requires modifying the expression which is not possible as the macros are currently implemented. Casts to _ are forbidden for now as they produce rather nasty type errors.

Type Overrides: Output Columns

Type overrides are also available for output columns, utilizing the SQL standard's support for arbitrary text in column names:

Force Not-Null

Selecting a column foo as "foo!" (Postgres / SQLite) or foo as `foo!` (MySQL) overrides inferred nullability and forces the column to be treated as NOT NULL; this is useful e.g. for selecting expressions in Postgres where we cannot infer nullability:

ⓘThis example is not tested
// Postgres: using a raw query string lets us use unescaped double-quotes
// Note that this query wouldn't work in SQLite as we still don't know the exact type of `id`
let record = sqlx::query!(r#"select 1 as "id!""#) // MySQL: use "select 1 as `id!`" instead
    .fetch_one(&mut conn)
    .await?;

// For Postgres this would have been inferred to be Option<i32> instead
assert_eq!(record.id, 1i32);
Force Nullable

Selecting a column foo as "foo?" (Postgres / SQLite) or foo as `foo?` (MySQL) overrides inferred nullability and forces the column to be treated as nullable; this is provided mainly for symmetry with !, but also because nullability inference currently has some holes and false negatives that may not be completely fixable without doing our own complex analysis on the given query.

ⓘThis example is not tested
// Postgres:
// Note that this query wouldn't work in SQLite as we still don't know the exact type of `id`
let record = sqlx::query!(r#"select 1 as "id?""#) // MySQL: use "select 1 as `id?`" instead
    .fetch_one(&mut conn)
    .await?;

// For Postgres this would have been inferred to be Option<i32> anyway
// but this is just a basic example
assert_eq!(record.id, Some(1i32));

One current such hole is exposed by left-joins involving NOT NULL columns in Postgres and SQLite; as we only know nullability for a given column based on the NOT NULL constraint of its original column in a table, if that column is then brought in via a LEFT JOIN we have no good way to know and so continue assuming it may not be null which may result in some UnexpectedNull errors at runtime.

Using ? as an override we can fix this for columns we know to be nullable in practice:

ⓘThis example is not tested
// Ironically this is the exact column we look at to determine nullability in Postgres
let record = sqlx::query!(
    r#"select attnotnull as "attnotnull?" from (values (1)) ids left join pg_attribute on false"#
)
.fetch_one(&mut conn)
.await?;

// For Postgres this would have been inferred to be `bool` and we would have gotten an error
assert_eq!(record.attnotnull, None);

See launchbadge/sqlx#367 for more details on this issue.

Force a Different/Custom Type

Selecting a column foo as "foo: T" (Postgres / SQLite) or foo as `foo: T` (MySQL) overrides the inferred type which is useful when selecting user-defined custom types (dynamic type checking is still done so if the types are incompatible this will be an error at runtime instead of compile-time). Note that this syntax alone doesn't override inferred nullability, but it is compatible with the forced not-null and forced nullable annotations:

ⓘThis example is not tested
#[derive(sqlx::Type)]
#[sqlx(transparent)
struct MyInt4(i32);

let my_int = MyInt4(1);

// Postgres/SQLite
sqlx::query!(r#"select 1 as "id!: MyInt4""#) // MySQL: use "select 1 as `id: MyInt4`" instead
    .fetch_one(&mut conn)
    .await?;

// For Postgres this would have been inferred to be `Option<i32>`, MySQL/SQLite `i32`
// Note that while using `id: MyInt4` (without the `!`) would work the same for MySQL/SQLite,
// Postgres would expect `Some(MyInt4(1))` and the code wouldn't compile
assert_eq!(record.id, MyInt4(1));
Overrides cheatsheet
SyntaxNullabilityType
foo!Forced not-nullInferred
foo?Forced nullableInferred
foo: TInferredOverridden
foo!: TForced not-nullOverridden
foo?: TForced nullableOverridden

Offline Mode (requires the offline feature)

The macros can be configured to not require a live database connection for compilation, but it requires a couple extra steps:

  • Run cargo install sqlx-cli.
  • In your project with DATABASE_URL set (or in a .env file) and the database server running, run cargo sqlx prepare.
  • Check the generated sqlx-data.json file into version control.
  • Don't have DATABASE_URL set during compilation.

Your project can now be built without a database connection (you must omit DATABASE_URL or else it will still try to connect). To update the generated file simply run cargo sqlx prepare again.

To ensure that your sqlx-data.json file is kept up-to-date, both with the queries in your project and your database schema itself, run cargo install sqlx-cli && cargo sqlx prepare --check in your Continuous Integration script.

See the README for sqlx-cli for more information.

See Also