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

help
1

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?

2

At least for MySQL - and probably for every DMBS, since this is a very common pattern - sqlx has no idea what that value is. It gets it from the response provided by the underlying DBMS.

MySQL, in turn, promises that the relevant response will contain the last insert ID, which is tracked per connection and is set based on the auto_increment field, if any, of the most recent row inserted by that connection specifically.

So the answer to your question is, the database keeps track of it for you and gives you the result for your connection. sqlx just makes that answer available as-is.

If you were using a DBMS that supported it, I'd suggest using a returning clause in the query, since it's more general and easier to understand. However, as of this writing, MySQL still does not support it.

1 Like
3

So if it is tracked per connection, it means that if:

At 13:00 user A inserts with ID 123
At 13:01 user B inserts with ID 124
At 13:02: user A executed last_insert_id the response will be 123?

4

If it functions the same as PHP’s insert_id it is okay. Does it?

5

In both cases it just sends the request to the database. And in both cases it's guaranteed to work for the MySQL and doesn't guaranteed to work for any random database.

6

I am using a MySQL database. So it's guaranteed to work the way I intent?

7

Yes. It's property of the database, not SQL bindings like SQLx for Rust or Mysqli for PHP.

And MySQL does have these guarantees that you need.

1 Like
8

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.