migration drop disregards `use_transaction -> Option<bool>`

[yuriy-yarosh]

Description

I can't rollback migrations with statements that can not be wrapped in transactions, e.g. DROP DATABASE DROP TABLESPACE.

Steps to Reproduce

As per #2980

1. Create migration

use sea_orm_migration::{prelude::*, sea_orm::Statement};

#[derive(DeriveMigrationName)]
pub struct DatabaseMigration;

#[async_trait::async_trait]
impl MigrationTrait for DatabaseMigration {
    fn use_transaction(&self) -> Option<bool> {
        Some(false)
    }

    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let db = manager.get_connection();

        db.execute_unprepared("CREATE DATABASE app;").await?;

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let db = manager.get_connection();

        // 1. Terminate all active connections to the target database
        let disconnect_query = format!(
            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{}' AND pid <> pg_backend_pid();",
            "app"
        );
        db.execute_unprepared(&disconnect_query).await?;


        // 2. Drop the database
        db.execute_unprepared("DROP DATABASE IF EXISTS app;").await?;

        // Raw does not do the trick either
        // db.execute_raw(Statement::from_string(db.get_database_backend(), "DROP DATABASE app;")).await?;

        Ok(())
    }
}

2. Drop it with

sea-orm-cli migrate -d .
sea-orm-cli migrate -d . reset

3. See that it wraps DROP DATABASE into a transaction

Expected Behavior

Supposedly, it should've rollback the same way it's been applied.

Actual Behavior

It does not rollback.

Reproduces How Often

Always.

Workarounds

Running raw sqlx queries myself.

Versions

cargo tree | grep sea-
├── sea-orm-migration v2.0.0-rc.37
│   ├── sea-orm v2.0.0-rc.37
│   │   ├── sea-orm-macros v2.0.0-rc.37 (proc-macro)
│   │   │   ├── sea-bae v0.2.1 (proc-macro)
│   │   ├── sea-query v1.0.0-rc.31
│   │   │   ├── sea-query-derive v1.0.0-rc.12 (proc-macro)
│   │   ├── sea-query-sqlx v0.8.0-rc.14
│   │   │   ├── sea-query v1.0.0-rc.31 (*)
│   │   ├── sea-schema v0.17.0-rc.17
│   │   │   ├── sea-query v1.0.0-rc.31 (*)
│   │   │   ├── sea-query-sqlx v0.8.0-rc.14 (*)
│   │   │   ├── sea-schema-derive v0.3.0 (proc-macro)
│   ├── sea-orm-cli v2.0.0-rc.37
│   ├── sea-schema v0.17.0-rc.17 (*)

[tyt2y3]

thanks for raising this. I've refined the design at 3002, can you check it out?