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

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 :slight_smile:

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: https://rocket.rs/v0.4/guide/state/#databases - 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 :smiley:

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!

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.

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 :-/

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

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.

2 Likes

Arrrgh ... I just took another look at the Rocket docs on databases: https://rocket.rs/v0.4/guide/state/#databases

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

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

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();
    // ...
  }
}

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 :slight_smile:)

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
1 Like

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

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

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 :slight_smile: