How to handle a failure to get a connection from the database pool?

Question

I'm building an API using Rocket and Diesel, and I'm managing the DbPool using Rocket's State. A search request handler might look like this, then:

#[get("/search?<term>")]
pub fn general_privileged(
  db_pool: &State<Pool>,
  _key: PrivilegedApiKey,
  term: &str,
) -> Json<Vec<SearchResult>> {
  let dao1_connection = db_pool.get().unwrap();
  let dao2_connection = db_pool.get().unwrap();
  let company_dao = CompanyDao::new(dao1_connection);
  let user_dao = UserDao::new(dao2_connection);
  let results = lib::search::general_privileged::search(term, company_dao, user_dao);
  Json(results)
}

As you can see, I'm unwrapping the connections here, which is not a good practice. In the case of a panic, it takes the service a long time to recover, which degrades performance. Obviously I could just return a `503 status instead of panicking (better!) but that's still not a great experience to have an API that frequently cannot talk to the DB.

I could use some help understanding a few things here:

1. Under what circumstances should I expect a failure to retrieve a connection from the pool; i.e. can I plan for and avoid these situations?
2. What are the recommended best practices for handling such failures?
3. What are the recommended best practices for responding to and recovering from such failures?

Answer 1

Yeah, I've definitely been 'starving' my pool by mismanaging my database connections. It's worth noting that r2d2 defaults to a connection pool with a max size of just 10. I upped mine to 32.

As @kmdreko alluded to, it's actually a bad idea to keep connections alive for a long time across multiple operations as a general rule of thumb. The code example above is better implemented as

#[get("/search?<term>")]
pub fn general_privileged(
  db_pool: &State<Pool>,
  _key: PrivilegedApiKey,
  term: &str,
) -> Json<Vec<SearchResult>> {
  let org_dao = OrgDao::new(db_pool);
  let user_dao = UserDao::new(db_pool);
  let results = lib::search::general_privileged::search(term, org_dao, user_dao);
  Json(results)
}

I didn't realize that connections are automatically returned to the pool when they fall out of scope (Rust lifetimes sure are handy!)...so an example DAO implementation should look something like this.

pub struct UserDao<'r> {
  pool: &'r Pool,
}

impl<'r> UserDao<'r>  {
  pub fn new(pool: &Pool) -> UserDao {
    UserDao { pool }
  }
}

impl<'r> IDaoSearch<User> for UserDao<'r> {
  fn search_all(&mut self, search_term: &str) -> Vec<User> {
    let connection = &mut self.pool.get().unwrap();
    User::search_by_name(connection, search_term)
  }
}

The connection is automatically returned to the pool at the end of this function, instead of being persisted throughout the lifetime of the DAO instance

Answer 2

The exact details depend on the Pool type. For example, looking at the docs for r2d2 it says:

Waits for at most the configured connection timeout before returning an error.

What should you do if you're timing out getting a database connection? It depends. You could:

• increase the number of connections in the pool
• return a 5xx error
• try again

Unfortunately, the "correct" behaviour depends a lot on your particular application.