multiple value inserts to Postgres using Tokio-postgres in Rust

Question

I am using the below code to insert to a Postgres DB using tokio-postgres, is there any better option :

let members = &[obj] //obj is a struct
let mut params = Vec::<&(dyn ToSql + Sync)>::new();
let mut i = 1;
let mut qry:String = "insert into tablename(id,userid,usertype) values".to_string();
for column in members{
    if(i ==1){
        qry = format!("{} (${},${},${})",qry,i,i+1,i+2);
    }else{
        qry = format!("{}, (${},${},${})",qry,i,i+1,i+2);

    }
    params.push(&column.id);
    params.push(&column.userid);
    params.push(&column.usertype);
    i = i+3;
               
}
println!("qry : {}",qry);
let result = p.execute(&qry, &params[..]).await; //p is the pool manager

Anthracosilicosis

I'm not sure whether or not format!() is smart enough to steal the value of the first argument if it's movable. At a minimum, I'd consider using concatenation instead of rebuilding the whole string each iteration. – Pneumothorax

Thanks I think format is doing String concatenation by creating a new string and leaving the original String untouched. But since I assign the return from format to the initial variable the original String memory should get released. Else I need to do String push, will check on that as well. Thanks for the suggestion. – Anthracosilicosis

Right, the original string is dropped, which is the wasteful part (along with copying the discarded string into a new string). There's a lot of allocation/deallocation and copying for no benefit over just concatenating onto your existing string. Note also that String implements std::fmt::Write, so you should be able to use write! on the string to append a format to it. For example: write!(&mut qry, " (${})", i)? – Pneumothorax

Ok great yes that's a good point. that will definitely speed things up. Will try that. – Anthracosilicosis

as @Pneumothorax suggested changed from format! to let mut buf = String::new(); buf.write_fmt(format_args!("(${},${},${})",i,i+1,i+2)); – Anthracosilicosis

Answer 1

No:

• Inserting multiple values at the same time
• Ability to insert multiple rows by specifying multiple rows in VALUES?

You can marginally improve it by using iterators:

use itertools::Itertools; // For tuples() and format_with()

let params: Vec<_> = members
    .iter()
    .flat_map(|row| [&row.id as &(dyn ToSql + Sync), &row.userid, &row.usertype])
    .collect();
let query = format!(
    "insert into tablename(id, userid, usertype) values {}",
    (0..params.len())
        .tuples()
        .format_with(", ", |(i, j, k), f| {
            f(&format_args!("(${i}, ${j}, ${k})"))
        }),
);

However I don't really think that's better.

Downall

This is a fine approach and can insert a few 100k rows per second, but one should probably limit the amount of total query parameters to something sane. The actual limit may be 32767, but from my experience, the row throughput difference between inserting a few hundred rows and a few thousand at once is low. So for large arrays, I'd prepare one query with e.g. 512 rows and another for the tail. – Fleshy

Isn't there a concern here about security, e.g., because you're manually populating the parameters with format!() you're not properly escaping any potentially malicious input, ala xkcd.com/327? Is there a way to do the above but using tokio-postgres::Client.query(stmt, params) so you get the proper safe parameter escaping? – Profiteer

Small addition to the above code, the (0..params.len()) caused an unclear error for me with tokio_postgres v0.7.10. The fix to this was to not start with a zero, but with one: (1..params.len()+1). – Nigh

@Profiteer The above uses parameters, it does not interpolate them inside. – Downall

Answer 2

You can split your insertion data into one array per column, and pass it like this:

let result = p.execute("
    INSERT INTO tablename(id, userid, usertype)
    SELECT unnest($1::integer[]), unnest($2::varchar[]), unnest($3::my_usertype_enum[])
",
    &[&ids, &userids, &usertypes]).await;

The advantage is that you can prepare the statement, since it is a fixed query. The disadvantage I don't know.

I think it is possible to pass a single array of records to unnest(), but that would require you to define a new rust struct for the record, and implement ToSql and FromSql on it.

Noncompliance