SQLite: Booleans become integer columns

Description

When using SQLite, boolean data types are converted to integer.

Whilst this is somewhat fair enough (SQLite doesn't have a native boolean type; it expects you to use an integer with 0 and 1), it means that sea-orm-cli generate entity gives you a i32 field rather than a bool field, which is ... sad.

This conflicts a little bit with the 'schema-first' goal of this project, as we lose information by encoding it into the schema and then decoding it again.

See

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

Line 105 in 1467e1d

ColumnType::Boolean => "integer".into(),

.

Steps to Reproduce

Create a schema migration including a boolean type, e.g.:

        manager
            .create_table(
                Table::create()
                    .table(TimelineSegment::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(TimelineSegment::SegmentId)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(
                        ColumnDef::new(TimelineSegment::PrevGappy)
                            .boolean()
                            .not_null(),
                    )
                    .to_owned(),
            )
            .await?;

Apply the migration
Generate entities using sea-orm-cli generate entity --database-url sqlite://$(realpath ../../../devdb.sqlite3)

Expected Behavior

I should have obtained this entity:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "timeline_segment")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub segment_id: i32,
    pub prev_gappy: bool,
}

Actual Behavior

I obtained this entity:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "timeline_segment")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub segment_id: i32,
    pub prev_gappy: i32,  // <---- !!!
}

SQLite's schema shows the reason why quite clearly:

sqlite> .schema
CREATE TABLE IF NOT EXISTS "seaql_migrations" ( "version" text NOT NULL PRIMARY KEY, "applied_at" integer NOT NULL );
CREATE TABLE IF NOT EXISTS "timeline_segment" ( "segment_id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "prev_gappy" integer NOT NULL );

Reproduces How Often

Always

Versions

│   └── sea-orm v0.8.0
│       ├── sea-orm-macros v0.8.0 (proc-macro)
│       ├── sea-query v0.24.6
│       │   ├── sea-query-derive v0.2.0 (proc-macro)
│       │   ├── sea-query-driver v0.1.1 (proc-macro)
│       ├── sea-strum v0.23.0
│       │   └── sea-strum_macros v0.23.0 (proc-macro)
├── sea-orm v0.8.0 (*)
├── sea-query v0.25.2
│   └── sea-query-derive v0.2.0 (proc-macro) (*)

Using SQLite on Ubuntu.

Additional Information

If we think maintaining the integer affinity is important, we could be cheeky and call the type integerboolean or something like that, which the entity generator could recognise to mean boolean.

The algorithm at https://www.sqlite.org/datatype3.html#determination_of_column_affinity suggests that will be absolutely fine as long as the name contains 'int'.

Interesting idea. Definitely worth doing!

Hi @tyt2y3 and @billy1624
I would like to work on this issue. I also personally use SQLite and ran into this problem where I was manually converting ints to boolean, and vice-versa, in my project. So I'd like to see this feature supported.

Note, that this issue is marked "good-first-issue" and I have already worked on a "good-first-issue" last week SeaQL/sea-orm#869

@anshulxyz hello! Thanks you for interest in this issue! If I can help you let me know.

Hi, I was looking at Django's code for reference and I found this.

https://github.com/django/django/blob/d38324edc840049d42c3454b9487ac370aab5ee9/django/db/backends/sqlite3/base.py#L52-L62

class DatabaseWrapper(BaseDatabaseWrapper):
    vendor = "sqlite"
    display_name = "SQLite"
    # SQLite doesn't actually support most of these types, but it "does the right
    # thing" given more verbose field definitions, so leave them as is so that
    # schema inspection is more useful.
    data_types = {
        "AutoField": "integer",
        "BigAutoField": "integer",
        "BinaryField": "BLOB",
        "BooleanField": "bool",

So I put it to test in raw SQLite

sqlite> CREATE TABLE "test" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "is_done" bool NOT NULL );
sqlite> .tables
test
sqlite> .schema test
CREATE TABLE IF NOT EXISTS "test" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "is_done" bool NOT NULL );
sqlite> INSERT INTO test ("is_done") VALUES (1);
sqlite> INSERT INTO test ("is_done") VALUES (0);
sqlite> INSERT INTO test ("is_done") VALUES (2);
sqlite> SELECT * FROM test;
1|1
2|0
3|2

So this works!

I noticed that SQLite already does the conversion for us.

sqlite> CREATE TABLE "test" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "is_done" bool NOT NULL );
sqlite> .schema test
CREATE TABLE IF NOT EXISTS "test" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "is_done" bool NOT NULL );
sqlite> INSERT INTO test ("is_done") VALUES (TRUE);
sqlite> INSERT INTO test ("is_done") VALUES (FALSE);
sqlite> SELECT * FROM test;
1|1
2|0

Notice how it already converted TRUE to 1 and FALSE to 0.

Update:

SELECT statements work as well.

sqlite> SELECT * FROM test WHERE is_done = TRUE;
1|1

I have added a test case for it. It passes. anshulxyz@688e41c