sea-query
sea-query copied to clipboard
SeaQL
SQLite `TEXT` data types specialization
Motivation
Data types such as DATE, DATETIME, UUID... etc. All of them will be written as TEXT data type when creating SQLite column.
https://github.com/SeaQL/sea-query/blob/bbd1c1ea1f4eb9277654526bb61ea974781ee4b4/src/backend/sqlite/table.rs#L42-L90
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"
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 😅)
@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.
+1 on this proposal, and yes it would be nice if we can put a table in our rustdoc.
I'd prefer adding a doctest to each methods in ColumnDef. A minimal demo the showcase the resulting SQL for each db backend. See https://github.com/SeaQL/sea-query/pull/566
Hey @ikrivosheev, yes, please. Your help is greatly appreciated!! You can push commits directly to https://github.com/SeaQL/sea-query/pull/566 :)
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
Push. This is an issue we'd like to tackle once and for all.
Noted. That means UUID should actually be text(16).
We also need this, looks like that generating entities w/sqlite isn't working so not usable (because information gets lost)
