Diesel + Postgres + Rocket example

[ynhhoJ]

Where i can find an example where i can see how i can use pools for diesel as in code below?

Now i have this:

pub fn establish_connection() -> PgConnection {
    dotenv().ok();

    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    PgConnection::establish(&database_url)
        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}

// diesel query
use schema::users::dsl::*;

users
        .filter(login.eq(user_login))
        .select(id)
        .first::<i32>(conn)
        .expect("Error gettings user ID");

Tried this:

pub async fn get_pool_estabilish_connection() -> deadpool::managed::Object<deadpool_diesel::Manager<PgConnection>> {
    dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let manager = Manager::new(database_url, Runtime::Tokio1);
    let pool = Pool::builder(manager).max_size(8).build().unwrap();

    pool.get().await.unwrap()
}

async fn main() -> Result<(), Error> {
  use schema::users::dsl::*;
  users
          .filter(login.eq("ynhhoJ"))
          .select(id)
          .first::<i32>(&get_pool_estabilish_connection().await)
          .expect("Error gettings user ID");
}

But it's doesn't work

[bikeshedder]

Sorry for the delay. I just came back from my vacation? Did you figure it out?

[ynhhoJ]

@bikeshedder, thank you for response.

I think it's related more for Rocket which uses own rocket_sync_db_pools in connection with diesel.

But i will glad if you can give me an example of establish_connection with pool's

[bikeshedder]

You should be using the aliases provided by the deadpool::postgres module rather than using deadpool directly: https://docs.rs/deadpool-diesel/0.3.1/deadpool_diesel/postgres/index.html - deadpool_diesel::postgres::Connection is an alias for deadpool::managed::Object<deadpool_diesel::Manager<PgConnection>>

It's not immediately obvious from the docs, but it actually uses deadpool_sync::SyncWrapper internally: https://docs.rs/deadpool-sync/latest/deadpool_sync/struct.SyncWrapper.html

Therefore you can't use it directly, but you need to call the interact method first which takes a closure. e.g.:

let conn = pool.get().await.expect("Database connection error");
let result = conn.interact(|conn| {
    users
        .filter(login.eq("ynhhoJ"))
        .select(id)
        .first::<i32>(conn)
        .expect("Error gettings user ID");
}).expect("Interaction with connection failed");

If you really don't want to use the interact method (which internally calls spawn_blocking) you can use the lock() method instead which returns the underlying diesel connection wrapped inside a MutexGuard. I highly recommend against using that unless you have very specific needs. You should never call into diesel from an async function without spawning first. Alice Ryhl has written a nice article about that: https://ryhl.io/blog/async-what-is-blocking/

For use within Rocket you want to create the pool once and add it as a state: https://rocket.rs/v0.5-rc/guide/state/#managed-state

pub async fn create_pool() -> deadpool_diesel::postgres::Pool {
    dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let manager = Manager::new(database_url, Runtime::Tokio1);
    let pool = Pool::builder(manager).max_size(8).build().unwrap();
    pool
}

rocket::build().manage(create_pool());

And then later inside the routes simply retrieve it:

#[get("/db-test")]
async fn count(pool: &State<Pool>) -> String {
    let conn = pool.get().await.unwrap();
    let result = conn.interact(|conn| {
        todo!("Add your DB query here");
    }).expect("DB interaction failed");
    todo!("Implement the rest of this route");
}

btw. I highly recommend creating your own error types and implementing the proper From traits so you can utilize the ?-operator. A very popular choice for that is the thiserror crate.

[ynhhoJ]

Thank you for your example and detailed answer!
I know, it's not stackoverflow, but i don't know if error what i get on compile my RUST project is my bad or it's a crate issue

Error: launching with unmanaged `deadpool::managed::Pool<deadpool_diesel::manager::Manager<diesel::pg::connection::PgConnection>>` state.
   >> Using `State` requires managing it with `.manage()`.
Error: SentinelAborts([Sentry { type_id: TypeId { t: 11701447820186996631 }, type_name: "&rocket::state::State<deadpool::managed::Pool<deadpool_diesel::manager::Manager<diesel::pg::connection::PgConnection>>>", parent: None, location: ("src/main.rs", 25, 24), default: true }])

Cargo.toml

[dependencies]
argon2 = "0.3.1"
chrono = "0.4"
deadpool-diesel = { version = "0.3.1", features = ["postgres"] }
diesel = { version = "1.4.8", features = ["postgres", "chrono"] }
dotenv = "0.15.0"
jsonwebtoken = { version = "8.0.0-beta.6" }
lazy_static = "1.4.0"
rand_core = { version = "0.6.3", features = ["std"] }
rocket_contrib = "0.4.10"
rocket_cors = { git = "https://github.com/lawliet89/rocket_cors", branch = "master" }

[dependencies.rocket]
version = "0.5.0-rc.1"
features = ["json"]

[dependencies.rocket_sync_db_pools]
version = "0.1.0-rc.1"
features = ["diesel_postgres_pool"]

main.rs

#[macro_use]
extern crate rocket;

use rocket::{State, Error};
use deadpool_diesel::Runtime;
use diesel::{ExpressionMethods, QueryDsl, RunQueryDsl};
use to_do::models::User;
use to_do::schema;
use deadpool_diesel::{
  postgres::{Manager, Pool},
};
use dotenv::dotenv;
use std::env;

pub async fn create_pool() -> Pool {
  dotenv().ok();

  let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
  let manager = Manager::new(database_url, Runtime::Tokio1);

  Pool::builder(manager).max_size(8).build().unwrap()
}

#[get("/db-test")]
async fn db_test(pool: &State<Pool>) -> String { // 25
  use schema::users::dsl::*;

  let conn = pool.get().await.unwrap();
  let result = conn
      .interact(|conn| {
          users.filter(login.eq("ynhhoJ")).first::<User>(conn)
      })
      .await
      .expect("DB interaction failed");

  match result {
      Ok(user) => {
          format!("{}", user.login)
      }

      Err(e) => format!("error: {:?}", e),
  }
}

#[rocket::main]
async fn main() -> Result<(), Error> {
  rocket::build()
      .manage(create_pool())
      .mount(
          "/",
          routes![
              db_test
          ],
      )
      .mount("/", rocket_cors::catch_all_options_routes())
      .launch()
      .await
}

[bikeshedder]

That's weird indeed. create_pool() returns a Pool and it should be possible to retrieve it via the &State<Pool> argument.

Maybe the rocket macros don't play well with the generics?

You could try the Newtype pattern: https://rust-unofficial.github.io/patterns/patterns/behavioural/newtype.html

That way you could also add some helper methods. e.g. an interact method that performs the pool.get() internally, etc. p.p.