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(())
}

CREATE TABLE public.my_table (
	id int4 NOT NULL,
	"name" varchar NOT NULL
);

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

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!!