Rust: rocket with sqlx

rocket-sqlx.rs

#[macro_use] extern crate rocket;

use std::env;

use anyhow::Result;

use rocket::State;
use rocket::http::Status;

use sqlx::{Pool, Postgres};
use sqlx::postgres::PgPoolOptions;

#[derive(Debug)]
pub struct User {
    pub id: i32,
    pub name: String,
}

impl User {
    pub async fn find_by_id(id: i32, pool: &Pool<Postgres>) -> Result<User> {
        let user = sqlx::query_as!(User, "SELECT * FROM my_table WHERE id = $1", id)
            .fetch_one(&*pool)
            .await?;

        Ok(user)
    }
}

#[get("/<id>")]
async fn hello(pool: State<'_, Pool<Postgres>>, id: i32) -> Result<String, Status> {
    let user = User::find_by_id(id, &pool).await;

    match user {
        Ok(user) => Ok(format!("Hello {}!", &user.name)),
        _ => Err(Status::NotFound)
    }
}

#[rocket::main]
async fn main() -> Result<()> {
    let database_url = env::var("DATABASE_URL")?;

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&database_url)
        .await?;

    rocket::ignite()
        .mount("/", routes![hello])
        .manage(pool)
        .launch()
        .await?;

    Ok(())
}

Hi. just want to say thank you very for this snippet.

Thank you for your comment, it really made my day :-) Be sure to check out the rocket channel on matrix as well, many friendly and helpful people hang out there!

hey, what did you set in the cargo.toml file for sqlx? I mean what runtime feature did you choose?

Not sure what I was using back then, but currently my Cargo.toml contains this:

sqlx = { version = "0.5.1", features = [ "runtime-tokio-rustls", "postgres", "chrono", "macros", "migrate", "uuid", "json" ] }

May I ask why you took i32 and not u32?

Just lazyness, I'd use a uuid in practice. Bit you're right, even in this example u32 would be preferable to i32.

Many thanks for this gist 🙇

What version of rocket does this use?

Line 30 doesn't compile unless I change it to &State<Pool<Postgres>>

Hi there 😊
This just saved my day. Where do I add create table if not exists.

Line 30 doesn't compile unless I change it to &State<Pool<Postgres>>

Been trying to figure out what to change this to for a while, this worked thanks!!