aster_forge_db_migration/
index.rs

1use sea_orm_migration::sea_orm::sea_query::{Alias, Index, IndexDropStatement};
2use sea_orm_migration::sea_orm::{ConnectionTrait, DatabaseBackend, DbErr, Statement};
3
4/// Drops an index when it exists across all supported database backends.
5///
6/// `MySQL` does not support `DROP INDEX IF EXISTS`, so this helper checks
7/// `information_schema.statistics` before issuing the backend-specific drop.
8///
9/// # Errors
10///
11/// Returns an error when the `MySQL` existence query or backend index-drop statement fails.
12pub async fn drop_index_if_exists<C>(
13    db: &C,
14    table_name: &str,
15    index_name: &str,
16) -> Result<(), DbErr>
17where
18    C: ConnectionTrait,
19{
20    let backend = db.get_database_backend();
21    if backend == DatabaseBackend::MySql && !mysql_index_exists(db, table_name, index_name).await? {
22        return Ok(());
23    }
24
25    db.execute(&drop_index_for_backend(backend, table_name, index_name))
26        .await?;
27    Ok(())
28}
29
30/// Renames a `MySQL` index when the source exists and the target does not.
31///
32/// Calling it repeatedly is safe.
33///
34/// # Errors
35///
36/// Returns an error for invalid identifiers, a non-`MySQL` connection, a failed existence query,
37/// or a failed `ALTER TABLE` statement.
38pub async fn rename_mysql_index_if_exists<C>(
39    db: &C,
40    table_name: &str,
41    old_index_name: &str,
42    new_index_name: &str,
43) -> Result<(), DbErr>
44where
45    C: ConnectionTrait,
46{
47    validate_mysql_identifier(table_name)?;
48    validate_mysql_identifier(old_index_name)?;
49    validate_mysql_identifier(new_index_name)?;
50
51    if db.get_database_backend() != DatabaseBackend::MySql {
52        return Err(DbErr::Custom(
53            "rename_mysql_index_if_exists requires a MySQL connection".to_string(),
54        ));
55    }
56
57    if !mysql_index_exists(db, table_name, old_index_name).await?
58        || mysql_index_exists(db, table_name, new_index_name).await?
59    {
60        return Ok(());
61    }
62
63    db.execute_unprepared(&format!(
64        "ALTER TABLE `{table_name}` RENAME INDEX `{old_index_name}` TO `{new_index_name}`"
65    ))
66    .await?;
67    Ok(())
68}
69
70fn drop_index_for_backend(
71    backend: DatabaseBackend,
72    table_name: &str,
73    index_name: &str,
74) -> IndexDropStatement {
75    let mut statement = Index::drop();
76    statement
77        .name(index_name.to_owned())
78        .table(Alias::new(table_name));
79    if backend != DatabaseBackend::MySql {
80        statement.if_exists();
81    }
82    statement.clone()
83}
84
85async fn mysql_index_exists<C>(db: &C, table_name: &str, index_name: &str) -> Result<bool, DbErr>
86where
87    C: ConnectionTrait,
88{
89    let row = db
90        .query_one_raw(Statement::from_sql_and_values(
91            DatabaseBackend::MySql,
92            "SELECT 1 FROM information_schema.statistics \
93             WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ? LIMIT 1",
94            [table_name.into(), index_name.into()],
95        ))
96        .await?;
97    Ok(row.is_some())
98}
99
100fn validate_mysql_identifier(identifier: &str) -> Result<(), DbErr> {
101    if !identifier.is_empty()
102        && identifier
103            .bytes()
104            .all(|byte| byte.is_ascii_alphanumeric() || byte == b'_')
105    {
106        return Ok(());
107    }
108
109    Err(DbErr::Custom(format!(
110        "invalid MySQL migration identifier: {identifier:?}"
111    )))
112}
113
114#[cfg(test)]
115mod tests {
116    use sea_orm_migration::sea_orm::sea_query::{
117        MysqlQueryBuilder, PostgresQueryBuilder, SqliteQueryBuilder,
118    };
119    use sea_orm_migration::sea_orm::{ConnectionTrait, Database, DatabaseBackend};
120
121    use super::{
122        drop_index_for_backend, drop_index_if_exists, rename_mysql_index_if_exists,
123        validate_mysql_identifier,
124    };
125
126    #[test]
127    fn drop_index_sql_respects_backend_capabilities() {
128        let mysql = drop_index_for_backend(DatabaseBackend::MySql, "example_table", "idx_example")
129            .to_string(MysqlQueryBuilder);
130        assert_eq!(mysql, "DROP INDEX `idx_example` ON `example_table`");
131
132        let postgres =
133            drop_index_for_backend(DatabaseBackend::Postgres, "example_table", "idx_example")
134                .to_string(PostgresQueryBuilder);
135        assert_eq!(postgres, "DROP INDEX IF EXISTS \"idx_example\"");
136
137        let sqlite =
138            drop_index_for_backend(DatabaseBackend::Sqlite, "example_table", "idx_example")
139                .to_string(SqliteQueryBuilder);
140        assert_eq!(sqlite, "DROP INDEX IF EXISTS \"idx_example\"");
141    }
142
143    #[test]
144    fn mysql_identifier_validation_rejects_raw_sql_fragments() {
145        assert!(validate_mysql_identifier("idx_example_2026").is_ok());
146        assert!(validate_mysql_identifier("").is_err());
147        assert!(validate_mysql_identifier("idx-example").is_err());
148        assert!(validate_mysql_identifier("idx` DROP TABLE users").is_err());
149    }
150
151    #[tokio::test]
152    async fn drop_index_if_exists_is_idempotent_on_sqlite() {
153        let db = Database::connect("sqlite::memory:").await.unwrap();
154        db.execute_unprepared("CREATE TABLE example_table (id INTEGER PRIMARY KEY)")
155            .await
156            .unwrap();
157        db.execute_unprepared("CREATE INDEX idx_example ON example_table (id)")
158            .await
159            .unwrap();
160
161        drop_index_if_exists(&db, "example_table", "idx_example")
162            .await
163            .unwrap();
164        drop_index_if_exists(&db, "example_table", "idx_example")
165            .await
166            .unwrap();
167    }
168
169    #[tokio::test]
170    async fn mysql_index_rename_rejects_non_mysql_connections() {
171        let db = Database::connect("sqlite::memory:").await.unwrap();
172        let error = rename_mysql_index_if_exists(
173            &db,
174            "example_table",
175            "idx_example_old",
176            "idx_example_new",
177        )
178        .await
179        .unwrap_err();
180
181        assert!(error.to_string().contains("requires a MySQL connection"));
182    }
183}
aster_forge_db_migration/index.rs

aster_forge_db_migration/
index.rs
