Laravel migration transaction

Question

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.

Answer 1

It is not a Laravel limitation, I bet you use MYSQL, right?

As MYSQL documentation says here

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

And we have a recommendation of Taylor Otwell himself here saying:

My best advice is to do a single operation per migration so that your migrations stay very granular.

-- UPDATE --

Do not worry!

The best practices say:

You should never make a breaking change.

It means, in one deployment you create new tables and fields and deploy a new release that uses them. In a next deployment, you delete unused tables and fields.

Now, even if you'll get a problem in either of these deployments, don't worry if your migration failed, the working release uses the functional data structure anyway. And with the single operation per migration, you'll find a problem in no time.

Answer 2

I'm using MySql and I'm having this problem.

My solution depends that your down() method does exactly what you do in the up() but backwards.

This is what i go:

try{     Schema::create('table1', function (Blueprint $table) {         //...     });     Schema::create('tabla2', function (Blueprint $table) {         //...     }); }catch(PDOException $ex){     $this->down();     throw $ex; } 

So here if something fails automatically calls the down() method and throws again the exception.

Instead of using the migration between transaction() do it between this try