Rusqlite: prepared statements in transactions

[jimis]

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.

Transaction in rusqlite - Rust

Any ideas?

[naim]

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

[jimis]

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

[blonk]

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.

[naim]

@jimis

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.

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.

[naim]

@blonk

Unfortunately, if I recall correctly, it matches them on the query string

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

[jimis]

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!

Using previously prepared statements in transactions

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!