Database access with axum-sqlx-tx

axum-sqlx-tx is an alternative, layer-based approach to using SQLx directly. One big advantage is that transactions are committed or rolled back automatically if any of the inner requests succeed or return an error.

Dependencies

[dependencies]
axum = { version = "0.5" }
axum-sqlx-tx = { git = "https://github.com/wasdacraic/axum-sqlx-tx", features = ["sqlite"] }
serde = "1"
serde_json = "1"
sqlx = { version = "0.5", features = ["runtime-tokio-rustls", "sqlite", "macros"] }
tokio = { version = "1", features = ["full"] }

Code

Unlike before, we do not have to manage the SQL pool in an std::Arc<> ourselves, hence it is enough to just create the pool

async fn new_pool() -> Result<SqlitePool, Box<dyn std::error::Error>> {
    let db_options = SqliteConnectOptions::from_str(":memory:")?
        .create_if_missing(true)
        .disable_statement_logging()
        .to_owned();

    let pool = SqlitePoolOptions::new().connect_with(db_options).await?;

    sqlx::query(
        "CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            content NOT NULL
        );",
    )
    .execute(&pool)
    .await?;

    Ok(pool)
}

and add the layer to the routes:

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let app = axum::Router::new()
        .route("/api/posts", get(posts).post(add_post))
        .route("/api/posts/:id", get(get_post))
        .layer(axum_sqlx_tx::Layer::new(new_pool().await?));

    let addr = std::net::SocketAddr::from(([0, 0, 0, 0], 3000));

    axum::Server::bind(&addr)
        .serve(app.into_make_service())
        .await?;

    Ok(())
}

Because the Tx type implements sqlx' executor interface, all we need to change is the type of the parameter:

async fn get_post(Path(id): Path<i64>, mut tx: Tx<Sqlite>) -> Json<Post> {
    Json(
        sqlx::query_as::<_, Post>("SELECT title, content FROM posts WHERE id=?")
            .bind(id)
            .fetch_one(&mut tx)
            .await
            .unwrap(),
    )
}

async fn add_post(mut tx: Tx<Sqlite>, Json(post): Json<Post>) {
    sqlx::query("INSERT INTO posts (title, content) VALUES (?, ?);")
        .bind(post.title)
        .bind(post.content)
        .execute(&mut tx)
        .await
        .unwrap();
}

async fn posts(mut tx: Tx<Sqlite>) -> Json<Vec<Post>> {
    Json(
        sqlx::query_as::<_, Post>("SELECT title, content FROM posts")
            .fetch_all(&mut tx)
            .await
            .unwrap(),
    )
}

Run

Start the server with

cargo run --bin with-sqlx-tx

and add posts with

curl -X POST -H 'Content-Type: application/json' -d '{"title": "Hello", "content": "World"}' http://127.0.0.1:3000/api/posts