sqlx_core/
query_builder.rs

1//! Runtime query-builder API.
2
3use std::fmt::Display;
4use std::fmt::Write;
5use std::marker::PhantomData;
6
7use crate::arguments::{Arguments, IntoArguments};
8use crate::database::Database;
9use crate::encode::Encode;
10use crate::from_row::FromRow;
11use crate::query::Query;
12use crate::query_as::QueryAs;
13use crate::query_scalar::QueryScalar;
14use crate::types::Type;
15use crate::Either;
16
17/// A builder type for constructing queries at runtime.
18///
19/// See [`.push_values()`][Self::push_values] for an example of building a bulk `INSERT` statement.
20/// Note, however, that with Postgres you can get much better performance by using arrays
21/// and `UNNEST()`. [See our FAQ] for details.
22///
23/// [See our FAQ]: https://github.com/launchbadge/sqlx/blob/master/FAQ.md#how-can-i-bind-an-array-to-a-values-clause-how-can-i-do-bulk-inserts
24pub struct QueryBuilder<'args, DB>
25where
26    DB: Database,
27{
28    query: String,
29    init_len: usize,
30    arguments: Option<<DB as Database>::Arguments<'args>>,
31}
32
33impl<'args, DB: Database> Default for QueryBuilder<'args, DB> {
34    fn default() -> Self {
35        QueryBuilder {
36            init_len: 0,
37            query: String::default(),
38            arguments: Some(Default::default()),
39        }
40    }
41}
42
43impl<'args, DB: Database> QueryBuilder<'args, DB>
44where
45    DB: Database,
46{
47    // `init` is provided because a query will almost always start with a constant fragment
48    // such as `INSERT INTO ...` or `SELECT ...`, etc.
49    /// Start building a query with an initial SQL fragment, which may be an empty string.
50    pub fn new(init: impl Into<String>) -> Self
51    where
52        <DB as Database>::Arguments<'args>: Default,
53    {
54        let init = init.into();
55
56        QueryBuilder {
57            init_len: init.len(),
58            query: init,
59            arguments: Some(Default::default()),
60        }
61    }
62
63    /// Construct a `QueryBuilder` with existing SQL and arguments.
64    ///
65    /// ### Note
66    /// This does *not* check if `arguments` is valid for the given SQL.
67    pub fn with_arguments<A>(init: impl Into<String>, arguments: A) -> Self
68    where
69        DB: Database,
70        A: IntoArguments<'args, DB>,
71    {
72        let init = init.into();
73
74        QueryBuilder {
75            init_len: init.len(),
76            query: init,
77            arguments: Some(arguments.into_arguments()),
78        }
79    }
80
81    #[inline]
82    fn sanity_check(&self) {
83        assert!(
84            self.arguments.is_some(),
85            "QueryBuilder must be reset before reuse after `.build()`"
86        );
87    }
88
89    /// Append a SQL fragment to the query.
90    ///
91    /// May be a string or anything that implements `Display`.
92    /// You can also use `format_args!()` here to push a formatted string without an intermediate
93    /// allocation.
94    ///
95    /// ### Warning: Beware SQL Injection Vulnerabilities and Untrusted Input!
96    /// You should *not* use this to insert input directly into the query from an untrusted user as
97    /// this can be used by an attacker to extract sensitive data or take over your database.
98    ///
99    /// Security breaches due to SQL injection can cost your organization a lot of money from
100    /// damage control and lost clients, betray the trust of your users in your system, and are just
101    /// plain embarrassing. If you are unfamiliar with the threat that SQL injection imposes, you
102    /// should take some time to learn more about it before proceeding:
103    ///
104    /// * [SQL Injection on OWASP.org](https://owasp.org/www-community/attacks/SQL_Injection)
105    /// * [SQL Injection on Wikipedia](https://en.wikipedia.org/wiki/SQL_injection)
106    ///     * See "Examples" for notable instances of security breaches due to SQL injection.
107    ///
108    /// This method does *not* perform sanitization. Instead, you should use
109    /// [`.push_bind()`][Self::push_bind] which inserts a placeholder into the query and then
110    /// sends the possibly untrustworthy value separately (called a "bind argument") so that it
111    /// cannot be misinterpreted by the database server.
112    ///
113    /// Note that you should still at least have some sort of sanity checks on the values you're
114    /// sending as that's just good practice and prevent other types of attacks against your system,
115    /// e.g. check that strings aren't too long, numbers are within expected ranges, etc.
116    pub fn push(&mut self, sql: impl Display) -> &mut Self {
117        self.sanity_check();
118
119        write!(self.query, "{sql}").expect("error formatting `sql`");
120
121        self
122    }
123
124    /// Push a bind argument placeholder (`?` or `$N` for Postgres) and bind a value to it.
125    ///
126    /// ### Note: Database-specific Limits
127    /// Note that every database has a practical limit on the number of bind parameters
128    /// you can add to a single query. This varies by database.
129    ///
130    /// While you should consult the manual of your specific database version and/or current
131    /// configuration for the exact value as it may be different than listed here,
132    /// the defaults for supported databases as of writing are as follows:
133    ///
134    /// * Postgres and MySQL: 65535
135    ///     * You may find sources that state that Postgres has a limit of 32767,
136    ///       but that is a misinterpretation of the specification by the JDBC driver implementation
137    ///       as discussed in [this Github issue][postgres-limit-issue]. Postgres itself
138    ///       asserts that the number of parameters is in the range `[0, 65535)`.
139    /// * SQLite: 32766 (configurable by [`SQLITE_LIMIT_VARIABLE_NUMBER`])
140    ///     * SQLite prior to 3.32.0: 999
141    /// * MSSQL: 2100
142    ///
143    /// Exceeding these limits may panic (as a sanity check) or trigger a database error at runtime
144    /// depending on the implementation.
145    ///
146    /// [`SQLITE_LIMIT_VARIABLE_NUMBER`]: https://www.sqlite.org/limits.html#max_variable_number
147    /// [postgres-limit-issue]: https://github.com/launchbadge/sqlx/issues/671#issuecomment-687043510
148    pub fn push_bind<T>(&mut self, value: T) -> &mut Self
149    where
150        T: 'args + Encode<'args, DB> + Type<DB>,
151    {
152        self.sanity_check();
153
154        let arguments = self
155            .arguments
156            .as_mut()
157            .expect("BUG: Arguments taken already");
158        arguments.add(value).expect("Failed to add argument");
159
160        arguments
161            .format_placeholder(&mut self.query)
162            .expect("error in format_placeholder");
163
164        self
165    }
166
167    /// Start a list separated by `separator`.
168    ///
169    /// The returned type exposes identical [`.push()`][Separated::push] and
170    /// [`.push_bind()`][Separated::push_bind] methods which push `separator` to the query
171    /// before their normal behavior. [`.push_unseparated()`][Separated::push_unseparated] and [`.push_bind_unseparated()`][Separated::push_bind_unseparated] are also
172    /// provided to push a SQL fragment without the separator.
173    ///
174    /// ```rust
175    /// # #[cfg(feature = "mysql")] {
176    /// use sqlx::{Execute, MySql, QueryBuilder};
177    /// let foods = vec!["pizza".to_string(), "chips".to_string()];
178    /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
179    ///     "SELECT * from food where name in ("
180    /// );
181    /// // One element vector is handled correctly but an empty vector
182    /// // would cause a sql syntax error
183    /// let mut separated = query_builder.separated(", ");
184    /// for value_type in foods.iter() {
185    ///   separated.push_bind(value_type);
186    /// }
187    /// separated.push_unseparated(") ");
188    ///
189    /// let mut query = query_builder.build();
190    /// let sql = query.sql();
191    /// assert!(sql.ends_with("in (?, ?) "));
192    /// # }
193    /// ```
194
195    pub fn separated<'qb, Sep>(&'qb mut self, separator: Sep) -> Separated<'qb, 'args, DB, Sep>
196    where
197        'args: 'qb,
198        Sep: Display,
199    {
200        self.sanity_check();
201
202        Separated {
203            query_builder: self,
204            separator,
205            push_separator: false,
206        }
207    }
208
209    // Most of the `QueryBuilder` API is purposefully very low-level but this was a commonly
210    // requested use-case so it made sense to support.
211    /// Push a `VALUES` clause where each item in `tuples` represents a tuple/row in the clause.
212    ///
213    /// This can be used to construct a bulk `INSERT` statement, although keep in mind that all
214    /// databases have some practical limit on the number of bind arguments in a single query.
215    /// See [`.push_bind()`][Self::push_bind] for details.
216    ///
217    /// To be safe, you can do `tuples.into_iter().take(N)` where `N` is the limit for your database
218    /// divided by the number of fields in each tuple; since integer division always rounds down,
219    /// this will ensure that you don't exceed the limit.
220    ///
221    /// ### Notes
222    ///
223    /// If `tuples` is empty, this will likely produce a syntactically invalid query as `VALUES`
224    /// generally expects to be followed by at least 1 tuple.
225    ///
226    /// If `tuples` can have many different lengths, you may want to call
227    /// [`.persistent(false)`][Query::persistent] after [`.build()`][Self::build] to avoid
228    /// filling up the connection's prepared statement cache.
229    ///
230    /// Because the `Arguments` API has a lifetime that must live longer than `Self`, you cannot
231    /// bind by-reference from an iterator unless that iterator yields references that live
232    /// longer than `Self`, even if the specific `Arguments` implementation doesn't actually
233    /// borrow the values (like `MySqlArguments` and `PgArguments` immediately encode the arguments
234    /// and don't borrow them past the `.add()` call).
235    ///
236    /// So basically, if you want to bind by-reference you need an iterator that yields references,
237    /// e.g. if you have values in a `Vec` you can do `.iter()` instead of `.into_iter()`. The
238    /// example below uses an iterator that creates values on the fly
239    /// and so cannot bind by-reference.
240    ///
241    /// ### Example (MySQL)
242    ///
243    /// ```rust
244    /// # #[cfg(feature = "mysql")]
245    /// # {
246    /// use sqlx::{Execute, MySql, QueryBuilder};
247    ///
248    /// struct User {
249    ///     id: i32,
250    ///     username: String,
251    ///     email: String,
252    ///     password: String,
253    /// }
254    ///
255    /// // The number of parameters in MySQL must fit in a `u16`.
256    /// const BIND_LIMIT: usize = 65535;
257    ///
258    /// // This would normally produce values forever!
259    /// let users = (0..).map(|i| User {
260    ///     id: i,
261    ///     username: format!("test_user_{i}"),
262    ///     email: format!("test-user-{i}@example.com"),
263    ///     password: format!("Test!User@Password#{i}"),
264    /// });
265    ///
266    /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
267    ///     // Note the trailing space; most calls to `QueryBuilder` don't automatically insert
268    ///     // spaces as that might interfere with identifiers or quoted strings where exact
269    ///     // values may matter.
270    ///     "INSERT INTO users(id, username, email, password) "
271    /// );
272    ///
273    /// // Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
274    /// query_builder.push_values(users.take(BIND_LIMIT / 4), |mut b, user| {
275    ///     // If you wanted to bind these by-reference instead of by-value,
276    ///     // you'd need an iterator that yields references that live as long as `query_builder`,
277    ///     // e.g. collect it to a `Vec` first.
278    ///     b.push_bind(user.id)
279    ///         .push_bind(user.username)
280    ///         .push_bind(user.email)
281    ///         .push_bind(user.password);
282    /// });
283    ///
284    /// let mut query = query_builder.build();
285    ///
286    /// // You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
287    /// // For the sake of demonstration though, we're just going to assert the contents
288    /// // of the query.
289    ///
290    /// // These are methods of the `Execute` trait, not normally meant to be called in user code.
291    /// let sql = query.sql();
292    /// let arguments = query.take_arguments().unwrap();
293    ///
294    /// assert!(sql.starts_with(
295    ///     "INSERT INTO users(id, username, email, password) VALUES (?, ?, ?, ?), (?, ?, ?, ?)"
296    /// ));
297    ///
298    /// assert!(sql.ends_with("(?, ?, ?, ?)"));
299    ///
300    /// // Not a normally exposed function, only used for this doctest.
301    /// // 65535 / 4 = 16383 (rounded down)
302    /// // 16383 * 4 = 65532
303    /// assert_eq!(arguments.len(), 65532);
304    /// # }
305    /// ```
306    pub fn push_values<I, F>(&mut self, tuples: I, mut push_tuple: F) -> &mut Self
307    where
308        I: IntoIterator,
309        F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),
310    {
311        self.sanity_check();
312
313        self.push("VALUES ");
314
315        let mut separated = self.separated(", ");
316
317        for tuple in tuples {
318            separated.push("(");
319
320            // use a `Separated` with a separate (hah) internal state
321            push_tuple(separated.query_builder.separated(", "), tuple);
322
323            separated.push_unseparated(")");
324        }
325
326        debug_assert!(
327            separated.push_separator,
328            "No value being pushed. QueryBuilder may not build correct sql query!"
329        );
330
331        separated.query_builder
332    }
333
334    /// Creates `((a, b), (..)` statements, from `tuples`.
335    ///
336    /// This can be used to construct a bulk `SELECT` statement like this:
337    /// ```sql
338    /// SELECT * FROM users WHERE (id, username) IN ((1, "test_user_1"), (2, "test_user_2"))
339    /// ```
340    ///
341    /// Although keep in mind that all
342    /// databases have some practical limit on the number of bind arguments in a single query.
343    /// See [`.push_bind()`][Self::push_bind] for details.
344    ///
345    /// To be safe, you can do `tuples.into_iter().take(N)` where `N` is the limit for your database
346    /// divided by the number of fields in each tuple; since integer division always rounds down,
347    /// this will ensure that you don't exceed the limit.
348    ///
349    /// ### Notes
350    ///
351    /// If `tuples` is empty, this will likely produce a syntactically invalid query
352    ///
353    /// ### Example (MySQL)
354    ///
355    /// ```rust
356    /// # #[cfg(feature = "mysql")]
357    /// # {
358    /// use sqlx::{Execute, MySql, QueryBuilder};
359    ///
360    /// struct User {
361    ///     id: i32,
362    ///     username: String,
363    ///     email: String,
364    ///     password: String,
365    /// }
366    ///
367    /// // The number of parameters in MySQL must fit in a `u16`.
368    /// const BIND_LIMIT: usize = 65535;
369    ///
370    /// // This would normally produce values forever!
371    /// let users = (0..).map(|i| User {
372    ///     id: i,
373    ///     username: format!("test_user_{i}"),
374    ///     email: format!("test-user-{i}@example.com"),
375    ///     password: format!("Test!User@Password#{i}"),
376    /// });
377    ///
378    /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
379    ///     // Note the trailing space; most calls to `QueryBuilder` don't automatically insert
380    ///     // spaces as that might interfere with identifiers or quoted strings where exact
381    ///     // values may matter.
382    ///     "SELECT * FROM users WHERE (id, username, email, password) in"
383    /// );
384    ///
385    /// // Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
386    /// query_builder.push_tuples(users.take(BIND_LIMIT / 4), |mut b, user| {
387    ///     // If you wanted to bind these by-reference instead of by-value,
388    ///     // you'd need an iterator that yields references that live as long as `query_builder`,
389    ///     // e.g. collect it to a `Vec` first.
390    ///     b.push_bind(user.id)
391    ///         .push_bind(user.username)
392    ///         .push_bind(user.email)
393    ///         .push_bind(user.password);
394    /// });
395    ///
396    /// let mut query = query_builder.build();
397    ///
398    /// // You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
399    /// // For the sake of demonstration though, we're just going to assert the contents
400    /// // of the query.
401    ///
402    /// // These are methods of the `Execute` trait, not normally meant to be called in user code.
403    /// let sql = query.sql();
404    /// let arguments = query.take_arguments().unwrap();
405    ///
406    /// assert!(sql.starts_with(
407    ///     "SELECT * FROM users WHERE (id, username, email, password) in ((?, ?, ?, ?), (?, ?, ?, ?), "
408    /// ));
409    ///
410    /// assert!(sql.ends_with("(?, ?, ?, ?)) "));
411    ///
412    /// // Not a normally exposed function, only used for this doctest.
413    /// // 65535 / 4 = 16383 (rounded down)
414    /// // 16383 * 4 = 65532
415    /// assert_eq!(arguments.len(), 65532);
416    /// }
417    /// ```
418    pub fn push_tuples<I, F>(&mut self, tuples: I, mut push_tuple: F) -> &mut Self
419    where
420        I: IntoIterator,
421        F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),
422    {
423        self.sanity_check();
424
425        self.push(" (");
426
427        let mut separated = self.separated(", ");
428
429        for tuple in tuples {
430            separated.push("(");
431
432            push_tuple(separated.query_builder.separated(", "), tuple);
433
434            separated.push_unseparated(")");
435        }
436        separated.push_unseparated(") ");
437
438        separated.query_builder
439    }
440
441    /// Produce an executable query from this builder.
442    ///
443    /// ### Note: Query is not Checked
444    /// It is your responsibility to ensure that you produce a syntactically correct query here,
445    /// this API has no way to check it for you.
446    ///
447    /// ### Note: Reuse
448    /// You can reuse this builder afterwards to amortize the allocation overhead of the query
449    /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
450    /// to the state it was in immediately after [`new()`][Self::new].
451    ///
452    /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
453    pub fn build(&mut self) -> Query<'_, DB, <DB as Database>::Arguments<'args>> {
454        self.sanity_check();
455
456        Query {
457            statement: Either::Left(&self.query),
458            arguments: self.arguments.take().map(Ok),
459            database: PhantomData,
460            persistent: true,
461        }
462    }
463
464    /// Produce an executable query from this builder.
465    ///
466    /// ### Note: Query is not Checked
467    /// It is your responsibility to ensure that you produce a syntactically correct query here,
468    /// this API has no way to check it for you.
469    ///
470    /// ### Note: Reuse
471    /// You can reuse this builder afterwards to amortize the allocation overhead of the query
472    /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
473    /// to the state it was in immediately after [`new()`][Self::new].
474    ///
475    /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
476    pub fn build_query_as<'q, T: FromRow<'q, DB::Row>>(
477        &'q mut self,
478    ) -> QueryAs<'q, DB, T, <DB as Database>::Arguments<'args>> {
479        QueryAs {
480            inner: self.build(),
481            output: PhantomData,
482        }
483    }
484
485    /// Produce an executable query from this builder.
486    ///
487    /// ### Note: Query is not Checked
488    /// It is your responsibility to ensure that you produce a syntactically correct query here,
489    /// this API has no way to check it for you.
490    ///
491    /// ### Note: Reuse
492    /// You can reuse this builder afterwards to amortize the allocation overhead of the query
493    /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
494    /// to the state it was in immediately after [`new()`][Self::new].
495    ///
496    /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
497    pub fn build_query_scalar<'q, T>(
498        &'q mut self,
499    ) -> QueryScalar<'q, DB, T, <DB as Database>::Arguments<'args>>
500    where
501        DB: Database,
502        (T,): for<'r> FromRow<'r, DB::Row>,
503    {
504        QueryScalar {
505            inner: self.build_query_as(),
506        }
507    }
508
509    /// Reset this `QueryBuilder` back to its initial state.
510    ///
511    /// The query is truncated to the initial fragment provided to [`new()`][Self::new] and
512    /// the bind arguments are reset.
513    pub fn reset(&mut self) -> &mut Self {
514        self.query.truncate(self.init_len);
515        self.arguments = Some(Default::default());
516
517        self
518    }
519
520    /// Get the current build SQL; **note**: may not be syntactically correct.
521    pub fn sql(&self) -> &str {
522        &self.query
523    }
524
525    /// Deconstruct this `QueryBuilder`, returning the built SQL. May not be syntactically correct.
526    pub fn into_sql(self) -> String {
527        self.query
528    }
529}
530
531/// A wrapper around `QueryBuilder` for creating comma(or other token)-separated lists.
532///
533/// See [`QueryBuilder::separated()`] for details.
534#[allow(explicit_outlives_requirements)]
535pub struct Separated<'qb, 'args: 'qb, DB, Sep>
536where
537    DB: Database,
538{
539    query_builder: &'qb mut QueryBuilder<'args, DB>,
540    separator: Sep,
541    push_separator: bool,
542}
543
544impl<'qb, 'args: 'qb, DB, Sep> Separated<'qb, 'args, DB, Sep>
545where
546    DB: Database,
547    Sep: Display,
548{
549    /// Push the separator if applicable, and then the given SQL fragment.
550    ///
551    /// See [`QueryBuilder::push()`] for details.
552    pub fn push(&mut self, sql: impl Display) -> &mut Self {
553        if self.push_separator {
554            self.query_builder
555                .push(format_args!("{}{}", self.separator, sql));
556        } else {
557            self.query_builder.push(sql);
558            self.push_separator = true;
559        }
560
561        self
562    }
563
564    /// Push a SQL fragment without a separator.
565    ///
566    /// Simply calls [`QueryBuilder::push()`] directly.
567    pub fn push_unseparated(&mut self, sql: impl Display) -> &mut Self {
568        self.query_builder.push(sql);
569        self
570    }
571
572    /// Push the separator if applicable, then append a bind argument.
573    ///
574    /// See [`QueryBuilder::push_bind()`] for details.
575    pub fn push_bind<T>(&mut self, value: T) -> &mut Self
576    where
577        T: 'args + Encode<'args, DB> + Type<DB>,
578    {
579        if self.push_separator {
580            self.query_builder.push(&self.separator);
581        }
582
583        self.query_builder.push_bind(value);
584        self.push_separator = true;
585
586        self
587    }
588
589    /// Push a bind argument placeholder (`?` or `$N` for Postgres) and bind a value to it
590    /// without a separator.
591    ///
592    /// Simply calls [`QueryBuilder::push_bind()`] directly.
593    pub fn push_bind_unseparated<T>(&mut self, value: T) -> &mut Self
594    where
595        T: 'args + Encode<'args, DB> + Type<DB>,
596    {
597        self.query_builder.push_bind(value);
598        self
599    }
600}
sqlx_core/query_builder.rs

sqlx_core/
query_builder.rs
