Rocket - how do I pass a mysql connection to endpoint controller?

[cbll]

Hi, first time poster here so apologies for bad code; trying to learn Rust. I'm a somewhat competent Go-programmer, which might explain me trying to slam down Rust code in a wrong way

I'm attempting to make a simple web application: It has a REST api(via Rocket), connects to a database(MySQL running locally) and there's a single endpoint. That's it!

I want to access the database connection inside the controller, I just cannot figure out how with Rocket.

Heres's my current code, pretty stuck at the moment:

#![feature(decl_macro)]
#[macro_use] extern crate rocket;
#[macro_use] extern crate rocket_contrib;

use serde::Serialize;
use rocket_contrib::json::Json;

#[macro_use]
extern crate mysql;
use mysql as my;
use mysql::Pool;
use mysql::consts::ColumnType::MYSQL_TYPE_DATE;
use mysql::prelude::Queryable;

#[get("/orgs")]
fn get_orgs(conn: MyDb) -> Json<Vec<Org>> {
    // I need to have the db connection here to pass it to get_all.
    let o = match Org::get_all() {
        Ok(o) => o,
        Err(e) => panic!(e),
    };

    Json(o)
}


#[derive(Serialize)]
pub struct Org {
    pub id: Option<i32>,
    pub name: String,
}

fn main() {
    let url = "mysql://root:mysql@(localhost:33061)/rustdb";
    let pool = Pool::new(url)?;
    let mut conn = pool.get_conn();
    // I need to pass "conn" around, first to get_orgs, then to Org::get_all.
    rocket::ignite()
        .mount("/", routes![get_orgs])
        .launch();
}

impl Org {
    fn get_all(mut conn: mysql::Conn) -> Result<Vec<Org>, Err> {
        let all_orgs = conn
            .query_map(
                "SELECT id, name from organization",
                |(id, name)| {
                    Org { id, name }
                },
            )?;

        return match all_orgs() {
            Ok(all_orgs) => all_orgs,
            Err(e) => e,
        };
    }
}

So, when using #[get("/orgs")] it probably does a bunch of code generation(assumption on my side - again, I'm new!). Googling around, I found this: State - Rocket Programming Guide - which looks correct. I just cannot for the life of me figure out a working example to connect to my local mysql instance. I'm sure I am missing something very simple here -- so keen to learn from you people! I am learning Rust to benchmark it with some of my own services -- if it performs well, perhaps I have a good argument to switch

Here are my dependencies:

[dependencies]
rocket = "0.4.2"
rocket_codegen = "0.4.2"
rocket_contrib = "0.4.2"
serde = {version = "1.0", features = ["derive"]}
serde_json = {version = "1.0"}
mysql = "*"

Once again, I'm just learning Rust so any critique to the code itself I wrote rather than the domain problem is also welcome!

[ZiCog]

I do hope someone can shed some light on this. I have been using Rocket with rust-postgres and after a nearly a year of looking at the docs I don't begin to fathom how to integrate a postgres connection pool into Rocket.

So far what I have working is to put "connect()" function into my data model structure. Then every request handler can open a connection for itself. Hardly ideal.

It does not help that I use ssl to make the connection so I can't just configure a database url in rocket.toml as shown in the docs, I need to specify the location of certs and key files as well.

[cbll]

I was thinking of a hack like that just to make it work, but it doesn't fit my use-case as I want to do load testing benchmarks of Go versus Rust - so I have to have an initialized database connection beforehand as in Go, otherwise I can't compare :-/

[alice]

If you get no answer here, I recommend opening an issue on their github.

[cbll]

I was thinking of just that; I think it would suit the repository with examples very well to have a "full" example of an endpoint with a database connection. It is, after all, a very common use-case in the domain you'd use rocket in.

[ZiCog]

Arrrgh ... I just took another look at the Rocket docs on databases: State - Rocket Programming Guide

Stalled immediately at "In your application's source code, create a unit-like struct with one internal type. " and the example:

#[database("sqlite_logs")]
struct LogsDbConn(diesel::SqliteConnection);

I have no idea what I'm looking at anymore. I cannot imagine how any of that is going to open a connection to my postgres using ssl.

Then I'm linked to contrib documentation : rocket_contrib::databases

Which repeats what I have already read and then immediately becomes unfathomable ....

[alice]

It would be nice to see what the database macro expands to.

[blonk]

You can manage() types to provide shared access to objects.

This is some snippets from a Rocket + sqlite3 test project:

use rusqlite::types::ToSql;
use rusqlite::{params, Connection, Error};

#[get("/")]
fn hellodb(
  db: State<Mutex<Connection>>
) -> Result<String, Error> {
  let conn = db.lock().expect("shared state lock");

  conn.query_row("SELECT name FROM entries", &[] as &[&dyn ToSql], |row| {
    row.get(0)
  })
}

fn main() {
  let path = "./rockettest.sqlite";
  let conn = Connection::open(&path).unwrap();
  // ...
  rocket::ignite()
    .mount("/", StaticFiles::from("static"))
    .mount("/", routes![helldb])
    .manage(Mutex::new(conn))
    .launch();

Note that the State<> matches on type, so you can't manage the same type more than once. Either put the pool in a shared state buffer or pass the pool around.

I have an application that has user session connections in the backend, I use a HashMap to map session cookies to a connection context buffer for that, but the basic mechanism (using manage() is the same).

Edit: Accessing the managed shared state from a request guard, for completeness:

impl<'a, 'r> FromRequest<'a, 'r> for Session {
  type Error = MyError;

  fn from_request(req: &'a Request<'r>) -> request::Outcome<Session, MyError> {
    let dbconn = req.guard::<State<Mutex<Connection>>>().unwrap();
    // ...
  }
}

[cbll]

I appreciate the response, I've read that example but still have little idea of how to apply it to my example with mysql. I'm fairly clueless and thus looking for a full example that actually compiles(I've got a bunch of snippets/suggestions, but stitching them together is meaningless for me )

[inejge]

@ZiCog

It does not help that I use ssl to make the connection so I can't just configure a database url in rocket.toml as shown in the docs

Is it not possible to add cert/key file locations as parameters to the connection url?

postgres://host:port/dbname?sslmode=require&sslcert=/path/to/cert.pem&sslkey=/path/to/key.pem

[alice]

This is why I hate connections strings. Their syntax always so extremely poorly documented.

[ZiCog]

Wow, thanks.

But also "wow, how would anyone ever guess that?"

I guess a hardened postgres user would. I am not, I am just using postgres through Rust and Rocket for the first time.

In fact I'm not even a postgres user, I'm using CockroachDB, which interoperates with popstres clients.

I don't recall seeing such a thing in all the years I have used Cockroach.

I really would not like to see such things in my code. I'd prefer something that looks like my existing postgress connection:

    fn connect(&self) -> postgres::Client {
        let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();

        builder.set_ca_file("/somewhere/certs/ca.crt").unwrap();
        builder
            .set_certificate_chain_file("/somewhere/certs/client.root.crt")
            .unwrap();
        builder
            .set_private_key_file("/somewhere/certs/client.root.key", SslFiletype::PEM)
            .unwrap();

        let connector = MakeTlsConnector::new(builder.build());

        let mut client =
            postgres::Client::connect("postgres://me@myserver.com:26257", connector)
                .unwrap();

        client.execute("USE mydb", &[]).unwrap();
        client
    }
}

[drmason13]

I too struggled to connect a database using rocket. I think in fairness to rocket, that it is actively being developed and they're still feeling out the API for dB connections:

I think they'd appreciate an issue ... just seen what looks like an issue from OP, thanks