Atomic migrations?

[gd87429]

If you run Migrator::up() in parallel (like when you spin up multiple servers), is there a chance the same migration might run more than once?

If yes -- are there plans to build-in support for atomic migrations, or should the user make sure migration cannot be applied twice (like checking if data exists before seeding the table)?

[billy1624]

Hey @gd87429, hi again! I think the atomic migration is database specific? For example, in MySQL, the DDL statement such as CREATE TABLE is not transactional that means it can't be rollback.

I'd love to support atomic migrations but seems it's not a easy task. As we need to come up with a plan for each of our supported database, unless I miss something.

What's your suggestions on that?

[gd87429]

Hey @billy1624, yes that's right - it seems it's not an easy task.

One way of going about it could be to have a single place (like a table with one row, or a global variable) that could act as a mutex. Before a migration starts, it could update the lock to on, and on finishing all migrations (or until it encounters the first error) set the lock to off. But then you'd introduce a new set of problems - like when another program is about to run migrations and encounters a lock, does it wait for it to become unlocked or does it simply skip running its own migrations?

Doesn't seem like the cleanest solution, so I'd probably lean towards holding off until more people weigh in.

Thank you for taking the time tho.

---

Edit: For anyone else stumbling on the same issue, rn I'm going around this using if_not_exists (table or index creation) and on_conflict / do_nothing (seeding data). Doesn't help with every use case tho (like when there are no indexes on table).

[tyt2y3]

The general recommendation is that you call https://docs.rs/sea-orm-migration/latest/sea_orm_migration/manager/struct.SchemaManager.html#method.get_connection and then open a transaction on it

[Mastermindaxe]

Could you provide an example on how to do this with Migrator::up? I can't really wrap my head around it...

[tyt2y3]

What is doable but require more effort is for SchemaManager to become a enum of DatabaseConnection and DatabaseTransaction,
and allowing one to open a transaction if on Postgres:

I imagine:

pub enum SchemaManager<'c> {
    DatabaseConnection(&'c DatabaseConnection),
    DatabaseTransaction(DatabaseTransaction),
}

// and thus

impl SchemaManager {
    fn get_transaction(&self) -> Self {
        if postgres {
        match self {
            DatabaseConnection(conn) => Self::DatabaseTransaction(conn.begin()),
            DatabaseTransaction(tran) => Self::DatabaseTransaction(tran.begin()), // this will open a nested transaction
        }
        } else {
            self.clone()
        }
    }
}

Just an imagination, I have not tested whether it will pass all the lifetime requirements.

[billy1624]

Hey @gd87429, tracking issue #1378

[RenovZ]

I think maybe we can try another way to just make sure the migration will be executed successfully. Like dryrun the migraiton, it will not update the database actually just check if the migration itself is ok.
Maybe this will need a PR for mysql, postgres, or sqlite (I'm not sure. LOL).