is it possible to run a async function in rust diesel transaction

Question

I am using rust diesel transaction to do some async task, now I am tried to use a new tokio runtime like this(this just a minimal demo to show the issue):

use diesel::result::Error;
use diesel::Connection;
use rust_wheel::config::db::config;
use tokio::runtime::Runtime;

#[tokio::main]
async fn main() {
    let mut connection = config::connection("TEX_DATABASE_URL".to_string());
    let _trans_result: Result<(), Error> = connection.transaction(|_connection| {
        let rt = Runtime::new().unwrap();
        Ok(rt.block_on(async { do_create_proj_trans().await }))
    });
}

async fn do_create_proj_trans() {
    println!("doing...")
}

and this code shows error like this:

thread 'main' panicked at 'Cannot start a runtime from within a runtime. This happens because a function (like `block_on`) attempted to block the current thread while the thread is being used to drive asynchronous tasks.', /Users/xiaoqiangjiang/.cargo/registry/src/mirrors.tuna.tsinghua.edu.cn-df7c3c540f42cdbd/tokio-1.32.0/src/runtime/scheduler/multi_thread/mod.rs:86:9
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

when I remove the runtime, shows error like this:

`await` is only allowed inside `async` functions and blocks
only allowed inside `async` functions and blocks

is it possible to run an async function in rust diesel transactio block? this is the Cargo.toml:

[package]
name = "rust-learn"
version = "0.1.0"
edition = "2018"

[dependencies]
tokio = { version = "1.17.0", features = ["full"] }
serde = { version = "1.0.64", features = ["derive"] }
serde_json = "1.0.64"
futures = "0.3"
tokio-stream = "0.1"
rust_wheel = { git = "https://github.com/jiangxiaoqiang/rust_wheel.git", branch = "diesel2.0" }
log4rs = "1.2.0"
log = "0.4.0"
diesel = { version = "2.1.0", features = ["postgres","64-column-tables","chrono","serde_json"] }

Answer 1

As an alternative, there is the diesel-async crate that can make your whole transaction async:

[dependencies]
diesel = { version = "2.1.1", features = ["postgres"] }
diesel-async = { version = "0.4.1", features = ["postgres"] }
tokio = { version = "1.32.0", features = ["macros", "rt-multi-thread"] }

use diesel::result::Error;
use diesel_async::pg::AsyncPgConnection;
use diesel_async::AsyncConnection;

fn connection(_: String) -> AsyncPgConnection { unimplemented!() }

#[tokio::main]
async fn main() {
    let mut conn = connection("TEX_DATABASE_URL".to_string());
    let _result: Result<(), Error> = conn.transaction(|_conn| Box::pin(async {
        // do database stuff
        do_create_proj_trans().await;
        // do database stuff
        Ok(())
    })).await;
}

async fn do_create_proj_trans() {
    println!("doing...")
}