Add Trait for sqlx::Pool::{acquire,try_acquire}

[tp971]

Allow explicitly acquiring connections from a connection pool to ensure that a sequence of SQL queries are executed on the same connection. Right now, this only seems to be possible using sea_orm::TransactionTrait by starting a transaction, but doing so without starting a transaction would be nice.

[billy1624]

Hey @tp971, you can unwrap the SQLx connection pool from SeaORM and perform the acquire on your own.
https://docs.rs/sea-orm/latest/sea_orm/entity/prelude/enum.DatabaseConnection.html#impl-DatabaseConnection-2

[jsimpson-gro]

It looks like the link has changed. I think this is the same one:

https://docs.rs/sea-orm/latest/sea_orm/enum.DatabaseConnection.html#impl-DatabaseConnection-2

Using that API, it looks like I would have a sqlx connection, which couldn't be passed to the sea-orm APIs?

I've also run into the issue outlined by the original poster. To make the example more concrete, in Postgres it is possible to create a temporary table. When created outside of a transaction, it lasts for the life of the session, i.e. the connection. It isn't visible outside of the connection where it was created, so unless the code keeps on using the same connection, the temp table is unavailable.

Another example is Postgres advisory locks. They can have the lifetime of a transaction or session (i.e. connection). In my use case I have multiple transactions because the work commits in stages and it is intended for partial progress to be visible as the work proceeds. The advisory lock is is used so that if the worker task exits without cleaning up correctly, the advisory lock will be dropped (when the session ends, for example when the process exits if nothing else).

[jsimpson-gro]

Oops, I notice that the original poster was asking for a way to get a sqlx connection. I'm interested in a way to get a stable sea-orm connection, something that maps to the same sqlx connection for as long as it is held.

[tp971]

Maybe I should have been a bit more specific on what I asked for. My idea is implementing a way to get a connection as an object implementing ConnectionTrait so that you can use it with the sea-orm APIs. What I imagine is: TransactionTrait has a method begin which returns a DatabaseTransaction implementing ConnectionTrait (and also StreamTrait and TransactionTrait). Similar to that, I imagine something like: an AcquireTrait that returns an AcquiredConnection, implementing the same traits as DatabaseTransaction.

Also I think unwrapping the sqlx pool is kinda unidiomatic right now, because there is no get_pool method on the Sqlx{MySql,Postgres,Sqlite}PoolConnection structs. Suppose your application supports multiple DBMSs, you would have to do something like

match db {
    DbConn::SqlxMySqlPoolConnection(pool) => {
        let sqlx_pool = db.get_mysql_connection_pool();
        ...
    },
    ...
}

I think it would be better to write pool.get_pool() instead of db.get_mysql_connection_pool().

[jsimpson-gro]

Not just multiple DBMS support is complicated, also mocking.

Edit: To be clear, I understand that a mock looks like another RDBMS. The reason I mention it explicitly is it fits my use case a little more. We only use one type of database, however we test everything.

[jemmyw]

Any movement on this since it was discussed? Using something like Postgres RLS policies would be much easier if we can acquire and release the connection from the pool independently of SeaORM. Another approach would be some way to execute a callback when a pool connection is acquired and another when it is returned to the pool.

[jemmyw]

Replying to myself because this is actually possible right now. Follow the instructions here: https://www.sea-ql.org/sea-orm-cookbook/017-auto-execution-of-command-after-connection.html to get a sqlx connection options object. Then you have access to before_acquire and after_release callbacks. Use something like tokio task_local! to store the information you need to inject into the session:

task_local! {
    static USER_ID: Arc<String>;
}

    let mut sqlx_options: sqlx::pool::PoolOptions<DB> = opt
        .sqlx_pool_options()
        .before_acquire(|conn, _meta| {
            Box::pin(async move {
                let user_id = USER_ID
                    .try_with(|user_id| user_id.clone())
                    .unwrap_or_default();

                let sql = format!("SET myapp.user_id TO '{}'", user_id);
                sqlx::query(&sql).execute(conn).await?;

                Ok(true)
            })
        })
        .after_release(|conn, _meta| {
            Box::pin(async move {
                let sql = "RESET myapp.user_id";
                sqlx::query(sql).execute(conn).await?;
                Ok(true)
            })
        });
    let sqlx_pool = sqlx_options.connect(&database_url).await?;
    let pool = SqlxPostgresConnector::from_sqlx_postgres_pool(sqlx_pool);