Laravel Custom Command: Drop All Tables

Author: Filip Lekić
Posted on 12 March, 2018
In this blog post we will go through the creation process of the custom artisan command that empties all tables in our database

During development there is often, at least in our team, a need to drop all tables followed by migrate and seed. We caught ourselves, especially in the process of initial application development, to repeat dropping tables from some external db application or console, and then using artisan migrate && artisan db:seed. Wouldn't it be nice if we can do all three steps from artisan itself?

In this blog post we will be creating custom command for dropping tables from currently used database (defined in .env file).

We will generate skeleton for custom command with artisan: php artisan make:command DropTables, then we should navigate to the newly generated file: "our-project/Console/Commands/DropTables.php", and include, beside required Command class DB facade and ConfirmableTrait.

File: app_path/app/Console/Commands/DropTables.php
namespace App\Console\Commands;

use DB;
use Illuminate\Console\Command;
use Illuminate\Console\ConfirmableTrait;

Now let's jump into declaration of class itself. We will add protected property which will hold name of the database, and change signature of the command.

class DropTables extends Command
{
    use ConfirmableTrait;

    /**
     * Current database name (from env)
     *
     * @var string
     */
    protected $db;

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:droptables {--force : Force the operation to run in production environment.}';

Using ConfirmableTrait we can extend functionality of our DropTables class by adding the method for confirmation before proceeding with the action. This method only asks for confirmation in production.

Then we should proceed with the changes on $description property and define constructor itself.

/**
* The console command description.
*
* @var string
*/
protected $description = 'Drop all tables in current DB (migration table too)';

/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
    $this->db = env('DB_DATABASE');

    parent::__construct();
}

Finally, we came to the central part of this blog post and it's time to go through the handle method, which will hold the main logic of the command class.

public function handle()
{
    // If in production environment, confirm before proceeding
    if (!$this->confirmToProceed()) {
        // or you will be back at the prompt line
        return;
    }

    // Get an array of all table names in project database
    $tables = DB::select('SHOW TABLES');

    if (empty($tables)) {
        $this->comment(PHP_EOL . "$this->db DB has no tables");
    } else {
        if ($this->confirm("All tables in {$this->db} DB will be deleted! Do you really want to continue?")) {
            $colname = 'Tables_in_' . $this->db;

            DB::beginTransaction();

            // Remove foreign key checking
            DB::statement('SET FOREIGN_KEY_CHECKS = 0');

            foreach ($tables as $table) {
                // Log action to terminal
                $this->comment("Dropping {$table->$colname} table");

                // Drop the table
                DB::unprepared("DROP TABLE {$table->$colname}");
            }

            // Reset foreign key checks back to 1
            DB::statement('SET FOREIGN_KEY_CHECKS = 1');

            // Commit changes into database
            DB::commit();
        } else {
            $this->comment(PHP_EOL . 'Command aborted' . PHP_EOL);
        }

        // Prints empty line, same as echo("\n")
        echo(PHP_EOL);
    }
}
Dropping tables from database is irreversible process and you will lose all data these tables contains. So use it with caution.

After completing the Class file you should be able to run php artisan db:droptables, follow the questions asked by the command and your database tables will be dropped for good.


This is all for now, and as always, keep dropping. :)