i've seen this for postgres but i am using mysql. does anyone have the code for mysql pool? how to create 100 "pool instance" for it?

[sprappcom]

can someone pls post mysql way to do pool? thx
#3232

this is my current messy way to do it with sonnet / chatgpt4 's "problem"

min_connections is not found

use std::sync::Arc;

// Define a OnceCell to hold the connection pool
static POOL: OnceCell<Arc<MySqlPool>> = OnceCell::const_new();

async fn initialize_pool() -> Result<Arc<MySqlPool>, SqlxError> {
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    println!("Connecting to database: {}", database_url);

    let mut options: MySqlConnectOptions = database_url.parse()?;
    //options = options
        //.min_connections(5) // Set minimum connections in the pool
        //.max_connections(20); // Set maximum connections in the pool
    Ok(Arc::new(MySqlPool::connect_with(options).await?))
}

[CommanderStorm]

Please see the docs: https://docs.rs/sqlx/latest/sqlx/pool/struct.PoolOptions.html#tymethod.min_connections

It exists for a Pool, but not for a connection.
The reason is that only here having >1 connection makes sense

[sprappcom]

@CommanderStorm fixed. thx!

[sprappcom]

@CommanderStorm now i have new problem.

what's the proper way to create the mysql pool?
my connection seemed to be stuck here:

use std::error::Error as StdError;
use sqlx::mysql::{MySqlPool, MySqlConnectOptions};
use sqlx::Error as SqlxError;
use tokio::sync::OnceCell;
use std::sync::Arc;

// Define a OnceCell to hold the connection pool
static POOL: OnceCell<Arc> = OnceCell::const_new();

async fn initialize_pool() -> Result<Arc, SqlxError> {
let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
println!("Connecting to database: {}", database_url);

let options: MySqlConnectOptions = database_url.parse()?;

// Create PoolOptions and set min and max connections
let pool_options = sqlx::mysql::MySqlPoolOptions::new()
    .min_connections(5)
    .max_connections(20);

Ok(Arc::new(pool_options.connect_with(options).await?))

}

pub async fn get_connection_pool() -> Result<Arc, SqlxError> {
POOL.get_or_try_init(initialize_pool).await.map(Arc::clone)
}

[CommanderStorm]

You can turn your PoolOptions into a Pool via the connect(url) (and related, such as connect_lazy) functions.
You already have that.

The code you delivered is not readable due to the formatting and I doubt that it even compiles, let alone works.
Please, work from the docs instead of trying to cut corners via GPT. Cutting corners like you are trying to won't work and will just cause more work for you and us.
If something is unclearly documented and this is why you are insisting on not reading the docs, that is a bug and feedback as such is appreciated ^^

The things that I can see from the code shown:

• static POOL: OnceCell<Arc> is not nesseary. Pool is Send, Sync and Clone.
  => no need to wrap in an Arc, double wrapping is not better ^^
• Having a static pool makes testing harder. I would suggest having as little static state as possible.
  For metrics yes, but not for the db.
• println!("Connecting to database: {}", database_url); prints the database password to the log. Some compliance frameworks forbid this. Also using a tracing library like tracing is something that I would recomend.
• In your case (since you want 5 connections to start) I would suggest starting with the lazy connection initalision.

Here is what I would start with:

use sqlx::mysql::{MySqlConnectOptions, MySqlPoolOptions, MySqlPool};

async fn try_initialize_pool() -> anyhow::Result<MySqlPool> {
    let database_url = std::env::var("DATABASE_URL")?;
    tracing::trace!("Connecting to database: {database_url}");

    let options: MySqlConnectOptions = database_url.parse()?;
    Ok(MySqlPoolOptions::new()
        .min_connections(5)
        .max_connections(20)
        .connect_lazy_with(options))
}

#[tokio::main]
async fn main()->anyhow::Result<()>{
    tracing_subscriber::fmt()
        .with_env_filter(EnvFilter::from_default_env())
        .init();
    let _pool = try_initialize_pool().await?;

    //...
    Ok(())
}

#[cfg(test)]
mod test {
    use super::*;
    // will require you to set `DATABASE_URL` as an environment variable or in a `.env` file like `sqlx::query!()` et al, to give the test driver a superuser connection with which to manage test databases.
    #[sqlx::test]
    async fn basic_test(pool: MySqlPool) -> sqlx::Result<()> {
        let foo = sqlx::query_as!(String, "SELECT name FROM foo")
            .fetch_one(&pool)
            .await?;
    
        assert_eq!(foo, "foobar!");
        
        Ok(())
    }
}

[sprappcom]

@CommanderStorm thx. will try it and revert. thx again!