i64 in sqlite: bigint NOT NULL PRIMARY KEY AUTOINCREMENT

[GuoMonth]

Description

I am define entity , this is code:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "t_test")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i64,
    pub create_user: Option<String>,
    pub create_time: DateTime,
    pub update_user: Option<String>,
    pub update_time: DateTime,

}

Migration code:

impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let backend = manager.get_database_backend();
        let schema = Schema::new(backend);

        let stmt = schema.create_table_from_entity(TestEntity);
        let r = manager.create_table(stmt).await;
        if let Err(err) = r {
            println!("{}", err);
            return Err(err);
        }

        Ok(())
    }

Steps to Reproduce

1. run this test code

env::set_var("RUST_LOG", "debug");
tracing_subscriber::fmt::init();
let mut opt = ConnectOptions::new("sqlite:file::memory:?cache=shared");
opt.sqlx_logging_level(log::LevelFilter::Info);
let db = Database::connect(opt.clone()).await.unwrap();
let _ = Migrator::up(&db, None).await;

2. will get error:

2023-08-30T04:46:55.588146Z DEBUG sea_orm::driver::sqlx_sqlite: CREATE TABLE "t_test" ( "id" bigint NOT NULL PRIMARY KEY AUTOINCREMENT, "create_user" text, "create_time" text NOT NULL, "update_user" text, "update_time" text NOT NULL)

thread 'test_create_test_server' panicked at 'called `Result::unwrap()` on an `Err` value: Exec(SqlxError(Database(SqliteError { code: 1, message: "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY" })))'

Expected Behavior

It is success, when i use postgresql.
But use sqlite, i am get error. why?

I am read this doc https://www.sea-ql.org/SeaORM/docs/generate-entity/entity-structure/
I think the create table sql is error。
In the doc, the i64 id should be "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT but not "id" bigint NOT NULL PRIMARY KEY AUTOINCREMENT

Actual Behavior

Different the document description
in sqlite,
i64 -> bigint, ❌，different doc
i64 -> integet, expected，and same doc. but actual behavior is bigint

Reproduces How Often

always reproducible!

Versions

Cargo.toml

sea-orm = { version = "0.12.2", features = [
    "sqlx-postgres",
    "sqlx-sqlite",
    "runtime-tokio-rustls",
] }
sea-orm-migration = { version = "0.12.2", features = [
    "sqlx-sqlite",
    "sqlx-postgres",
] }

[tyt2y3]

It appears to be a quirkiness of SQLite
Ref: https://sqlite.org/forum/info/2dfa968a702e1506e885cb06d92157d492108b22bf39459506ab9f7125bca7fd
In most cases BIGINT is synonyms with INTEGER, but not in PRIMARY KEY AUTOINCREMENT

I would suggest a workaround for now:

#[sea_orm(primary_key)]
#[cfg_attr(feature = "sqlite", sea_orm(column_type = "Integer"))]
pub id: i64,

[tyt2y3]

Created an issue in sea-query, as there is nothing to be done on sea-orm side.