How to implement ToSql for a new type in Sqlite?

[LennDG]

I have been trying and failing to get a simple ToSql implementation for a timestamp going in SQLite.
After much looking through docs and hitting some deadends looking at out-of-date stuff I arrived at this implementation:

use diesel::{
    deserialize::FromSqlRow,
    expression::AsExpression,
    prelude::*,
    serialize::{self, Output, ToSql},
    sql_types::{self, Text},
    sqlite::Sqlite,
};

#[derive(Queryable, Selectable)]
#[diesel(table_name = crate::db::schema::plans)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
pub struct Post {
    pub id: i32,
    pub ctime: Timestamp,
}

#[derive(Debug, FromSqlRow, AsExpression)]
#[diesel(sql_type=Text)]
pub struct Timestamp(time::OffsetDateTime);

impl ToSql<Text, Sqlite> for Timestamp {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        let v = self.0.to_string();
        <String as serialize::ToSql<Text, Sqlite>>::to_sql(&v, out)
    }
}

This does not work due to the lifetime of v being too short. I'm a bit of a noob when it comes to lifetimes and need some help understanding how I would solve this problem.

From the docs it seems that another way to get this to work would be

impl ToSql<Text, Sqlite> for Timestamp
where
    String: ToSql<Text, Sqlite>,
{
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        let v = self.0.to_string();
        out.set_value(v);
        Ok(serialize::IsNull::No)
    }
}

but that seems strange when I am pretty sure the ToSql<Text, Sqlite> should exist for String.

(I know there is a "supported" timestamp for sqlite but that is not what I am asking, #4192 is a bit too much for me and this is an issue I will have in the future for other types too)

[weiznich]

From the docs it seems that another way to get this to work would be

That's the correct way to go in this case

but that seems strange when I am pretty sure the ToSql<Text, Sqlite> should exist for String.

It exists bit it requires that &self (== the string you pass in) outlives the output passed into the function as second argument. That's not the case for your value v as it only is valid for the scope of the current function, so shorter than Output. The main reason for this behavior is that we don't want to clone strings provided from a higher level at at all, we just hand out a pointer to sqlite at some point, but for this we must ensure that the string is not dropped until then (which is somewhen after existing the to_sql() function).

[LennDG]

That makes sense.

If a newtype had a String as internal type then the first approach does work, like this:

#[derive(Debug, FromSqlRow, AsExpression)]
#[diesel(sql_type=Text)]
pub struct Test(String);

impl ToSql<Text, Sqlite> for Test {
    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
        <String as serialize::ToSql<Text, Sqlite>>::to_sql(&self.0, out)
    }
}

If I understand correctly that is because the newtype struct lives longer than the function, since it's a pointer passed into to_sql, i.e. &self?

Somewhat tangentially related:
Output is a buffer to be written to the SQLite file, is that synchronously written? i.e. if I set a Timestamp and then immediately after request it in a query it should be updated right?

[weiznich]

If I understand correctly that is because the newtype struct lives longer than the function, since it's a pointer passed into to_sql, i.e. &self?

Yas that's postly correct

Output is a buffer to be written to the SQLite file, is that synchronously written? i.e. if I set a Timestamp and then immediately after request it in a query it should be updated right?

For SQLite output is nothing that's writing to, it merely holds some references to the data that are later read by SQLite while the query is executed. It will then be written to the database file as soon as your query.execute()/load()/get_result() call returns.