Syntax error when trying to drop multiple tables in migrations with SQLite

[Bromles]

Description

When trying to roll back migration with drop statement which includes more than one table with SQLite, it fails with Execution Error: error returned from database: (code: 1) near ",": syntax error

The same migrations work fine with PostgreSQL.

It seems like SQLite doesn't support dropping multiple tables in one statement. I think this should be either worked around by Sea ORM itself or mentioned in the official documentation in the Migration section

Steps to Reproduce

1. Create several tables in migration up function
2. Apply migration
3. Try to drop more than one table in one statement in down function
4. Get an error during roll back

Expected Behavior

All tables mentioned in drop statement are dropped

Actual Behavior

Migration roll back fails with syntax error

Reproduces How Often

At every attempt

Workarounds

Split drop statements so that they include only one table per statement

Reproducible Example

Cargo.toml of migration crate

[package]
name = "migration"
version = "0.1.0"
edition = "2021"
publish = false

[lib]
name = "migration"
path = "src/lib.rs"

[dependencies]
async-std = { version = "1", features = ["attributes", "tokio1"] }
# for logs formatting in terminal
tracing-subscriber = "0.3"

[dependencies.sea-orm-migration]
version = "1.0.0-rc"
features = [
    "runtime-tokio-rustls",
    "sqlx-sqlite",
    "sqlx-postgres",
    "with-uuid",
]

migration file (i.e. m20220101_000001_create_table)

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(Book::Table)
                    .if_not_exists()
                    .col(ColumnDef::new(Book::Id).uuid().primary_key())
                    .to_owned(),
            )
            .await?;

        manager
            .create_table(
                Table::create()
                    .table(Author::Table)
                    .if_not_exists()
                    .col(ColumnDef::new(Author::Id).uuid().primary_key())
                    .to_owned(),
            )
            .await?;

        manager
            .create_table(
                Table::create()
                    .table(BookAuthor::Table)
                    .if_not_exists()
                    .col(ColumnDef::new(BookAuthor::Id).uuid().primary_key())
                    .to_owned(),
            )
            .await?;

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .drop_table(
                Table::drop()
                    .if_exists()
                    .table(BookAuthor::Table)
                    .cascade()
                    .to_owned(),
            )
            .await?;

        manager
            .drop_table(
                Table::drop()
                    .if_exists()
                    .table(Book::Table)
                    .table(Author::Table)
                    .cascade()
                    .to_owned(),
            )
            .await?;

        Ok(())
    }
}

#[derive(DeriveIden)]
enum Book {
    Table,
    Id,
}

#[derive(DeriveIden)]
enum Author {
    Table,
    Id,
}

#[derive(DeriveIden)]
enum BookAuthor {
    Table,
    Id,
}

.env

DATABASE_URL="sqlite://main.sqlite?mode=rwc"

lib.rs and main.rs are unchanged from sea-orm-cli migrate init

The first drop statement with only BookAuthor table executes successfully, but the second one, which includes both Book and Author tables, fails with SQLite while executing successfully with PostgreSQL.

Error happens during execution of the following SQL statement: \n\nDROP TABLE IF EXISTS \"book\",\n\"author\"\n. Error message is Execution Error: error returned from database: (code: 1) near ",": syntax error, which is not really informative without debug output which logs statements

Versions

Tried with sea-orm-migration 1.0.0-rc.7 and 0.12.15

[Bromles]

The workaround (splitting drop statement) is unpleasant because it requires user to manually arrange all drop statements in correct order, otherwise an error is thrown (i.e. if BookAuthor is a junction table, and you are trying to drop Book or Author tables before dropping BookAuthor)