Update (not really since I never submitted the original but oh well)

This was originally a bug report, but now it is really a feature request, but I'm keeping the bug report template because I already wrote it out and I suspect others might come here thinking this is a bug.

After writing out this issue, I asked on the discord just in case, and found out that there has been some discussion of this in the past, not just for uuids but for datetime and other types as well, because sqlite doesn't support those types and they will be TEXT in the database anyway, but I still think it would be nice for type-safety to have the models be the correct types in rust anyway (though from my brief skimming of the discussion on discord, it seems like there are some drawbacks to this, at least in development of the ORM?). I'd like to continue the discussion of this here, or if it has already been started somewhere else if someone could point me to it that would be great.

Original discussion on discord for anyone interested: https://discord.com/channels/873880840487206962/900758376164757555/997017818933497967

Also I'm not really sure what to title this issue, so the title right now is kind of confusing, sorry!

Original Issue:

Description

I want to use uuids for my ids in my models, so in the migration I have

.col(ColumnDef::new(User::Id).uuid().not_null().primary_key())

but when I generate with sea-orm-cli generate entity, the generated model looks like

pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    // ...
}

which I suppose technically works because I can turn Uuids into Strings, but that's not particularly type-safe. Also I can just change the type of the id field manually, and it all seems to work, so my guess is this is not intended behavior/I am missing something, or if it is intended behavior it should probably be changed.

Steps to Reproduce

1. Create a table with a field using .uuid() in a migration
2. Generate the entities with sea-orm-cli generate entity
3. Get a struct in which the type of the field is String, not Uuid

Expected Behavior

The field is type Uuid

Actual Behavior

The field is type String

Reproduces How Often

Every time

Versions

OS: Void Linux Database: sqlite3

$ cargo tree | grep sea-
    ├── sea-orm v0.9.1
    │   ├── sea-orm-macros v0.9.1 (proc-macro)
    │   ├── sea-query v0.26.2
    │   │   ├── sea-query-derive v0.2.0 (proc-macro)
    │   │   ├── sea-query-driver v0.2.0 (proc-macro)
    │   ├── sea-strum v0.23.0
    │   │   └── sea-strum_macros v0.23.0 (proc-macro)
└── sea-orm-migration v0.9.1
    ├── sea-orm v0.9.1 (*)
    ├── sea-orm-cli v0.9.1
    │   ├── sea-schema v0.9.3
    │   │   ├── sea-query v0.26.2 (*)
    │   │   └── sea-schema-derive v0.1.0 (proc-macro)
    ├── sea-schema v0.9.3 (*)
├── sea-orm v0.9.1 (*)

Additional Information

// migration/src/m20220728_000001_create_user_table.rs

use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .create_table(
                Table::create()
                    .table(User::Table)
                    .col(ColumnDef::new(User::Id).uuid().not_null().primary_key())
                    .col(ColumnDef::new(User::Name).string().not_null())
                    .to_owned(),
            )
            .await
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .drop_table(Table::drop().table(User::Table).to_owned())
            .await
    }
}

/// Learn more at https://docs.rs/sea-query#iden
#[derive(Iden)]
enum User {
    Table,
    Id,
    Name,
}

Hey @Dash-L, thanks for filing this report! As discussed on Discord. Maybe we can add a special comment to indicate it's a uuid / date time column when creating the table.

https://dbfiddle.uk/?rdbms=sqlite_3.39&fiddle=f68c2b3786bb3c581eb6cf4c2ecadbed

Thoughts? @tyt2y3 @ikrivosheev

Thoughts? @tyt2y3 @ikrivosheev

Hello! I think this is similar to: https://github.com/SeaQL/sea-query/issues/375 We can use: https://www.sqlite.org/datatype3.html#determination_of_column_affinity

But as listed on the table datetime will be NUMERIC after affinity. And I guess uuid would become BLOB as it's not mentioned in the table.

But as listed on the table datetime will be NUMERIC after affinity. And I guess uuid would become BLOB as it's not mentioned in the table.

I mean we can rename BLOB to BLOBUUID for example. SQLite work it as BLOB and SeaORM work it as UUID.

That's hacky lol

That's hacky lol

That`s feature!))

This is larger than just sqlite. I'm running into this with Uuid and bool on mysql.

This is larger than just sqlite. I'm running into this with Uuid and bool on mysql.

Hey @WyseNynja, thanks for the report!

Currently, the boolean and uuid column types in MySQL are prepared as SQL: bool and binary(16) correspondingly. Source code at here.

ColumnType::Boolean => "bool".into(),
ColumnType::Uuid => "binary(16)".into(),

I'm not sure why bool column failed to be discovered as a boolean column. But for uuid column, I guess the only way is to look at some special column comment that stored in the schema?

I think we can start with the following SQLite ColumnType mappings?

ColumnType::DateTime => "BlobDateTime"
ColumnType::Timestamp => "BlobTimestamp"
ColumnType::TimestampWithTimeZone => "BlobTimestampWithTimeZone"
ColumnType::Time => "BlobTime"
ColumnType::Date => "BlobDate"
ColumnType::Json => "BlobJson"
ColumnType::JsonBinary => "BlobJsonBinary"
ColumnType::Uuid => "BlobUuid"

CC @ikrivosheev

I believe SeaORM should probably use numeric values to represent timestamps with sqlite - that way, we can sort and filter with them as we can with timestamps in MySQL/PostgreSQL, like here:

	let mut message_pages = MessageEntity::find()
		.filter(MessageColumn::Timestamp.between(start_of_day, end_of_day))
		.order_by_asc(MessageColumn::Timestamp)
		.paginate(db, MAX_MESSAGES_PER_PAGE);

(unless sqlite has magic for handling sorting/filtering text timestamps?)

Hey @Absolucy, I think SQLite did have some magic for comparing datetime like string.

create table tbl (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  created TEXT
);

insert into tbl (created) values
('2022-09-12 12:37:00'),
('2022-09-10 12:37:12'),
('2022-09-09 12:32:01'),
('2022-09-08 12:37:02'),
('2022-09-09 12:32:00');

select * from tbl where created between '2022-09-09 12:00:00' and '2022-09-09 16:00:00' order by created asc;

Output:

• 

Tested on https://sqliteonline.com/

Has anyone experience how other ORMs (maybe from other programming languages) deal with this issue? I would assume they derive the Entities from the Schema definition directly. Is the reason to derive it from the actual database tables in Sea-Orm so that you can quickly query existing databases?

@malteneuss this is in Rails https://github.com/rails/rails/blob/5c4a7a711068c1dff2bd873da510ddbb82dcadd7/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L73