Laravel migration transaction

user_1026619 0
Viewed 14997

When developing i'm having so many issues with migrations in laravel.

I create a migration. When i finish creating it, there's a small error by the middle of the migration (say, a foreign key constraint) that makes "php artisan migrate" fail. He tells me where the error is, indeed, but then migrate gets to an unconsistent state, where all the modifications to the database made before the error are made, and not the next ones.

This makes that when I fix the error and re-run migrate, the first statement fails, as the column/table is already created/modified. Then the only solution I know is to go to my database and "rollback" everything by hand, which is way longer to do.

migrate:rollback tries to rollback the previous migrations, as the current was not applied succesfully.

I also tried to wrap all my code into a DB::transaction(), but it still doesn't work.

Is there any solution for this? Or i just have to keep rolling things back by hand?



edit, adding an example (not writing Schema builder code, just some kind of pseudo-code):
Migration1:

Create Table users (id, name, last_name, email)

Migration1 executed OK. Some days later we make Migration 2:

Create Table items (id, user_id references users.id)
Alter Table users make_some_error_here

Now what will happen is that migrate will call the first statement and will create the table items with his foreign key to users. Then when he tries to apply the next statement it will fail.

If we fix the make_some_error_here, we can't run migrate because the table "items" it's created. We can't rollback (nor refresh, nor reset), because we can't delete the table users since there's a foreign key constraint from the table items.

Then the only way to continue is to go to the database and delete the table items by hand, to get migrate in a consistent state.

laravel migrate php
7 Answers
user_14012960

I know it's an old topic, but there was activity a month ago, so here are my 2 cents.

This answer is for MySql 8 and Laravel 5.8 MySql, since MySql 8, introduced atomic DDL: https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html Laravel at the start of migration checks if the schema grammar supports migrations in a transaction and if it does starts it as such. The problem is that the MySql schema grammar has it set to false. We can extend the Migrator, MySql schema grammar and MigrationServiceProvider, and register the service provider like so:

<?php

namespace App\Console;

use Illuminate\Database\Migrations\Migrator as BaseMigrator;

use App\Database\Schema\Grammars\MySqlGrammar;

class Migrator extends BaseMigrator {
    protected function getSchemaGrammar( $connection ) {
        if ( get_class( $connection ) === 'Illuminate\Database\MySqlConnection' ) {
            $connection->setSchemaGrammar( new MySqlGrammar );
        }
        if ( is_null( $grammar = $connection->getSchemaGrammar() ) ) {
            $connection->useDefaultSchemaGrammar();
            $grammar = $connection->getSchemaGrammar();
        }
        return $grammar;
    }
}


<?php

namespace App\Database\Schema\Grammars;

use Illuminate\Database\Schema\Grammars\MySqlGrammar as BaseMySqlGrammar;

class MySqlGrammar extends BaseMySqlGrammar {
    public function __construct() {
        $this->transactions = config( "database.transactions", false );
    }
}


<?php

namespace App\Providers;

use Illuminate\Database\MigrationServiceProvider as BaseMigrationServiceProvider;

use App\Console\Migrator;

class MigrationServiceProvider extends BaseMigrationServiceProvider {   
    /**
     * Register the migrator service.
     * @return void
     */
    protected function registerMigrator() {
        $this->app->singleton( 'migrator', function( $app ) {
            return new Migrator( $app[ 'migration.repository' ], $app[ 'db' ], $app[ 'files' ] );
        } );
        $this->app->singleton(\Illuminate\Database\Migrations\Migrator::class, function ( $app ) {
            return $app[ 'migrator' ];
        } );
    }


<?php

return [
    'providers' => [

        /*
         * Laravel Framework Service Providers...
         */
        App\Providers\MigrationServiceProvider::class,

    ],
];

Of course, we have to add transactions to our database config... DISCLAIMER - Haven't tested yet, but looking only at the code it should work as advertised :) Update to follow when I test...

user_16494980

Most of the answers overlook a very important fact about a very simple way to structure your development against this. If one were to make all migrations reversible and add as much of the dev testing data as possible through seeders, then when artisan migrate fails on the dev environment one can correct the error and then do

php artisan migrate:fresh --seed

Optionally coupled with a :rollback to test rolling back.

For me personally artisan migrate:fresh --seed is the second most used artisan command after artisan tinker.

user_36361840
Related