Custom type that returns BigInt or Nextval('xx') if None

[rxdiscovery]

Hello,

I am soliciting your help after several unsuccessful attempts, I want to create a new Diesel type, which has a special feature, this type encapsulates an Option value :

pub struct AutoIncrementID(Option<i64>);

• if it contains Some(xx) => it writes a BigInt
• and if it contains None => it writes nextval('sequence').

here is the code for the ToSql implementation of this type :

#[derive(Debug, Clone, Copy, PartialEq, Hash, Eq, FromSqlRow, AsExpression, serde::Serialize, serde::Deserialize)]
#[diesel(sql_type = diesel::sql_types::BigInt)]
pub struct AutoIncrementID(Option<i64>);

impl AutoIncrementID {
    pub fn new(value: Option<i64>) -> Self {
        AutoIncrementID { 0: value }
    }
}

impl Default for AutoIncrementID {
    fn default() -> Self {
        Self(Default::default())
    }
}

implToString for AutoIncrementID {
    fn to_string(&self) -> String {
        match self.0 {
            Some(v) => v.to_string(),
            None => String::new(),
        }
    }
}


impl ToSql<diesel::sql_types::BigInt, diesel::pg::Pg> for AutoIncrementID
where
//  DB: Backend,
//i64: ToSql<diesel::sql_types::BigInt, diesel::pg::Pg>,
{
    fn to_sql<'a>(&'a self, out: &mut Output<'a, '_, diesel::pg::Pg>) -> serialize::Result {
        println!("run to sql");
        match self.0 {
            Some(ref id) => ToSql::<diesel::sql_types::BigInt, diesel::pg::Pg>::to_sql(id, out),
            None => {
                out.write_all(b"nextval('acc_seq')")?;
                Ok(diesel::serialize::IsNull::No)
            }
        }
    }
}

impl<DB> FromSql<diesel::sql_types::BigInt, DB> for AutoIncrementID
where
    DB: Backend,
    i64: FromSql<diesel::sql_types::BigInt, DB>,
{
    fn from_sql(bytes: DB::RawValue<'_>) -> diesel::deserialize::Result<Self> {
        match i64::from_sql(bytes) {
            Ok(v) => Ok(AutoIncrementID(Some(v))),
            Err(x) => Err(format!("Unrecognized variant : {}", x).into()),
        }
    }
}

when it's Some, it works fine, but when it's None, I get this error :

incorrect binary data format in bind parameter x

Thank you in advance for your help..

[weiznich]

ToSql is fundamentally the wrong abstraction for this kind of behavior. It's only responsible for serializing bind data into a format the database understands. You cannot pass any SQL from this trait.

If you want to implement exactly this behavior the right way is to implement a custom query DSL node. That requires usually to implement at least QueryFragment (describes how to translate something to SQL), QueryId describes how to SQL part interacts with caching prepared statements and Expression describes the SQL side type of your DSL node.

[rxdiscovery]

Thank you for your answer, so I'll have to give up on that and create several structures, one for selection, another for update, and a final one for insertion. 👍

[weiznich]

If you don't need to specify the sequence in diesel because you are able to set a default value on the SQL side, you don't need to do any special handling there. You can just pass a None value to insertable in this case and it will automatically use the database side default value for that field. See the relevant guide for details.