[−][src]Macro sqlx::query_as

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

A variant of query! which takes a path to an explicitly defined struct as the output type.

This lets you return the struct from a function or add your own trait implementations.

No trait implementations are required; the macro maps rows using a struct literal where the names of columns in the query are expected to be the same as the fields of the struct (but the order does not need to be the same). The types of the columns are based on the query and not the corresponding fields of the struct, so this is type-safe as well.

This enforces a few things:

  • The query must output at least one column.
  • The column names of the query must match the field names of the struct.
  • The field types must be the Rust equivalent of their SQL counterparts; see the corresponding module for your database for mappings:
  • If a column may be NULL, the corresponding field's type must be wrapped in Option<_>.
  • Neither the query nor the struct may have unused fields.

The only modification to the query!() syntax is that the struct name is given before the SQL string:

ⓘThis example is not tested
#[derive(Debug)]
struct Account {
    id: i32,
    name: String
}

// let mut conn = <impl sqlx::Executor>;
let account = sqlx::query_as!(
        Account,
        "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);

Column Type Override: Infer from Struct Field

In addition to the column type overrides supported by query!, query_as!() supports an additional override option:

If you select a column foo as "foo: _" (Postgres/SQLite) or foo as `foo: _` (MySQL) it causes that column to be inferred based on the type of the corresponding field in the given record struct. Runtime type-checking is still done so an error will be emitted if the types are not compatible.

This allows you to override the inferred type of a column to instead use a custom-defined type:

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

struct Record {
    id: MyInt4,
}

let my_int = MyInt4(1);

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

assert_eq!(record.id, MyInt4(1));

Troubleshooting: "error: mismatched types"

If you get a "mismatched types" error from an invocation of this macro and the error isn't pointing specifically at a parameter.

For example, code like this (using a Postgres database):

ⓘThis example is not tested
struct Account {
    id: i32,
    name: Option<String>,
}

let account = sqlx::query_as!(
    Account,
    r#"SELECT id, name from (VALUES (1, 'Herp Derpinson')) accounts(id, name)"#,
)
    .fetch_one(&mut conn)
    .await?;

Might produce an error like this:

error[E0308]: mismatched types
   --> tests/postgres/macros.rs:126:19
    |
126 |       let account = sqlx::query_as!(
    |  ___________________^
127 | |         Account,
128 | |         r#"SELECT id, name from (VALUES (1, 'Herp Derpinson')) accounts(id, name)"#,
129 | |     )
    | |_____^ expected `i32`, found enum `std::option::Option`
    |
    = note: expected type `i32`
               found enum `std::option::Option<i32>`

This means that you need to check that any field of the "expected" type (here, i32) matches the Rust type mapping for its corresponding SQL column (see the types module of your database, listed above, for mappings). The "found" type is the SQL->Rust mapping that the macro chose.

In the above example, the returned column is inferred to be nullable because it's being returned from a VALUES statement in Postgres, so the macro inferred the field to be nullable and so used Option<i32> instead of i32. In this specific case we could use select id as "id!" to override the inferred nullability because we know in practice that column will never be NULL and it will fix the error.

Nullability inference and type overrides are discussed in detail in the docs for query!.

It unfortunately doesn't appear to be possible right now to make the error specifically mention the field; this probably requires the const-panic feature (still unstable as of Rust 1.45).