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