Does last_insert_id of Sqlx return the ID of the current query?

[NielDuysters]

Hello

Sqxl Rust has a function last_insert_id() after executing an INSERT query.

E.g:

let result = sqlx::query("INSERT INTO example (a, b) VALUES (?,?)")
            .bind(&a)
            .bind(&b)
            .execute(&mysql.conn)
            .await;

match result {
           //...
            Ok(r) => {
                //....
                println!("{}", r.last_insert_id());
                //...
            }
        }

But is this trustworthy? Is this always the inserted id of the recently executed query? What if this code gets executed twice by two different users? Won't it return the ID of the wrong user if it collided?

How would I get the row ID of the query I just executed in sqlx?

[HiImJulien]

DISCLAIMER: I am still pretty new to raw SQL myself. Correct me, if I am wrong.

If you're using a database such as Postgres or MariaDB you should work with the RETURNING clause; e.g.:

INSERT INTO plumbuses (fleeb)
VALUES (?)
RETURNING id -- (or whatever column from table plumbuses you want)

Is this always the inserted id of the recently executed query?

(Assuming you're on MySQL:)

As far as I know: yes. Also, if you're bulk inserting, the value returned should point to the first inserted row (although, I am not sure, if this is still the case).

This also gives you the issue, that last_insert_id might be overwritten by a more recent query. To deal with that you could use transactions.

The following code wasn't tested; it might not work, however, it'll illustrate the idea:

let mut tx = db.begin().await?;

// Step 1: Create
sqlx::query!("INSERT INTO ...", params).execute(&mut tx).await?;
// Step 2:  Retrieve the ID
let id: i64 = sqlx::query("SELECT LAST_INSERT_ID()").execute(&mut tx).await?;
// Step 3: Execute transaction
tx.commit().await?;

Because the INSERT and SELECT are packed inside a transaction they're executed as one "atomic unit" thus no other insert takes places before your SELECT LAST_INSERT_ID() returns.

---

If my suspicion is correct that you're still using MySQL, my advice would be to switch either to MariaDB (directly compatible with MySQL) or use Postgres for the extended and new features.