Laravel is great at good many things and its inbuilt ORM & query builder makes working with multiple databases a breeze. But while working yesterday, I came across a curious issue – running artisan migrate:fresh command breaks if your tables are spread out among multiple databases.
The problem!
Let’s look at what the issue is. Consider that you have two database connections named db-a & db-b and db-a is the default connection.
db-a
- table-a
- table-b
db-b
- table-c
Now for table-c your migration would look something like this:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::connection('db-b')->create('table_c_s', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->text('description')->nullable();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::connection('db-b')->dropIfExists('table_c_s');
}
};
If you run artisan migrate:fresh for the first time then all will go well. But all subsequent runs will result in QueryException being thrown because Laravel is unable to use the specified connection to drop the table & so when it tries to create it again the database will not allow it as the table already exists.
Now there are a couple of ways to go around this issue. Let’s take a look at the first one.
A straightforward solution
One way to go around this issue is to call the down() method within the up() method before Schema is called to create the table. So your migration would look something like this:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
$this->down();
Schema::connection('db-b')->create('table_c_s', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->text('description')->nullable();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::connection('db-b')->dropIfExists('table_c_s');
}
};
But this approach is not that great, can be risky and if more than one person is working on the codebase then its easy to forget about this for all the migrations which are meant for non-default databases.
So how do we go about it in a better way?
The better solution!
Since Laravel has events & listeners and all its core services fire events which can easily be hooked into (like magic), we can create an event listener which drops all tables in non-default database(s) if migrate:fresh command is run.
Run the below command to create the class for our event listener.
php artisan make:listener BeforeMigrateFreshListener
This will create the BeforeMigrateFreshListener class in app/listeners folder.
Now we don’t need to do anything to register our event listener with Laravel. It will be auto-discovered by Laravel and automatically registered provided there’s a method whose name begins with handle or __invoke in the listener class and it accepts a parameter which is type-hinted with the event on which it is listening on. (refer here) Since we want to listen for an event which is triggered before our migrate:fresh artisan command is run, we will type-hint the parameter with CommandStarting event.
So our event listener class would look something like this:
namespace App\Listeners;
use Illuminate\Console\Events\CommandStarting;
class BeforeMigrateFreshListener {
/**
* Handle the event.
*/
public function handle ( CommandStarting $event ) : void
{
if ( 'migrate:fresh' !== $event->command ) {
return;
}
// Here we write the code to drop all tables
// in our secondary database.
}
}
Now, if you are using MySQL/MariaDB then you should know that there is no straightforward way to drop all tables in a database. There are multiple ways to go about it & the way I chose is to fetch all table names for the database, loop over them & then drop them one at a time.
So our code would be something like:
$tables = DB::connection( 'db-b' )
->select(
'SELECT table_name FROM information_schema.tables WHERE table_schema = :dbname;',
[ 'dbname' => config( 'database.connections.db-b.database' ) ]
);
foreach ( $tables as $table ) {
DB::connection( 'db-b' )
->statement(
"DROP TABLE IF EXISTS `{$table->table_name}`;"
);
}
And just to make sure this does not run accidentally on production, we will use app()->isProduction().
So putting it all together, here’s how our event listener would look like:
namespace App\Listeners;
use Illuminate\Console\Events\CommandStarting;
use Illuminate\Support\Facades\DB;
class BeforeMigrateFreshListener {
protected const DB_CONNECTION_TO_USE = 'db-b';
/**
* Handle the event.
*/
public function handle ( CommandStarting $event ) : void
{
// Let's NOT run this on production environment.
if ( app()->isProduction() ) {
return;
}
// Let's make sure to run this only on our artisan command.
if ( 'migrate:fresh' !== $event->command ) {
return;
}
$tables = DB::connection( static::DB_CONNECTION_TO_USE )
->select(
'SELECT table_name FROM information_schema.tables WHERE table_schema = :dbname;',
[ 'dbname' => config( 'database.connections.db-b.database' ) ]
);
if ( empty( $tables ) ) {
return;
}
foreach ( $tables as $table ) {
DB::connection( static::DB_CONNECTION_TO_USE )
->statement(
"DROP TABLE IF EXISTS `{$table->table_name}`;"
);
}
}
}
We can now run php artisan migrate:fresh and it will not have a seizure because it ignores the DB connection when trying to drop tables on non-default databases. And a new person can be onboarded to the codebase without them having to remember to call the down() method inside the up() method when creating migrations meant for non-default databases.
Closing words
You might be wondering that this seems like a bug in the Laravel framework & should be resolved there. You are quite right. I searched for this issue & I couldn’t find anything that’s open at present, so I opened one on Github. Turns out there’s a similar issue already open which is being tracked by the Laravel team.
Hopefully this issue will get resolved soon. But until that happens, the show must go on & the work-arounds we looked at here will keep us going.