Macro sqlx::query[−][src]

macro_rules! query {
    ($query : expr) => { ... };
    ($query : expr, $($args : tt) *) => { ... };
}

This is supported on crate feature macros only.

Expand description

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:

// 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);

The method you want to call depends on how many rows you’re expecting.

Number of Rows	Method to Call*	Returns	Notes
None†	`.execute(...).await`	`sqlx::Result<DB::QueryResult>`	For `INSERT`/`UPDATE`/`DELETE` without `RETURNING`.
Zero or One	`.fetch_optional(...).await`	`sqlx::Result<Option<{adhoc struct}>>`	Extra rows are ignored.
Exactly One	`.fetch_one(...).await`	`sqlx::Result<{adhoc struct}>`	Errors if no rows were returned. Extra rows are ignored. Aggregate queries, use this.
At Least One	`.fetch(...)`	`impl Stream<Item = sqlx::Result<{adhoc struct}>>`	Call `.try_next().await` to get each row result.
Multiple	`.fetch_all(...)`	`sqlx::Result<Vec<{adhoc struct}>>`

* All methods accept one of &mut {connection type}, &mut Transaction or &Pool. † Only callable if the query returns no columns; otherwise it’s assumed the query may return at least one row.

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:

// 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

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:

#[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:

// 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 !.

// Postgres/SQLite:
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));

MySQL should be accurate with regards to nullability as it directly tells us when a column is expected to never be NULL. Any mistakes should be considered a bug in MySQL.

However, inference in SQLite and Postgres is more fragile as it depends primarily on observing NOT NULL constraints on columns. If a NOT NULL column is brought in by a LEFT JOIN then that column may be NULL if its row does not satisfy the join condition. Similarly, a FULL JOIN or RIGHT JOIN may generate rows from the primary table that are all NULL.

Unfortunately, the result of mistakes in inference is a UnexpectedNull error at runtime.

In Postgres, we patch up this inference by analyzing EXPLAIN VERBOSE output (which is not well documented, is highly dependent on the query plan that Postgres generates, and may differ between releases) to find columns that are the result of left/right/full outer joins. This analysis errs on the side of producing false positives (marking columns nullable that are not in practice) but there are likely edge cases that it does not cover yet.

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

// Ironically this is the exact column we primarily 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?;

// Although we do our best, under Postgres this might have been inferred to be `bool`
// In that case, we would have gotten an error
assert_eq!(record.attnotnull, None);

If you find that you need to use this override, please open an issue with a query we can use to reproduce the problem. For Postgres users, especially helpful would be the output of EXPLAIN (VERBOSE, FORMAT JSON) <your query> with bind parameters substituted in the query (as the exact value of bind parameters can change the query plan) and the definitions of any relevant tables (or sufficiently anonymized equivalents).

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:

#[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

Syntax	Nullability	Type
`foo!`	Forced not-null	Inferred
`foo?`	Forced nullable	Inferred
`foo: T`	Inferred	Overridden
`foo!: T`	Forced not-null	Overridden
`foo?: T`	Forced nullable	Overridden

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.