How to read data from an API and populate the database without duplicates in Laravel

Today in this tutorial, you will learn how to read data from an External API and populate the database without duplicates in Laravel 8 applications. This tutorial assumes you are already familiar with basic Laravel implementations. I will be using a Public API that fetches entry records https://api.publicapis.org/entries. This API does not require a token for authorization.

Before getting started, you should ensure that you have installed the Guzzle package as a dependency of your application. By default, Laravel automatically includes this dependency. However, if you have previously removed the package, you may install it again via Composer.

Step 1: Create a model and migration

Generate a database migration when you generate the model with this command

php artisan make:model ApiEntry -m

Step 2: Define database Schema

Go to the migration file that was just created and define your database schema :

public function up()
    {
        Schema::create('api_entries', function (Blueprint $table) {
            $table->id();
            $table->string('api');
            $table->string('description');
            $table->string('auth')->nullable();
            $table->boolean('https');
            $table->string('cors');
            $table->string('link');
            $table->string('category');
            $table->timestamps();
    });
    }

Note: The column here is what I’m expecting from the external API I will be consuming.

Then run the php artisan migrate command to migrate your table

Step 3: Define the model attribute

You should define which model attributes you want to make mass assignable. You may do this using the $fillable property on the model.

Go to app\Models\ApiEntry.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ApiEntry extends Model
{
    use HasFactory;

    protected $fillable = [
        'api',
        'description',
        'auth',
        'https',
        'cors',
        'link',
        'category'
    ];
}

Step 4: Define routes

Define your routes/web.php file. You can also define your route in routes/api.php (if you want to create an API route).

<?php

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ApiEntryController;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/public/endpoint', [ApiEntryController::class, 'insertApiRecords']);

Step 5: Create a Controller

Create the ApiEntryController using the command

Php artisan make:controller ApiEntryController

Step 6: Implement logic
Here I choose to keep my controller skinny and the model thin. So I put my logic in a Service class and import it into the controller.

<?php
namespace App\Services\ReadDataApiService;
use App\Models\ApiEntry;
use Illuminate\Support\Facades\Http;

class ReadDataApi
{
    public function getApiEntry()
    {
        $url = "https://api.publicapis.org/entries";//assign API to a variable
        $response = Http::get(
            $url
        );
        $responseBodyClient = $response->json();
        $externalApiEntries = $responseBodyClient['entries'];

        foreach ($externalApiEntries as $externalApiEntry) {
            ApiEntry::updateOrInsert(
                [
                    'api' => $externalApiEntry['API'],
                    'description' => $externalApiEntry['Description'],
                    'link' => $externalApiEntry['Link'],
                    'category' => $externalApiEntry['Category'],
                ],
                [
                    'auth' => $externalApiEntry['Auth'],
                    'httpS' => $externalApiEntry['HTTPS'],
                    'cors' => $externalApiEntry['Cors'],
                ]
            );
        }
    }
}

Explanation

• I created the file ReadDataApi.php - app/Services/ReadDataApiService/ReadDataApi.php

• Assign the API URL to a variable $url.

• I make a get request to the URL

• Convert the response to a JSON format

• I loop through the response and use the updateOrInsert method (I made sure to specify the unique attributes in the first parameter to ensure that there are no duplicates)

The updateOrInsert method will attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments. Read more here.

Note: You can also use the firstOrCreate or UpdateOrCreate method. Also, make sure to specify the unique attributes in the first parameter to ensure that there are no duplicates.

Step 7: Import it into your controller

Go to app/Http/Controllers/ApiEntryController and use Controller dependency injection.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Services\ReadDataApiService\ReadDataApi;

class ApiEntryController extends Controller
{
    protected $readDataApi;

    public function __construct(ReadDataApi $readDataApi)
    {
        $this->readDataApi = $readDataApi;
    }

    public function insertApiRecords()
    {
        try {

            $this->readDataApi->getApiEntry();
        } catch (\Throwable $th) {
            return $th->getMessage();//you can also return you custom exception
        }
        return 'Operation Successful';//you can return a view or JSON
    }
}

Testing

Go to http://127.0.0.1:8000/public/endpoint to test your implementation. You can get the complete code from the Github repo