sqlx_core/
query_scalar.rs

1use either::Either;
2use futures_core::stream::BoxStream;
3use futures_util::{StreamExt, TryFutureExt, TryStreamExt};
4
5use crate::arguments::IntoArguments;
6use crate::database::{Database, HasStatementCache};
7use crate::encode::Encode;
8use crate::error::{BoxDynError, Error};
9use crate::executor::{Execute, Executor};
10use crate::from_row::FromRow;
11use crate::query_as::{
12    query_as, query_as_with_result, query_statement_as, query_statement_as_with, QueryAs,
13};
14use crate::types::Type;
15
16/// A single SQL query as a prepared statement which extracts only the first column of each row.
17/// Returned by [`query_scalar()`].
18#[must_use = "query must be executed to affect database"]
19pub struct QueryScalar<'q, DB: Database, O, A> {
20    pub(crate) inner: QueryAs<'q, DB, (O,), A>,
21}
22
23impl<'q, DB: Database, O: Send, A: Send> Execute<'q, DB> for QueryScalar<'q, DB, O, A>
24where
25    A: 'q + IntoArguments<'q, DB>,
26{
27    #[inline]
28    fn sql(&self) -> &'q str {
29        self.inner.sql()
30    }
31
32    fn statement(&self) -> Option<&DB::Statement<'q>> {
33        self.inner.statement()
34    }
35
36    #[inline]
37    fn take_arguments(&mut self) -> Result<Option<<DB as Database>::Arguments<'q>>, BoxDynError> {
38        self.inner.take_arguments()
39    }
40
41    #[inline]
42    fn persistent(&self) -> bool {
43        Execute::persistent(&self.inner)
44    }
45}
46
47impl<'q, DB: Database, O> QueryScalar<'q, DB, O, <DB as Database>::Arguments<'q>> {
48    /// Bind a value for use with this SQL query.
49    ///
50    /// See [`Query::bind`](crate::query::Query::bind).
51    pub fn bind<T: 'q + Encode<'q, DB> + Type<DB>>(mut self, value: T) -> Self {
52        self.inner = self.inner.bind(value);
53        self
54    }
55}
56
57impl<'q, DB, O, A> QueryScalar<'q, DB, O, A>
58where
59    DB: Database + HasStatementCache,
60{
61    /// If `true`, the statement will get prepared once and cached to the
62    /// connection's statement cache.
63    ///
64    /// If queried once with the flag set to `true`, all subsequent queries
65    /// matching the one with the flag will use the cached statement until the
66    /// cache is cleared.
67    ///
68    /// If `false`, the prepared statement will be closed after execution.
69    ///
70    /// Default: `true`.
71    pub fn persistent(mut self, value: bool) -> Self {
72        self.inner = self.inner.persistent(value);
73        self
74    }
75}
76
77// FIXME: This is very close, nearly 1:1 with `Map`
78// noinspection DuplicatedCode
79impl<'q, DB, O, A> QueryScalar<'q, DB, O, A>
80where
81    DB: Database,
82    O: Send + Unpin,
83    A: 'q + IntoArguments<'q, DB>,
84    (O,): Send + Unpin + for<'r> FromRow<'r, DB::Row>,
85{
86    /// Execute the query and return the generated results as a stream.
87    #[inline]
88    pub fn fetch<'e, 'c: 'e, E>(self, executor: E) -> BoxStream<'e, Result<O, Error>>
89    where
90        'q: 'e,
91        E: 'e + Executor<'c, Database = DB>,
92        DB: 'e,
93        A: 'e,
94        O: 'e,
95    {
96        self.inner.fetch(executor).map_ok(|it| it.0).boxed()
97    }
98
99    /// Execute multiple queries and return the generated results as a stream
100    /// from each query, in a stream.
101    #[inline]
102    #[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."]
103    pub fn fetch_many<'e, 'c: 'e, E>(
104        self,
105        executor: E,
106    ) -> BoxStream<'e, Result<Either<DB::QueryResult, O>, Error>>
107    where
108        'q: 'e,
109        E: 'e + Executor<'c, Database = DB>,
110        DB: 'e,
111        A: 'e,
112        O: 'e,
113    {
114        #[allow(deprecated)]
115        self.inner
116            .fetch_many(executor)
117            .map_ok(|v| v.map_right(|it| it.0))
118            .boxed()
119    }
120
121    /// Execute the query and return all the resulting rows collected into a [`Vec`].
122    ///
123    /// ### Note: beware result set size.
124    /// This will attempt to collect the full result set of the query into memory.
125    ///
126    /// To avoid exhausting available memory, ensure the result set has a known upper bound,
127    /// e.g. using `LIMIT`.
128    #[inline]
129    pub async fn fetch_all<'e, 'c: 'e, E>(self, executor: E) -> Result<Vec<O>, Error>
130    where
131        'q: 'e,
132        E: 'e + Executor<'c, Database = DB>,
133        DB: 'e,
134        (O,): 'e,
135        A: 'e,
136    {
137        self.inner
138            .fetch(executor)
139            .map_ok(|it| it.0)
140            .try_collect()
141            .await
142    }
143
144    /// Execute the query, returning the first row or [`Error::RowNotFound`] otherwise.
145    ///
146    /// ### Note: for best performance, ensure the query returns at most one row.
147    /// Depending on the driver implementation, if your query can return more than one row,
148    /// it may lead to wasted CPU time and bandwidth on the database server.
149    ///
150    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
151    /// can result in a more optimal query plan.
152    ///
153    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
154    ///
155    /// Otherwise, you might want to add `LIMIT 1` to your query.
156    #[inline]
157    pub async fn fetch_one<'e, 'c: 'e, E>(self, executor: E) -> Result<O, Error>
158    where
159        'q: 'e,
160        E: 'e + Executor<'c, Database = DB>,
161        DB: 'e,
162        O: 'e,
163        A: 'e,
164    {
165        self.inner.fetch_one(executor).map_ok(|it| it.0).await
166    }
167
168    /// Execute the query, returning the first row or `None` otherwise.
169    ///
170    /// ### Note: for best performance, ensure the query returns at most one row.
171    /// Depending on the driver implementation, if your query can return more than one row,
172    /// it may lead to wasted CPU time and bandwidth on the database server.
173    ///
174    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
175    /// can result in a more optimal query plan.
176    ///
177    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
178    ///
179    /// Otherwise, you might want to add `LIMIT 1` to your query.
180    #[inline]
181    pub async fn fetch_optional<'e, 'c: 'e, E>(self, executor: E) -> Result<Option<O>, Error>
182    where
183        'q: 'e,
184        E: 'e + Executor<'c, Database = DB>,
185        DB: 'e,
186        O: 'e,
187        A: 'e,
188    {
189        Ok(self.inner.fetch_optional(executor).await?.map(|it| it.0))
190    }
191}
192
193/// Execute a single SQL query as a prepared statement (transparently cached) and extract the first
194/// column of each row.
195///
196/// Extracts the first column of each row. Additional columns are ignored.
197/// Any type that implements `Type<DB> + Decode<DB>` may be used.
198///
199/// For details about prepared statements and allowed SQL syntax, see [`query()`][crate::query::query].
200///
201/// ### Example: Simple Lookup
202/// If you just want to look up a single value with little fanfare, this API is perfect for you:
203///
204/// ```rust,no_run
205/// # async fn example_lookup() -> Result<(), Box<dyn std::error::Error>> {
206/// # let mut conn: sqlx::PgConnection = unimplemented!();
207/// use uuid::Uuid;
208///
209/// // MySQL and MariaDB: use `?`
210/// let user_id: Option<Uuid> = sqlx::query_scalar("SELECT user_id FROM users WHERE username = $1")
211///     .bind("alice")
212///     // Use `&mut` where `conn` is a connection or a transaction, or use `&` for a `Pool`.
213///     .fetch_optional(&mut conn)
214///     .await?;
215///
216/// let user_id = user_id.ok_or("unknown user")?;
217///
218/// # Ok(())
219/// # }
220/// ```
221///
222/// Note how we're using `.fetch_optional()` because the lookup may return no results,
223/// in which case we need to be able to handle an empty result set.
224/// Any rows after the first are ignored.
225///
226/// ### Example: `COUNT`
227/// This API is the easiest way to invoke an aggregate query like `SELECT COUNT(*)`, because you
228/// can conveniently extract the result:
229///
230/// ```rust,no_run
231/// # async fn example_count() -> sqlx::Result<()> {
232/// # let mut conn: sqlx::PgConnection = unimplemented!();
233/// // Note that `usize` is not used here because unsigned integers are generally not supported,
234/// // and `usize` doesn't even make sense as a mapping because the database server may have
235/// // a completely different architecture.
236/// //
237/// // `i64` is generally a safe choice for `COUNT`.
238/// let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM users WHERE accepted_tos IS TRUE")
239///     // Use `&mut` where `conn` is a connection or a transaction, or use `&` for a `Pool`.
240///     .fetch_one(&mut conn)
241///     .await?;
242///
243/// // The above is functionally equivalent to the following:
244/// // Note the trailing comma, required for the compiler to recognize a 1-element tuple.
245/// let (count,): (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users WHERE accepted_tos IS TRUE")
246///     .fetch_one(&mut conn)
247///     .await?;
248/// # Ok(())
249/// # }
250/// ```
251///
252/// ### Example: `EXISTS`
253/// To test if a row exists or not, use `SELECT EXISTS(<query>)`:
254///
255/// ```rust,no_run
256/// # async fn example_exists() -> sqlx::Result<()> {
257/// # let mut conn: sqlx::PgConnection = unimplemented!();
258/// // MySQL and MariaDB: use `?`
259/// let username_taken: bool = sqlx::query_scalar(
260///     "SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)"
261/// )
262///     .bind("alice")
263///     // Use `&mut` where `conn` is a connection or a transaction, or use `&` for a `Pool`.
264///     .fetch_one(&mut conn)
265///     .await?;
266/// # Ok(())
267/// # }
268/// ```
269///
270/// ### Example: Other Aggregates
271/// Be aware that most other aggregate functions return `NULL` if the query yields an empty set:
272///
273/// ```rust,no_run
274/// # async fn example_aggregate() -> sqlx::Result<()> {
275/// # let mut conn: sqlx::PgConnection = unimplemented!();
276/// let max_upvotes: Option<i64> = sqlx::query_scalar("SELECT MAX(upvotes) FROM posts")
277///     // Use `&mut` where `conn` is a connection or a transaction, or use `&` for a `Pool`.
278///     .fetch_one(&mut conn)
279///     .await?;
280/// # Ok(())
281/// # }
282/// ```
283///
284/// Note how we're using `Option<i64>` with `.fetch_one()`, because we're always expecting one row
285/// but the column value may be `NULL`. If no rows are returned, this will error.
286///
287/// This is in contrast to using `.fetch_optional()` with `Option<i64>`, which implies that
288/// we're expecting _either_ a row with a `i64` (`BIGINT`), _or_ no rows at all.
289///
290/// Either way, any rows after the first are ignored.
291///
292/// ### Example: `Vec` of Scalars
293/// If you want to collect a single column from a query into a vector,
294/// try `.fetch_all()`:
295///
296/// ```rust,no_run
297/// # async fn example_vec() -> sqlx::Result<()> {
298/// # let mut conn: sqlx::PgConnection = unimplemented!();
299/// let top_users: Vec<String> = sqlx::query_scalar(
300///     // Note the `LIMIT` to ensure that this doesn't return *all* users:
301///     "SELECT username
302///      FROM (
303///          SELECT SUM(upvotes) total, user_id
304///          FROM posts
305///          GROUP BY user_id
306///      ) top_users
307///      INNER JOIN users USING (user_id)
308///      ORDER BY total DESC
309///      LIMIT 10"
310/// )
311///     // Use `&mut` where `conn` is a connection or a transaction, or use `&` for a `Pool`.
312///     .fetch_all(&mut conn)
313///     .await?;
314///
315/// // `top_users` could be empty, too.
316/// assert!(top_users.len() <= 10);
317/// # Ok(())
318/// # }
319/// ```
320#[inline]
321pub fn query_scalar<'q, DB, O>(
322    sql: &'q str,
323) -> QueryScalar<'q, DB, O, <DB as Database>::Arguments<'q>>
324where
325    DB: Database,
326    (O,): for<'r> FromRow<'r, DB::Row>,
327{
328    QueryScalar {
329        inner: query_as(sql),
330    }
331}
332
333/// Execute a SQL query as a prepared statement (transparently cached), with the given arguments,
334/// and extract the first column of each row.
335///
336/// See [`query_scalar()`] for details.
337///
338/// For details about prepared statements and allowed SQL syntax, see [`query()`][crate::query::query].
339#[inline]
340pub fn query_scalar_with<'q, DB, O, A>(sql: &'q str, arguments: A) -> QueryScalar<'q, DB, O, A>
341where
342    DB: Database,
343    A: IntoArguments<'q, DB>,
344    (O,): for<'r> FromRow<'r, DB::Row>,
345{
346    query_scalar_with_result(sql, Ok(arguments))
347}
348
349/// Same as [`query_scalar_with`] but takes arguments as Result
350#[inline]
351pub fn query_scalar_with_result<'q, DB, O, A>(
352    sql: &'q str,
353    arguments: Result<A, BoxDynError>,
354) -> QueryScalar<'q, DB, O, A>
355where
356    DB: Database,
357    A: IntoArguments<'q, DB>,
358    (O,): for<'r> FromRow<'r, DB::Row>,
359{
360    QueryScalar {
361        inner: query_as_with_result(sql, arguments),
362    }
363}
364
365// Make a SQL query from a statement, that is mapped to a concrete value.
366pub fn query_statement_scalar<'q, DB, O>(
367    statement: &'q DB::Statement<'q>,
368) -> QueryScalar<'q, DB, O, <DB as Database>::Arguments<'_>>
369where
370    DB: Database,
371    (O,): for<'r> FromRow<'r, DB::Row>,
372{
373    QueryScalar {
374        inner: query_statement_as(statement),
375    }
376}
377
378// Make a SQL query from a statement, with the given arguments, that is mapped to a concrete value.
379pub fn query_statement_scalar_with<'q, DB, O, A>(
380    statement: &'q DB::Statement<'q>,
381    arguments: A,
382) -> QueryScalar<'q, DB, O, A>
383where
384    DB: Database,
385    A: IntoArguments<'q, DB>,
386    (O,): for<'r> FromRow<'r, DB::Row>,
387{
388    QueryScalar {
389        inner: query_statement_as_with(statement, arguments),
390    }
391}
sqlx_core/query_scalar.rs

sqlx_core/
query_scalar.rs
