Magento 2, Database Level Inventory Management

You’re always looking for ways to speed things up when you work with a catalog of 125,000 SKUs and growing. Management of the catalog is no different. Magento’s built-in import can’t handle the load and some of the other import modules might be out of budget. Fortunately there’s a way for you to tackle your inventory management issues at the database level.

I remember reading the ticket from Magento support. They explained that the import was not able to handle a catalog of 125,000 SKUs and suggested we break it down to 30,000 and then 10,000 if that didn’t work. I checked into a couple of modules I was familiar with, but they were out of budget for this project. All we wanted to do was push daily qty and price from our inventory system. I had to get creative.

Database Architecture

Magento 2’s database has 3 places that you’ll find price and qty information. Let’s look at those tables.

1. catalog_product_entity_decimal

This is where you’re going to find the price information. You’ll want to look for attribute_id 77 and match your product with the row_id from catalog_product_entity to the row_id in this table.

2. cataloginventory_stock_item

This is where you’re going to find the qty information. In addition to qty, you’ll also want to set the is_in_stock value to the right value. This is important when things go in and out of stock each day. You don’t want to tell Magento the item is in stock when there is a qty of 0.

3. cataloginventory_stock_status

This is a table that Magento is going to populate on its own. This happens when you the indexer. We won’t touch this table, but I’ll share an important lesson you’ll want to have for debugging or cleaning.

My Work Flow

Our inventory management system exports a CSV file each morning and sends it over to our Magento server. From there, we have a cron that runs every 30 minutes and checks for files. It finds the CSV, and starts going through each line. We look up our product IDs with the SKU. From there we can update our essential tables. Let’s step through this.

SELECT row_id, entity_id FROM catalog_product_entity WHERE sku = 'ABC_123456';

This is how we get our row_id and entity_id (product_id and row_id in our target tables).

UPDATE cataloginventory_stock_item SET qty = 15, is_in_stock = 1 WHERE product_id = 121752

This is where we take our entity_id and plug it into our query to update the qty. The cataloginventory_stock_item.product_id = catalog_product_entity.entity_id.

UPDATE catalog_product_entity_decimal SET value = 175.25 WHERE attribute_id = 77 AND row_id = 121752;

Finally, we will update the price information. This is matched by the price attribute_id, which in our case is 77. I would recommend making sure that is the case in your database. You can find this in the eav_attribute table. From here, the value is the price. You’ll match this to your attribute_id and row_id. Your catalog_product_entity_decimal.row_id = catalog_product_entity.row_id.

Will updating the database alone work?

The short answer is no. There are a couple of things that need to happen next.

First, you want to reindex your web catalog.

bin/magento indexer:reindex

You can do the individual indexes associated with the catalog if you want. Please refer to the Magento docs on how to specify the indexes and work with the Magento 2 cli.

Next, you will want to clear your cache.

php/magento cache:clean

What happens when I index?

Among other things, Magento 2 is going to populate or update the cataloginventory_stock_status. This is a table you don’t want to touch. We were originally updating this along with the other 2 tables. One day our catalog disappeared from view and it took us several hours to figure it out.

If you do find yourself in this situation, I was able to fix this by emptying 3 tables:

cataloginventory_stock_status
cataloginventory_stock_status_idx
cataloginventory_stock_status_tmp

After that, I took the update lines out of my cron and re-ran everything. When it got to the indexing part, the catalog re-appeared.

Conclusion

Magento 2 solves some of the importing issues that I saw in Magento 1. It works well, but not with large catalogs. You can easily handle inventory management at the database level if you spend time to understand it and work through problems carefully. This can speed up your stocking and pricing processes and give you a good avenue for good inventory management.

Written by Dan
Welcome to my blog! Here you'll find my collection of bible study lessons, book reviews, and other posts I feel inspired to write. I am a Christian currently serving at First Baptist Church of Northville in Northville, MI.