SQLite TEXT data types specialization

[billy1624]

Motivation

Data types such as DATE, DATETIME, UUID... etc. All of them will be written as TEXT data type when creating SQLite column.

sea-query/src/backend/sqlite/table.rs

Lines 42 to 90 in bbd1c1e

	match column_type {
	ColumnType::Char(length) => match length {
	Some(length) => format!("text({})", length),
	None => "text".into(),
	},
	ColumnType::String(length) => match length {
	Some(length) => format!("text({})", length),
	None => "text".into(),
	},
	ColumnType::Text => "text".into(),
	ColumnType::TinyInteger | ColumnType::TinyUnsigned => "integer".into(),
	ColumnType::SmallInteger | ColumnType::SmallUnsigned => "integer".into(),
	ColumnType::Integer | ColumnType::Unsigned => "integer".into(),
	ColumnType::BigInteger | ColumnType::BigUnsigned => "bigint".into(),
	ColumnType::Float => "real".into(),
	ColumnType::Double => "real".into(),
	ColumnType::Decimal(precision) => match precision {
	Some((precision, scale)) => format!("real({}, {})", precision, scale),
	None => "real".into(),
	},
	ColumnType::DateTime => "text".into(),
	ColumnType::Timestamp => "text".into(),
	ColumnType::TimestampWithTimeZone => "text".into(),
	ColumnType::Time => "text".into(),
	ColumnType::Date => "text".into(),
	ColumnType::Interval(_, _) => "unsupported".into(),
	ColumnType::Binary(blob_size) => match blob_size {
	BlobSize::Blob(Some(length)) => format!("binary({})", length),
	_ => "blob".into(),
	},
	ColumnType::VarBinary(length) => format!("binary({})", length),
	ColumnType::Boolean => "boolean".into(),
	ColumnType::Money(precision) => match precision {
	Some((precision, scale)) => format!("integer({}, {})", precision, scale),
	None => "integer".into(),
	},
	ColumnType::Json => "text".into(),
	ColumnType::JsonBinary => "text".into(),
	ColumnType::Uuid => "text(36)".into(),
	ColumnType::Custom(iden) => iden.to_string(),
	ColumnType::Enum { .. } => "text".into(),
	ColumnType::Array(_) => unimplemented!("Array is not available in Sqlite."),
	ColumnType::Cidr => unimplemented!("Cidr is not available in Sqlite."),
	ColumnType::Inet => unimplemented!("Inet is not available in Sqlite."),
	ColumnType::MacAddr => unimplemented!("MacAddr is not available in Sqlite."),
	ColumnType::Year(_) => unimplemented!("Year is not available in Sqlite."),
	ColumnType::Bit(_) => unimplemented!("Bit is not available in Sqlite."),
	ColumnType::VarBit(_) => unimplemented!("VarBit is not available in Sqlite."),
	}

This result in a "data lost": where sea-schema failed to discover the original data type of a column. E.g. A DATETIME column will be treated as TEXT.

Proposed Solutions

Given that SQLite is essentially "typeless", we can simply write the specialized TEXT data types directly:

• ColumnType::DateTime => "datetime"
• ColumnType::Timestamp => "timestamp"
• ColumnType::TimestampWithTimeZone => "timestamp"
• ColumnType::Time => "time"
• ColumnType::Date => "date"
• ColumnType::Json => "json"
• ColumnType::JsonBinary => "json"
• ColumnType::Uuid => "uuid"

[billy1624]

Should we? @tyt2y3

[Sytten]

That is essentially what the other orms and query builders do. One thing that would be nice would be to document that so people dont have to read the source code to get the mapping (looking at you diesel 😅)

[ikrivosheev]

@billy1624 this is a cool idea! But breaking changes. Well, on the other hand we have been talking about this for a long time and I saw similar issues.
If type names are ok I can implement this.

[tyt2y3]

+1 on this proposal, and yes it would be nice if we can put a table in our rustdoc.

[billy1624]

I'd prefer adding a doctest to each methods in ColumnDef. A minimal demo the showcase the resulting SQL for each db backend. See #566

[billy1624]

Hey @ikrivosheev, yes, please. Your help is greatly appreciated!! You can push commits directly to #566 :)

[Sytten]

FYI while I was building the diesel integration, I saw that UUID is written as text(36) but no engine stores them as text, they all store them as a Blob:

• https://github.com/launchbadge/sqlx/blob/main/sqlx-sqlite/src/types/uuid.rs#L25
• https://github.com/rusqlite/rusqlite/blob/master/src/types/value.rs#L59

[tyt2y3]

Push. This is an issue we'd like to tackle once and for all.
Noted. That means UUID should actually be text(16).

[jondot]

We also need this, looks like that generating entities w/sqlite isn't working so not usable (because information gets lost)

[tyt2y3]

Closed via #735