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

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

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:

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.

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.

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() is also provided to push a SQL fragment without the separator.

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);

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.

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.

Auto Trait Implementations

Blanket Implementations

Gets the TypeId of self. Read more

Immutably borrows from an owned value. Read more

Mutably borrows from an owned value. Read more

Returns the argument unchanged.

Calls U::from(self).

That is, this conversion is whatever the implementation of From<T> for U chooses to do.

Should always be Self

The type returned in the event of a conversion error.

Performs the conversion.

The type returned in the event of a conversion error.

Performs the conversion.