DB Search and Replace. The automated kind

Imagine you had to make a change in your database records. You have been assigned the task of updating the affected records. There is only one problem: you do not know which tables or how many records are affected. What do you do?

That was an actual scenario that occurred sometime this week. Except, it wasn't some nefarious line of code. It was time to fix those horrible class names used in Laravel relationships. You know what I am talking about: the App\Models\User kind littered across the database.

This tutorial will show you how to run a search and replace using Laravel's built-in migration and DB classes. It should help you avoid manually running a search and replace or using an external tool.

Create a migration

Generate a migration file using the artisan console.
php artisan make:migration search_replace_relationships

Search and replace logic

Remember, you do not know what tables are affected and what column so, you cannot run: DB::update(). Instead, we have to do the following:
Get a list of items to search for and replace
Get a list of tables
Get a list of columns in each table
Run a nested for loop
If you are updating multiple items, you will need an additional loop for each item you wish to replace.

Get a list of changes

In this case, we are replacing relationship morphs. You can create a morph map as described here. For example, our morph map looks like this:

Relation::morphMap([
            'user' => User::class,
            'post' => Post::class,
        ]);

We can then retrieve the morph map using:

$morphMap = \Illuminate\Database\Eloquent\Relations\Relation::morphMap();

Get a list of all tables

I could not find native support for retrieving table names in Laravel. Alternatively, you can run this query to retrieve a list of database tables.
$tables = \Illuminate\Support\Facades\DB::select('SHOW TABLES');
Inspired by

Get a list of columns for each table

Here is our first loop.

foreach ($tables as $table) {
            $tableName = $table->{sprintf('Tables_in_%s',
                \Illuminate\Support\Facades\DB::connection()->getDatabaseName())};
            $columns   = Schema::getColumnListing($tableName);

The result of the first query to retrieve the table names returns an array of objects. Each object key is Tables_in_dbname. Using the correct key syntax, get to the value which holds the actual table name.
Next, we use the Schema class to get the columns of the table name while still in the loop.

Run a nested loop

Here comes our first nested loop. For each table, loop through each column to find the text that we want to change and perform the replacement:

foreach ($columns as $column) {
                $columnType = Schema::getColumnType($tableName, $column);
                if ($columnType === 'string') {

We use the Schema::getColumnType function to get the type of the column. That is because in this case, we are replacing the morph types so we know that the field for morph types is usually a string. If this is different in your setup (very unlikely) you can skip this entirely.

Run the replace

Finally, we run the search and replace logic.

\Illuminate\Support\Facades\DB::table($tableName)->where($column, $value)->update([$column => $key]);

Retrieve an instance of the table, then search for the value in each column and replace it using the update function.
The SQL equivalent is:
UPDATE FROM $tableName where $column = $value SET $column = $key the $key in this case being the user for the first item in our morph map and $value being the class name in this case App\Models\User.

Please note that the update method does not fire any eloquent events, which is the behaviour we want since we are running a migration.

Bring it all together.

In your migration up function, you should end up with the following:

$tables   = \Illuminate\Support\Facades\DB::select('SHOW TABLES');
        $morphMap = \Illuminate\Database\Eloquent\Relations\Relation::morphMap();
        foreach ($tables as $table) {
            $tableName = $table->{sprintf('Tables_in_%s',
                \Illuminate\Support\Facades\DB::connection()->getDatabaseName())};
            $columns   = Schema::getColumnListing($tableName);
            foreach ($columns as $column) {
                $columnType = Schema::getColumnType($tableName, $column);
                if ($columnType === 'string') {
                    foreach ($morphMap as $key => $value) {
                        \Illuminate\Support\Facades\DB::table($tableName)->where($column, $value)->update([$column => $key]);
                    }
                }
            }
        }

I think migrations are one of the most powerful features offered by any framework. Once you have mastered how to use migrations, you can resolve minor to large database infractions without ever logging into your production database.

Have you found this useful, comment below.

Photo by Anete Lusina: