Trait rusqlite::Params

source · [−]
pub trait Params: Sealed { }
Expand description

Trait used for sets of parameter passed into SQL statements/queries.

Note: Currently, this trait can only be implemented inside this crate. Additionally, it’s methods (which are doc(hidden)) should currently not be considered part of the stable API, although it’s possible they will stabilize in the future.

Passing parameters to SQLite

Many functions in this library let you pass parameters to SQLite. Doing this lets you avoid any risk of SQL injection, and is simpler than escaping things manually. Aside from deprecated functions and a few helpers, this is indicated by the function taking a generic argument that implements Params (this trait).

Positional parameters

For cases where you want to pass a list of parameters where the number of parameters is known at compile time, this can be done in one of the following ways:

  • For small lists of parameters up to 16 items, they may alternatively be passed as a tuple, as in thing.query((1, "foo")).

    This is somewhat inconvenient for a single item, since you need a weird-looking trailing comma: thing.query(("example",)). That case is perhaps more cleanly expressed as thing.query(["example"]).

  • Using the rusqlite::params! macro, e.g. thing.query(rusqlite::params![1, "foo", bar]). This is mostly useful for heterogeneous lists where the number of parameters greater than 16, or homogenous lists of parameters where the number of parameters exceeds 32.

  • For small homogeneous lists of parameters, they can either be passed as:

    • an array, as in thing.query([1i32, 2, 3, 4]) or thing.query(["foo", "bar", "baz"]).

    • a reference to an array of references, as in thing.query(&["foo", "bar", "baz"]) or thing.query(&[&1i32, &2, &3]).

      (Note: in this case we don’t implement this for slices for coherence reasons, so it really is only for the “reference to array” types — hence why the number of parameters must be <= 32 or you need to reach for rusqlite::params!)

    Unfortunately, in the current design it’s not possible to allow this for references to arrays of non-references (e.g. &[1i32, 2, 3]). Code like this should instead either use params!, an array literal, a &[&dyn ToSql] or if none of those work, ParamsFromIter.

  • As a slice of ToSql trait object references, e.g. &[&dyn ToSql]. This is mostly useful for passing parameter lists around as arguments without having every function take a generic P: Params.

Example (positional)

fn update_rows(conn: &Connection) -> Result<()> {
    let mut stmt = conn.prepare("INSERT INTO test (a, b) VALUES (?, ?)")?;

    // Using a tuple:
    stmt.execute((0, "foobar"))?;

    // Using `rusqlite::params!`:
    stmt.execute(params![1i32, "blah"])?;

    // array literal — non-references
    stmt.execute([2i32, 3i32])?;

    // array literal — references
    stmt.execute(["foo", "bar"])?;

    // Slice literal, references:
    stmt.execute(&[&2i32, &3i32])?;

    // Note: The types behind the references don't have to be `Sized`
    stmt.execute(&["foo", "bar"])?;

    // However, this doesn't work (see above):
    // stmt.execute(&[1i32, 2i32])?;
    Ok(())
}

Named parameters

SQLite lets you name parameters using a number of conventions (“:foo”, “@foo”, “$foo”). You can pass named parameters in to SQLite using rusqlite in a few ways:

  • Using the rusqlite::named_params! macro, as in stmt.execute(named_params!{ ":name": "foo", ":age": 99 }). Similar to the params macro, this is most useful for heterogeneous lists of parameters, or lists where the number of parameters exceeds 32.

  • As a slice of &[(&str, &dyn ToSql)]. This is what essentially all of these boil down to in the end, conceptually at least. In theory you can pass this as stmt.

  • As array references, similar to the positional params. This looks like thing.query(&[(":foo", &1i32), (":bar", &2i32)]) or thing.query(&[(":foo", "abc"), (":bar", "def")]).

Note: Unbound named parameters will be left to the value they previously were bound with, falling back to NULL for parameters which have never been bound.

Example (named)

fn insert(conn: &Connection) -> Result<()> {
    let mut stmt = conn.prepare("INSERT INTO test (key, value) VALUES (:key, :value)")?;
    // Using `rusqlite::params!`:
    stmt.execute(named_params! { ":key": "one", ":val": 2 })?;
    // Alternatively:
    stmt.execute(&[(":key", "three"), (":val", "four")])?;
    // Or:
    stmt.execute(&[(":key", &100), (":val", &200)])?;
    Ok(())
}

No parameters

You can just use an empty tuple or the empty array literal to run a query that accepts no parameters. (The rusqlite::NO_PARAMS constant which was common in previous versions of this library is no longer needed, and is now deprecated).

Example (no parameters)

The empty tuple:

fn delete_all_users(conn: &Connection) -> Result<()> {
    // You may also use `()`.
    conn.execute("DELETE FROM users", ())?;
    Ok(())
}

The empty array:

fn delete_all_users(conn: &Connection) -> Result<()> {
    // Just use an empty array (e.g. `[]`) for no params.
    conn.execute("DELETE FROM users", [])?;
    Ok(())
}

Dynamic parameter list

If you have a number of parameters which is unknown at compile time (for example, building a dynamic query at runtime), you have two choices:

  • Use a &[&dyn ToSql]. This is often annoying to construct if you don’t already have this type on-hand.
  • Use the ParamsFromIter type. This essentially lets you wrap an iterator some T: ToSql with something that implements Params. The usage of this looks like rusqlite::params_from_iter(something).

A lot of the considerations here are similar either way, so you should see the ParamsFromIter documentation for more info / examples.

Implementations on Foreign Types

Implementors