sqlx_core/
query_as.rs

1use std::marker::PhantomData;
2
3use either::Either;
4use futures_core::stream::BoxStream;
5use futures_util::{StreamExt, TryStreamExt};
6
7use crate::arguments::IntoArguments;
8use crate::database::{Database, HasStatementCache};
9use crate::encode::Encode;
10use crate::error::{BoxDynError, Error};
11use crate::executor::{Execute, Executor};
12use crate::from_row::FromRow;
13use crate::query::{query, query_statement, query_statement_with, query_with_result, Query};
14use crate::types::Type;
15
16/// A single SQL query as a prepared statement, mapping results using [`FromRow`].
17/// Returned by [`query_as()`].
18#[must_use = "query must be executed to affect database"]
19pub struct QueryAs<'q, DB: Database, O, A> {
20    pub(crate) inner: Query<'q, DB, A>,
21    pub(crate) output: PhantomData<O>,
22}
23
24impl<'q, DB, O: Send, A: Send> Execute<'q, DB> for QueryAs<'q, DB, O, A>
25where
26    DB: Database,
27    A: 'q + IntoArguments<'q, DB>,
28{
29    #[inline]
30    fn sql(&self) -> &'q str {
31        self.inner.sql()
32    }
33
34    #[inline]
35    fn statement(&self) -> Option<&DB::Statement<'q>> {
36        self.inner.statement()
37    }
38
39    #[inline]
40    fn take_arguments(&mut self) -> Result<Option<<DB as Database>::Arguments<'q>>, BoxDynError> {
41        self.inner.take_arguments()
42    }
43
44    #[inline]
45    fn persistent(&self) -> bool {
46        self.inner.persistent()
47    }
48}
49
50impl<'q, DB: Database, O> QueryAs<'q, DB, O, <DB as Database>::Arguments<'q>> {
51    /// Bind a value for use with this SQL query.
52    ///
53    /// See [`Query::bind`](Query::bind).
54    pub fn bind<T: 'q + Encode<'q, DB> + Type<DB>>(mut self, value: T) -> Self {
55        self.inner = self.inner.bind(value);
56        self
57    }
58}
59
60impl<'q, DB, O, A> QueryAs<'q, DB, O, A>
61where
62    DB: Database + HasStatementCache,
63{
64    /// If `true`, the statement will get prepared once and cached to the
65    /// connection's statement cache.
66    ///
67    /// If queried once with the flag set to `true`, all subsequent queries
68    /// matching the one with the flag will use the cached statement until the
69    /// cache is cleared.
70    ///
71    /// If `false`, the prepared statement will be closed after execution.
72    ///
73    /// Default: `true`.
74    pub fn persistent(mut self, value: bool) -> Self {
75        self.inner = self.inner.persistent(value);
76        self
77    }
78}
79
80// FIXME: This is very close, nearly 1:1 with `Map`
81// noinspection DuplicatedCode
82impl<'q, DB, O, A> QueryAs<'q, DB, O, A>
83where
84    DB: Database,
85    A: 'q + IntoArguments<'q, DB>,
86    O: Send + Unpin + for<'r> FromRow<'r, DB::Row>,
87{
88    /// Execute the query and return the generated results as a stream.
89    pub fn fetch<'e, 'c: 'e, E>(self, executor: E) -> BoxStream<'e, Result<O, Error>>
90    where
91        'q: 'e,
92        E: 'e + Executor<'c, Database = DB>,
93        DB: 'e,
94        O: 'e,
95        A: 'e,
96    {
97        // FIXME: this should have used `executor.fetch()` but that's a breaking change
98        // because this technically allows multiple statements in one query string.
99        #[allow(deprecated)]
100        self.fetch_many(executor)
101            .try_filter_map(|step| async move { Ok(step.right()) })
102            .boxed()
103    }
104
105    /// Execute multiple queries and return the generated results as a stream
106    /// from each query, in a stream.
107    #[deprecated = "Only the SQLite driver supports multiple statements in one prepared statement and that behavior is deprecated. Use `sqlx::raw_sql()` instead. See https://github.com/launchbadge/sqlx/issues/3108 for discussion."]
108    pub fn fetch_many<'e, 'c: 'e, E>(
109        self,
110        executor: E,
111    ) -> BoxStream<'e, Result<Either<DB::QueryResult, O>, Error>>
112    where
113        'q: 'e,
114        E: 'e + Executor<'c, Database = DB>,
115        DB: 'e,
116        O: 'e,
117        A: 'e,
118    {
119        executor
120            .fetch_many(self.inner)
121            .map(|v| match v {
122                Ok(Either::Right(row)) => O::from_row(&row).map(Either::Right),
123                Ok(Either::Left(v)) => Ok(Either::Left(v)),
124                Err(e) => Err(e),
125            })
126            .boxed()
127    }
128
129    /// Execute the query and return all the resulting rows collected into a [`Vec`].
130    ///
131    /// ### Note: beware result set size.
132    /// This will attempt to collect the full result set of the query into memory.
133    ///
134    /// To avoid exhausting available memory, ensure the result set has a known upper bound,
135    /// e.g. using `LIMIT`.
136    #[inline]
137    pub async fn fetch_all<'e, 'c: 'e, E>(self, executor: E) -> Result<Vec<O>, Error>
138    where
139        'q: 'e,
140        E: 'e + Executor<'c, Database = DB>,
141        DB: 'e,
142        O: 'e,
143        A: 'e,
144    {
145        self.fetch(executor).try_collect().await
146    }
147
148    /// Execute the query, returning the first row or [`Error::RowNotFound`] otherwise.
149    ///
150    /// ### Note: for best performance, ensure the query returns at most one row.
151    /// Depending on the driver implementation, if your query can return more than one row,
152    /// it may lead to wasted CPU time and bandwidth on the database server.
153    ///
154    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
155    /// can result in a more optimal query plan.
156    ///
157    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
158    ///
159    /// Otherwise, you might want to add `LIMIT 1` to your query.
160    pub async fn fetch_one<'e, 'c: 'e, E>(self, executor: E) -> Result<O, Error>
161    where
162        'q: 'e,
163        E: 'e + Executor<'c, Database = DB>,
164        DB: 'e,
165        O: 'e,
166        A: 'e,
167    {
168        self.fetch_optional(executor)
169            .await
170            .and_then(|row| row.ok_or(Error::RowNotFound))
171    }
172
173    /// Execute the query, returning the first row or `None` otherwise.
174    ///
175    /// ### Note: for best performance, ensure the query returns at most one row.
176    /// Depending on the driver implementation, if your query can return more than one row,
177    /// it may lead to wasted CPU time and bandwidth on the database server.
178    ///
179    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
180    /// can result in a more optimal query plan.
181    ///
182    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
183    ///
184    /// Otherwise, you might want to add `LIMIT 1` to your query.
185    pub async fn fetch_optional<'e, 'c: 'e, E>(self, executor: E) -> Result<Option<O>, Error>
186    where
187        'q: 'e,
188        E: 'e + Executor<'c, Database = DB>,
189        DB: 'e,
190        O: 'e,
191        A: 'e,
192    {
193        let row = executor.fetch_optional(self.inner).await?;
194        if let Some(row) = row {
195            O::from_row(&row).map(Some)
196        } else {
197            Ok(None)
198        }
199    }
200}
201
202/// Execute a single SQL query as a prepared statement (transparently cached).
203/// Maps rows to Rust types using [`FromRow`].
204///
205/// For details about prepared statements and allowed SQL syntax, see [`query()`][crate::query::query].
206///
207/// ### Example: Map Rows using Tuples
208/// [`FromRow`] is implemented for tuples of up to 16 elements<sup>1</sup>.
209/// Using a tuple of N elements will extract the first N columns from each row using [`Decode`][crate::decode::Decode].
210/// Any extra columns are ignored.
211///
212/// See [`sqlx::types`][crate::types] for the types that can be used.
213///
214/// The `FromRow` implementation will check [`Type::compatible()`] for each column to ensure a compatible type mapping
215/// is used. If an incompatible mapping is detected, an error is returned.
216/// To statically assert compatible types at compile time, see the `query!()` family of macros.
217///
218/// **NOTE**: `SELECT *` is not recommended with this approach because the ordering of returned columns may be different
219/// than expected, especially when using joins.
220///
221/// ```rust,no_run
222/// # async fn example1() -> sqlx::Result<()> {
223/// use sqlx::Connection;
224/// use sqlx::PgConnection;
225///
226/// // This example can be applied to any database as it only uses standard types and syntax.
227/// let mut conn: PgConnection = PgConnection::connect("<Database URL>").await?;
228///
229/// sqlx::raw_sql(
230///     "CREATE TABLE users(id INTEGER PRIMARY KEY, username TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT (now()))"
231/// )
232///     .execute(&mut conn)
233///     .await?;
234///
235/// sqlx::query("INSERT INTO users(id, username) VALUES (1, 'alice'), (2, 'bob');")
236///     .execute(&mut conn)
237///     .await?;
238///
239/// // Get the first row of the result (note the `LIMIT 1` for efficiency)
240/// // This assumes the `time` feature of SQLx is enabled.
241/// let oldest_user: (i32, String, time::OffsetDateTime) = sqlx::query_as(
242///     "SELECT id, username, created_at FROM users ORDER BY created_at LIMIT 1"
243/// )
244///     .fetch_one(&mut conn)
245///     .await?;
246///
247/// assert_eq!(oldest_user.0, 1);
248/// assert_eq!(oldest_user.1, "alice");
249///
250/// // Get at most one row
251/// let maybe_charlie: Option<(i32, String, time::OffsetDateTime)> = sqlx::query_as(
252///     "SELECT id, username, created_at FROM users WHERE username = 'charlie'"
253/// )
254///     .fetch_optional(&mut conn)
255///     .await?;
256///
257/// assert_eq!(maybe_charlie, None);
258///
259/// // Get all rows in result (Beware of the size of the result set! Consider using `LIMIT`)
260/// let users: Vec<(i32, String, time::OffsetDateTime)> = sqlx::query_as(
261///     "SELECT id, username, created_at FROM users ORDER BY id"
262/// )
263///     .fetch_all(&mut conn)
264///     .await?;
265///
266/// println!("{users:?}");
267/// # Ok(())
268/// # }
269/// ```
270///
271/// <sup>1</sup>: It's impossible in Rust to implement a trait for tuples of arbitrary size.
272/// For larger result sets, either use an explicit struct (see below) or use [`query()`][crate::query::query]
273/// instead and extract columns dynamically.
274///
275/// ### Example: Map Rows using `#[derive(FromRow)]`
276/// Using `#[derive(FromRow)]`, we can create a Rust struct to represent our row type
277/// so we can look up fields by name instead of tuple index.
278///
279/// When querying this way, columns will be matched up to the corresponding fields by name, so `SELECT *` is safe to use.
280/// However, you will still want to be aware of duplicate column names in your query when using joins.
281///
282/// The derived `FromRow` implementation will check [`Type::compatible()`] for each column to ensure a compatible type
283/// mapping is used. If an incompatible mapping is detected, an error is returned.
284/// To statically assert compatible types at compile time, see the `query!()` family of macros.
285///
286/// An error will also be returned if an expected column is missing from the result set.
287///
288/// `#[derive(FromRow)]` supports several control attributes which can be used to change how column names and types
289/// are mapped. See [`FromRow`] for details.
290///
291/// Using our previous table definition, we can convert our queries like so:
292/// ```rust,no_run
293/// # async fn example2() -> sqlx::Result<()> {
294/// use sqlx::Connection;
295/// use sqlx::PgConnection;
296///
297/// use time::OffsetDateTime;
298///
299/// #[derive(sqlx::FromRow, Debug, PartialEq, Eq)]
300/// struct User {
301///     id: i64,
302///     username: String,
303///     // Note: the derive won't compile if the `time` feature of SQLx is not enabled.
304///     created_at: OffsetDateTime,
305/// }
306///
307/// let mut conn: PgConnection = PgConnection::connect("<Database URL>").await?;
308///
309/// // Get the first row of the result (note the `LIMIT 1` for efficiency)
310/// let oldest_user: User = sqlx::query_as(
311///     "SELECT id, username, created_at FROM users ORDER BY created_at LIMIT 1"
312/// )
313///     .fetch_one(&mut conn)
314///     .await?;
315///
316/// assert_eq!(oldest_user.id, 1);
317/// assert_eq!(oldest_user.username, "alice");
318///
319/// // Get at most one row
320/// let maybe_charlie: Option<User> = sqlx::query_as(
321///     "SELECT id, username, created_at FROM users WHERE username = 'charlie'"
322/// )
323///     .fetch_optional(&mut conn)
324///     .await?;
325///
326/// assert_eq!(maybe_charlie, None);
327///
328/// // Get all rows in result (Beware of the size of the result set! Consider using `LIMIT`)
329/// let users: Vec<User> = sqlx::query_as(
330///     "SELECT id, username, created_at FROM users ORDER BY id"
331/// )
332///     .fetch_all(&mut conn)
333///     .await?;
334///
335/// assert_eq!(users[1].id, 2);
336/// assert_eq!(users[1].username, "bob");
337/// # Ok(())
338/// # }
339///
340/// ```
341#[inline]
342pub fn query_as<'q, DB, O>(sql: &'q str) -> QueryAs<'q, DB, O, <DB as Database>::Arguments<'q>>
343where
344    DB: Database,
345    O: for<'r> FromRow<'r, DB::Row>,
346{
347    QueryAs {
348        inner: query(sql),
349        output: PhantomData,
350    }
351}
352
353/// Execute a single SQL query, with the given arguments as a prepared statement (transparently cached).
354/// Maps rows to Rust types using [`FromRow`].
355///
356/// For details about prepared statements and allowed SQL syntax, see [`query()`][crate::query::query].
357///
358/// For details about type mapping from [`FromRow`], see [`query_as()`].
359#[inline]
360pub fn query_as_with<'q, DB, O, A>(sql: &'q str, arguments: A) -> QueryAs<'q, DB, O, A>
361where
362    DB: Database,
363    A: IntoArguments<'q, DB>,
364    O: for<'r> FromRow<'r, DB::Row>,
365{
366    query_as_with_result(sql, Ok(arguments))
367}
368
369/// Same as [`query_as_with`] but takes arguments as a Result
370#[inline]
371pub fn query_as_with_result<'q, DB, O, A>(
372    sql: &'q str,
373    arguments: Result<A, BoxDynError>,
374) -> QueryAs<'q, DB, O, A>
375where
376    DB: Database,
377    A: IntoArguments<'q, DB>,
378    O: for<'r> FromRow<'r, DB::Row>,
379{
380    QueryAs {
381        inner: query_with_result(sql, arguments),
382        output: PhantomData,
383    }
384}
385
386// Make a SQL query from a statement, that is mapped to a concrete type.
387pub fn query_statement_as<'q, DB, O>(
388    statement: &'q DB::Statement<'q>,
389) -> QueryAs<'q, DB, O, <DB as Database>::Arguments<'_>>
390where
391    DB: Database,
392    O: for<'r> FromRow<'r, DB::Row>,
393{
394    QueryAs {
395        inner: query_statement(statement),
396        output: PhantomData,
397    }
398}
399
400// Make a SQL query from a statement, with the given arguments, that is mapped to a concrete type.
401pub fn query_statement_as_with<'q, DB, O, A>(
402    statement: &'q DB::Statement<'q>,
403    arguments: A,
404) -> QueryAs<'q, DB, O, A>
405where
406    DB: Database,
407    A: IntoArguments<'q, DB>,
408    O: for<'r> FromRow<'r, DB::Row>,
409{
410    QueryAs {
411        inner: query_statement_with(statement, arguments),
412        output: PhantomData,
413    }
414}
sqlx_core/query_as.rs

sqlx_core/
query_as.rs
