Rusqlite: prepared statements in transactions

Hello!

I have saved a prepared statement stmt in a struct, together with the database connection dbconn. How can I execute the prepared statement within a transaction?

The following is working fine, but does not use my prepared statement:

let tx = self.dbconn.transaction()?;
tx.execute("INSERT INTO ...", params![...]);
tx.commit()?;

I can also execute the prepared statement, but then I can't put it in a transaction:

self.stmt.execute(params![...]);

Ideally I would like to do the following, but can't find anything similar in the docs:

let tx = self.dbconn.transaction()?;
tx.execute_prepared(&stmt, params![...]);    // this method does not exist!
tx.commit()?;

I can see there is Transaction::prepare_cached() but this looks up the statement in some kind of internal cache, that I don't want to use, since I've cached exactly what need manually.
https://docs.rs/rusqlite/0.22.0/rusqlite/struct.Transaction.html#method.prepare_cached

Any ideas?

1 Like

Create a Transaction from a Connection, and then create a Statement from the Transaction and call execute on the Statement.

use rusqlite::{Connection, Transaction, Result};

fn insert_data(tx: &Transaction) -> Result<()> {
    let mut stmt = tx.prepare("INSERT INTO test (a) VALUES (?)")?;
    
    for i in 0..10_000 {
        stmt.execute(&[ i ])?;
    }
    
    Ok(())
}

fn main() {
    let mut conn = Connection::open_in_memory().unwrap();
    conn.execute_batch("CREATE TABLE test(a INTEGER);").unwrap();
    
    // create transaction
    let tx = conn.transaction().unwrap();
    
    insert_data(&tx).unwrap();
    
    tx.commit().unwrap();
}
1 Like

Unfortunately this is the opposite of what I'm looking for. Preparing the statements in every transaction would be too slow. My plan is to:

  • Initialize the application, prepare the statements
  • loop thousands of times
    • begin transaction
    • execute some of the prepared statements
    • commit transaction
1 Like

I've been looking for ways to do this myself, and the conclusion I came to is that caching prepared statements yourself tends to lead to fights with the borrow-checker, and the prepare_cached() exists for this exact reason.

Unfortunately, if I recall correctly, it matches them on the query string, which is kind of sub-optimal. Someone(tm) should add an option to allow it to store prepared statements in an hashmap instead, key'd on integer values that are allocated and returned to the application.

rusqlite uses lru_cache internally (should be very performant), the cache's capacity can be adjust by calling set_prepared_statement_cache_capacity.

NB: The example above works also by just replacing prepare with prepare_cached.

1 Like

lru_cache hashes keys using the RandomState hasher to match keys, which is HashMap's hasher.

1 Like

So this looks like rusqlite's limitation. But before I look for alternatives, I looked into contacting the project's community and look at that, there is an issue on github already!


Quoting the issue:

Yes, you should use prepare_cached .
The downsides are (non-exhaustive):

  • cache lookup,
  • cache may not be correctly sized,
  • same query/sql can be cached only one at a time (you can use SQL comment as a workaround).

Another solution is to create transaction manually ( db.execute_batch("BEGIN"); ... db.execute_batch("COMMIT"); )

So I guess the limitation is by design. But there is a dirty workaround that I'll try to follow: writing raw SQL statements for BEGIN/END TRANSACTION.

Thanks everyone for helping!

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.