[−][src]Macro sqlx::query
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
whereN
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 ofquery!()
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 asquery!()
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://
ormysql://
) will be used to determine the database type.
- The schema of the database URL (e.g.
See Also
- query_as! if you want to use a struct you can name,
- query_file! if you want to define the SQL query out-of-line,
- query_file_as! if you want both of the above.