Implementation of database transaction on Eloquent functions

[themegazord]

Recently I've been through something weird on table creation using Eloquent (I know what i done wrong).

I code this:

public function up(): void
{
Schema::create('empcont', function (Blueprint $table) {
$table->id('empcont_id');
$table->unsignedBigInteger('empresa_id');
$table->unsignedBigInteger('contabilidade_id');
$table->timestamps();

  $table->foreign('empresa_id')->references('empresa_id')->on('empresas');
  $table->foreign('contabilidade_id')->references('contabilidade')->on('contabilidades');
});

}

And receive this error:

SQLSTATE[HY000]: General error: 3734 Failed to add the foreign key constraint. Missing column 'contabilidade' for constraint 'empcont_contabilidade_id_foreign' in the referenced table 'contabilidades' (Connection: mysql, SQL: alter table empcont add constraint empcont_contabilidade_id_foreign foreign key (contabilidade_id) references contabilidades (contabilidade))

This error occurred 'cause a I wrote "contabilidade" as attribute references() function. And I wrote this code to fix and actually create my table:

public function up(): void
{
Schema::table('empcont', function (Blueprint $table) {
$table->dropForeign('empcont_empresa_id_foreign');
});

Schema::dropIfExists('empcont');
Schema::create('empcont', function (Blueprint $table) {
  $table->id('empcont_id');
  $table->unsignedBigInteger('empresa_id');
  $table->unsignedBigInteger('contabilidade_id');
  $table->timestamps();

  $table->foreign('empresa_id')->references('empresa_id')->on('empresas');
  $table->foreign('contabilidade_id')->references('contabilidade_id')->on('contabilidades');
});

}

After that, i ran php artisan migrate and my table was finally created.

All of this could be avoided if Eloquent functions had a database transaction. like DB::beginTransaction facade.

[macropay-solutions]

Hi. You mean if Schema::create had in it a DB transaction?

[themegazord]

Not just create, but any Schema facade.

[henzeb]

DDL actions do not fit in transactions as they have an implicit commit. At least that is for MySQL.

You simply need to test and make sure your migrations work....

[themegazord]

You are correct, if I had done a test to check if the migration is working correctly it would solve the problem, but your argument also does not invalidate that there could be this possibility of the transaction or some internal private or protected method that validates what is being created, changed or removed, however, I think that in this way the performance would not be as good as that of a transaction.

[marius-mcp]

@themegazord imagine your migration takes 20 minutes to run. A db transaction in that case would mess/block something in your prod environment? How about the case when you know it will take 1 hour to finish and you prefer to run it manually via sql rather than via migration?