Guest post by We Are Magneto 

Recently, we had a requirement to integrate a Microsoft SQL (MSSQL) product database into a Magento 2 store. This integration would constantly sync product data between the two systems. For this integration, we would have to find some way of communicating with the external database, receiving the product data, and importing it into Magneto’s own database. Unfortunately Magento 2, like a lot of PHP frameworks, doesn't provide a PDO adapter for communicating with MSSQL directly. An added complication, in this case, was that access to the MSSQL server was protected behind a firewall. 


One of the ideas that we had was to use a bridging application that did support MSSQL, whose job it would be to communicate between the external database and Magento. This bridge would have to be simple, lightweight, and written in PHP. We decided on using the Lumen microframework. Lumen is Laravel's little brother: a stripped down version of Laravel with only the most basic packages for routing and handling models. As it supports MSSQL as a PDO extension, Lumen is a good choice to use for communicating with MSSQL if you already have PHP knowledge. The Lumen application would read product data, store it in its database, and then push up the data to Magento via the API.


Here's a diagram of how it would work:

https://lh5.googleusercontent.com/owpaKA4ZGGlnQg0f7up2OFfYtqJyXoRVhfTG_Hk1FJ7iwqvLT-S4CIomonDXEcUfer5O9fd-uJkJq3xnFejISX29rClEsFCWUw7wMG-fbF8xbNrNaP9gwnjxmS1IEG9hY2hjAL-Z

Magento 2 API vs Magneto 1 API

Magento 2's API looks similar to Magento 1's API. It has both Rest and SOAP options and can be authenticated using admin and customer bearer tokens using a username and password. One difference between the two versions' APIs is in the way that entities are exposed to the API. In Magento 2 almost anything can be added as a CRUD resource to the API using a couple of lines of XML. Another benefit for the developer is that API calls in Magento 2 use the same interfaces as those used across the application. This reduces the complexity of utilizing the API as different calls will behave predictably.


Using Guzzle to Push to Magento’s API

We decided to use the popular Guzzle package for pushing the inventory from Lumen to Magento's API, see here. We won't cover authentication here in detail, but it is well documented on the Magento docs site .

To get started, we first need to get the bearer token for the API as seen in the code below:

<?php

namespace App\Models\Magento;

use GuzzleHttp\Client as GuzzleClient;
use GuzzleHttp\Exception;

/**
 * Abstract Client
 */
 abstract class AbstractClient
{

     /**
      * Auth Token
      *
      * @var string
      */
     protected $_token;

     /**
      * Magento Client
      *
      * @var GuzzleClient
      */
     protected $_client;

     /**
      * Client constructor
      *
      * @param string $uri
      * @param string $username
      * @param string $password
      *
      * @throws \Exception
      */
    public function __construct($uri, $username, $password)
    {
         $this->_client = new GuzzleClient();

         try {
            $response = $this->_client->request('POST', 'V1/integration/admin/token', [
                'json' => [
                    'username' => $username,
                    'password' => $password
                ]
            ]);
            $this->_token = str_replace('"', '', $response->getBody()->getContents());
         } catch (Exception\ClientException $e) {
 throw new \Exception('Error establishing connection to magento site');
         }
     }

     /**
      * Get the users access token
      *
      * @return mixed
      */
     public function getToken()
     {
         return $this->_token;
     }

     /**
      * Get and instance of the client
      *
      * @return GuzzleClient
      */
     public function getClient()
     {
         return $this->_client;
     }
}



Then we push product information into the Magento API endpoint for products:


<?php

namespace App\Models\Magento;

use App\Models\Parts;

/**
 * Catalog Product Client
 */
class CatalogProductClient extends AbstractClient
{

     /**
      * This method will create a post request to add a product and return the product object
      *
      * @param Parts $product
      *
      * @return array
      */
     public function addProduct($product)
     {
         $response = $this->getClient()->request('POST', 
'V1/products', [
             'headers' => [
                 'Authorization' => "Bearer " . $this->getToken()
                 ],
                 'json' => [
                     'product' => 
$this->_getAddProductRequestData($product)
                 ]
         ]);
         return json_decode($response->getBody(), true);
     }
}



The (simplified) JSON packet that we send to Magento looks like:


{
     "product": {
         "sku": "FAN-1",
         "name": "Fan Assembly",
         "price": 120,
         "weight": "1",
         "status": "1",
         "visibility": "4"
}


Results of Testing

When testing this code, we found that the rate that we could send products to the API is limited to an average of 2 products per second. This slows to around 1 product per second over time. This would be fine with a small database of products, but we had over 16,000 products to sync regularly with Magento. A full sync, even assuming that no degradation in performance occurred over time, would take over 2 hours.

We can see one disadvantage of Magento 2’s approach to its API here: it’s using the same models and interfaces as a full web browser request, which is quite heavy for our use case of pushing product data into the catalog This call rate was also measured in a test environment with ideal conditions but could be slower in production with a server under heavy load.


Handling Bulk Updates in Magento 2.2

In Magento 2.2, it’s not possible to bulk update product data through the API. But it is possible to get around this limitation by creating a new custom endpoint that can handle bulk updates more efficiently. For this purpose, we created an extension and defined a new endpoint in etc/webapi.xml:

<?xml version="1.0"?>
<routes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Webapi:etc/webapi.xsd">
    <route url="/V1/products/bulk" method="POST">
        <service class="Vendor\BulkApi\Api\ProductBulkInterface" method="save"/>
        <resources>
            <resource ref="Vendor_BulkApi::bulk" />
        </resources>
    </route>
</routes>


That’s it. As you can see it’s very simple to define a new endpoint. The route tag defines the URL for the endpoint and its accepted HTTP methods. The service tag defines which class and method to pass the POST data to. Finally, the resources tag defines the ACL conditions to authorize access to the endpoint. Luckily, this is as simple as ensuring that the admin user whose username and password we used to retrieve the authentication token also has access to this resource.


Dealing With A Large Catalog

We can now push whatever data we want to this endpoint in JSON format. To deal with a large catalog more efficiently, we can utilize an asynchronous queue system to process the data once we’ve received it at our endpoint. This has the advantage of not blocking our API requests while processing the product data.

The current version of Magento 2 Commerce (2.2) supports Message Queue frameworks out of the box, and it has been announced that Magento 2.3 Open Source will support asynchronous queues. After looking at a number of alternatives we decided to implement an open source package with a simple MySQL backend (available on GitHub).

Rather than individually creating new HTTP requests for each product and waiting for the data to be processed, we can now pass our product data in bulk to Magento. Here’s an example JSON payload:

{
    "data": [
          {
              "sku": "FAN-1",
              "name": "Fan Assembly",
              "price": 120,
              "weight": "1",
              "status": "1",
              "visibility": "4"
          },
          {
              "sku": "FAN-2",
              "name": "Fan Assembly 2",
              "price": 130,
              "weight": "1",
              "status": "1",
              "visibility": "4"
          }
     ]
}



In the service class that we defined in our webapi.xml file, we receive the POST data in the form of method argument, which Magento helpfully has already decoded into an array. Using this we can add each product to the queue:


<?php

namespace Vendor\BulkApi\Model;

use Springbot\Queue\Model\Queue as QueueModel;

class ProductBulk
{
     /**
      * @var QueueModel $queue
      */
     protected $queue;

     /**
      * Queue constructor.
      * @param QueueModel $queue
      */
     public function __construct(
         QueueModel $queue
     )
     {
         $this->queue = $queue;
     }

     /**
      * Process data
      * @api
      * @param mixed $data
      * @return void
      */
     public function save($data)
     {
         foreach ($data as $product) {
             $this->queue->scheduleJob(
                 \Vendor\BulkApi\Model\Import\ProductImporter::class,
                 'import',
                 [$product]
             );
         }
     }
}


Although we are passing in a simple array called $data to the save method, it’s also possible to type hint a model or class instead. Magento will then magically transform your JSON payload into the “data” part of the class, allowing you to use magic methods and transform the data as you want. Alan Kent has written a tutorial using this method.


Benefits Of Using This Code

Now we can pass through hundreds of products at a time, which the code will add to a queue to be processed later. This saves us the overhead created by multiple HTTP requests. We also now have the option of more intelligently handling our data than we did before, for example, by checking if the product has been modified in the external database or still needs to be listed before processing its job in the queue. We could also simplify the queue model by writing directly to the database table, saving on more resources required to process the API request. Rather than hours, this method only took 30 minutes to complete the product inventory sync as we could process the data in parallel to sending it to the API.

While Magento 2’s API currently (up to version 2.2) only supports single product uploads, bulk product importing through the API is coming to 2.3. But it remains to be seen if this is a better way of handling large product inventories than implementing a queue system.


Final Thoughts

So, what did we learn from this project?

  • Magento 2’s API doesn’t yet (2.2) support bulk product inventory imports

  • Magento 2’s API, while flexible, can be quite heavy and not really suited to pushing a lot of product data through it

  • It’s relatively simple to add new API endpoints

  • Queuing makes external integrations a lot simpler and faster by removing the overhead from HTTP requests and parallel data processing

Technologies Used:

Magento 2 API:  https://devdocs.magento.com/guides/v2.2/get-started/bk-get-started-api.html 

Lumen:  https://lumen.laravel.com 

Springbot Queue:  https://github.com/springbot/magento2-queue 

Guzzle:  https://github.com/guzzle/guzzle