SQLite custom type support

[etienneasln]

Hello,

I am a new developper to SQLite, Rust and Diesel. From my understanding, it is possible to create a table in SQLite with the following query, with custom types that are internally stored by SQLite as a type that it recognizes:
CREATE TABLE example(column1 customtype1, column2 customtype2);

When I try to do this for diesel, running a migration for the creation of this table, the initial creation of the table works, but running the diesel migration redo command fails with the error message Unsupported type: 'customtype1'. The same error message comes from running diesel print-schema.

I know the usual solution would be to modify the SQL create query to be a type directly supported by SQLite, but I was wondering if there is a workaround solution for these custom types. The reason I am asking this is because I am working on a sqlite database (containing such custom SQLite types) that has already been created by other developers (who did not use Diesel to build its content) and I wanted to use diesel print-schema on it. This is a first step to my project, and I will probably replicate the creation of the database later on using diesel, but I still wanted to start off with this, if it is indeed possible.

When I was looking at the documentation, I did read up on the sql_types module and the possibility to implement a custom sql type, but from my understanding this is relevant to how you want to represent the SQL data in Rust (on the Diesel side) and therefore not really relevant to my problem.

I hope I was clear, and thanks in advance for your feedback!

[weiznich]

At the time of writing diesel print-schema (and any other subcommand of the CLI tool that emits the schema as table! macro) does not support this as it uses a hard coded list of types. You can find this list here:

diesel/diesel_cli/src/infer_schema_internals/sqlite.rs

Lines 322 to 379 in 58be152

	pub fn determine_column_type(
	conn: &mut SqliteConnection,
	attr: &ColumnInformation,
	table: &TableName,
	primary_keys: &[String],
	config: &PrintSchema,
	) -> Result<ColumnType, crate::errors::Error> {
	let mut type_name = attr.type_name.to_lowercase();
	if type_name == "generated always" {
	type_name.clear();
	}
	let path = if is_bool(&type_name) {
	String::from("Bool")
	} else if is_smallint(&type_name) {
	String::from("SmallInt")
	} else if is_bigint(&type_name) {
	String::from("BigInt")
	} else if type_name.contains("int") {
	let sqlite_integer_primary_key_is_bigint = config
	.sqlite_integer_primary_key_is_bigint
	.unwrap_or_default();
	if sqlite_integer_primary_key_is_bigint
	&& column_is_row_id(conn, table, primary_keys, &attr.column_name, &type_name)?
	{
	String::from("BigInt")
	} else {
	String::from("Integer")
	}
	} else if is_text(&type_name) {
	String::from("Text")
	} else if is_binary(&type_name) {
	String::from("Binary")
	} else if is_float(&type_name) {
	String::from("Float")
	} else if is_double(&type_name) {
	String::from("Double")
	} else if type_name == "datetime" || type_name == "timestamp" {
	String::from("Timestamp")
	} else if type_name == "date" {
	String::from("Date")
	} else if type_name == "time" {
	String::from("Time")
	} else {
	return Err(crate::errors::Error::UnsupportedType(type_name));
	};
	Ok(ColumnType {
	schema: None,
	rust_name: path.clone(),
	sql_name: path,
	is_array: false,
	is_nullable: attr.nullable,
	is_unsigned: false,
	max_length: attr.max_length,
	})
	}

The only workaround that comes to my mind is to filter out the relevant tables via the CLI flags and later manually write a table! definition for these. Yes I know that's rather cumbersome to do that, but at least you can do it.

Now we have some ideas how to make this mapping more flexible by essentially let users specify/customize it via their diesel.toml file. That's nothing we've started to implement yet, and at least I personally do unfortunately also not have the capacity to do that yet.

[etienneasln]

Ok I see, thank you for your answer. I was asking for the general case with a random custom type, but actually in my case (I probably should have specified this in my first post), the custom type string is actually 'serial' (which exists in PostgreSQL). I know this is less than ideal and very specific to my problem, but can I somehow use the diesel code for Postgresql interaction (in complement of the code for sqlite interaction) to support print-schema with 'serial' type?

[etienneasln]

Also, very uneducated remark, it's probably not that simple, but for this piece of code (the else branch): https://github.com/diesel-rs/diesel/blob/58be1522122694406886e21f8a8b39137f876090/diesel_cli/src/infer_schema_internals/sqlite.rs#L367C8-L367C64 , can't you simply just return like a String::from("Text") or String::from("Binary") (or other) like a default type? I'm not sure which type would be the ideal default type in this case.

[weiznich]

I know this is less than ideal and very specific to my problem, but can I somehow use the diesel code for Postgresql interaction (in complement of the code for sqlite interaction) to support print-schema with 'serial' type?

That unfortunately won't work as the relevant type matching code is highly backend specific and tightly coupled to the way we read these information from the database.

can't you simply just return like a String::from("Text") or String::from("Binary") (or other) like a default type? I'm not sure which type would be the ideal default type in this case.

Technically that wouldn't be that hard. The implication of such a change is that we could never add a new type there without that being a breaking change. That's something we want to avoid for rather obvious reasons

The better fix for that is to allow you to specify the mapping as kind of hash map in your diesel.toml by essentially saying "If diesel-cli encounters the following type string map it to that diesel sql type". This would solve that particular use case and a few other related ones. I'm happy to provide more details on what needs to be actually done to implement this, but I don't have the time to do it myself

[etienneasln]

Ok, I see, thank you for your detailed answers, and your work in general.