Laravel - Renaming Tables in Production

laravel-renaming-tables-in-production.md

Laravel - Renaming Tables in Production

So I ran into a use-case where I joined a project that had a prefix on all tables in a production database. Now I couldn't just drop all tables and migrate + seed, this is Production we are talking about!

Since the database had a second (much smaller) app's tables in the same database, I had to be careful that I didn't rename those either.

Here's a quick script I threw in a developer-only route which would generate a query which I could manually run on production at the time of deploying the new code which was stripped of hard coded prefixes. Don't forget when you run this to remove any prefixes set for your connection in config/database.php otherwise you'll have some angry customers and "unable to find table" errors.

script.php

<?php

$prefix   = 'foobar_';
$database = \DB::getDatabaseName();
$tables = \DB::select("
  SHOW TABLES
    WHERE tables_in_{$database} NOT LIKE 'othersite_%'
    AND   tables_in_{$database} LIKE '{$prefix}%'
");
  
foreach ($tables as $table) {
    // Get the table name
    $table = current(array_values((array) $table));

    // If the table starts with the prefix then rename it and append it to the collection
    if (\Illuminate\Support\Str::startsWith($table, $prefix)) {
        $tablesToRename[$table] = str_replace($prefix , '' , $table);
    }
}

// If we have tables to rename
if (count($tablesToRename)) {
    $query = 'RENAME TABLE ';
    foreach ($tablesToRename as $from => $to) {
        $query .= " {$from} TO {$to}, ";
    }
    $query = trim($query, ', '). ';';
    return $query;
}

return 'No tables to rename 😊';