Atomic migration by billy1624 · Pull Request #1379 · SeaQL/sea-orm

billy1624 · 2023-01-09T15:23:57Z

PR Info

Closes Atomic migrations #1378

New Features

Executes "atomic" migrations on Postgres, that is migrations will be executed with a transaction when the target database is Postgres
Starts a transaction and execute SQL inside migration up and down

use sea_orm_migration::prelude::*;
use sea_orm_migration::sea_orm::{entity::*, query::*};

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let db = manager.get_connection();

        let transaction = db.begin().await?;

        cake::ActiveModel {
            name: Set("Cheesecake".to_owned()),
            ..Default::default()
        }
        .insert(&transaction)
        .await?;

        transaction.commit().await?;

        if std::env::var_os("ABOARD_MIGRATION").eq(&Some("YES".into())) {
            return Err(DbErr::Migration(
                "Aboard migration and rollback changes".into(),
            ));
        }

        Ok(())
    }

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

        let transaction = db.begin().await?;

        cake::Entity::delete_many()
            .filter(cake::Column::Name.eq("Cheesecake"))
            .exec(&transaction)
            .await?;

        transaction.commit().await?;

        Ok(())
    }
}

mod cake {
    use sea_orm_migration::sea_orm::entity::prelude::*;

    #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
    #[sea_orm(table_name = "cake")]
    pub struct Model {
        #[sea_orm(primary_key)]
        pub id: i32,
        pub name: String,
    }

    #[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
    pub enum Relation {}

    impl ActiveModelBehavior for ActiveModel {}
}

Breaking Changes

Changed the type of SchemaManager::conn to SchemaManagerConnection
Changed the input type of SchemaManager::new() to IntoSchemaManagerConnection
Changed the return type of SchemaManager::get_connection() to &SchemaManagerConnection<'c>
Changed the connection parameter of MigratorTrait's methods
- get_migration_models, get_migration_with_status, get_pending_migrations, get_applied_migrations, install and status methods take ConnectionTrait as the connection
- fresh, refresh, reset, up and down methods take IntoSchemaManagerConnection as the connection

Changes

Added SchemaManagerConnection and IntoSchemaManagerConnection

tyt2y3 · 2023-01-10T07:06:19Z

Just to clear some questions in my mind, so the updated default behaviour will be: wrapping the entire up/down execution in a transaction, only if it's postgres?

billy1624 · 2023-01-20T09:21:03Z

Just to clear some questions in my mind, so the updated default behaviour will be: wrapping the entire up/down execution in a transaction, only if it's postgres?

Hey @tyt2y3, the new behaviour is:

The fresh, refresh, reset, up and down methods now take a &DatabaseConnection or a &DatabaseTransaction.

Then, inside each method, it will start a transaction to execute the migrations only if it's a Postgres connection. For non-Postgres connection, it just use connection as-is and execute the migrations as usual.

tyt2y3 · 2023-01-29T03:39:15Z

sea-orm-migration/tests/main.rs

+        println!("\nRoll back changes when encounter errors");
+
+        // Set a flag to throw error inside `m20230109_000001_seed_cake_table.rs`
+        std::env::set_var("ABOARD_MIGRATION", "YES");


I suspect this should be abort ?

Oppps, what a typo

tyt2y3 · 2023-01-29T04:47:37Z

sea-orm-migration/tests/migrator/m20230109_000001_seed_cake_table.rs

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


Why open a transaction here? Just want to confirm, the behaviour should be no different whether we open a transaction or not, right? Because it's already inside a transaction (on Postgres)?

I want to show it's possible to open a transaction inside migrate up & down.

For Postgres, yes, it's no difference. Because as you said you're already inside a transaction. However, the story is different for MySQL and SQLite. Opening a transaction is vital because the migration itself isn't atomic. If you want to seed the database atomically then you need to start a transaction yourself.

Got it, cool

tyt2y3 · 2023-01-29T04:48:41Z

sea-orm-migration/tests/migrator/m20230109_000001_seed_cake_table.rs

+        .insert(&transaction)
+        .await?;
+
+        transaction.commit().await?;


For any backend, aborting the migration BEFORE committing makes more sense?

Sure! see if both transactions got rolled back at the same time

tyt2y3

OK. I also have some adjustments to make

…/sea-orm#1379)

* Update 02-writing-migration.md * Update SeaORM/docs/03-migration/02-writing-migration.md * Support various UUID formats that are available in `uuid::fmt` module (SeaQL/sea-orm#1325) * Casting columns as a different data type on select, insert and update (SeaQL/sea-orm#1304) * Methods of `ActiveModelBehavior` receive db connection as a parameter (SeaQL/sea-orm#1145, SeaQL/sea-orm#1328) * Added `execute_unprepared` method to `DatabaseConnection` and `DatabaseTransaction` (SeaQL/sea-orm#1327) * Added `Select::into_tuple` to select rows as tuples (instead of defining a custom Model) (SeaQL/sea-orm#1311) * Generate `#[serde(skip)]` for hidden columns (SeaQL/sea-orm#1171, SeaQL/sea-orm#1320) * Generate entity with extra derives and attributes for model struct (SeaQL/sea-orm#1124, SeaQL/sea-orm#1321) * Generate entity with extra derives and attributes for model struct (SeaQL/sea-orm#1124, SeaQL/sea-orm#1321) * async_trait * Migrations are now performed inside a transaction for Postgres (SeaQL/sea-orm#1379) * `MockDatabase::append_exec_results()`, `MockDatabase::append_query_results()`, `MockDatabase::append_exec_errors()` and `MockDatabase::append_query_errors()` take any types implemented `IntoIterator` trait (SeaQL/sea-orm#1367) * Cleanup the use of `vec!` macros * Added `DatabaseConnection::close` (SeaQL/sea-orm#1236) * Added `ActiveValue::reset` to convert `Unchanged` into `Set` (SeaQL/sea-orm#1177) * Added `QueryTrait::apply_if` to optionally apply a filter (SeaQL/sea-orm#1415) * Added the `sea-orm-internal` feature flag to expose some SQLx types (SeaQL/sea-orm#1297, SeaQL/sea-orm#1434) * Add `QuerySelect::columns` method - select multiple columns (SeaQL/sea-orm#1264) * Edit * Update SeaORM/docs/02-install-and-config/02-connection.md Co-authored-by: Chris Tsang <[email protected]> * Update SeaORM/docs/05-basic-crud/03-insert.md Co-authored-by: Chris Tsang <[email protected]> * fmt * Edit --------- Co-authored-by: Chris Tsang <[email protected]>

Running atomic migration

a2f5e81

billy1624 self-assigned this Jan 9, 2023

billy1624 added 6 commits January 20, 2023 00:27

refactor

1a3be36

clippy

c3dcb8b

visibility

18ae130

Execute migration in transaction when it's a Postgres connection

2db29c3

Testing rolling back on migrate up and down

6382432

lifetimes elision

d07b23e

billy1624 marked this pull request as ready for review January 20, 2023 08:52

billy1624 requested a review from tyt2y3 January 20, 2023 09:21

tyt2y3 reviewed Jan 29, 2023

View reviewed changes

tyt2y3 added this to the 0.11.x milestone Jan 29, 2023

billy1624 added 3 commits January 30, 2023 12:45

typo

8d26dfe

abort migration before committing nested transaction

face72b

Merge branch 'master' into atomic-migration

a217633

billy1624 requested a review from tyt2y3 January 30, 2023 10:10

tyt2y3 approved these changes Jan 30, 2023

View reviewed changes

tyt2y3 merged commit 6d7bcb3 into master Jan 30, 2023

tyt2y3 deleted the atomic-migration branch January 30, 2023 14:55

billy1624 added a commit to SeaQL/seaql.github.io that referenced this pull request Feb 3, 2023

Migrations are now performed inside a transaction for Postgres (SeaQL…

e321105

…/sea-orm#1379)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Atomic migration#1379

Atomic migration#1379
tyt2y3 merged 10 commits intomasterfrom
atomic-migration

billy1624 commented Jan 9, 2023 •

edited

Loading

Uh oh!

tyt2y3 commented Jan 10, 2023 •

edited

Loading

Uh oh!

billy1624 commented Jan 20, 2023

Uh oh!

tyt2y3 Jan 29, 2023

Uh oh!

billy1624 Jan 30, 2023

Uh oh!

tyt2y3 Jan 29, 2023 •

edited

Loading

Uh oh!

billy1624 Jan 30, 2023 •

edited

Loading

Uh oh!

tyt2y3 Jan 30, 2023

Uh oh!

tyt2y3 Jan 29, 2023

Uh oh!

billy1624 Jan 30, 2023

Uh oh!

billy1624 Jan 30, 2023

Uh oh!

tyt2y3 left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

billy1624 commented Jan 9, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Info

New Features

Breaking Changes

Changes

Uh oh!

tyt2y3 commented Jan 10, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

billy1624 commented Jan 20, 2023

Uh oh!

tyt2y3 Jan 29, 2023

Choose a reason for hiding this comment

Uh oh!

billy1624 Jan 30, 2023

Choose a reason for hiding this comment

Uh oh!

tyt2y3 Jan 29, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

billy1624 Jan 30, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

tyt2y3 Jan 30, 2023

Choose a reason for hiding this comment

Uh oh!

tyt2y3 Jan 29, 2023

Choose a reason for hiding this comment

Uh oh!

billy1624 Jan 30, 2023

Choose a reason for hiding this comment

Uh oh!

billy1624 Jan 30, 2023

Choose a reason for hiding this comment

Uh oh!

tyt2y3 left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

billy1624 commented Jan 9, 2023 •

edited

Loading

tyt2y3 commented Jan 10, 2023 •

edited

Loading

tyt2y3 Jan 29, 2023 •

edited

Loading

billy1624 Jan 30, 2023 •

edited

Loading