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 separated.query_builder
327 }
328
329 /// Creates `((a, b), (..)` statements, from `tuples`.
330 ///
331 /// This can be used to construct a bulk `SELECT` statement like this:
332 /// ```sql
333 /// SELECT * FROM users WHERE (id, username) IN ((1, "test_user_1"), (2, "test_user_2"))
334 /// ```
335 ///
336 /// Although keep in mind that all
337 /// databases have some practical limit on the number of bind arguments in a single query.
338 /// See [`.push_bind()`][Self::push_bind] for details.
339 ///
340 /// To be safe, you can do `tuples.into_iter().take(N)` where `N` is the limit for your database
341 /// divided by the number of fields in each tuple; since integer division always rounds down,
342 /// this will ensure that you don't exceed the limit.
343 ///
344 /// ### Notes
345 ///
346 /// If `tuples` is empty, this will likely produce a syntactically invalid query
347 ///
348 /// ### Example (MySQL)
349 ///
350 /// ```rust
351 /// # #[cfg(feature = "mysql")]
352 /// # {
353 /// use sqlx::{Execute, MySql, QueryBuilder};
354 ///
355 /// struct User {
356 /// id: i32,
357 /// username: String,
358 /// email: String,
359 /// password: String,
360 /// }
361 ///
362 /// // The number of parameters in MySQL must fit in a `u16`.
363 /// const BIND_LIMIT: usize = 65535;
364 ///
365 /// // This would normally produce values forever!
366 /// let users = (0..).map(|i| User {
367 /// id: i,
368 /// username: format!("test_user_{i}"),
369 /// email: format!("test-user-{i}@example.com"),
370 /// password: format!("Test!User@Password#{i}"),
371 /// });
372 ///
373 /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
374 /// // Note the trailing space; most calls to `QueryBuilder` don't automatically insert
375 /// // spaces as that might interfere with identifiers or quoted strings where exact
376 /// // values may matter.
377 /// "SELECT * FROM users WHERE (id, username, email, password) in"
378 /// );
379 ///
380 /// // Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
381 /// query_builder.push_tuples(users.take(BIND_LIMIT / 4), |mut b, user| {
382 /// // If you wanted to bind these by-reference instead of by-value,
383 /// // you'd need an iterator that yields references that live as long as `query_builder`,
384 /// // e.g. collect it to a `Vec` first.
385 /// b.push_bind(user.id)
386 /// .push_bind(user.username)
387 /// .push_bind(user.email)
388 /// .push_bind(user.password);
389 /// });
390 ///
391 /// let mut query = query_builder.build();
392 ///
393 /// // You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
394 /// // For the sake of demonstration though, we're just going to assert the contents
395 /// // of the query.
396 ///
397 /// // These are methods of the `Execute` trait, not normally meant to be called in user code.
398 /// let sql = query.sql();
399 /// let arguments = query.take_arguments().unwrap();
400 ///
401 /// assert!(sql.starts_with(
402 /// "SELECT * FROM users WHERE (id, username, email, password) in ((?, ?, ?, ?), (?, ?, ?, ?), "
403 /// ));
404 ///
405 /// assert!(sql.ends_with("(?, ?, ?, ?)) "));
406 ///
407 /// // Not a normally exposed function, only used for this doctest.
408 /// // 65535 / 4 = 16383 (rounded down)
409 /// // 16383 * 4 = 65532
410 /// assert_eq!(arguments.len(), 65532);
411 /// }
412 /// ```
413 pub fn push_tuples<I, F>(&mut self, tuples: I, mut push_tuple: F) -> &mut Self
414 where
415 I: IntoIterator,
416 F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),
417 {
418 self.sanity_check();
419
420 self.push(" (");
421
422 let mut separated = self.separated(", ");
423
424 for tuple in tuples {
425 separated.push("(");
426
427 push_tuple(separated.query_builder.separated(", "), tuple);
428
429 separated.push_unseparated(")");
430 }
431 separated.push_unseparated(") ");
432
433 separated.query_builder
434 }
435
436 /// Produce an executable query from this builder.
437 ///
438 /// ### Note: Query is not Checked
439 /// It is your responsibility to ensure that you produce a syntactically correct query here,
440 /// this API has no way to check it for you.
441 ///
442 /// ### Note: Reuse
443 /// You can reuse this builder afterwards to amortize the allocation overhead of the query
444 /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
445 /// to the state it was in immediately after [`new()`][Self::new].
446 ///
447 /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
448 pub fn build(&mut self) -> Query<'_, DB, <DB as Database>::Arguments<'args>> {
449 self.sanity_check();
450
451 Query {
452 statement: Either::Left(&self.query),
453 arguments: self.arguments.take().map(Ok),
454 database: PhantomData,
455 persistent: true,
456 }
457 }
458
459 /// Produce an executable query from this builder.
460 ///
461 /// ### Note: Query is not Checked
462 /// It is your responsibility to ensure that you produce a syntactically correct query here,
463 /// this API has no way to check it for you.
464 ///
465 /// ### Note: Reuse
466 /// You can reuse this builder afterwards to amortize the allocation overhead of the query
467 /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
468 /// to the state it was in immediately after [`new()`][Self::new].
469 ///
470 /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
471 pub fn build_query_as<'q, T: FromRow<'q, DB::Row>>(
472 &'q mut self,
473 ) -> QueryAs<'q, DB, T, <DB as Database>::Arguments<'args>> {
474 QueryAs {
475 inner: self.build(),
476 output: PhantomData,
477 }
478 }
479
480 /// Produce an executable query from this builder.
481 ///
482 /// ### Note: Query is not Checked
483 /// It is your responsibility to ensure that you produce a syntactically correct query here,
484 /// this API has no way to check it for you.
485 ///
486 /// ### Note: Reuse
487 /// You can reuse this builder afterwards to amortize the allocation overhead of the query
488 /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
489 /// to the state it was in immediately after [`new()`][Self::new].
490 ///
491 /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
492 pub fn build_query_scalar<'q, T>(
493 &'q mut self,
494 ) -> QueryScalar<'q, DB, T, <DB as Database>::Arguments<'args>>
495 where
496 DB: Database,
497 (T,): for<'r> FromRow<'r, DB::Row>,
498 {
499 QueryScalar {
500 inner: self.build_query_as(),
501 }
502 }
503
504 /// Reset this `QueryBuilder` back to its initial state.
505 ///
506 /// The query is truncated to the initial fragment provided to [`new()`][Self::new] and
507 /// the bind arguments are reset.
508 pub fn reset(&mut self) -> &mut Self {
509 self.query.truncate(self.init_len);
510 self.arguments = Some(Default::default());
511
512 self
513 }
514
515 /// Get the current build SQL; **note**: may not be syntactically correct.
516 pub fn sql(&self) -> &str {
517 &self.query
518 }
519
520 /// Deconstruct this `QueryBuilder`, returning the built SQL. May not be syntactically correct.
521 pub fn into_sql(self) -> String {
522 self.query
523 }
524}
525
526/// A wrapper around `QueryBuilder` for creating comma(or other token)-separated lists.
527///
528/// See [`QueryBuilder::separated()`] for details.
529#[allow(explicit_outlives_requirements)]
530pub struct Separated<'qb, 'args: 'qb, DB, Sep>
531where
532 DB: Database,
533{
534 query_builder: &'qb mut QueryBuilder<'args, DB>,
535 separator: Sep,
536 push_separator: bool,
537}
538
539impl<'qb, 'args: 'qb, DB, Sep> Separated<'qb, 'args, DB, Sep>
540where
541 DB: Database,
542 Sep: Display,
543{
544 /// Push the separator if applicable, and then the given SQL fragment.
545 ///
546 /// See [`QueryBuilder::push()`] for details.
547 pub fn push(&mut self, sql: impl Display) -> &mut Self {
548 if self.push_separator {
549 self.query_builder
550 .push(format_args!("{}{}", self.separator, sql));
551 } else {
552 self.query_builder.push(sql);
553 self.push_separator = true;
554 }
555
556 self
557 }
558
559 /// Push a SQL fragment without a separator.
560 ///
561 /// Simply calls [`QueryBuilder::push()`] directly.
562 pub fn push_unseparated(&mut self, sql: impl Display) -> &mut Self {
563 self.query_builder.push(sql);
564 self
565 }
566
567 /// Push the separator if applicable, then append a bind argument.
568 ///
569 /// See [`QueryBuilder::push_bind()`] for details.
570 pub fn push_bind<T>(&mut self, value: T) -> &mut Self
571 where
572 T: 'args + Encode<'args, DB> + Type<DB>,
573 {
574 if self.push_separator {
575 self.query_builder.push(&self.separator);
576 }
577
578 self.query_builder.push_bind(value);
579 self.push_separator = true;
580
581 self
582 }
583
584 /// Push a bind argument placeholder (`?` or `$N` for Postgres) and bind a value to it
585 /// without a separator.
586 ///
587 /// Simply calls [`QueryBuilder::push_bind()`] directly.
588 pub fn push_bind_unseparated<T>(&mut self, value: T) -> &mut Self
589 where
590 T: 'args + Encode<'args, DB> + Type<DB>,
591 {
592 self.query_builder.push_bind(value);
593 self
594 }
595}