Laravel Tip: Schema::getColumnListing method

Introduction

In a previous post, I talked about the usage of Eloquent queries or the DB Facade to perform a mass insert of records, which you can read here.

But what if, for some reason, you're inserting data by chunks and such data that comes from a 3rd party API suddenly changes its structure by adding new columns? Sure, we can argue that this is a bad practice from the API provider as their API should be versioned, but it's an issue that you may encounter in your day-to-day as a Software Engineer (it happened to me today, hence the post on how to workaround it 😅)

Schema::getColumnListing() to the rescue

The getColumnListing(string $table) method returns all the columns from a given table in array format. This is what's gonna help us remove unwanted keys from the records that we want to dump into our DB by comparing if any of the object properties are not included in our table columns. Let's take a look at the code sample below:

private function insertCallRecords(Collection $sessionRecords): void
{
    $recordsIds = $sessionRecords->pluck('SdrId');

    $dbRecords = SessionRecord::whereIn('SdrId', $recordsIds)->get();

    foreach($dbRecords ?? [] as $dbRecord) {
        // We will remove all the already existing records that are in DB to avoid double insertions
        $sessionRecords = $sessionRecords->filter(function ($callRecord) use ($dbRecord) {
            return $callRecord->SdrId != $dbRecord->SdrId;
        });
    }

    if ($sessionRecords->isNotEmpty()) {
        $sessionRecords = $sessionRecords->map(function ($record) {
            // The API in this case usually return values as an empty stdClass value, so we need to convert it to null so it doesnt break when inserting the rows in DB
            foreach ($record as $key => $value) {
                // Check if the value is an empty stdClass
                if ($value instanceof stdClass && count((array)$value) === 0) {
                    $record->$key = null;
                }
            }

            return (array) $record;
        });

        try {
            $recordsChunk = $sessionRecords->chunk(500);

            DB::beginTransaction();

            foreach ($recordsChunk as $chunk) {
                DB::table('session_records')->insert($chunk->all());
            }

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Something went wrong inserting the call records into the DB. Reason: " . $e->getMessage());
        }
    }
}

This is a script from a real application that handles the insertion of thousands of records every 5 minutes. Let's tear it down into small list items to see what's going on here:

• The method receives the collection of raw records that come from the 3rd party API
• We pluck the records SdrId to grab the records that our DB already has
• We perform a filter of the records that are already in our DB to insert the new ones
• We sanitize some fields that contain an unproper format due to the nature of the 3rd party API
• We chunk the data and perform the insertion

The implementation works, but one day you wake up on a support ticket saying the records aren't being inserted anymore (after it's been working for months), so when you debug, you find out this log:

[2024-05-15 10:15:12] local.ERROR: Something went wrong inserting the call records into the DB. Reason: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'NewRecordKey' in 'field list'

That is because we never created this column, it's actually a brand new field that comes from our API provider. There are 2 options now, one being create the column so that the insertion works again. This option is the fastest, but then you may encounter the same error once again in the future.

The second option is making use of the getListingColumn() method. This way we bring the column names from our table and we only insert the records' key/values that match our columns:

private function insertCallRecords(Collection $sessionRecords)
{
    ...    

    if ($sessionRecords->isNotEmpty()) {
        // Here we store the column names to perform the rows key/values filtering
        $columns = Schema::getColumnListing('session_records');


        $sessionRecords = $sessionRecords->map(function ($record) use ($columns) {
            foreach ($record as $key => $value) {
                // If key doesnt exist in our column names, remove it from the record and move to the next one
                if (!in_array($key, $columns)) {
                    unset($record->$key);
                    continue;
                }
                
                // Check if the value is an empty stdClass
                if ($value instanceof stdClass && count((array)$value) === 0) {
                    $record->$key = null;
                }

            }

            return (array) $record;
        });

        ...
    }
}

This way we make sure that our records will be inserted according to our database structure and if new fields come from the 3rd party API, the insertion will not break, and if we need to make use of the new data that comes from the API, then we can just create the new column in our database while preserving this logic to avoid future breaks.

Liked it? Feel free to subscribe to my newsletter for future post notifications! (No spam)

-Gonza