Laravel Tip: Schema::getColumnListing method

May 15, 2024 • 4 min read

Home / Blog / Laravel Tip: Schema::getColumnListing method
Laravel Tip: Schema::getColumnListing method | Avoid future breaks if your external source of information performs a structure change

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

357
Laravel

About the author

Author

Gonzalo Gomez

Sr. Software Engineer

I have over 6 years of experience building highly scalable web applications using a wide variety of technologies. Currently focusing on Laravel, Livewire, Vue.js and AWS as my go-to stack.

Subscribe to my newsletter

If you enjoy the content that I make, you can subscribe and receive insightful information through email. No spam is going to be sent, just updates about interesting posts or specialized content that I talk about.

Ready to take your project to the next level?

Contact me

Related posts

Eloquent queries vs DB Facade

May 01, 2024
IntroductionIn Laravel development, making the right choices regarding database operations can significantly impact application performance and scalability. When dealing with tasks such as bulk data... Continue reading

Twilio: Create an A2P 10DLC Campaign

April 08, 2024
IntroductionIf your company requires to implement a messaging service to send, for example, SMS reminders for appointments, booking confirmations or any other message of any... Continue reading

How to integrate Google Calendar with Laravel

September 03, 2024
How to Seamlessly Integrate Google Calendar into Your Laravel AppIntegrating Google Calendar into your Laravel application can be a game-changer, especially if you need to... Continue reading

Traits and Laravel: the practical guide

April 18, 2024
IntroductionTraits are a mechanism for code re-use in single inheritance languages such as PHP. They were designed to allow us developers to save logic and... Continue reading