Possibility to make the whole migrate command transactional

[stof]

Currently, each migration gets wrapped in a transaction, ensuring that we don't execute half of the migration in case of failure (for platforms supporting to put DDL statements in a transaction, which is the case of PostgreSQL but was not the case of MySQL last time I used it).
However, if you have multiple unexecuted migrations, it could be useful to ask the migrations:migrate command to wrap the whole execution in a transaction, so that the whole execution of the command is atomic. My use case is to be able to have a all-or-nothing migration during a deployment process, even in case multiple features shipping a DB migrations are deployed in the same deployment rather than multiple ones.

What do you think about this feature ?

[PowerKiKi]

Atomic DDL just landed in the brand new MySQL 8. Unfortunately it seems it is not supported in MariaDB yet. But that would still be awesome to support it in this project.

[stof]

Atomic DDL still does not allow to perform DDL statements in a transaction involving other SQL statements (which is almost always required as soon as your migrations apply on a non-empty database)

[lcobucci]

@stof I think that it's useful and it could be set in the configuration file for the project, so the user can specify the granularity level of the transaction.

[jwage]

@stof Do you think it is best to make this an option on the migrate command or a configuration option? Or both?

[Majkl578]

Info about support by other vendors: https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

[stof]

@jwage I would like to be able to provide this in the configuration, to avoid having to tell each of our devs to use it in the CLI. but I'm fine if it available in both places.

[stof]

Note that DBAL already has a platform API to know whether the platform supports transactional DDL. And doctrine/migrations already uses this API to wrap each migration in a transaction when supported.
This feature request is about being able to switch between transaction per migration to single transaction per run (and it would stay on no transaction when unsupported, like today)

[PowerKiKi]

I would rather have no configuration at all and always use the highest level of atomicity as possible. This would be consistent with existing behavior, less things to implement, and you would still be able to execute a single migration with the migrations:executecommand should you need to have a partially migrated db.