Correct way to deserialize data using diesel

help
1

Hi everyone, how are you?

I would like to understand some points, I have a 1 to N relationship of a table called Agents and Tasks

This is my schema.rs

// @generated automatically by Diesel CLI.

diesel::table! {
    agent (id) {
        id -> Text,
        address -> Text,
        port -> Integer,
    }
}

diesel::table! {
    task (id) {
        id -> Integer,
        task_name -> Text,
        agent_id -> Text,
    }
}

diesel::joinable!(task -> agent (agent_id));

diesel::allow_tables_to_appear_in_same_query!(
    agent,
    task,
);

and in my Task Model, I have two structs, a TaskDTO and a Task, correct me but the first question is this, are the macro patterns correct in each struct? Since from what I understand DTO is what interfaces the data to the database and the Task is what will represent the data in the application.

use serde::{Serialize, Deserialize};
use diesel::sqlite::SqliteConnection;
use diesel::{
    prelude::*, Queryable, Insertable, Selectable
};
use crate::schema::task::{self, dsl::*};
use crate::schema::agent::{self, dsl::*};
use super::agent::Agent;


#[derive(Queryable, Serialize, Deserialize)]
pub struct Task {
    pub task_name: String,
    pub agent_id: String,
}

#[derive(Queryable, Serialize, Deserialize)]
pub struct TaskByAgent {
    pub task_name: String,
}

#[derive(Insertable, Serialize, Deserialize, Selectable)]
#[diesel(belongs_to(AgentDTO, foreign_key = agent_id))]
#[table_name = "task"]
pub struct TaskDTO {
    pub task_name: String,
    pub agent_id: String,
}

impl Task {
    pub fn insert(new_task: TaskDTO, conn: &mut SqliteConnection) -> QueryResult<usize> {
        diesel::insert_into(task)
            .values(&new_task)
            .execute(conn)
    }

    pub fn find_all(conn: &mut SqliteConnection) -> QueryResult<Vec<Task>> {
        task.select((task_name, agent_id))
        .load::<Task>(conn)
    }
    
    pub fn find_by_agent_id(other_agent_id: String, conn: &mut SqliteConnection) -> QueryResult<Vec<TaskByAgent>> {
        task.filter(agent_id.eq(other_agent_id))
            .select(task_name)
            .load::<TaskByAgent>(conn)
    }

}

An example of logging this data, where I am retrieving the entire table

[
  {
    "task_name": "test_task",
    "agent_id": "9213fe7a-c4c6-4d4b-9e92-ceb8c574363b"
  },
  {
    "task_name": "test_task#2",
    "agent_id": "0fb81f6f-4471-4145-974c-96904b71aa18"
  },
  {
    "task_name": "test_task#3",
    "agent_id": "0fb81f6f-4471-4145-974c-96904b71aa18"
  },
  {
    "task_name": "test_task#4",
    "agent_id": "0fb81f6f-4471-4145-974c-96904b71aa18"
  }
]

I just want to find the tasks of a given agent using its uuid, so I created a function that returns only the task_name column and ignores the others, and I started having an error in the conn variable where it says

he trait bound TaskByAgent: Queryable<diesel::sql_types::Text, _> is not satisfied
the trait Queryable<(_,), _> is implemented for TaskByAgent
for that trait implementation, expected (_,), found diesel::sql_types::Text
required for TaskByAgent to implement FromSqlRow<diesel::sql_types::Text, _>
required for diesel::sql_types::Text to implement load_dsl::private::CompatibleType<TaskByAgent, _>
required for SelectStatement<FromClause<table>, SelectClause<task_name>, NoDistinctClause, WhereClause<Grouped<...>>> to implement LoadQuery<'_, _, TaskByAgent>


#[derive(Queryable, Serialize, Deserialize)]
pub struct TaskByAgent {
    pub task_name: String,
}

    pub fn find_by_agent_id(other_agent_id: String, conn: &mut SqliteConnection) -> QueryResult<Vec<TaskByAgent>> {
        task.filter(agent_id.eq(other_agent_id))
            .select(task_name)
            .load::<TaskByAgent>(conn)
    }

In my head the problem was finding a compatible struct to deserialize the data coming from the bank, that's why I created this TaskByAgent but I'm still having an error, I tried to follow some solutions suggested by the AI, but it didn't reach something acceptable in my opinion, could you help me understand this?

2

Seems like you hit a rather unfortunate edge case in diesels deserialization framework. Diesel maps tuples to structs, so your query is expected to return a tuple if you map it to a struct that implements Queryable. That's not the case here as you only return a single column. You can easily change it by writing .select((task_name, )) (note the extra set of parenthesis + the extra ,).

You can generally prevent this kind of issue by:

  • Always annotating your structs with #[derive(Queryable, Selectable)]
  • Always having #[diesel(check_for_backend(Sqlite))] on the struct itself to get better error messages for type mismatches
  • Always using .select(YourStruct::as_select()) to have a matching select clause.
4 Likes
3

Thanks brother, just add a extra set of parenthesis plus comma, worked!

but when you say about macros on my struct, help to understand, When we are dealing with databases, we have two or more structs, but initially two, one that will interface/insert data into the database (DTO) and the other that will bring this data to be displayed on the screen for the user. When I use the Selectable trait, it requires me to have #[diesel(table_name= <name_here>)] and isn't this for the DTO struct?

4

Queryable and Selectable are only useful for structs that load data from the database. The former describes how to map a query result to your struct and the later describes how to construct a select clause that matches your query. They are not relevant for structs that are only used to insert or update data in your database.

More general speaking: Diesel just does not have this strict: One struct/type maps to one table model. It's more like: Just use structs/types as it matches your input/output requirements.

1 Like
5

The use of #[diesel(table_name=..) on a struct that load data from database is a good practice? I was analysing some codes in github to find out how to solve this issue that you help me, and what else I noticed was a the use of only in struct to insert data to database

6

The attribute is required if your struct is not named like your table. So if you have a struct User the derive macros expect that there is a table named users in scope. The rules there are literally: Struct name in lower case + s. In any other case you need to use the attribute.

For the case that the names match it's a matter of taste whether or not you should add this attribute.

1 Like
7

ahhhhhhhhhhh! Thanks brother!

8

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.