如何在Rocket的on_ignite Fairing中正确访问数据库连接？

问题背景 #

尝试在Rocket框架的on_ignite Fairing中查询数据库并缓存结果，先后两次编写代码均出现错误：

第一次代码及错误 #

代码：

use sqlx::{ self, FromRow };
use rocket::fairing::{self, Fairing, Info, Kind};
use rocket::{Build, Rocket};
use rocket_db_pools::Connection;

use crate::database::PostgresDb;
    
#[derive(FromRow)]
struct TableRow {
  column_a: String,
  column_b: String
}

#[rocket::async_trait]
impl Fairing for TableRow {
  fn info(&self) -> Info {
    Info {
      name: "Cache table row",
      kind: Kind::Ignite,
    }
  }

  async fn on_ignite(&self, rocket: Rocket<Build>) -> fairing::Result {
    let mut db = rocket
      .state::<Connection<PostgresDb>>()
      .expect("Unable to find db connection.");

    let row = sqlx::query_as::<_, TableRow>("SELECT * FROM table WHERE id = 1;")
      .fetch_one(&mut db)
      .await
      .unwrap();

    fairing::Result::Ok(rocket.manage(row))
  }
}

错误提示：

the trait bound &mut rocket_db_pools::Connection<PostgresDb>: Executor<'_> is not satisfied
the following other types implement trait Executor<'c>:
<&'c mut PgConnection as Executor<'c>>
<&'c mut PgListener as Executor<'c>>
<&'c mut PoolConnection as Executor<'c>>
<&'t mut Transaction<'c, Postgres> as Executor<'t>>
<&sqlx::Pool as Executor<'p>>

第二次修改后的代码及错误 #

代码：

use sqlx::{ self, FromRow, Database };
use rocket::fairing::{self, Fairing, Info, Kind};
use rocket::{Build, Rocket};

use crate::database::PostgresDb;
    
#[derive(FromRow)]
struct TableRow {
  column_a: String,
  column_b: String
}

#[rocket::async_trait]
impl Fairing for TableRow {
  fn info(&self) -> Info {
    Info {
      name: "Cache table row",
      kind: Kind::Ignite,
    }
  }

  async fn on_ignite(&self, rocket: Rocket<Build>) -> fairing::Result {
    let mut db = PostgresDb::get_one(rocket).await.unwrap();

    let row = sqlx::query_as::<_, TableRow>("SELECT * FROM table WHERE id = 1;")
      .fetch_one(&mut db)
      .await
      .unwrap();

    fairing::Result::Ok(rocket.manage(row))
  }
}

错误提示：

no function or associated item named get_one found for struct PostgresDb in the current scope
function or associated item not found in PostgresDb

解决方案及正确代码 #

错误原因分析 #

1. 第一次错误：rocket_db_pools::Connection<PostgresDb>是Rocket对sqlx连接的包装类型，并不直接实现sqlx的Executor trait，无法直接传入sqlx查询方法。
2. 第二次错误：PostgresDb是Rocket通过#[database("postgres")]生成的池类型，不存在get_one方法，正确的方式是先获取池实例，再从池中获取连接。

正确实现代码 #

use sqlx::{self, FromRow};
use rocket::fairing::{self, Fairing, Info, Kind};
use rocket::{Build, Rocket};

// 假设PostgresDb是通过#[database("postgres")]宏生成的池类型
use crate::database::PostgresDb;

#[derive(Debug, Clone, FromRow)]
struct TableRow {
    column_a: String,
    column_b: String,
}

// 创建专门的Fairing结构体，避免与数据结构耦合
struct CacheTableRowFairing;

#[rocket::async_trait]
impl Fairing for CacheTableRowFairing {
    fn info(&self) -> Info {
        Info {
            name: "Cache table row",
            kind: Kind::Ignite,
        }
    }

    async fn on_ignite(&self, rocket: Rocket<Build>) -> fairing::Result {
        // 1. 从Rocket实例中获取PostgresDb的池实例
        let db_pool = rocket
            .state::<PostgresDb>()
            .expect("PostgresDb pool not initialized. Did you attach it to Rocket?");

        // 2. 从池中获取可用连接
        let mut db_conn = db_pool.acquire().await.expect("Failed to acquire database connection");

        // 3. 执行查询，db_conn直接实现了sqlx的Executor trait
        let row = sqlx::query_as::<_, TableRow>("SELECT column_a, column_b FROM table WHERE id = 1;")
            .fetch_one(&mut db_conn)
            .await
            .expect("Failed to fetch table row");

        // 4. 将结果存入Rocket全局状态，供后续请求使用
        Ok(rocket.manage(row))
    }
}

// 导出Fairing供Rocket启动时挂载
pub fn fairing() -> impl Fairing {
    CacheTableRowFairing
}

关键要点 #

• 职责分离：不要让数据结构体实现Fairing，创建专门的Fairing结构体，代码逻辑更清晰。
• 获取池实例：从Rocket的state中获取数据库池(PostgresDb)，而非单个连接，再通过acquire()方法从池中获取连接。
• 类型匹配：PostgresDb的连接实例原生实现了sqlx的Executor trait，可直接用于sqlx查询。
• 错误处理：示例使用expect做快速错误处理，实际项目中建议返回fairing::Result的错误变体，提供更友好的错误信息。

内容的提问来源于stack exchange，提问作者SinxroFozotron