diesel/query_builder/
sql_query.rs

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

diesel/query_builder/
sql_query.rs
