How to take backup of DB table before truncating in a Laravel migration file?

Question

The question has similarity with this.

Although that question seems unresolved, the comment section points out:

A true revertible migration would make a backup copy of the table visitors on up() before truncating and then copy the backup into the original on down().

Could not find any working solution for implementing the above steps.

How to take the dump of the DB table before truncating? Found some examples showing how to import from a .sql file, but that is not helpful here without first exporting the data.

The code would look something like:

class TruncateApiKeysTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // take backup of current api_keys
        // run the truncate
        if (Schema::hasTable('api_keys')) {
            Schema::table('api_keys', function(Blueprint $table) {
                $table->truncate();
            });
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
       // import data from the sql dump file
       // insert data into api_keys to revert changes
    }

This would be a very uncommon usecase if I understand your question correctly. So you are proposing to drop an entire table, but want to be able to retrieve it if you ever rolled back? The content of this table would need to be static and/or nullable to some extent as it's content will be truncated after the point at which you take the backup in your `up()` function (i.e. you will have a gap in the data between when you ran the migration and when you reverted), so your application must be capable of handling this missing data? — Spholt, Jun 16 '20 at 08:45
@Spholt i see the point. What is the feasible step to do in the `down()` function of a truncate migration? — Abrar, Jun 16 '20 at 08:58
Depending on the content of the table, i'd look at maybe using a seeder to repopulate based off of the state of the rest of the database. However, if you need the exact `api_keys` that were in the table previously, i'd question whether truncating the table was a good idea in the first place! Can you provide any more context about your specific use case? — Spholt, Jun 16 '20 at 10:13

score 1 · Answer 1 · answered Jun 16 '20 at 23:26

Solution 1: save your existing record as seeders before truncate. Using db to seeder tool such as https://github.com/orangehill/iseed, you could call php artisan iseed my_table in CLI or Artisan::call('iseed my_table'); in php.

Solution 2: create a backup mysql db, then save the table there before truncating. Meaning you will have 02 mysql connection:

current_db
backup_db

You can easily handle multiple DB with laravel.

th3coop · Answer 2 · 2023-06-01T22:09:21.183

You could copy the table using CREATE TABLE blah_backup LIKE blah;

public function up()
{
    DB::statement('CREATE TABLE foo_backup LIKE foo;');
    DB::statement('
    INSERT foo_backup
    SELECT *
    FROM foo;');

    Schema::table('foo', function (Blueprint $table){
        //Change the table in a way that could lose data
    }
}
public function down()
{
    // recover the backup
    Schema::drop('foo');

    DB::statement('CREATE TABLE foo LIKE foo_backup;');
    DB::statement('
    INSERT foo
    SELECT *
    FROM foo_backup;');

    Schema::drop('foo_backup');
}

You do have to worry about cleaning up the backup eventually but that seems like a minor detail to me for the convenience of a complete rollback in your migration where you don't lose data.

How to take backup of DB table before truncating in a Laravel migration file?

2 Answers2