Use Rust (sqlx) with ClickHouse Managed Postgres

DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_rust?sslmode=verify-full&sslrootcert=ca-certificate.pem"

CREATE TABLE todos (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      text NOT NULL,
    done       boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now()
);

use std::str::FromStr;

use axum::{
    Json, Router,
    extract::{Path, State},
    http::StatusCode,
    routing::{get, patch},
};
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::PgPool;
use sqlx::postgres::{PgConnectOptions, PgPoolOptions};

#[derive(Serialize, sqlx::FromRow)]
struct Todo {
    id: i64,
    title: String,
    done: bool,
    created_at: DateTime<Utc>,
}

#[derive(Deserialize)]
struct NewTodo {
    title: String,
}

type ApiResult<T> = Result<Json<T>, (StatusCode, String)>;

// Return 404 when no row matches, and 500 for any other database error.
fn db_error(err: sqlx::Error) -> (StatusCode, String) {
    match err {
        sqlx::Error::RowNotFound => (StatusCode::NOT_FOUND, "Not found\n".into()),
        err => {
            eprintln!("Database error: {err}");
            (StatusCode::INTERNAL_SERVER_ERROR, "Database error\n".into())
        }
    }
}

// Read
async fn list_todos(State(pool): State<PgPool>) -> ApiResult<Vec<Todo>> {
    let todos = sqlx::query_as::<_, Todo>("SELECT * FROM todos ORDER BY id")
        .fetch_all(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todos))
}

// Create
async fn create_todo(State(pool): State<PgPool>, Json(new): Json<NewTodo>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("INSERT INTO todos (title) VALUES ($1) RETURNING *")
        .bind(new.title)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

// Update: toggle the done flag
async fn toggle_todo(State(pool): State<PgPool>, Path(id): Path<i64>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("UPDATE todos SET done = NOT done WHERE id = $1 RETURNING *")
        .bind(id)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

// Delete
async fn delete_todo(State(pool): State<PgPool>, Path(id): Path<i64>) -> ApiResult<Todo> {
    let todo = sqlx::query_as::<_, Todo>("DELETE FROM todos WHERE id = $1 RETURNING *")
        .bind(id)
        .fetch_one(&pool)
        .await
        .map_err(db_error)?;
    Ok(Json(todo))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Load DATABASE_URL from .env, if the variable isn't already set
    dotenvy::dotenv().ok();

    // sslmode and sslrootcert come from the URL. Not sending extra_float_digits
    // lets the same code also connect through PgBouncer, which rejects it.
    let options = PgConnectOptions::from_str(&std::env::var("DATABASE_URL")?)?
        .extra_float_digits(None);
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect_with(options)
        .await?;

    // Confirm that the connection is encrypted
    let tls: Option<String> =
        sqlx::query_scalar("SELECT version FROM pg_stat_ssl WHERE pid = pg_backend_pid()")
            .fetch_one(&pool)
            .await?;
    println!("Connected to Postgres, TLS version: {}", tls.as_deref().unwrap_or("none"));

    let app = Router::new()
        .route("/todos", get(list_todos).post(create_todo))
        .route("/todos/{id}", patch(toggle_todo).delete(delete_todo))
        .with_state(pool);

    let listener = tokio::net::TcpListener::bind("127.0.0.1:3117").await?;
    println!("Listening on http://localhost:3117/todos");
    axum::serve(listener, app).await?;
    Ok(())
}