truncate all tables in laravel using eloquent

Question

Is there a way I could truncate all the tables in a db using eloquent or fluent in laravel 4? I do not want to specify table names, I just want to truncate all the tables. In other words empty all the tables.

Answer 1

NOTE: doctrine/dbal Package is Required for Performing this Operations

So Make Sure that is Installed composer require doctrine/dbal

1. Get all the table names

$tableNames = Schema::getConnection()->getDoctrineSchemaManager()->listTableNames();

2. Loop through the array of table names and truncate with Schema Builder

foreach ($tableNames as $name) {
    //if you don't want to truncate migrations
    if ($name == 'migrations') {
        continue;
    }
    DB::table($name)->truncate();
}

Help: If you have Got Some Error Such as

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint

You Can disable foriegn Key Checks

Schema::disableForeignKeyConstraints();

and make sure to ReEnable it

Schema::enableForeignKeyConstraints();

Answer 2

Here is my answer based on @Hao Luo. Moreover, it has these pros:

1. You do not need to install any extra package (no need for doctrine)
2. It supports Laravel 5 (or newer) very well
3. It disables foreign key constraint (If you truncate without caring about the orders and enables foreign key constraint, you will likely get an error)

Here is the code:

DB::statement("SET foreign_key_checks=0");
$databaseName = DB::getDatabaseName();
$tables = DB::select("SELECT * FROM information_schema.tables WHERE table_schema = '$databaseName'");
foreach ($tables as $table) {
    $name = $table->TABLE_NAME;
    //if you don't want to truncate migrations
    if ($name == 'migrations') {
        continue;
    }
    DB::table($name)->truncate();
}
DB::statement("SET foreign_key_checks=1");

Hope you like it! :)

Answer 3

In laravel 5, migrate:fresh will drop all the tables in the database (even if tables aren't related to migrate)

Answer 4

Use this:

$tables = DB::select('SHOW TABLES');
// it do truncate all tables in database
   foreach($tables as $table){
      if ($table == 'migrations') {
          continue;
      }
      DB::table($table->Tables_in_portal_test)->truncate();
}

Remember you import

use Illuminate\Support\Facades\DB;

PD: Tables_in_YOUR_DATABASE_NAME

Answer 5

This is how i truncate all tables inside a database (including table exceptions), it works for me.

    // set tables don't want to trucate here
    $excepts = ['migrations'];
    $tables = DB::connection()
    ->getPdo()
    ->query("SHOW FULL TABLES")
    ->fetchAll();
    $tableNames = [];

    $keys = array_keys($tables[0]);
    $keyName = $keys[0];
    $keyType = $keys[1];

    foreach ($tableNames as $name) {
        //if you don't want to truncate migrations
        if (in_array($name[$keyName], $excepts))
            continue;

        // truncate tables only
        if('BASE TABLE' !== $name[$keyType])
            continue;

        \DB::table($name)->truncate();
    }

Answer 6

Based on previous answers, I filter table names directly into the SQL query. I'm agree it's a small optimization but that avoids unnecessary loop.

protected function truncateDatabase($excepts = []): void
{
    $excepts = array_merge(['migrations'], $excepts);
    \DB::statement('SET foreign_key_checks=0');
    $table_names = \DB::query()->select('TABLE_NAME')->from('information_schema.tables')
        ->where('TABLE_SCHEMA', \DB::getDatabaseName())
        ->whereNotIn('TABLE_NAME', $excepts)
        ->get()
        ->pluck('TABLE_NAME')
        ->toArray();
    foreach ($table_names as $table_name) {
        \DB::table($table_name)->truncate();
    }
    \DB::statement('SET foreign_key_checks=1');
}

Answer 7

You can add this in your base test case class

    protected static function truncateTableAll(array $skip = [])
    {
        Schema::disableForeignKeyConstraints();
        
        foreach (Schema::getConnection()
                     ->getDoctrineSchemaManager()
                     ->listTableNames() as $name) {
            
            if ($name == 'migrations' || in_array($name, $skip)) {
                continue;
            }
            DB::table($name)->truncate();
        }
        
        Schema::enableForeignKeyConstraints();
    }

Answer 8

Laravel 8

i improved Hao Luo's answer

Add this code to your app/routes/console.php file :

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

.
.
.


Artisan::command('truncate', function () {

$this->comment('Truncating all tables');
 try{
     Schema::disableForeignKeyConstraints();
     $databaseName = DB::getDatabaseName();
     $tables = DB::select("SELECT * FROM information_schema.tables WHERE table_schema = '$databaseName'");
     foreach ($tables as $table) {
         $name = $table->TABLE_NAME;

         if ($name == 'migrations') {
             continue;
         }
         DB::table($name)->truncate();
     }
     Schema::enableForeignKeyConstraints();
     $this->comment('Truncate finished.');
     $this->comment('Don\'t forget to run db:seed');
 }catch(Exception $e){
     $this->comment('Error !');
     $this->comment($e->getMessage());
     Schema::enableForeignKeyConstraints();#dont forget this if it fails
 }
})->purpose('Truncate all tables in database');

Now you can simply open terminal and run :

> php artisan truncate