How to Copy an Existing Table Structure in Laravel　　　　　　　　　　　　　　　　　　　

Introduction

When working with Laravel, there may be scenarios where we need to create a new table based on the structure of an existing table, with some additional columns. While Laravel’s Schema Builder doesn’t provide a direct method for copying columns from one table to another, we can achieve this by using raw SQL queries. In this blog post, I’ll walk through how to create a new table with columns copied from an existing table and add new columns using Laravel migrations.

Step-by-Step Guide　

Step 1: Create a New Migration File　

First, we need to create a new migration file. Run the following Artisan command to generate the migration:

php artisan make:migration create_new_table_name_table

This command will create a new migration file in the database/migrations directory.

Step 2: Edit the Migration File　

Open the newly created migration file and modify it to fetch the existing table columns and create the new table with those columns. Here is the updated migration file with the added functionality to handle decimal values properly:

<?php
Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateNewTableNameTable extends Migration
{
    public function up()
    {
        $existingTable = 'existing_table_name';
        $newTable = 'new_table_name';

        // Fetch columns from the existing table
        $columns = DB::select("SHOW COLUMNS FROM {$existingTable}");

        // Create the new table with the copied columns
        Schema::create($newTable, function (Blueprint $table) use ($columns) {
            foreach ($columns as $column) {
                $type = $this->getColumnType($column->Type);

                if ($column->Field == 'id') {
                    $table->bigIncrements($column->Field)->default($column->Default);
                } else {
                    $columnType = $this->mapColumnType($type[0], $column);
                    if ($columnType === 'decimal') {
                        $separateValues = explode(',', $type[1]);
                        $table->{$columnType}($column->Field, $separateValues[0], substr($separateValues[1], 0, -1))
                            ->nullable($column->Null == 'NO' ? false : true)
                            ->default($column->Default);
                    } else {
                        $table->{$columnType}($column->Field)->nullable($column->Null == 'NO' ? false : true)
                            ->default($column->Default);
                    }
                }
            }

            // Add new columns
            $table->string('new_column1')->nullable();

            // Usually previous table should have created_at and modified_at , so you can ignore this, otherwise you can add 
            //$table->timestamps();
        });
    }

    /**
     * Get column type from the MySQL type string.
     *
     * @param string $type
     * @return array
     */
    private function getColumnType($type)
    {
        $parts = explode('(', $type);
        return $parts;
    }

    /**
     * Map MySQL column types to Laravel column types.
     *
     * @param string $type
     * @param object $column
     * @return string
     */
    private function mapColumnType($type, $column)
    {
        $typeMapping = [
            'bigint' => 'bigInteger',
            'tinyint' => 'tinyInteger',
            'int' => 'integer',
            'varchar' => 'string',
            'text' => 'text',
            'date' => 'date',
            'datetime' => 'dateTime',
            'timestamp' => 'timestamp',
            'float' => 'float',
            'double' => 'double',
            'decimal' => 'decimal',
//Add more if needed
        ];

        return $typeMapping[$type] ?? 'string';
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('new_table_name');
    }
}
?>

Explanation

• Fetching Columns from the Existing Table: The SHOW COLUMNS FROM {$existingTable} query fetches the column definitions from the existing table.
• Creating the New Table: The Schema::create method is used to create the new table with the copied columns. The columns are dynamically added using the fetched column definitions.
• Handling Decimal Columns: The if($columnType==='decimal'){...} block ensures that decimal columns are handled correctly by specifying the precision and scale.
• Mapping Column Types: The mapColumnType method maps MySQL column types to Laravel column types. We may need to extend this mapping depending on the types used in our existing table.
• Adding New Columns: New columns (new_column1 and new_column2) are added to the new table.

Step 3: Run the Migration　

Finally, run the migration to create the new table with the copied columns and additional columns:

php artisan migrate

Conclusion

By following these steps, we can easily create a new table in Laravel based on the structure of an existing table and add new columns. This approach is particularly useful when we need to replicate table structures dynamically and make modifications as needed. Feel free to extend the column type mapping and add any additional functionality as per our requirements.

Feel free to ask questions. Happy coding!