Migrate fresh not deleting postgres type

[MaderNoob]

Description

If the migration creates postgres types, running sea-orm-cli migrate fresh will fail because it doesn't delete types, and it will say that the type already exists.

Steps to Reproduce

1. Create a migration which creates a postgres type.
2. Run the migration sea-orm-cli migrate up
3. Try to run a fresh migration sea-orm-cli migrate fresh

Expected Behavior

The fresh migration should work

Actual Behavior

The fresh migration returns an error saying that the enum type already exists.

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-migration v0.8.2
│ │ ├── sea-orm v0.8.0 ()
│ │ ├── sea-orm-cli v0.8.1
│ │ │ ├── sea-schema v0.8.0
│ │ │ │ ├── sea-query v0.24.6 ()
│ │ │ │ └── sea-schema-derive v0.1.0 (proc-macro)
│ │ ├── sea-schema v0.8.0 ()
├── sea-orm v0.8.0 ()
├── sea-orm-rocket v0.5.0
│ └── sea-orm-rocket-codegen v0.5.0 (proc-macro)

uname -a
Linux arch 5.17.5-arch1-1 #1 SMP PREEMPT Wed, 27 Apr 2022 20:56:11 +0000 x86_64 GNU/Linux

pacman -Q postgresql
postgresql 14.2-1

Additional Information

Types should be first deleted when running a fresh migration.

[billy1624]

Hey @MaderNoob, sorry for the delay. As discussed on Discord, we could delete all PostgreSQL types in current schema when calling fresh on migrator.

• sea-orm/sea-orm-migration/src/migrator.rs

  Lines 130 to 212 in 9983e5d

  	/// Drop all tables from the database, then reapply all migrations
  	async fn fresh(db: &DbConn) -> Result<(), DbErr> {
  	Self::install(db).await?;
  	let db_backend = db.get_database_backend();
  	// Temporarily disable the foreign key check
  	if db_backend == DbBackend::Sqlite {
  	info!("Disabling foreign key check");
  	db.execute(Statement::from_string(
  	db_backend,
  	"PRAGMA foreign_keys = OFF".to_owned(),
  	))
  	.await?;
  	info!("Foreign key check disabled");
  	}
  	// Drop all foreign keys
  	if db_backend == DbBackend::MySql {
  	info!("Dropping all foreign keys");
  	let mut stmt = Query::select();
  	stmt.columns([Alias::new("TABLE_NAME"), Alias::new("CONSTRAINT_NAME")])
  	.from((
  	Alias::new("information_schema"),
  	Alias::new("table_constraints"),
  	))
  	.cond_where(
  	Condition::all()
  	.add(
  	Expr::expr(get_current_schema(db)).equals(
  	Alias::new("table_constraints"),
  	Alias::new("table_schema"),
  	),
  	)
  	.add(Expr::expr(Expr::value("FOREIGN KEY")).equals(
  	Alias::new("table_constraints"),
  	Alias::new("constraint_type"),
  	)),
  	);
  	let rows = db.query_all(db_backend.build(&stmt)).await?;
  	for row in rows.into_iter() {
  	let constraint_name: String = row.try_get("", "CONSTRAINT_NAME")?;
  	let table_name: String = row.try_get("", "TABLE_NAME")?;
  	info!(
  	"Dropping foreign key '{}' from table '{}'",
  	constraint_name, table_name
  	);
  	let mut stmt = ForeignKey::drop();
  	stmt.table(Alias::new(table_name.as_str()))
  	.name(constraint_name.as_str());
  	db.execute(db_backend.build(&stmt)).await?;
  	info!("Foreign key '{}' has been dropped", constraint_name);
  	}
  	info!("All foreign keys dropped");
  	}
  	// Drop all tables
  	let stmt = query_tables(db);
  	let rows = db.query_all(db_backend.build(&stmt)).await?;
  	for row in rows.into_iter() {
  	let table_name: String = row.try_get("", "table_name")?;
  	info!("Dropping table '{}'", table_name);
  	let mut stmt = Table::drop();
  	stmt.table(Alias::new(table_name.as_str()))
  	.if_exists()
  	.cascade();
  	db.execute(db_backend.build(&stmt)).await?;
  	info!("Table '{}' has been dropped", table_name);
  	}
  	// Restore the foreign key check
  	if db_backend == DbBackend::Sqlite {
  	info!("Restoring foreign key check");
  	db.execute(Statement::from_string(
  	db_backend,
  	"PRAGMA foreign_keys = ON".to_owned(),
  	))
  	.await?;
  	info!("Foreign key check restored");
  	}
  	// Reapply all migrations
  	Self::up(db, None).await
  	}

It would be a two steps process:

1. Select all type names out of PostgreSQL
2. Construct delete type statement and execute it

[billy1624]

I'll open this issue for anyone who have interested in contributing :)

[karpa4o4]

@billy1624 can you assign me with this issue?

[ikrivosheev]

@karpa4o4 done!

[karpa4o4]

@ikrivosheev thanks

[billy1624]

Thanks for the interest! @karpa4o4