"Drop all tables" instead of "migration rollback"

Just delete the tables in the database and migrate again?

Also I do think migrations up and down function really do add value to your application, especially when you have an application running!

I don't want to manually delete them every time I run the test suite on the testing database, so if it could be automated (or exists somewhere as a command), that would be a lot easier :-)

Ie do "php artisan migrate:refresh" but not rolling back, just "drop table" on all existing tables, then migrate forward?

migrate:refresh does exactly that though?

No, it rolls back migrations:

        foreach ($migrations as $migration) {
            $this->runDown((object) ['migration' => $migration], $pretend);
        }

I alias this for a fresh migration

alias ms="php artisan migrate:reset && php artisan migrate --seed"

Queries run

@bashy That only works if you have the down function setup for all migrations. The OP is not using that at all...

What you can do is create your own command which deletes all tables for you...

I've no problem with the solution of creating my own command, I was just wondering if it was needed, or if someone knew of something that would be faster than me writing and debugging it :-)

I didn't see anywhere where it states there's no migrations?

@bashy "The reason for this is that rolling back migrations is not really implemented in our firm" We have migrations, just not the down() functionality

@Tominator if you didnt get it done yet, in the best traditions of Blue Peter here's one I made earlier.

I guess you could also do it via infomation_schema.

I dont know if you might need the fk checks on or not. and I think the trans isnt needed. anyway...

<?php namespace App\Console\Commands;

use DB;
use Illuminate\Console\Command;

class DropTables extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'droptables';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {

        if (!$this->confirm('CONFIRM DROP AL TABLES IN THE CURRENT DATABASE? [y|N]')) {
            exit('Drop Tables command aborted');
        }

        $colname = 'Tables_in_' . env('DB_DATABASE');

        $tables = DB::select('SHOW TABLES');

        foreach($tables as $table) {

            $droplist[] = $table->$colname;

        }
        $droplist = implode(',', $droplist);

        DB::beginTransaction();
        //turn off referential integrity
        //DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        DB::statement("DROP TABLE $droplist");
        //turn referential integrity back on
        //DB::statement('SET FOREIGN_KEY_CHECKS = 1');
        DB::commit();

        $this->comment(PHP_EOL."If no errors showed up, all tables were dropped".PHP_EOL);

    }
}

regards

l.

Thanks, I had just finished my version :-)

Using some code I found on the internet, this is the gist of it:

    $pdo = \DB::connection($this->option('connection'))->getPdo();

    $tables = $pdo
        ->query("SHOW FULL TABLES;")
        ->fetchAll();

    $sql = 'SET FOREIGN_KEY_CHECKS=0;';

    foreach ($tables as $tableInfo) {
        // truncate tables only
        if('BASE TABLE' !== $tableInfo[1])
            continue;

        $name = $tableInfo[0];
        $sql .= 'DROP TABLE ' . $name . ';';
        $this->info('Dropping table ' . $name);
    }

    $sql .= 'SET FOREIGN_KEY_CHECKS=1;';

    $pdo->exec($sql);

However, yours is better I feel, since you don't use the specific PDO part...

Using yours (@londoh) I tweaked it just a little bit by making it a little more flexible:

    DB::setDefaultConnection($this->option('connection'));

    $colname = 'Tables_in_' . DB::getDatabaseName();

Why not just DROP DATABASE db_name; then CREATE DATABASE db_name;?

In some cases (not mine so it could work) this would also mean your mysql user needs to be given the correct access rights again.

Why not easier:

    public function handle()
    {
        foreach(\DB::select('SHOW TABLES') as $table) {
            $table_array = get_object_vars($table);
            \Schema::drop($table_array[key($table_array)]);
        }
    }

If you're using Postgres, you can delete all your tables like this:

$query = 'SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname=\'public\'';
$tables = array_column(DB::select($query), 'tablename');

foreach ($tables as $table) {
    DB::statement('drop table '.$table.' cascade');
}

The cascade bit in there helps get around any foreign key constraints you might have.

Thanks! if you have multiple connections, you can use this:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use \DB;

class DropDb extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'dropdb {database}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'drops all tables of specific or all connections';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        //
        $database = $this->argument('database');
        
        if($database == "all") {
            $databases = array("mysql", "connection2", "connection3");
        }
        else{
            $databases = array($database);
        }
        foreach ($databases as $databasetoempty) {
            $pdo = \DB::connection($databasetoempty)->getPdo();
    
            $tables = $pdo
                ->query("SHOW FULL TABLES;")
                ->fetchAll();
    
            $sql = 'SET FOREIGN_KEY_CHECKS=0;';
    
            foreach ($tables as $tableInfo) {
                // truncate tables only
                if ('BASE TABLE' !== $tableInfo[1])
                    continue;
        
                $name = $tableInfo[0];
                $sql .= 'DROP TABLE ' . $name . ';';
                $this->info('Dropping table ' . $name);
            }
    
            $sql .= 'SET FOREIGN_KEY_CHECKS=1;';
    
            $pdo->exec($sql);
        }
    }
}

now you can do:

php artisan dropdb all

or

php artisan dropdb connection2

<?php

\Schema::dropAllTables();

:)

Since Laravel 5.5, there is a migrate:fresh command included.

It drops all tables and run migrations again ..see artisan help for more details.

I ran into a problem with using the $column variable to access the property dynamically in the londoh best answer script above? I am running php 7.3. After doing a little more searching, I found and tested this code in the db:drop command script. Worked for me using the array_map line below.

    public function handle()
    {
        //

        if (!$this->option('force')) {
            if (!$this->confirm('CONFIRM DROP AL TABLES IN THE CURRENT DATABASE? [y|N]')) {
                exit('Drop Tables command aborted');
            }
        }

        $tables = array_map('reset', \DB::select('SHOW TABLES'));

        $droplist = implode(',', $tables);

        DB::beginTransaction();
        //turn off referential integrity
        DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        DB::statement("DROP TABLE $droplist");
        //turn referential integrity back on
        DB::statement('SET FOREIGN_KEY_CHECKS = 1');
        DB::commit();

        $this->comment(PHP_EOL . "If no errors showed up, all tables were dropped" . PHP_EOL);

    }

Doesn't artisan migrate:fresh do what you want to do ? Docs