使用Diesel操作SQLite时遇CompatibleType未实现错误求解决方案

尝试创建支持字节文件上传的SQLite数据库，在通过ID过滤数据并查询时触发如下错误：

[E0277] the trait bound (diesel::sql_types::Integer, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Binary): load_dsl::private::CompatibleType<Attachment, Sqlite> is not satisfied.
[Note] the trait load_dsl::private::CompatibleType<Attachment, Sqlite> is not implemented for (diesel::sql_types::Integer, diesel::sql_types::Text, diesel::sql_types::Text, diesel::sql_types::Binary)

数据库Schema定义 #

diesel::table! {
    MyTable (ID) {
        ID -> Integer,
        ContentType -> Text,
        AttachmentName -> Text,
        AttachmentData -> Binary,
    }
}

模型代码 #

use diesel;
use diesel::{Insertable, Queryable};
use serde::*;
use crate::schema::MyTable as file_attachment;

#[derive(Queryable)]
pub struct Attachment {
    pub ID: i32,
    pub AttachmentData: Vec<u8>,
    pub AttachmentName: String,
    pub ContentType: String,
}

#[derive(Insertable, Serialize)]
#[diesel(table_name=file_attachment)]
pub struct NewAttachment {
    pub AttachmentData: Vec<u8>,
    pub AttachmentName: String,
    pub ContentType: String,
}

出错代码（download_db_file函数） #

use diesel::connection::Connection;
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
use dotenvy::dotenv;
use serde_json::Value;
use std::env;
use std::io;
use std::io::Write;

use self::models::NewAttachment;
use self::models::Attachment as attachment_model;
use self::schema::MyTable as attachment_schema;


pub mod models;
pub mod schema;

#[allow(non_snake_case)]
pub fn establish_connection() -> SqliteConnection {
    dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    SqliteConnection::establish(&database_url)
        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}


pub fn create_db_release_activity_task_attachment(
    conn: &mut SqliteConnection,
    file: Vec<u8>,
    file_name: String,
    content_type: String,
) {
    let new_file = NewAttachment {
        AttachmentData: file,
        AttachmentName: file_name,
        ContentType: content_type,
    };
    diesel::insert_into(attachment_schema::table)
        .values(&new_file)
        .execute(conn)
        .expect("Error inserting data into database");
}
fn option_vec_to_slice(opt_vec: Option<Vec<u8>>) -> &'static [u8] {
    match opt_vec {
        Some(vec) => Box::leak(vec.into_boxed_slice()),
        None => &[],
    }
}

pub fn download_db_file(
    id: i32,
) -> io::Result<std::fs::File> {
    let connection = &mut establish_connection();
    let file_data = attachment_schema::table
        .filter(attachment_schema::ID.eq(id))
        .first::<attachment_model>(connection) //错误发生在此处
        .unwrap();
    let data = file_data.File;
    let file_name = file_data.FileName.unwrap();
    let download_dir = match dirs_2::download_dir() {
        Some(path) => path,
        None => {
            return Err(std::io::Error::new(
                std::io::ErrorKind::NotFound,
                "Download directory not found",
            ))
        }
    };
    let file_path = download_dir.join(&file_name);
    let mut file = std::fs::File::create(&file_path)?;
    let data_ref: &[u8] = option_vec_to_slice(data);
    file.write_all(data_ref)?;
    Ok(file)
}

已确认模型为公共可见，且模型与schema类型匹配，求错误解决建议。

• 修正结构体字段顺序：Diesel的Queryable trait要求结构体字段顺序必须和Schema定义的完全一致。当前Schema字段顺序是ID → ContentType → AttachmentName → AttachmentData，但Attachment结构体顺序不符，需修改为：

#[derive(Queryable)]
pub struct Attachment {
    pub ID: i32,
    pub ContentType: String,
    pub AttachmentName: String,
    pub AttachmentData: Vec<u8>,
}

• 修复字段引用错误：download_db_file中引用的file_data.File、file_data.FileName与模型字段名不匹配，需同步改为：

let data = file_data.AttachmentData;
let file_name = file_data.AttachmentName;

（模型中这两个字段不是Option类型，无需调用.unwrap()）

• 移除冗余代码：option_vec_to_slice函数是为处理Option<Vec<u8>>设计的，但模型中AttachmentData是Vec<u8>，可直接转为切片：

let data_ref: &[u8] = &data;

同时删除该函数。

• 检查版本兼容性：确保Cargo.toml中Diesel版本与SQLite适配，推荐使用稳定版本：

diesel = { version = "2.1", features = ["sqlite"] }

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