TimestamptzSqlite in SQLite schema breaks diesel migration (or gets overwritten)

[badrihippo]

I've been trying to set up Diesel with the SQLite backend, and using chrono's Datetime<Utc> for timestamp fields. I think there's an issue with the way "times with timestamps" is set up in SQLite, but since I'm new to Diesel it might also be something wrong I'm doing on my end. So I decided to ask here first!

The context

• SQLite doesn't natively support timestamp "fields". Instead, they suggest you use a text or number field and use SQLite's "Date And Time" functions to parse it. Diesel does this (or its own implementation?) using the Timestamp data type.
• Conversion from chrono's Datetime<Utc> is not supported by Diesel's Timestamp type, but it is supported for SQLite with the TimestamptzSqlite class introduced in add support for timestamp with timezone for sqlite DB #3087

But TimestamptzSqlite is (obviously) not a standard SQLite field. As discussed in this thread, SQLite allows us to define any random name for the field, like

sqlite> CREATE TABLE t4(a INT, b TIMESTAMPTZLAKSJDFLAKJ);

and it would be saved internally as a text field, but it was decided not to implement it in this case.

@weiznich's suggested how to handle this in #3320 (comment):

Which currently you have to change manually in the schema since there is no inference for it.

That's how it's currently designed as we have no way to infer the correct type here. See the corresponding discussion here for details: #3087 (comment) There is just no straight forward way to automatically infer whether a column supports time zones or not by only knowing the type name. That all written: I'm open for suggestions here.

My setup

All the below code is using the following Cargo.toml:

[package]
name = "diesel-sqlite"
version = "0.1.0"
edition = "2021"

[dependencies]
diesel = { version = "2.2.2", features = ["sqlite", "chrono"] }

Option one: write the SQL, then edit the schema

Following the Diesel tutorial, I first handwrote my SQL:

migrations/2024-xxxx-initial/up.sql

-- Your SQL goes here
CREATE TABLE `things`(
	`id` INTEGER PRIMARY KEY NOT NULL,
	`name` TEXT NOT NULL,
	`last_updated_at` TIMESTAMP NOT NULL
);

I did diesel migration run on this, and then, going with the suggestion from the above comment, I edited my schema.rs to use TimestamptzSqlite:

src/schema.rs

// @generated automatically by Diesel CLI.

diesel::table! {
    things (id) {
        id -> Integer,
        name -> Text,
        // Was Timestamp; edited by me to TimestamptzSqlite
        last_updated_at -> TimestamptzSqlite,
    }
}

This works fine to start with. But when I run any subsequent diesel migrate command (eg. diesel migrate run) the TimestamptzSqlite in schema.rs gets changed back to Timestamp.

(Maybe I should disable auto-updating schema.rs if I'm manually editing it? But it was auto-generated the first time, so it seems wrong to be manually writing SQL the first time but editing the schema on subsequent rounds....)

Option 2: Write the schema, then generate the SQL

The other option is to manually make a schema.rs and generate the SQL from that:

diesel::table! {
    things (id) {
        id -> Integer,
        name -> Text,
        last_updated_at -> TimestamptzSqlite,
    }
}

Running diesel migration generate --diff-schema initial results in the following up.sql:

-- Your SQL goes here
CREATE TABLE `things`(
	`id` INTEGER NOT NULL PRIMARY KEY,
	`name` TEXT NOT NULL,
	`last_updated_at` TIMESTAMPTZSQLITE NOT NULL
);

...so Diesel decided to define its own custom field after all! Unfortunately, runnig diesel migration run throws the error:

Unsupported type: `timestamptzsqlite`

The only way to get it to run is:

• Edit up.sql to be something like TIMESTAMP instead of TIMESTAMPTZSQLITE
• Delete the test SQLite database (I'm guessing it writes the unrecognised table type there, and then is unable to handle it)
• Run diesel migration run

Under the default config, this overwrites schema.rs as well, changing TimestamptzSqlitetoTimestamp` as before.

After the migration is run, I can edit schema.rs back (but I have to keep changing it back every time I run another migrate command, unless I disable auto-updating the schema entirely).

My questions

1. What's the recommended way to handle schema-driven migration generation? Should I just disable updating schema.rs and keep manually changing TIMESTAMPTZSQLITE into a supported format in the SQL? Or is there some other way to do it?
2. It took me a while to realise how to use TimestamptzSqlite. This is probably because I'm not familiar with Diesel, but the documentation for that data type is the first place I would look. Should I update the documentation for that class to explain the recommended way to use it (i.e. manually write the schema, then autogenerate the SQL, rather than the other way round)?
3. Is TIMESTAMPTZSQLITE being written to SQL a bug? I'd imagine that it should be written as a supported type like INTEGER instead (or we should decide to support something like TIMESTAMPTZSQLITE after all and automatically use it as TimestamptzSqlite in the schema). Unless I'm handling my schema.rs wrong in my first question and this situation wouldn't come up anyway 😅

[weiznich]

What's the recommended way to handle schema-driven migration generation? Should I just disable updating schema.rs and keep manually changing TIMESTAMPTZSQLITE into a supported format in the SQL? Or is there some other way to do it?

That's on of the cases where you are currently expected to manually change the generated SQL. We explicitly do not apply these migrations automatically for exactly this reason. As for updating the schema.rs file: The recommend way for this is to use a patch file as described in the corresponding guide (See the patch_file section there for details).

It took me a while to realise how to use TimestamptzSqlite. This is probably because I'm not familiar with Diesel, but the documentation for that data type is the first place I would look. Should I update the documentation for that class to explain the recommended way to use it (i.e. manually write the schema, then autogenerate the SQL, rather than the other way round)?

We are always open to contributors that improve the documentation of something, but be warned that this case is a bit special and we generally do not like to introduce special handing for types anywhere outside of the type itself. That explicitly includes generating the schema.rs file and generating the migrations.

Is TIMESTAMPTZSQLITE being written to SQL a bug? I'd imagine that it should be written as a supported type like INTEGER instead (or we should decide to support something like TIMESTAMPTZSQLITE after all and automatically use it as TimestamptzSqlite in the schema). Unless I'm handling my schema.rs wrong in my first question and this situation wouldn't come up anyway 😅

It's currently expected behavior as we don't perform any type checking there. We literally always take the name of the type as written in your schema.rs file as we don't want to have any special casing there.

So to sum that up:

• It's currently expected to work in that way as you described it due to how features play together
• You need to manually adjust the generated migrations if you want to use them and you likely should use a patch file for your schema.rs file to automatically change the type to TimestampTzSqlite for later runs.

That all written: Obviously things could be better here. The proper solution would be to give you as a user more influence on generating these types names (in your schema.rs and in the generated migrations). That would require some extension of the configuration file (diesel.toml), which is something that nobody has spoke up to design and implement yet. Your contributions there are welcome. (Edit: There is some discussion on introducing such a configurable type-map in #4113 (comment))

[badrihippo]

Thanks for the detailed explanation! The patch_file feature is what I was missing. I think I know how to set things up now!

It's currently expected behavior as we don't perform any type checking there. We literally always take the name of the type as written in your schema.rs file as we don't want to have any special casing there.

Hmm that makes sense given you're expected to manually edit the SQL. I suppose the lesson here is SQL is the primary source of truth for your database (which is what the tutorial suggests too, and it sounds self-evident, but some frameworks like Python's Django expect you to define all your data types as classes and let the framework figure out the SQL for you).

We are always open to contributors that improve the documentation of something, but be warned that this case is a bit special and we generally do not like to introduce special handing for types anywhere outside of the type itself. That explicitly includes generating the schema.rs file and generating the migrations.

I was basically thinking of some way to suggest to new users like me how to use the TimestamptzSqlite class. Perhaps a note saying something along the lines of, "Since this type is not officially recognised in SQLite, Diesel cannot automatically infer the type. If you want to use this type, you will have to manually add it to schema.rs (possibly by using the patch_file setting)"?

Let me try it out in my project first; then I can create a PR for the documentation.

[andsmedeiros]

I've been banging my head against the wall on this for a few weeks! TBH schema patching feels a bit too hacky from a user's perspective.
I think the biggest roadblock here is how schema.rs can paradoxically be both an auto-generated file or a seed file, sometimes simultaneously in a single workflow. E.g.: As OP mentioned, diesel migration generate NAME --diff-schema && diesel migration run. This overwrites the schema, but that is also the single source of truth to begin with.

The possible solutions I can think of are:

1. Make diesel migration run --locked-schema refrain from overwriting the schema file, so schema.rs is always a single source of truth, and the user is responsible for ensuring that it is up to date with the DB. This would require the user to use the schema-driven approach, as generating schema.rs from diesel print-schema would yield a default-generated schema with standard field types.
2. Implement some type of annotation as proposed here. This would have the benefit of allowing TimestamptzSqlite fields to be created through both schema and migration-driven workflows, but seems harder to build.
3. Keep things as they are, mostly. If SQLite does not complain about TIMESTAMPTZSQLITE, implement Diesel mapping it back to TimestamptzSqlite. I honestly don't know how much effort this would take.
4. Provide a flag to diesel migration run to generate a reverse-patch. This seems even more hackish.

I don't know if I am being naive here, but am willing to help implement this.

[weiznich]

As already acknowledged above: The situation is not optimal, but it is what it is. As also pointed out above: I would be open to accept an improvement to the underlying issue by implementing this proposal.

I wouldn't like to go with any of the other suggested solutions here as they all do not really address the underlying problem, as they only "workaround" the problem you encounter.