Thread-safe last_insert_rowid in sqlite?

[ywegel]

I am using a SqlitePool to insert a row and return its id via this query:
"INSERT INTO ... (...) VALUES ($1); SELECT LAST_INSERT_ID() AS id;"

I am afraid, that i get into a race condition when executing multiple queries at once. Where one query overwrites the other querys' last inserted id. I checked the documentation for information, if sqlitepool uses different connections for each tokio thread but did not find something. I also found some blog posts, on how sqlite does not really insert in parallelism and waits for each insertion, but there is always someone who sais the opposit. How can i handle this? With a transaction? Or does sqlx handle this for me, and i don't need to worry about it?

[Ltrlg]

For this specific situation, if you have a recent enough SQLite, I think the documentation guarantees that INSERT INTO … (…) VALUES (…) RETURNING rowid will return the correct value (and I find it a bit more readable).

[abonander]

last_insert_rowid() should always be consistent, provided that you actually execute it on the same connection that you did the insert.

If you are making your queries like this:

// where `pool` is `SqlitePool`:
sqlx::query!("INSERT INTO ... VALUES (...)")
    .execute(&pool)
    .await?;

let id = sqlx::query_scalar!("SELECT last_insert_rowid()")
    .fetch_one(&pool)
    .await?;

This will not be consistent as the two calls may potentially use different connections. You should either ensure you execute the two queries with the same connection (using either pool.acquire() for a connection or pool.begin() for a transaction), or better yet, use INSERT INTO ... RETURNING rowid as @Ltrlg suggested so you only need a single query.