No autocommit on "RETURNING" clauses in sqlite

[j-freimuth]

Bug Description

When using a "RETURNING" on an UPDATE query, sqlx requires a fetch command. Yet, the fetch command doesn't seem to auto commit. Returned values look correct but if the application ends without another command committing, then the value in the database is not changed.

Work around is to manually start an transaction and commit at the end.

Minimal Reproduction

Migration:

CREATE TABLE tests(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    foo VARCHAR(255) NOT NULL
);

INSERT INTO tests(foo) VALUES ('First value');

Code:

use sqlx::{Connection, SqliteConnection};

#[tokio::main]
async fn main() {
    dotenv::dotenv().unwrap();
    sqlx::migrate!("./migrations");

    does_not_work().await;
}

async fn get_connection() -> SqliteConnection {
    let connection_string = std::env::var("DATABASE_URL").unwrap();
    sqlx::SqliteConnection::connect(&connection_string)
        .await
        .unwrap()
}

async fn does_not_work() {
    let mut conn = get_connection().await;
    sqlx::query!(
        r#"UPDATE tests SET foo = ?1 WHERE id = 1 RETURNING *"#,
        "does_not_work_method"
    )
    .fetch_one(&mut conn)
    .await
    .unwrap();
}

async fn works() {
    let mut conn = get_connection().await;
    let mut tx = conn.begin().await.unwrap();
    sqlx::query!(
        r#"UPDATE tests SET foo = ?1 WHERE id = 1 RETURNING *"#,
        "works_method"
    )
    .fetch_one(&mut *tx)
    .await
    .unwrap();

    tx.commit().await.unwrap();
}

Info

• SQLx version: 0.8.0
• SQLx features enabled: "sqlite", "runtime-tokio"
• Database server and version: [REQUIRED] (MySQL / Postgres / SQLite <x.y.z>)
• Operating system: Linux Fedora Workstation 40
• rustc --version: rustc 1.79.0 (129f3b996 2024-06-10)

[abonander]

This has been fixed already by #3354 and does not reproduce on the current main.

You could use [patch] to get the fix until we get around to cutting the next release. I recommend pinning to a specific revision to be safe:

[patch.crates-io.sqlx-sqlite]
git = "https://github.com/launchbadge/sqlx"
rev = "fac53b05a4fd13529d915f1350258a81d8b372aa"

[j-freimuth]

Great, thank you!