Passing a MySQL connection/pool to Rocket endpoints

Hello,

I'm picking up Rocket framework to write some Rest APIs. However, I can't seem to find any documentation on how to pass my database connection to Rocket.

There was a post here a couple years ago where someone ran into the same issue, but a solution was not provided.

Here's a snippet of what I'm running:

Cargo.toml

[dependencies]
mysql = "*"
rocket = "0.5.0-rc.1"

main.rs

#[macro_use] extern crate rocket;

use std::process::exit;
use mysql::OptsBuilder;
use mysql::Pool;

#[get("/")]
fn index() -> &'static str {
    "index"
}

#[get("/world")]
fn world() -> &'static str {
    "Hello, world!"
}

#[rocket::main]
async fn main() {

    let opts = OptsBuilder::new()
        .ip_or_hostname(Some("localhost"))
        .user(Some("admin"))
        .pass(Some("pass"))
        .db_name(Some("main"));

    let pool = Pool::new(opts).unwrap();

    let conn = pool.get_conn();

    match rocket::build()
        .mount("/api", routes![world, index])
        .launch()
        .await {
            Ok(v) => v,
            Err(e) => {
                println!("{}", e);
                exit(1)
            }
        };
}

What I'm trying to figure out, is how can I get conn passed to either index or world functions. to do something like this:

#[get("/")]
fn index(db: State<PooledConn>) -> &'static str {
    // do something with db
    db.query_map(...)
    ....
    "index"
}

Everything that I've read/found seems to point to having to use Diesel ORM. Is using the ORM the only way to cleanly hook MySQL into Rocket? I'm thinking that I should be able to pass any object to the endpoints, but I can't seem to figure out how.

I assume you need to use attach with your MySQL connection pool in the same way you have to if you use diesel.

Thanks @alice - You're mention of attach lead me to this database example

which lead me to this

#[macro_use]
extern crate rocket;

use std::process::exit;

use rocket_db_pools::sqlx;
use rocket_db_pools::Database;
use rocket_db_pools::Connection;

#[derive(Database)]
#[database("main")]
struct Db(sqlx::MySqlPool);

#[get("/")]
async fn index(db: Connection<Db>) -> &'static str {
    "index"
}

#[rocket::main]
async fn main() {
    match rocket::build()
        .mount("/api", routes![index])
        .attach(Db::init())
        .launch()
        .await {
            Ok(v) => v,
            Err(e) => {
                println!("{}", e);
                exit(1)
            }
        };
}

db within the index function is of type sqlx_core::PoolConnection<sqlx_core::mysql::database::MySql> and I can see the connection being made via my DB logs.

Now I need to figure out how to use it.

Trying to use the sqlx::query! macro like in the example leads me to the following build error

error: `DATABASE_URL` must be set, or `cargo sqlx prepare` must have been run and sqlx-data.json must exist, to use query macros

Feels that I'm getting closer, just not quite there yet...

Well, the error tells you to set the environment variable DATABASE_URL, so you should do that.

ha yeah, I can try that. but that still doesn't make sense. Why the need of setting another ENV var to connect to the DB, if the connection to the DB has already been made.

I believe that environment variable is used in compilation. Sqlx will connect to your database while compiling the code to check that your SQL statements are correct.

Sqlx will connect to your database while compiling the code to check that your SQL statements are correct.

Oh Interesting, That's really good to know. Do you happen to have any doc links around that, I'd like to read up more on it if possible.

thx, I'll read over this, and let you know how it goes once I have some downtime at work.

With this example and the help from @alice - I got this to work

I couldn't get it to work with the rocket 0.5.0-rc.1 crate, I had to use what was on the master branch. I also needed rocket_db_pools which is currently only available on master too.

My Cargo.toml file ended up looking like this:

[dependencies]
mysql = "*"

[dependencies.rocket]
path = "/path/to/Rocket/core/lib"
features = ["json"]

[dependencies.sqlx]
version = "0.5.1"
default-features = false
features = ["macros", "offline"]

[dependencies.rocket_db_pools]
path = "/path/to/Rocket/contrib/db_pools/lib"
features = ["sqlx_mysql"]

Since sqlx::query! checks SQL at build time, I also had to set this environment var for building:

DATABASE_URL="mysql://<user>:<pass>@localhost:3306/main"

sqlx also has a sqlx-cli that can be used to work "offline" so that you don't need the DB to run your build

additionally, I needed to create a Rocket.toml file in the same dir as my Cargo.toml file with the following entry:

[default.databases.main]
url = "mysql://<user>:<pass>@localhost:3306/main"

created a dummy table with some data

mysql> select * from test;
+------+-------+
| id   | field |
+------+-------+
|  100 | foo   |
|  200 | bar   |
|  300 | car   |
+------+-------+

src/main.rs

#[macro_use]
extern crate rocket;

use rocket_db_pools::sqlx;
use rocket_db_pools::Database;
use rocket_db_pools::Connection;
use rocket::futures::TryStreamExt;
use rocket::serde::json::Json;

#[derive(Database)]
#[database("main")]
struct Db(sqlx::MySqlPool);

#[get("/")]
async fn index(mut db: Connection<Db>) -> Json<Vec<i32>> {

    let ids = sqlx::query!("SELECT id FROM test")
        .fetch(&mut *db)
        .map_ok(|record| record.id)
        .try_collect::<Vec<_>>()
        .await;

    let filter: Vec<_> = ids.unwrap().into_iter().filter_map(|e| e).collect();

    Json(filter)
}

#[rocket::main]
async fn main() {
    match rocket::build()
        .mount("/api", routes![index])
        .attach(Db::init())
        .launch()
        .await {
            Ok(v) => v,
            Err(e) => {
                println!("{}", e);
            }
        };
}

now hitting /api/ gets me the expected results

❯ curl localhost:8000/api/
[100,200,300]

Thanks for your help.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.