Database access with SQLx

SQLx is an async-first SQL crate to access various SQL databases. While compile-time verification of queries is one of the outstanding features, we will use ordinary queries here to show how to use it together with axum.

Dependencies

[dependencies]
axum = { version = "0" }
serde = "1"
serde_json = "1"
sqlx = { version = "0.5", features = ["runtime-tokio-rustls", "sqlite", "macros"] }
tokio = { version = "1", features = ["full"] }

Code

In this example, we will implement a super simple blog backend, consisting of posts with a title and some content string. We accept two routes /api/post and /api/posts/, the latter used to query all existing posts and to add a new one.

As usual, import some used modules first:

use axum::extract::{Extension, Path};
use axum::response::Json;
use axum::routing::get;
use serde::{Deserialize, Serialize};
use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
use sqlx::{ConnectOptions, FromRow};
use std::str::FromStr;
use std::sync::Arc;

Now, we define a small helper struct that keeps the connection pool alive as well as an implementation to set up the (in-memory SQLite) database:

/// Database object encapsulating the connection pool and providing convenience functions.
struct Database {
    pool: SqlitePool,
}

impl Database {
    pub async fn new() -> Result<Self, 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(Self { pool })
    }
}

Let's define a struct to represent a post and add the handlers to insert and query posts:

#[derive(FromRow, Serialize, Deserialize)]
struct Post {
    title: String,
    content: String,
}

async fn get_post(Path(id): Path<i64>, Extension(db): Extension<Arc<Database>>) -> Json<Post> {
    Json(
        sqlx::query_as::<_, Post>("SELECT title, content FROM posts WHERE id=?")
            .bind(id)
            .fetch_one(&db.pool)
            .await
            .unwrap(),
    )
}

async fn add_post(Extension(db): Extension<Arc<Database>>, Json(post): Json<Post>) {
    sqlx::query("INSERT INTO posts (title, content) VALUES (?, ?);")
        .bind(post.title)
        .bind(post.content)
        .execute(&db.pool)
        .await
        .unwrap();
}

async fn posts(Extension(db): Extension<Arc<Database>>) -> Json<Vec<Post>> {
    Json(
        sqlx::query_as::<_, Post>("SELECT title, content FROM posts")
            .fetch_all(&db.pool)
            .await
            .unwrap(),
    )
}

As you can see we can re-use the same struct (and benefit from type-safety guarantees) for both serialization and deserialization in the database as well as for sending and receiving posts to and from the client by deriving the appropriate traits.

All that's left is setting up the server itself:

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let state = Arc::new(Database::new().await?);

    let app = axum::Router::new()
        .route("/api/posts", get(posts).post(add_post))
        .route("/api/posts/:id", get(get_post))
        .layer(Extension(state));

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

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

    Ok(())
}

Run

Start the server with

cargo run --bin with-sqlx

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