Struct sqlx::QueryBuilder
source ¡ pub struct QueryBuilder<'args, DB>where
DB: Database,{ /* private fields */ }
Expand description
A builder type for constructing queries at runtime.
See .push_values()
for an example of building a bulk INSERT
statement.
Note, however, that with Postgres you can get much better performance by using arrays
and UNNEST()
. See our FAQ for details.
Implementations§
source§impl<'args, DB> QueryBuilder<'args, DB>where
DB: Database,
impl<'args, DB> QueryBuilder<'args, DB>where
DB: Database,
sourcepub fn new(init: impl Into<String>) -> QueryBuilder<'args, DB>
pub fn new(init: impl Into<String>) -> QueryBuilder<'args, DB>
Start building a query with an initial SQL fragment, which may be an empty string.
sourcepub fn with_arguments<A>(
init: impl Into<String>,
arguments: A,
) -> QueryBuilder<'args, DB>where
DB: Database,
A: IntoArguments<'args, DB>,
pub fn with_arguments<A>(
init: impl Into<String>,
arguments: A,
) -> QueryBuilder<'args, DB>where
DB: Database,
A: IntoArguments<'args, DB>,
Construct a QueryBuilder
with existing SQL and arguments.
§Note
This does not check if arguments
is valid for the given SQL.
sourcepub fn push(&mut self, sql: impl Display) -> &mut QueryBuilder<'args, DB>
pub fn push(&mut self, sql: impl Display) -> &mut QueryBuilder<'args, DB>
Append a SQL fragment to the query.
May be a string or anything that implements Display
.
You can also use format_args!()
here to push a formatted string without an intermediate
allocation.
§Warning: Beware SQL Injection Vulnerabilities and Untrusted Input!
You should not use this to insert input directly into the query from an untrusted user as this can be used by an attacker to extract sensitive data or take over your database.
Security breaches due to SQL injection can cost your organization a lot of money from damage control and lost clients, betray the trust of your users in your system, and are just plain embarrassing. If you are unfamiliar with the threat that SQL injection imposes, you should take some time to learn more about it before proceeding:
- SQL Injection on OWASP.org
- SQL Injection on Wikipedia
- See âExamplesâ for notable instances of security breaches due to SQL injection.
This method does not perform sanitization. Instead, you should use
.push_bind()
which inserts a placeholder into the query and then
sends the possibly untrustworthy value separately (called a âbind argumentâ) so that it
cannot be misinterpreted by the database server.
Note that you should still at least have some sort of sanity checks on the values youâre sending as thatâs just good practice and prevent other types of attacks against your system, e.g. check that strings arenât too long, numbers are within expected ranges, etc.
sourcepub fn push_bind<T>(&mut self, value: T) -> &mut QueryBuilder<'args, DB>
pub fn push_bind<T>(&mut self, value: T) -> &mut QueryBuilder<'args, DB>
Push a bind argument placeholder (?
or $N
for Postgres) and bind a value to it.
§Note: Database-specific Limits
Note that every database has a practical limit on the number of bind parameters you can add to a single query. This varies by database.
While you should consult the manual of your specific database version and/or current configuration for the exact value as it may be different than listed here, the defaults for supported databases as of writing are as follows:
- Postgres and MySQL: 65535
- You may find sources that state that Postgres has a limit of 32767,
but that is a misinterpretation of the specification by the JDBC driver implementation
as discussed in this Github issue. Postgres itself
asserts that the number of parameters is in the range
[0, 65535)
.
- You may find sources that state that Postgres has a limit of 32767,
but that is a misinterpretation of the specification by the JDBC driver implementation
as discussed in this Github issue. Postgres itself
asserts that the number of parameters is in the range
- SQLite: 32766 (configurable by
SQLITE_LIMIT_VARIABLE_NUMBER
)- SQLite prior to 3.32.0: 999
- MSSQL: 2100
Exceeding these limits may panic (as a sanity check) or trigger a database error at runtime depending on the implementation.
sourcepub fn separated<'qb, Sep>(
&'qb mut self,
separator: Sep,
) -> Separated<'qb, 'args, DB, Sep>where
'args: 'qb,
Sep: Display,
pub fn separated<'qb, Sep>(
&'qb mut self,
separator: Sep,
) -> Separated<'qb, 'args, DB, Sep>where
'args: 'qb,
Sep: Display,
Start a list separated by separator
.
The returned type exposes identical .push()
and
.push_bind()
methods which push separator
to the query
before their normal behavior. .push_unseparated()
and .push_bind_unseparated()
are also
provided to push a SQL fragment without the separator.
use sqlx::{Execute, MySql, QueryBuilder};
let foods = vec!["pizza".to_string(), "chips".to_string()];
let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
"SELECT * from food where name in ("
);
// One element vector is handled correctly but an empty vector
// would cause a sql syntax error
let mut separated = query_builder.separated(", ");
for value_type in foods.iter() {
separated.push_bind(value_type);
}
separated.push_unseparated(") ");
let mut query = query_builder.build();
let sql = query.sql();
assert!(sql.ends_with("in (?, ?) "));
sourcepub fn push_values<I, F>(
&mut self,
tuples: I,
push_tuple: F,
) -> &mut QueryBuilder<'args, DB>
pub fn push_values<I, F>( &mut self, tuples: I, push_tuple: F, ) -> &mut QueryBuilder<'args, DB>
Push a VALUES
clause where each item in tuples
represents a tuple/row in the clause.
This can be used to construct a bulk INSERT
statement, although keep in mind that all
databases have some practical limit on the number of bind arguments in a single query.
See .push_bind()
for details.
To be safe, you can do tuples.into_iter().take(N)
where N
is the limit for your database
divided by the number of fields in each tuple; since integer division always rounds down,
this will ensure that you donât exceed the limit.
§Notes
If tuples
is empty, this will likely produce a syntactically invalid query as VALUES
generally expects to be followed by at least 1 tuple.
If tuples
can have many different lengths, you may want to call
.persistent(false)
after .build()
to avoid
filling up the connectionâs prepared statement cache.
Because the Arguments
API has a lifetime that must live longer than Self
, you cannot
bind by-reference from an iterator unless that iterator yields references that live
longer than Self
, even if the specific Arguments
implementation doesnât actually
borrow the values (like MySqlArguments
and PgArguments
immediately encode the arguments
and donât borrow them past the .add()
call).
So basically, if you want to bind by-reference you need an iterator that yields references,
e.g. if you have values in a Vec
you can do .iter()
instead of .into_iter()
. The
example below uses an iterator that creates values on the fly
and so cannot bind by-reference.
§Example (MySQL)
use sqlx::{Execute, MySql, QueryBuilder};
struct User {
id: i32,
username: String,
email: String,
password: String,
}
// The number of parameters in MySQL must fit in a `u16`.
const BIND_LIMIT: usize = 65535;
// This would normally produce values forever!
let users = (0..).map(|i| User {
id: i,
username: format!("test_user_{i}"),
email: format!("test-user-{i}@example.com"),
password: format!("Test!User@Password#{i}"),
});
let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
// Note the trailing space; most calls to `QueryBuilder` don't automatically insert
// spaces as that might interfere with identifiers or quoted strings where exact
// values may matter.
"INSERT INTO users(id, username, email, password) "
);
// Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
query_builder.push_values(users.take(BIND_LIMIT / 4), |mut b, user| {
// If you wanted to bind these by-reference instead of by-value,
// you'd need an iterator that yields references that live as long as `query_builder`,
// e.g. collect it to a `Vec` first.
b.push_bind(user.id)
.push_bind(user.username)
.push_bind(user.email)
.push_bind(user.password);
});
let mut query = query_builder.build();
// You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
// For the sake of demonstration though, we're just going to assert the contents
// of the query.
// These are methods of the `Execute` trait, not normally meant to be called in user code.
let sql = query.sql();
let arguments = query.take_arguments().unwrap();
assert!(sql.starts_with(
"INSERT INTO users(id, username, email, password) VALUES (?, ?, ?, ?), (?, ?, ?, ?)"
));
assert!(sql.ends_with("(?, ?, ?, ?)"));
// Not a normally exposed function, only used for this doctest.
// 65535 / 4 = 16383 (rounded down)
// 16383 * 4 = 65532
assert_eq!(arguments.len(), 65532);
sourcepub fn push_tuples<I, F>(
&mut self,
tuples: I,
push_tuple: F,
) -> &mut QueryBuilder<'args, DB>
pub fn push_tuples<I, F>( &mut self, tuples: I, push_tuple: F, ) -> &mut QueryBuilder<'args, DB>
Creates ((a, b), (..)
statements, from tuples
.
This can be used to construct a bulk SELECT
statement like this:
SELECT * FROM users WHERE (id, username) IN ((1, "test_user_1"), (2, "test_user_2"))
Although keep in mind that all
databases have some practical limit on the number of bind arguments in a single query.
See .push_bind()
for details.
To be safe, you can do tuples.into_iter().take(N)
where N
is the limit for your database
divided by the number of fields in each tuple; since integer division always rounds down,
this will ensure that you donât exceed the limit.
§Notes
If tuples
is empty, this will likely produce a syntactically invalid query
§Example (MySQL)
use sqlx::{Execute, MySql, QueryBuilder};
struct User {
id: i32,
username: String,
email: String,
password: String,
}
// The number of parameters in MySQL must fit in a `u16`.
const BIND_LIMIT: usize = 65535;
// This would normally produce values forever!
let users = (0..).map(|i| User {
id: i,
username: format!("test_user_{i}"),
email: format!("test-user-{i}@example.com"),
password: format!("Test!User@Password#{i}"),
});
let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
// Note the trailing space; most calls to `QueryBuilder` don't automatically insert
// spaces as that might interfere with identifiers or quoted strings where exact
// values may matter.
"SELECT * FROM users WHERE (id, username, email, password) in"
);
// Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
query_builder.push_tuples(users.take(BIND_LIMIT / 4), |mut b, user| {
// If you wanted to bind these by-reference instead of by-value,
// you'd need an iterator that yields references that live as long as `query_builder`,
// e.g. collect it to a `Vec` first.
b.push_bind(user.id)
.push_bind(user.username)
.push_bind(user.email)
.push_bind(user.password);
});
let mut query = query_builder.build();
// You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
// For the sake of demonstration though, we're just going to assert the contents
// of the query.
// These are methods of the `Execute` trait, not normally meant to be called in user code.
let sql = query.sql();
let arguments = query.take_arguments().unwrap();
assert!(sql.starts_with(
"SELECT * FROM users WHERE (id, username, email, password) in ((?, ?, ?, ?), (?, ?, ?, ?), "
));
assert!(sql.ends_with("(?, ?, ?, ?)) "));
// Not a normally exposed function, only used for this doctest.
// 65535 / 4 = 16383 (rounded down)
// 16383 * 4 = 65532
assert_eq!(arguments.len(), 65532);
}
sourcepub fn build(&mut self) -> Query<'_, DB, <DB as Database>::Arguments<'args>>
pub fn build(&mut self) -> Query<'_, DB, <DB as Database>::Arguments<'args>>
Produce an executable query from this builder.
§Note: Query is not Checked
It is your responsibility to ensure that you produce a syntactically correct query here, this API has no way to check it for you.
§Note: Reuse
You can reuse this builder afterwards to amortize the allocation overhead of the query
string, however you must call .reset()
first, which returns Self
to the state it was in immediately after new()
.
Calling any other method but .reset()
after .build()
will panic for sanity reasons.
sourcepub fn build_query_as<'q, T>(
&'q mut self,
) -> QueryAs<'q, DB, T, <DB as Database>::Arguments<'args>>
pub fn build_query_as<'q, T>( &'q mut self, ) -> QueryAs<'q, DB, T, <DB as Database>::Arguments<'args>>
Produce an executable query from this builder.
§Note: Query is not Checked
It is your responsibility to ensure that you produce a syntactically correct query here, this API has no way to check it for you.
§Note: Reuse
You can reuse this builder afterwards to amortize the allocation overhead of the query
string, however you must call .reset()
first, which returns Self
to the state it was in immediately after new()
.
Calling any other method but .reset()
after .build()
will panic for sanity reasons.
sourcepub fn build_query_scalar<'q, T>(
&'q mut self,
) -> QueryScalar<'q, DB, T, <DB as Database>::Arguments<'args>>
pub fn build_query_scalar<'q, T>( &'q mut self, ) -> QueryScalar<'q, DB, T, <DB as Database>::Arguments<'args>>
Produce an executable query from this builder.
§Note: Query is not Checked
It is your responsibility to ensure that you produce a syntactically correct query here, this API has no way to check it for you.
§Note: Reuse
You can reuse this builder afterwards to amortize the allocation overhead of the query
string, however you must call .reset()
first, which returns Self
to the state it was in immediately after new()
.
Calling any other method but .reset()
after .build()
will panic for sanity reasons.
sourcepub fn reset(&mut self) -> &mut QueryBuilder<'args, DB>
pub fn reset(&mut self) -> &mut QueryBuilder<'args, DB>
Reset this QueryBuilder
back to its initial state.
The query is truncated to the initial fragment provided to new()
and
the bind arguments are reset.
Trait Implementations§
source§impl<'args, DB> Default for QueryBuilder<'args, DB>where
DB: Database,
impl<'args, DB> Default for QueryBuilder<'args, DB>where
DB: Database,
source§fn default() -> QueryBuilder<'args, DB>
fn default() -> QueryBuilder<'args, DB>
Auto Trait Implementations§
impl<'args, DB> Freeze for QueryBuilder<'args, DB>
impl<'args, DB> RefUnwindSafe for QueryBuilder<'args, DB>
impl<'args, DB> Send for QueryBuilder<'args, DB>
impl<'args, DB> Sync for QueryBuilder<'args, DB>
impl<'args, DB> Unpin for QueryBuilder<'args, DB>
impl<'args, DB> UnwindSafe for QueryBuilder<'args, DB>
Blanket Implementations§
source§impl<T> BorrowMut<T> for Twhere
T: ?Sized,
impl<T> BorrowMut<T> for Twhere
T: ?Sized,
source§fn borrow_mut(&mut self) -> &mut T
fn borrow_mut(&mut self) -> &mut T
source§impl<T> Instrument for T
impl<T> Instrument for T
source§fn instrument(self, span: Span) -> Instrumented<Self>
fn instrument(self, span: Span) -> Instrumented<Self>
source§fn in_current_span(self) -> Instrumented<Self>
fn in_current_span(self) -> Instrumented<Self>
source§impl<T> IntoEither for T
impl<T> IntoEither for T
source§fn into_either(self, into_left: bool) -> Either<Self, Self> â
fn into_either(self, into_left: bool) -> Either<Self, Self> â
self
into a Left
variant of Either<Self, Self>
if into_left
is true
.
Converts self
into a Right
variant of Either<Self, Self>
otherwise. Read moresource§fn into_either_with<F>(self, into_left: F) -> Either<Self, Self> â
fn into_either_with<F>(self, into_left: F) -> Either<Self, Self> â
self
into a Left
variant of Either<Self, Self>
if into_left(&self)
returns true
.
Converts self
into a Right
variant of Either<Self, Self>
otherwise. Read more