create index on non-unique text field throws error for Sqlite

[boan-anbo]

Description

Migrate with index created on text field without unique throws errors for Sqlite.

Steps to Reproduce

1. Define migration:

.col(
    ColumnDef::new(MyEntity::MyColumn)
        .text()
        .null()
        .default("NULL")
)
.index(
    Index::create()
        .name("my_column")
        .col(MyEntity::MyColumn)
)

3. the cli throws

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Exec("error returned from database: (code: 1) near \"(\": syntax error")', migration\src\m20220929_111159_create_db.rs:225:20

6. It works if you change migration to

.col(
    ColumnDef::new(MyEntity::MyColumn)
        .text()
        .null()
        .default("NULL")
)
.index(
    Index::create()
        .name("my_column")
        .col(MyEntity::MyColumn)
        .unique() // add this
)

Expected Behavior

Not to throw as Sqlite supports indexing non-unique field.

Versions

sea-orm={version = "0.9.2", features = [
    "sqlx-sqlite",
    "runtime-tokio-rustls",
    "mock",
    "macros",
    "debug-print",
    "uuid"
]}

Additional Information

[0xpr03]

This also happens on postgres.

manager
.create_table(
    Table::create()
        .table(ExampleTable::Table)
        .if_not_exists()
        .col(ColumnDef::new(ExampleTable::Id).integer().not_null().auto_increment().primary_key())
        .col(ColumnDef::new(ExampleTable::Url).string().not_null())
        .index(Index::create().col(ExampleTable::Url).name("index_url"))
        .to_owned(),
)
.await
.unwrap();

This generates

CREATE TABLE IF NOT EXISTS "example_table" ( "id" serial NOT NULL PRIMARY KEY, "url" varchar NOT NULL, CONSTRAINT "index_url" ("url") )

Which postgres will complain about:

Motivation: Creating indexes on foreign keys, which postgres won't do by itself. The keys don't have to unique, could be part of a composite key.

Workaround: Manual index creation

        manager.create_index(Index::create()
        .name("fk_123")
        .table(ExampleTable::Table)
        .col(ExampleTable::Url)
        .to_owned()).await.unwrap();

[ikrivosheev]

@0xpr03 @boan-anbo, hello! Thank you for the report. This bug is in SeaQuery, I create issue)

[billy1624]

I just transfer this issue to SeaQuery

[ikrivosheev]

Hmmm, @boan-anbo @billy1624 @oonxt , after read docs:

1. https://www.sqlite.org/lang_createtable.html
2. https://www.postgresql.org/docs/current/sql-createtable.html

what behavior is expected?

[0xpr03]

I for one would expect that it either errors out from the start (you have to make it unique, best case comp-time), or just make two SQL commands with some docs that index-creation may be separated to a second command. The latter one may be a bad idea when looking at atomic commands and errors-for-command relations. In PG and SQLite you could at least put both into one begin-clause, so they are more connected.