diesel/query_builder/
sql_query.rs

Help
1use std::marker::PhantomData;
2
3use super::Query;
4use crate::backend::{Backend, DieselReserveSpecialization};
5use crate::connection::Connection;
6use crate::query_builder::{AstPass, QueryFragment, QueryId};
7use crate::query_dsl::RunQueryDsl;
8use crate::result::QueryResult;
9use crate::serialize::ToSql;
10use crate::sql_types::{HasSqlType, Untyped};
11
12#[derive(Debug, Clone)]
13#[must_use = "Queries are only executed when calling `load`, `get_result` or similar."]
14/// The return value of `sql_query`.
15///
16/// Unlike most queries in Diesel, `SqlQuery` loads its data by column name,
17/// rather than by index. This means that you cannot deserialize this query into
18/// a tuple, and any structs used must implement `QueryableByName`.
19///
20/// See [`sql_query`](crate::sql_query()) for examples.
21pub struct SqlQuery<Inner = self::private::Empty> {
22    inner: Inner,
23    query: String,
24}
25
26impl<Inner> SqlQuery<Inner> {
27    pub(crate) fn new(inner: Inner, query: String) -> Self {
28        SqlQuery { inner, query }
29    }
30
31    /// Bind a value for use with this SQL query. The given query should have
32    /// placeholders that vary based on the database type,
33    /// like [SQLite Parameter](https://sqlite.org/lang_expr.html#varparam) syntax,
34    /// [PostgreSQL PREPARE syntax](https://www.postgresql.org/docs/current/sql-prepare.html),
35    /// or [MySQL bind syntax](https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-bind-param.html).
36    ///
37    /// For binding a variable number of values in a loop, use `into_boxed` first.
38    ///
39    /// # Safety
40    ///
41    /// This function should be used with care, as Diesel cannot validate that
42    /// the value is of the right type nor can it validate that you have passed
43    /// the correct number of parameters.
44    ///
45    /// # Example
46    ///
47    /// ```
48    /// # include!("../doctest_setup.rs");
49    /// #
50    /// # use schema::users;
51    /// #
52    /// # #[derive(QueryableByName, Debug, PartialEq)]
53    /// # struct User {
54    /// #     id: i32,
55    /// #     name: String,
56    /// # }
57    /// #
58    /// # fn main() {
59    /// #     use diesel::sql_query;
60    /// #     use diesel::sql_types::{Integer, Text};
61    /// #
62    /// #     let connection = &mut establish_connection();
63    /// #     diesel::insert_into(users::table)
64    /// #         .values(users::name.eq("Jim"))
65    /// #         .execute(connection).unwrap();
66    /// # #[cfg(feature = "postgres")]
67    /// # let users = sql_query("SELECT * FROM users WHERE id > $1 AND name != $2");
68    /// # #[cfg(not(feature = "postgres"))]
69    /// // sqlite/mysql bind syntax
70    /// let users = sql_query("SELECT * FROM users WHERE id > ? AND name <> ?")
71    /// # ;
72    /// # let users = users
73    ///     .bind::<Integer, _>(1)
74    ///     .bind::<Text, _>("Tess")
75    ///     .get_results(connection);
76    /// let expected_users = vec![
77    ///     User { id: 3, name: "Jim".into() },
78    /// ];
79    /// assert_eq!(Ok(expected_users), users);
80    /// # }
81    /// ```
82    pub fn bind<ST, Value>(self, value: Value) -> UncheckedBind<Self, Value, ST> {
83        UncheckedBind::new(self, value)
84    }
85
86    /// Internally boxes the query, which allows to  calls `bind` and `sql` so that they don't
87    /// change the type nor the instance. This allows to call `bind` or `sql`
88    /// in a loop, e.g.:
89    ///
90    /// ```
91    /// # include!("../doctest_setup.rs");
92    /// #
93    /// # use schema::users;
94    /// #
95    /// # #[derive(QueryableByName, Debug, PartialEq)]
96    /// # struct User {
97    /// #     id: i32,
98    /// #     name: String,
99    /// # }
100    /// #
101    /// # fn main() {
102    /// #     use diesel::sql_query;
103    /// #     use diesel::sql_types::{Integer};
104    /// #
105    /// #     let connection = &mut establish_connection();
106    /// #     diesel::insert_into(users::table)
107    /// #         .values(users::name.eq("Jim"))
108    /// #         .execute(connection).unwrap();
109    /// let mut q = diesel::sql_query("SELECT * FROM users WHERE id IN(").into_boxed();
110    /// for (idx, user_id) in [3, 4, 5].into_iter().enumerate() {
111    ///     if idx != 0 {
112    ///         q = q.sql(", ");
113    ///     }
114    /// # #[cfg(feature = "postgres")]
115    /// # {
116    ///    q = q
117    ///        // postgresql bind syntax
118    ///        .sql(format!("${}", idx + 1))
119    ///        .bind::<Integer, _>(user_id);
120    /// # }
121    /// # #[cfg(not(feature = "postgres"))]
122    /// # {
123    /// #   q = q
124    /// #       .sql(format!("?"))
125    /// #       .bind::<Integer, _>(user_id);
126    /// # }
127    /// }
128    /// let users = q.sql(");").get_results(connection);
129    /// let expected_users = vec![
130    ///     User { id: 3, name: "Jim".into() },
131    /// ];
132    /// assert_eq!(Ok(expected_users), users);
133    /// # }
134    /// ```
135    ///
136    /// This allows doing things you otherwise couldn't do, e.g. `bind`ing in a
137    /// loop.
138    pub fn into_boxed<'f, DB: Backend>(self) -> BoxedSqlQuery<'f, DB, Self> {
139        BoxedSqlQuery::new(self)
140    }
141
142    /// Appends a piece of SQL code at the end.
143    pub fn sql<T: AsRef<str>>(mut self, sql: T) -> Self {
144        self.query += sql.as_ref();
145        self
146    }
147}
148
149impl SqlQuery {
150    pub(crate) fn from_sql(query: String) -> SqlQuery {
151        Self {
152            inner: self::private::Empty,
153            query,
154        }
155    }
156}
157
158impl<DB, Inner> QueryFragment<DB> for SqlQuery<Inner>
159where
160    DB: Backend + DieselReserveSpecialization,
161    Inner: QueryFragment<DB>,
162{
163    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
164        out.unsafe_to_cache_prepared();
165        self.inner.walk_ast(out.reborrow())?;
166        out.push_sql(&self.query);
167        Ok(())
168    }
169}
170
171impl<Inner> QueryId for SqlQuery<Inner> {
172    type QueryId = ();
173
174    const HAS_STATIC_QUERY_ID: bool = false;
175}
176
177impl<Inner> Query for SqlQuery<Inner> {
178    type SqlType = Untyped;
179}
180
181impl<Inner, Conn> RunQueryDsl<Conn> for SqlQuery<Inner> {}
182
183#[derive(Debug, Clone, Copy)]
184#[must_use = "Queries are only executed when calling `load`, `get_result` or similar."]
185/// Returned by the [`SqlQuery::bind()`] method when binding a value to a fragment of SQL.
186///
187pub struct UncheckedBind<Query, Value, ST> {
188    query: Query,
189    value: Value,
190    _marker: PhantomData<ST>,
191}
192
193impl<Query, Value, ST> UncheckedBind<Query, Value, ST> {
194    pub fn new(query: Query, value: Value) -> Self {
195        UncheckedBind {
196            query,
197            value,
198            _marker: PhantomData,
199        }
200    }
201
202    pub fn bind<ST2, Value2>(self, value: Value2) -> UncheckedBind<Self, Value2, ST2> {
203        UncheckedBind::new(self, value)
204    }
205
206    pub fn into_boxed<'f, DB: Backend>(self) -> BoxedSqlQuery<'f, DB, Self> {
207        BoxedSqlQuery::new(self)
208    }
209
210    /// Construct a full SQL query using raw SQL.
211    ///
212    /// This function exists for cases where a query needs to be written that is not
213    /// supported by the query builder. Unlike most queries in Diesel, `sql_query`
214    /// will deserialize its data by name, not by index. That means that you cannot
215    /// deserialize into a tuple, and structs which you deserialize from this
216    /// function will need to have `#[derive(QueryableByName)]`.
217    ///
218    /// This function is intended for use when you want to write the entire query
219    /// using raw SQL. If you only need a small bit of raw SQL in your query, use
220    /// [`sql`](dsl::sql()) instead.
221    ///
222    /// Query parameters can be bound into the raw query using [`SqlQuery::bind()`].
223    ///
224    /// # Safety
225    ///
226    /// The implementation of `QueryableByName` will assume that columns with a
227    /// given name will have a certain type. The compiler will be unable to verify
228    /// that the given type is correct. If your query returns a column of an
229    /// unexpected type, the result may have the wrong value, or return an error.
230    ///
231    /// # Examples
232    ///
233    /// ```rust
234    /// # include!("../doctest_setup.rs");
235    /// #
236    /// # use schema::users;
237    /// #
238    /// # #[derive(QueryableByName, Debug, PartialEq)]
239    /// # struct User {
240    /// #     id: i32,
241    /// #     name: String,
242    /// # }
243    /// #
244    /// # fn main() {
245    /// #     use diesel::sql_query;
246    /// #     use diesel::sql_types::{Integer, Text};
247    /// #
248    /// #     let connection = &mut establish_connection();
249    /// #     diesel::insert_into(users::table)
250    /// #         .values(users::name.eq("Jim"))
251    /// #         .execute(connection).unwrap();
252    /// # #[cfg(feature = "postgres")]
253    /// # let users = sql_query("SELECT * FROM users WHERE id > $1 AND name != $2");
254    /// # #[cfg(not(feature = "postgres"))]
255    /// // sqlite/mysql bind syntax
256    /// let users = sql_query("SELECT * FROM users WHERE id > ? AND name <> ?")
257    /// # ;
258    /// # let users = users
259    ///     .bind::<Integer, _>(1)
260    ///     .bind::<Text, _>("Tess")
261    ///     .get_results(connection);
262    /// let expected_users = vec![
263    ///     User { id: 3, name: "Jim".into() },
264    /// ];
265    /// assert_eq!(Ok(expected_users), users);
266    /// # }
267    /// ```
268    /// [`SqlQuery::bind()`]: query_builder::SqlQuery::bind()
269    pub fn sql<T: Into<String>>(self, sql: T) -> SqlQuery<Self> {
270        SqlQuery::new(self, sql.into())
271    }
272}
273
274impl<Query, Value, ST> QueryId for UncheckedBind<Query, Value, ST>
275where
276    Query: QueryId,
277    ST: QueryId,
278{
279    type QueryId = UncheckedBind<Query::QueryId, (), ST::QueryId>;
280
281    const HAS_STATIC_QUERY_ID: bool = Query::HAS_STATIC_QUERY_ID && ST::HAS_STATIC_QUERY_ID;
282}
283
284impl<Query, Value, ST, DB> QueryFragment<DB> for UncheckedBind<Query, Value, ST>
285where
286    DB: Backend + HasSqlType<ST> + DieselReserveSpecialization,
287    Query: QueryFragment<DB>,
288    Value: ToSql<ST, DB>,
289{
290    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
291        self.query.walk_ast(out.reborrow())?;
292        out.push_bind_param_value_only(&self.value)?;
293        Ok(())
294    }
295}
296
297impl<Q, Value, ST> Query for UncheckedBind<Q, Value, ST> {
298    type SqlType = Untyped;
299}
300
301impl<Conn, Query, Value, ST> RunQueryDsl<Conn> for UncheckedBind<Query, Value, ST> {}
302
303#[must_use = "Queries are only executed when calling `load`, `get_result`, or similar."]
304/// See [`SqlQuery::into_boxed`].
305///
306/// [`SqlQuery::into_boxed`]: SqlQuery::into_boxed()
307#[allow(missing_debug_implementations)]
308pub struct BoxedSqlQuery<'f, DB: Backend, Query> {
309    query: Query,
310    sql: String,
311    binds: Vec<Box<dyn QueryFragment<DB> + Send + 'f>>,
312}
313
314struct RawBind<ST, U> {
315    value: U,
316    p: PhantomData<ST>,
317}
318
319impl<ST, U, DB> QueryFragment<DB> for RawBind<ST, U>
320where
321    DB: Backend + HasSqlType<ST>,
322    U: ToSql<ST, DB>,
323{
324    fn walk_ast<'b>(&'b self, mut pass: AstPass<'_, 'b, DB>) -> QueryResult<()> {
325        pass.push_bind_param_value_only(&self.value)
326    }
327}
328
329impl<'f, DB: Backend, Query> BoxedSqlQuery<'f, DB, Query> {
330    pub(crate) fn new(query: Query) -> Self {
331        BoxedSqlQuery {
332            query,
333            sql: "".to_string(),
334            binds: vec![],
335        }
336    }
337
338    /// See [`SqlQuery::bind`].
339    ///
340    /// [`SqlQuery::bind`]: SqlQuery::bind()
341    pub fn bind<BindSt, Value>(mut self, b: Value) -> Self
342    where
343        DB: HasSqlType<BindSt>,
344        Value: ToSql<BindSt, DB> + Send + 'f,
345        BindSt: Send + 'f,
346    {
347        self.binds.push(Box::new(RawBind {
348            value: b,
349            p: PhantomData,
350        }) as Box<_>);
351        self
352    }
353
354    /// See [`SqlQuery::sql`].
355    ///
356    /// [`SqlQuery::sql`]: SqlQuery::sql()
357    pub fn sql<T: AsRef<str>>(mut self, sql: T) -> Self {
358        self.sql += sql.as_ref();
359        self
360    }
361}
362
363impl<DB, Query> QueryFragment<DB> for BoxedSqlQuery<'_, DB, Query>
364where
365    DB: Backend + DieselReserveSpecialization,
366    Query: QueryFragment<DB>,
367{
368    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
369        out.unsafe_to_cache_prepared();
370        self.query.walk_ast(out.reborrow())?;
371        out.push_sql(&self.sql);
372
373        for b in &self.binds {
374            b.walk_ast(out.reborrow())?;
375        }
376        Ok(())
377    }
378}
379
380impl<DB: Backend, Query> QueryId for BoxedSqlQuery<'_, DB, Query> {
381    type QueryId = ();
382
383    const HAS_STATIC_QUERY_ID: bool = false;
384}
385
386impl<DB, Q> Query for BoxedSqlQuery<'_, DB, Q>
387where
388    DB: Backend,
389{
390    type SqlType = Untyped;
391}
392
393impl<Conn: Connection, Query> RunQueryDsl<Conn> for BoxedSqlQuery<'_, Conn::Backend, Query> {}
394
395mod private {
396    use crate::backend::{Backend, DieselReserveSpecialization};
397    use crate::query_builder::{QueryFragment, QueryId};
398
399    #[derive(Debug, Clone, Copy, QueryId)]
400    pub struct Empty;
401
402    impl<DB> QueryFragment<DB> for Empty
403    where
404        DB: Backend + DieselReserveSpecialization,
405    {
406        fn walk_ast<'b>(
407            &'b self,
408            _pass: crate::query_builder::AstPass<'_, 'b, DB>,
409        ) -> crate::QueryResult<()> {
410            Ok(())
411        }
412    }
413}
414
415#[cfg(test)]
416mod tests {
417    fn assert_send<S: Send>(_: S) {}
418
419    #[diesel_test_helper::test]
420    fn check_boxed_sql_query_is_send() {
421        let query = crate::sql_query("SELECT 1")
422            .into_boxed::<<crate::test_helpers::TestConnection as crate::Connection>::Backend>(
423        );
424
425        assert_send(query);
426    }
427}
diesel/query_builder/sql_query.rs

diesel/query_builder/
sql_query.rs
