How do I keep a global Postgres connection?

Question

I want to store the Postgres connection on global scope to access from any function in a module. Here is an example:

use postgres::{Client, NoTls};

static mut client: Option<Client> = None;

pub fn get_player(id: i32) {
    // Use global client connection object:
    for row in client.unwrap().query("SELECT * FROM public.\"User\" WHERE \"accountID\"=$1;",&[&id]).unwrap(){
        let id: i32 = row.get(0);
        let name: &str = row.get(1);

        println!("found player: {} {}", id, name);
    }
}

pub fn init() {
    let mut connection = Client::connect("host=localhost user=postgres", NoTls);
    match connection {
        Ok(cli) => {
            println!("Database connected.");
            client = Some(cli);
        }
        Err(_) => println!("Database ERROR while connecting."),
    }
}

It is not compiling & working as intended and I don't know how to make this in Rust.

Does this answer your question? [How do I create a global, mutable singleton?](https://stackoverflow.com/questions/27791532/how-do-i-create-a-global-mutable-singleton) — E_net4, Jul 29 '20 at 09:11
lazy_static! { static ref client: Option = None; } this gives compile error. — eminfedar, Jul 29 '20 at 09:24
For what it's worth, my recommendation is not to do this. Instead, use an `r2d2` connection pool, and pass that pool around in some kind of context object. Global state is just too much pain. — Sven Marnach, Jul 29 '20 at 09:27
@SvenMarnach I faced a [similar problem](https://stackoverflow.com/q/63144311/8990329) recently. Wrapping the `Client` into the `Mutex` and passing it whenever is required worked pretty well. Are there some drawbacks in such approach? — Some Name, Jul 29 '20 at 10:24
@SomeName My main point in my previous comment was about global state, though I didn't really make this clear. The main downside of using a `Mutex` is that there is no timeout for the `get()` method, so it's easier to run into deadlocks. If it works for your use case, it souds like a reasonable approach to me, though. — Sven Marnach, Jul 29 '20 at 10:30
Please [edit] your question and paste the exact and entire error that you're getting — that will help us to understand what the problem is so we can help best. Sometimes trying to interpret an error message is tricky and it's actually a different part of the error message that's important. Please use the message from running the compiler directly, not the message produced by an IDE, which might be trying to interpret the error for you. — Shepmaster, Jul 29 '20 at 12:45

score 4 · Accepted Answer · answered Jul 29 '20 at 12:43

Here is an example with lazy_static and r2d2_postgres that provides a database connection pool:

use r2d2_postgres::postgres::{NoTls, Client};
use r2d2_postgres::PostgresConnectionManager;

#[macro_use]
extern crate lazy_static;

lazy_static! {
    static ref POOL: r2d2::Pool<PostgresConnectionManager<NoTls>> = {
        let manager = PostgresConnectionManager::new(
            // TODO: PLEASE MAKE SURE NOT TO USE HARD CODED CREDENTIALS!!!
            "host=localhost user=postgres password=password".parse().unwrap(),
            NoTls,
        );
        r2d2::Pool::new(manager).unwrap()
    };
}



pub fn get_player(id: i32) {
    // Use global client connection object:
    let mut client = POOL.get().unwrap();
    for row in client.query("SELECT * FROM public.\"User\" WHERE \"accountID\"=$1;",&[&id]).unwrap(){
        let id: i32 = row.get(0);
        let name: &str = row.get(1);

        println!("found player: {} {}", id, name);
    }
}

How do I keep a global Postgres connection?

1 Answers1