sqlite driver returns result before the actual commit

Bug Description

The result of INSERT INTO query is returned before it's actually in the DB. Lower is attached a snipped of client code. Those 2 queries are 2 different Actix endpoints.

Minimal Reproduction

sqlx::query!("INSERT INTO some_table (id, name) VALUES ($1, $2) RETURNING id", id, name).fetch_one(db_pool.get_ref()).await?.id; // => 1

sqlx::query_as!(SomeStruct, "SELECT id, name FROM some_table WHERE id = $1", id).fetch_one(db_pool.get_ref()).await?; // => sqlx::Error no row found

const result = await api({
      url: `group`,
      method: 'POST',
      data: {
        name,
      },
    });

    if (result.status === 200) {
      await getEntity(result.id);
    }

Info

• SQLx version: 0.6.2
• SQLx features enabled: ["runtime-actix-rustls", "sqlite", "migrate", "macros", "chrono"]
• Database server and version: SQLite 3.31.1
• Operating system: Ubuntu 20.04 LTS
• rustc --version: rustc 1.62.1 (e092d0b6b 2022-07-16)

This issue still exists in the current version of sqlx. I ran into it just after hitting a runtime error because of #2542.

You can work around this (#2099) issue by manually starting a transaction before running the query and committing it afterwards, but it was quite surprising to me, that the usage of a returning clause introduces two potential runtime error sources while using a compile time checked query.

Here is an example on how to work around the problem described by @vbakc:

let mut tx = pool.begin().await?;
let id = sqlx::query!(
        "INSERT INTO some_table (id, name) VALUES ($1, $2) RETURNING id",
        id,
        name
    )
    .fetch_one(&mut *tx)
    .await?
    .id;
tx.commit().await?;

Versions:

• rustc: 1.72.1
• sqlx: 0.7.2
  • features: sqlite, runtime-tokio
• libsqlite-sys: 0.26.0
  • features: bundled (SQLite 3.41.2 according to the rusqlite readme)

Could this be from .fetch_one() having weird early return behavior on SQLite? According to @abonander:

[...] .fetch_one(), which doesn't run the SQLite statement to completion. It gets the first result row and immediately returns, but SQLite probably would rather have us run sqlite3_step() until we actually get SQLITE_DONE as it may still have some work to do.

If you read the SQLite docs it clearly says to NOT do this:

If the page cache is not under memory pressure, then probably nothing will be written to disk until after the transaction completes, which is after sqlite3_step() returns SQLITE_DONE.

So by this, when you insert with RETURNING and .fetch_one() (or I think .fetch_optional() for that matter) the database starts sending back what it's "planning" to commit but hasn't yet and Sqlx doesn't wait around until it is committed.

I've recently implemented a work around by having my insert calls always use .fetch_all() which seems to have resolved this issue.

Reference:

1. https://github.com/launchbadge/sqlx/issues/1760#issuecomment-1076754865
2. https://www.sqlite.org/lang_returning.html#acid_changes