Repository: diesel-rs/diesel (language: rust)

Issue:
`diesel migrations redo` doesn't respect `run_in_transaction` migration metadata
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:** rustc 1.67.0 
- **Diesel:** 2.0.3
- **Database:** sqlite 3.40.1
- **Operating System** Arch Linux

### Feature Flags

- **diesel:** "sqlite", "time"
- **diesel_cli:** "sqlite"

## Problem Description

The `diesel migrations redo` command doesn't respect the `run_in_transaction` migration metadata, while `run` and `revert` separately do.

This seems to be because it is [hard-coded](https://github.com/diesel-rs/diesel/blob/8afb1bb47f31a292c7853f6cb48145231994ee7e/diesel_cli/src/main.rs#L452-L474) to use a transaction for anything except mysql, specifically for the redo command.

### What are you trying to accomplish?

A migration that needs to handle transactions itself

Specifically temporarily turn off an [sqlite specific pragma](https://www.sqlite.org/pragma.html#pragma_foreign_keys) as dictated [here](https://www.sqlite.org/foreignkeys.html#fk_schemacommands), which cannot be done inside a transaction.

### What is the expected output?

```
Rolling back migration <migration>
Running migration <migration>
```

### What is the actual output?

```
Rolling back migration <migration>
Failed to run <migration> with: cannot start a transaction within a transaction
```

### Are you seeing any additional errors?


### Steps to reproduce

<!--
Please include as much of your codebase as needed to reproduce the error.  If the relevant files are large, please consider linking to a public repository or a [Gist](https://gist.github.com/). This includes normally the following parts:

* The exact code where your hit the problem
* Relevant parts your schema, so any `table!` macro calls required for the
* Any other type definitions involved in the code, which produces your problem
-->

- `diesel migration generate example`
- Add the following

```toml
# example/metadata.toml
run_in_transaction = false
```

```sql
-- example/up.sql
BEGIN TRANSACTION;
COMMIT TRANSACTION;
```

```sql
-- example/down.sql
BEGIN TRANSACTION;
COMMIT TRANSACTION;
```

- Run `diesel migration run` and see it works as expected
- Run `diesel migration revert` and see it works as expected
- Run `diesel migration redo` and see it fail

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues)  and the [disussion forum](https://github.com/diesel-rs/diesel/discussions)  for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->

`diesel migrations redo` doesn't respect `run_in_transaction` migration metadata
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:** rustc 1.67.0 
- **Diesel:** 2.0.3
- **Database:** sqlite 3.40.1
- **Operating System** Arch Linux

### Feature Flags

- **diesel:** "sqlite", "time"
- **diesel_cli:** "sqlite"

## Problem Description

The `diesel migrations redo` command doesn't respect the `run_in_transaction` migration metadata, while `run` and `revert` separately do.

This seems to be because it is [hard-coded](https://github.com/diesel-rs/diesel/blob/8afb1bb47f31a292c7853f6cb48145231994ee7e/diesel_cli/src/main.rs#L452-L474) to use a transaction for anything except mysql, specifically for the redo command.

### What are you trying to accomplish?

A migration that needs to handle transactions itself

Specifically temporarily turn off an [sqlite specific pragma](https://www.sqlite.org/pragma.html#pragma_foreign_keys) as dictated [here](https://www.sqlite.org/foreignkeys.html#fk_schemacommands), which cannot be done inside a transaction.

### What is the expected output?

```
Rolling back migration <migration>
Running migration <migration>
```

### What is the actual output?

```
Rolling back migration <migration>
Failed to run <migration> with: cannot start a transaction within a transaction
```

### Are you seeing any additional errors?


### Steps to reproduce

<!--
Please include as much of your codebase as needed to reproduce the error.  If the relevant files are large, please consider linking to a public repository or a [Gist](https://gist.github.com/). This includes normally the following parts:

* The exact code where your hit the problem
* Relevant parts your schema, so any `table!` macro calls required for the
* Any other type definitions involved in the code, which produces your problem
-->

- `diesel migration generate example`
- Add the following

```toml
# example/metadata.toml
run_in_transaction = false
```

```sql
-- example/up.sql
BEGIN TRANSACTION;
COMMIT TRANSACTION;
```

```sql
-- example/down.sql
BEGIN TRANSACTION;
COMMIT TRANSACTION;
```

- Run `diesel migration run` and see it works as expected
- Run `diesel migration revert` and see it works as expected
- Run `diesel migration redo` and see it fail

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues)  and the [disussion forum](https://github.com/diesel-rs/diesel/discussions)  for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:
Thanks for the report. Yes that's a bug and it should be fixed. Contributions are welcome.
> Thanks for the report. Yes that's a bug and it should be fixed. Contributions are welcome.

I can take a look at this if this is still an open issue?
This issue is still open. Contributions are welcome. The issue is in these lines of code
  https://github.com/diesel-rs/diesel/blob/master/diesel_cli/src/migrations/mod.rs#L324-L330
Awesome, ill take a look at it.
Thanks for the report. Yes that's a bug and it should be fixed. Contributions are welcome.
> Thanks for the report. Yes that's a bug and it should be fixed. Contributions are welcome.

I can take a look at this if this is still an open issue?
This issue is still open. Contributions are welcome. The issue is in these lines of code
  https://github.com/diesel-rs/diesel/blob/master/diesel_cli/src/migrations/mod.rs#L324-L330
Awesome, ill take a look at it.

Fix patch:
diff --git a/diesel_cli/src/migrations/mod.rs b/diesel_cli/src/migrations/mod.rs
index 9b6245bd652e..b12e7308c104 100644
--- a/diesel_cli/src/migrations/mod.rs
+++ b/diesel_cli/src/migrations/mod.rs
@@ -1,7 +1,7 @@
 use chrono::Utc;
 use clap::ArgMatches;
 use diesel::backend::Backend;
-use diesel::migration::MigrationSource;
+use diesel::migration::{Migration, MigrationSource};
 use diesel::Connection;
 use diesel_migrations::{FileBasedMigrations, HarnessWithOutput, MigrationError, MigrationHarness};
 use std::any::Any;
@@ -263,8 +263,11 @@ pub fn migrations_dir(matches: &ArgMatches) -> Result<PathBuf, MigrationError> {
 /// Reverts all the migrations, and then runs them again, if the `--all`
 /// argument is used. Otherwise it only redoes a specific number of migrations
 /// if the `--number` argument is used.
-/// Migrations are performed in a transaction. If either part fails,
+/// We try to execute the migrations in a single transaction so that f either part fails,
 /// the transaction is not committed.
+/// If the list of migrations that need to be redone contains a single migration
+/// with `run_in_transaction = false` or if the backend is MySQL we cannot use a
+/// transaction.
 fn redo_migrations<Conn, DB>(
     conn: &mut Conn,
     migrations_dir: FileBasedMigrations,
@@ -273,55 +276,79 @@ fn redo_migrations<Conn, DB>(
     DB: Backend,
     Conn: MigrationHarness<DB> + Connection<Backend = DB> + 'static,
 {
-    let migrations_inner = |harness: &mut HarnessWithOutput<Conn, _>| -> Result<(), Box<dyn Error + Send + Sync + 'static>> {
-        let reverted_versions = if args.get_flag("REDO_ALL") {
-            harness.revert_all_migrations(migrations_dir.clone())?
-        } else {
-            let number = args.get_one::<u64>("REDO_NUMBER").unwrap();
-            (0..*number)
-                .filter_map(|_|{
-                    match harness.revert_last_migration(migrations_dir.clone()) {
-                        Ok(v) => {
-                            Some(Ok(v))
-                        }
-                        Err(e) if e.is::<MigrationError>() => {
-                            match e.downcast_ref::<MigrationError>() {
-                                // If n is larger then the actual number of migrations,
-                                // just stop reverting them
-                                Some(MigrationError::NoMigrationRun) => None,
-                                _ => Some(Err(e)),
+    let migrations = MigrationSource::<DB>::migrations(&migrations_dir)
+        .unwrap_or_else(handle_error)
+        .into_iter()
+        .map(|m| (m.name().version().as_owned(), m))
+        .collect::<HashMap<_, _>>();
+    let applied_migrations = conn.applied_migrations().unwrap_or_else(handle_error);
+    let versions_to_revert = if args.get_flag("REDO_ALL") {
+        &applied_migrations
+    } else {
+        let number = args.get_one::<u64>("REDO_NUMBER").unwrap();
+        let number = std::cmp::min(*number as usize, applied_migrations.len());
+        &applied_migrations[..number]
+    };
+    let should_use_not_use_transaction = versions_to_revert.iter().any(|v| {
+        migrations
+            .get(v)
+            .map(|m| !m.metadata().run_in_transaction())
+            .unwrap_or_default()
+    });
+
+    let migrations_inner =
+        |harness: &mut HarnessWithOutput<Conn, _>|
+         -> Result<(), Box<dyn Error + Send + Sync + 'static>> {
+            // revert all the migrations
+            let reverted_versions = if args.get_flag("REDO_ALL") {
+                harness.revert_all_migrations(migrations_dir.clone())?
+            } else {
+                let number = args.get_one::<u64>("REDO_NUMBER").unwrap();
+                (0..*number)
+                    .filter_map(|_| {
+                        match harness.revert_last_migration(migrations_dir.clone()) {
+                            Ok(v) => Some(Ok(v)),
+                            Err(e) if e.is::<MigrationError>() => {
+                                match e.downcast_ref::<MigrationError>() {
+                                    // If n is larger then the actual number of migrations,
+                                    // just stop reverting them
+                                    Some(MigrationError::NoMigrationRun) => None,
+                                    _ => Some(Err(e)),
+                                }
                             }
+                            Err(e) => Some(Err(e)),
                         }
-                        Err(e) => {
-                            Some(Err(e))
-                        }
-                    }
-                })
-                .collect::<Result<Vec<_>, _>>()?
-        };
+                    })
+                    .collect::<Result<Vec<_>, _>>()?
+            };
 
-        let mut migrations = MigrationSource::<DB>::migrations(&migrations_dir)?
-            .into_iter()
-            .map(|m| (m.name().version().as_owned(), m))
-            .collect::<HashMap<_, _>>();
+            // get a mapping between migrations and migration versions
+             let mut migrations = MigrationSource::<DB>::migrations(&migrations_dir)
+                 .unwrap_or_else(handle_error)
+                 .into_iter()
+                 .map(|m| (m.name().version().as_owned(), m))
+                 .collect::<HashMap<_, _>>();
 
-        let mut migrations = reverted_versions
-            .into_iter()
-            .map(|v| {
-                migrations
-                    .remove(&v)
-                    .ok_or_else(|| MigrationError::UnknownMigrationVersion(v.as_owned()))
-            })
-            .collect::<Result<Vec<_>, _>>()?;
-        // Sort the migrations by version to apply them in order.
-        migrations.sort_by_key(|m| m.name().version().as_owned());
+            // build a list of migrations that need to be applied
+            let mut migrations = reverted_versions
+                .into_iter()
+                .map(|v| {
+                    migrations
+                        .remove(&v)
+                        .ok_or_else(|| MigrationError::UnknownMigrationVersion(v.as_owned()))
+                })
+                .collect::<Result<Vec<_>, _>>()?;
 
-        harness.run_migrations(&migrations)?;
+            // Sort the migrations by version to apply them in order.
+            migrations.sort_by_key(|m| m.name().version().as_owned());
 
-        Ok(())
-    };
+            // apply all outstanding migrations
+            harness.run_migrations(&migrations)?;
+
+            Ok(())
+        };
 
-    if should_redo_migration_in_transaction(conn) {
+    if !should_use_not_use_transaction && should_redo_migration_in_transaction(conn) {
         conn.transaction(|conn| migrations_inner(&mut HarnessWithOutput::write_to_stdout(conn)))
             .unwrap_or_else(handle_error);
     } else {


Test patch:
diff --git a/diesel_cli/tests/database_reset.rs b/diesel_cli/tests/database_reset.rs
index 23a5a8aa697d..795418d1d3db 100644
--- a/diesel_cli/tests/database_reset.rs
+++ b/diesel_cli/tests/database_reset.rs
@@ -32,6 +32,7 @@ fn reset_runs_database_setup() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(db.table_exists("posts"));
@@ -102,6 +103,7 @@ fn reset_works_with_migration_dir_by_arg() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(db.table_exists("posts"));
@@ -133,6 +135,7 @@ fn reset_works_with_migration_dir_by_env() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(db.table_exists("posts"));
@@ -215,6 +218,7 @@ fn reset_respects_migrations_dir_from_diesel_toml() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(db.table_exists("users"));
diff --git a/diesel_cli/tests/database_setup.rs b/diesel_cli/tests/database_setup.rs
index dfa6b4347fd7..ffedc1630a20 100644
--- a/diesel_cli/tests/database_setup.rs
+++ b/diesel_cli/tests/database_setup.rs
@@ -50,6 +50,7 @@ fn database_setup_runs_migrations_if_no_schema_table() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
@@ -97,6 +98,7 @@ fn database_setup_respects_migration_dir_by_arg_to_database() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
@@ -129,6 +131,7 @@ fn database_setup_respects_migration_dir_by_arg() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
@@ -161,6 +164,7 @@ fn database_setup_respects_migration_dir_by_env() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
@@ -200,6 +204,7 @@ fn database_setup_respects_migrations_dir_from_diesel_toml() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
diff --git a/diesel_cli/tests/migration_list.rs b/diesel_cli/tests/migration_list.rs
index e94c0aa46ee3..f98497dd0a8e 100644
--- a/diesel_cli/tests/migration_list.rs
+++ b/diesel_cli/tests/migration_list.rs
@@ -19,6 +19,7 @@ fn migration_list_lists_pending_applied_migrations() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -67,7 +68,7 @@ fn migration_list_lists_migrations_ordered_by_timestamp() {
     p.command("setup").run();
 
     let tag1 = format!("{}_initial", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag1, "", Some(""));
+    p.create_migration(&tag1, "", Some(""), None);
 
     let result = p.command("migration").arg("list").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
@@ -76,7 +77,7 @@ fn migration_list_lists_migrations_ordered_by_timestamp() {
     sleep(Duration::from_millis(1100));
 
     let tag2 = format!("{}_alter", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag2, "", Some(""));
+    p.create_migration(&tag2, "", Some(""), None);
 
     let result = p.command("migration").arg("list").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
@@ -93,23 +94,23 @@ fn migration_list_orders_unknown_timestamps_last() {
     p.command("setup").run();
 
     let tag1 = format!("{}_migration1", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag1, "", Some(""));
+    p.create_migration(&tag1, "", Some(""), None);
 
     let tag4 = "abc_migration4";
-    p.create_migration(tag4, "", Some(""));
+    p.create_migration(tag4, "", Some(""), None);
 
     let tag5 = "zzz_migration5";
-    p.create_migration(tag5, "", Some(""));
+    p.create_migration(tag5, "", Some(""), None);
 
     sleep(Duration::from_millis(1100));
 
     let tag2 = format!("{}_migration2", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag2, "", Some(""));
+    p.create_migration(&tag2, "", Some(""), None);
 
     sleep(Duration::from_millis(1100));
 
     let tag3 = format!("{}_migration3", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag3, "", Some(""));
+    p.create_migration(&tag3, "", Some(""), None);
 
     let result = p.command("migration").arg("list").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
@@ -126,22 +127,22 @@ fn migration_list_orders_nontimestamp_versions_alphabetically() {
     p.command("setup").run();
 
     let tag4 = "a_migration";
-    p.create_migration(tag4, "", Some(""));
+    p.create_migration(tag4, "", Some(""), None);
 
     let tag6 = "bc_migration";
-    p.create_migration(tag6, "", Some(""));
+    p.create_migration(tag6, "", Some(""), None);
 
     let tag5 = "aa_migration";
-    p.create_migration(tag5, "", Some(""));
+    p.create_migration(tag5, "", Some(""), None);
 
     let tag1 = "!wow_migration";
-    p.create_migration(tag1, "", Some(""));
+    p.create_migration(tag1, "", Some(""), None);
 
     let tag3 = "7letters";
-    p.create_migration(tag3, "", Some(""));
+    p.create_migration(tag3, "", Some(""), None);
 
     let tag2 = format!("{}_stamped_migration", Utc::now().format(TIMESTAMP_FORMAT));
-    p.create_migration(&tag2, "", Some(""));
+    p.create_migration(&tag2, "", Some(""), None);
 
     let result = p.command("migration").arg("list").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
@@ -158,10 +159,10 @@ fn migration_list_orders_old_and_new_timestamp_forms_mixed_correctly() {
     p.command("setup").run();
 
     let tag1 = "20170505070309_migration";
-    p.create_migration(tag1, "", Some(""));
+    p.create_migration(tag1, "", Some(""), None);
 
     let tag2 = "2017-11-23-064836_migration";
-    p.create_migration(tag2, "", Some(""));
+    p.create_migration(tag2, "", Some(""), None);
 
     let result = p.command("migration").arg("list").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
@@ -190,6 +191,7 @@ fn migration_list_respects_migrations_dir_from_diesel_toml() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
diff --git a/diesel_cli/tests/migration_redo.rs b/diesel_cli/tests/migration_redo.rs
index 94aff4103f72..19fa76f5de1e 100644
--- a/diesel_cli/tests/migration_redo.rs
+++ b/diesel_cli/tests/migration_redo.rs
@@ -7,6 +7,7 @@ fn migration_redo_runs_the_last_migration_down_and_up() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY);",
         Some("DROP TABLE users;"),
+        None,
     );
 
     // Make sure the project is setup
@@ -38,18 +39,21 @@ fn migration_redo_runs_the_last_two_migrations_down_and_up() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -88,6 +92,7 @@ fn migration_redo_respects_migration_dir_var() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY);",
         Some("DROP TABLE users;"),
+        None,
     );
 
     // Make sure the project is setup
@@ -121,6 +126,7 @@ fn migration_redo_respects_migration_dir_env() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY);",
         Some("DROP TABLE users;"),
+        None,
     );
 
     // Make sure the project is setup
@@ -154,6 +160,7 @@ fn error_migrations_fails() {
         "redo_error_migrations_fails",
         "CREATE TABLE users (id INTEGER PRIMARY KEY);",
         Some("DROP TABLE users};"),
+        None,
     );
 
     // Make sure the project is setup
@@ -185,6 +192,7 @@ fn migration_redo_respects_migrations_dir_from_diesel_toml() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY);",
         Some("DROP TABLE users;"),
+        None,
     );
 
     // Make sure the project is setup
@@ -216,18 +224,21 @@ fn migration_redo_all_runs_all_migrations_down_and_up() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -269,18 +280,21 @@ fn migration_redo_with_more_than_max_should_redo_all() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -356,6 +370,7 @@ fn migration_redo_with_zero_should_not_revert_any_migration() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     // Make sure the project is setup
@@ -369,3 +384,121 @@ fn migration_redo_with_zero_should_not_revert_any_migration() {
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
     assert!(result.stdout() == "");
 }
+
+#[cfg(not(feature = "mysql"))] // mysql does not support DDL + Transactions
+#[test]
+fn migration_redo_without_transaction() {
+    let p = project("migration_redo_without_transaction")
+        .folder("migrations")
+        .build();
+    let db = database(&p.database_url());
+
+    p.create_migration(
+        "2023-05-08-210424_without_transaction",
+        "BEGIN TRANSACTION;CREATE TABLE customers ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    // Make sure the project is setup
+    p.command("setup").run();
+
+    assert!(db.table_exists("customers"));
+
+    // Should not revert any migration.
+    let result = p.command("migration").arg("redo").run();
+
+    assert!(result.is_success(), "Result was unsuccessful {:?}", result);
+    assert!(
+        result.stdout()
+            == "Rolling back migration 2023-05-08-210424_without_transaction\n\
+                                Running migration 2023-05-08-210424_without_transaction\n"
+    );
+    assert!(db.table_exists("customers"));
+}
+
+#[cfg(not(feature = "mysql"))] // mysql does not support DDL + Transactions
+#[test]
+fn migration_redo_without_transaction_twice() {
+    let p = project("migration_redo_without_transaction_twice")
+        .folder("migrations")
+        .build();
+    let db = database(&p.database_url());
+
+    p.create_migration(
+        "2023-05-08-210424_without_transaction",
+        "BEGIN TRANSACTION;CREATE TABLE customers ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    p.create_migration(
+        "2023-05-08-210425_without_transaction2",
+        "BEGIN TRANSACTION;CREATE TABLE customers2 ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers2; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    // Make sure the project is setup
+    p.command("setup").run();
+
+    assert!(db.table_exists("customers"));
+    assert!(db.table_exists("customers2"));
+
+    // Should not revert any migration.
+    let result = p.command("migration").arg("redo").arg("-n").arg("2").run();
+
+    assert!(result.is_success(), "Result was unsuccessful {:?}", result);
+    assert!(
+        result.stdout()
+            == "Rolling back migration 2023-05-08-210425_without_transaction2\n\
+                Rolling back migration 2023-05-08-210424_without_transaction\n\
+                Running migration 2023-05-08-210424_without_transaction\n\
+                Running migration 2023-05-08-210425_without_transaction2\n"
+    );
+    assert!(db.table_exists("customers"));
+    assert!(db.table_exists("customers2"));
+}
+
+#[cfg(not(feature = "mysql"))] // mysql does not support DDL + Transactions
+#[test]
+fn migration_redo_without_transaction_all() {
+    let p = project("migration_redo_without_transaction_all")
+        .folder("migrations")
+        .build();
+    let db = database(&p.database_url());
+
+    p.create_migration(
+        "2023-05-08-210424_without_transaction",
+        "BEGIN TRANSACTION;CREATE TABLE customers ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    p.create_migration(
+        "2023-05-08-210425_without_transaction2",
+        "BEGIN TRANSACTION;CREATE TABLE customers2 ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers2; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    // Make sure the project is setup
+    p.command("setup").run();
+
+    assert!(db.table_exists("customers"));
+    assert!(db.table_exists("customers2"));
+
+    // Should not revert any migration.
+    let result = p.command("migration").arg("redo").arg("--all").run();
+
+    assert!(result.is_success(), "Result was unsuccessful {:?}", result);
+    assert!(
+        result.stdout()
+            == "Rolling back migration 2023-05-08-210425_without_transaction2\n\
+                Rolling back migration 2023-05-08-210424_without_transaction\n\
+                Running migration 2023-05-08-210424_without_transaction\n\
+                Running migration 2023-05-08-210425_without_transaction2\n"
+    );
+    assert!(db.table_exists("customers"));
+    assert!(db.table_exists("customers2"));
+}
diff --git a/diesel_cli/tests/migration_revert.rs b/diesel_cli/tests/migration_revert.rs
index d51273f41e5f..e4aed95d6fe8 100644
--- a/diesel_cli/tests/migration_revert.rs
+++ b/diesel_cli/tests/migration_revert.rs
@@ -11,6 +11,7 @@ fn migration_revert_runs_the_last_migration_down() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // Make sure the project is setup
@@ -39,6 +40,7 @@ fn migration_revert_respects_migration_dir_var() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // Make sure the project is setup.
@@ -71,6 +73,7 @@ fn migration_revert_respects_migration_dir_env() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // Make sure the project is setup.
@@ -113,6 +116,7 @@ fn migration_revert_respects_migration_dir_from_diesel_toml() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // Make sure the project is setup.
@@ -142,18 +146,21 @@ fn migration_revert_runs_the_last_two_migration_down() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -195,18 +202,21 @@ fn migration_revert_all_runs_the_migrations_down() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -245,6 +255,7 @@ fn migration_revert_with_zero_should_not_revert_any_migration() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     // Make sure the project is setup
@@ -307,18 +318,21 @@ fn migration_revert_with_more_than_max_should_revert_all() {
         "2017-08-31-210424_create_customers",
         "CREATE TABLE customers ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE customers"),
+        None,
     );
 
     p.create_migration(
         "2017-09-03-210424_create_contracts",
         "CREATE TABLE contracts ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE contracts"),
+        None,
     );
 
     p.create_migration(
         "2017-09-12-210424_create_bills",
         "CREATE TABLE bills ( id INTEGER PRIMARY KEY )",
         Some("DROP TABLE bills"),
+        None,
     );
 
     // Make sure the project is setup
@@ -362,6 +376,7 @@ fn migration_revert_gives_reasonable_error_message_on_missing_down() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         None,
+        None,
     );
 
     // Make sure the project is setup
diff --git a/diesel_cli/tests/migration_run.rs b/diesel_cli/tests/migration_run.rs
index 4baa3f463128..72d3c92e18ed 100644
--- a/diesel_cli/tests/migration_run.rs
+++ b/diesel_cli/tests/migration_run.rs
@@ -16,6 +16,7 @@ fn migration_run_runs_pending_migrations() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -45,6 +46,7 @@ fn migration_run_inserts_run_on_timestamps() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     let migrations_done: bool = select(sql::<Bool>(
@@ -104,7 +106,7 @@ fn empty_migrations_are_not_valid() {
 
     p.command("setup").run();
 
-    p.create_migration("12345_empty_migration", "", None);
+    p.create_migration("12345_empty_migration", "", None, None);
 
     let result = p.command("migration").arg("run").run();
 
@@ -126,6 +128,7 @@ fn error_migrations_fails() {
         "run_error_migrations_fails",
         "CREATE TABLE users (id INTEGER PRIMARY KEY}",
         Some("DROP TABLE users"),
+        None,
     );
 
     let result = p.command("migration").arg("run").run();
@@ -150,6 +153,7 @@ fn error_migrations_when_use_invalid_database_url() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     let result = p
@@ -177,6 +181,7 @@ fn any_pending_migrations_works() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     let result = p.command("migration").arg("pending").run();
@@ -196,6 +201,7 @@ fn any_pending_migrations_after_running() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     p.command("migration").arg("run").run();
@@ -217,6 +223,7 @@ fn any_pending_migrations_after_running_and_creating() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     p.command("migration").arg("run").run();
@@ -225,6 +232,7 @@ fn any_pending_migrations_after_running_and_creating() {
         "123456_create_posts_table",
         "CREATE TABLE posts (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE posts"),
+        None,
     );
 
     let result = p.command("migration").arg("pending").run();
@@ -247,6 +255,7 @@ fn migration_run_runs_pending_migrations_custom_database_url_1() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -282,6 +291,7 @@ fn migration_run_runs_pending_migrations_custom_database_url_2() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -318,6 +328,7 @@ fn migration_run_runs_pending_migrations_custom_migration_dir_1() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -354,6 +365,7 @@ fn migration_run_runs_pending_migrations_custom_migration_dir_2() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -394,6 +406,7 @@ fn migration_run_updates_schema_if_config_present() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!p.has_file("src/my_schema.rs"));
@@ -422,6 +435,7 @@ fn migrations_can_be_run_with_no_config_file() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -453,6 +467,7 @@ fn migrations_can_be_run_with_no_cargo_toml() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -484,6 +499,7 @@ fn migrations_can_be_run_with_no_down() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         None,
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -519,6 +535,7 @@ fn verify_schema_errors_if_schema_file_would_change() {
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!p.has_file("src/my_schema.rs"));
@@ -532,6 +549,7 @@ fn verify_schema_errors_if_schema_file_would_change() {
         "12346_create_posts_table",
         "CREATE TABLE posts (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE posts"),
+        None,
     );
 
     let result = p
@@ -580,6 +598,7 @@ fn migration_run_runs_pending_migrations_custom_migrations_dir_from_diesel_toml(
         "12345_create_users_table",
         "CREATE TABLE users (id INTEGER PRIMARY KEY)",
         Some("DROP TABLE users"),
+        None,
     );
 
     assert!(!db.table_exists("users"));
@@ -594,3 +613,28 @@ fn migration_run_runs_pending_migrations_custom_migrations_dir_from_diesel_toml(
     );
     assert!(db.table_exists("users"));
 }
+
+#[cfg(not(feature = "mysql"))] // mysql does not support DDL + Transactions
+#[test]
+fn migration_run_without_transaction() {
+    let p = project("migration_run_without_transaction")
+        .folder("migrations")
+        .build();
+    let db = database(&p.database_url());
+
+    // Make sure the project is setup
+    p.command("setup").run();
+
+    p.create_migration(
+        "2023-05-08-210424_without_transaction",
+        "BEGIN TRANSACTION;CREATE TABLE customers ( id INTEGER PRIMARY KEY );COMMIT TRANSACTION;",
+        Some("BEGIN TRANSACTION;DROP TABLE customers; COMMIT TRANSACTION;"),
+        Some("run_in_transaction = false"),
+    );
+
+    let result = p.command("migration").arg("run").run();
+    assert!(db.table_exists("customers"));
+
+    assert!(result.is_success(), "Result was unsuccessful {:?}", result);
+    assert!(result.stdout() == "Running migration 2023-05-08-210424_without_transaction\n");
+}
diff --git a/diesel_cli/tests/print_schema.rs b/diesel_cli/tests/print_schema.rs
index d2e54ff43c83..d1e4a9108c3c 100644
--- a/diesel_cli/tests/print_schema.rs
+++ b/diesel_cli/tests/print_schema.rs
@@ -367,7 +367,7 @@ fn test_print_schema_config(test_name: &str, test_path: &Path, schema: String) {
     let p = p.build();
 
     p.command("setup").run();
-    p.create_migration("12345_create_schema", &schema, None);
+    p.create_migration("12345_create_schema", &schema, None, None);
     let result = p.command("migration").arg("run").run();
     assert!(result.is_success(), "Result was unsuccessful {:?}", result);
 
diff --git a/diesel_cli/tests/setup.rs b/diesel_cli/tests/setup.rs
index 2d6ba09943d9..d0bab07b5b17 100644
--- a/diesel_cli/tests/setup.rs
+++ b/diesel_cli/tests/setup.rs
@@ -89,6 +89,7 @@ fn setup_runs_migrations_if_no_schema_table() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     // sanity check
@@ -117,6 +118,7 @@ fn setup_doesnt_run_migrations_if_schema_table_exists() {
         "12345_create_users_table",
         "CREATE TABLE users ( id INTEGER )",
         Some("DROP TABLE users"),
+        None,
     );
 
     let result = p.command("setup").run();
diff --git a/diesel_cli/tests/support/project_builder.rs b/diesel_cli/tests/support/project_builder.rs
index 547cbd245e19..92465379e41b 100644
--- a/diesel_cli/tests/support/project_builder.rs
+++ b/diesel_cli/tests/support/project_builder.rs
@@ -156,8 +156,8 @@ impl Project {
         migration_path.display().to_string()
     }
 
-    pub fn create_migration(&self, name: &str, up: &str, down: Option<&str>) {
-        self.create_migration_in_directory("migrations", name, up, down);
+    pub fn create_migration(&self, name: &str, up: &str, down: Option<&str>, config: Option<&str>) {
+        self.create_migration_in_directory("migrations", name, up, down, config);
     }
 
     pub fn create_migration_in_directory(
@@ -166,6 +166,7 @@ impl Project {
         name: &str,
         up: &str,
         down: Option<&str>,
+        config: Option<&str>,
     ) {
         let migration_path = self.directory.path().join(directory).join(name);
         fs::create_dir(&migration_path)
@@ -177,6 +178,11 @@ impl Project {
             let mut down_file = fs::File::create(migration_path.join("down.sql")).unwrap();
             down_file.write_all(down.as_bytes()).unwrap();
         }
+
+        if let Some(config) = config {
+            let mut metadata_file = fs::File::create(migration_path.join("metadata.toml")).unwrap();
+            metadata_file.write_all(config.as_bytes()).unwrap();
+        }
     }
 }