Struct sqlx_core::query_builder::QueryBuilder

pub struct QueryBuilder<'args, DB>where
    DB: Database,{ /* private fields */ }

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

impl<'args, DB> QueryBuilder<'args, DB>where DB: Database,

pub fn new(init: impl Into<String>) -> Selfwhere <DB as HasArguments<'args>>::Arguments: Default,

Start building a query with an initial SQL fragment, which may be an empty string.

pub fn push(&mut self, sql: impl Display) -> &mut Self

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.

pub fn push_bind<T>(&mut self, value: T) -> &mut Selfwhere T: 'args + Encode<'args, DB> + Send + Type<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).
• 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.

pub fn separated<'qb, Sep>( &'qb mut self, separator: Sep ) -> Separated<'qb, 'args, DB, Sep>where Sep: Display, 'args: 'qb,

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 (?, ?) "));

pub fn push_values<I, F>(&mut self, tuples: I, push_tuple: F) -> &mut Selfwhere I: IntoIterator, F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),

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-{}@example.com", i),
    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);

pub fn push_tuples<I, F>(&mut self, tuples: I, push_tuple: F) -> &mut Selfwhere I: IntoIterator, F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),

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-{}@example.com", i),
    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);
}

pub fn build(&mut self) -> Query<'_, DB, <DB as HasArguments<'args>>::Arguments>

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.

pub fn build_query_as<'q, T: FromRow<'q, DB::Row>>( &'q mut self ) -> QueryAs<'q, DB, T, <DB as HasArguments<'args>>::Arguments>

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.

pub fn reset(&mut self) -> &mut Self

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.

pub fn sql(&self) -> &str

Get the current build SQL; note: may not be syntactically correct.

pub fn into_sql(self) -> String

Deconstruct this QueryBuilder, returning the built SQL. May not be syntactically correct.