Not all databases run migrations the same

In a tip earlier this month, I made the statement about "one of the database engines that supports transactions around schema changes." This prompted some questions, so today I'm going to explain what I was referring to in more detail.

Some database engines, like Postgres, SQL Server and SQLite, allow schema changes inside a transaction. MySQL, on the other hand, does not. If you issue any schema changes inside a MySQL transaction, it automatically commits the transaction.

Why does this matter? It affects how your migrations are run.

Laravel is smart enough to know which database engines support schema changes inside a transaction and which do not. When you run php artisan migrate, Laravel will wrap all the migrations in a transaction for the databases that support it.

So let's say your migration creates a table, adds a few columns to another table, and then drops a column in a table.

What happens if that last drop column fails? If you're using a database that supports transactions around schema changes, Laravel will roll back all the changes. But if you're using MySQL, it will fail and leave the database in an inconsistent state.

I'm not saying this to complain about MySQL. Currently, it's still my default database on Laravel applications.

But it is important to know about these differences, especially if you use a different database for testing than you run in production. These are the sorts of differences that can cause unpleasant surprises on deployment.

Here to help,

Joel

P.S. I am getting more interested in trying SQLite or Postgres in production. Have you used them? What do you like? Hit reply and let me know.