Commerce Data, A Practical Approach: Part 3 (Best?)

	Time to read: About 5-7 minutes
	Intended for: Sitecore commerce developers, Sitecore developers, Senior developers & leads
	Key takeaway: In this “Best” approach, simply back up and restore your production Commerce “Shared” database to your lower environments.

If you missed them, be sure to read Part 1 (Good) and Part 2 (Better) before diving in this post.

Before we dive into the specifics of the third and final part of this series, let’s revisit the problem we are trying to solve: Sitecore Commerce, indeed any commerce system, requires a tremendous amount of data setup to use. This data includes catalogs, sellable items, variants, pricing, promotions, fulfillment methods, example customer accounts, inventory data, etc. In fact, every one of these is necessary just to place a single order (with the exception of promotions, I guess). However, setting up this data in a way that is consistent with the business requirements, repeatable across many developers’ workstations, and easy to replicate can be a daunting problem.

Recap of parts 1 and 2

In Part 1, I covered how you could use Postman to automate the initialization of this data in your Sitecore Commerce environments. If you haven’t read Part 1, I suggested that while using Postman was at least “good” for creating consistent, automated data, there were some significant drawbacks. In Part 2, I suggested the alternative of using a custom .NET application to automate calling the Sitecore Commerce APIs to create the necessary data. Both of these approaches leverage using the plethora of out-of-the-box APIs and Pipelines provided by Sitecore Commerce, and also its extensibility as a platform for creating your own endpoints. In Part 3, the final article in this series, I am going to suggest a completely different, but likely far more obvious approach.

And the best approach is…

This final “Best” approach is simply: back up and restore your production Commerce “Shared” database to your lower environments. In Azure, this is simply a matter of clicking the “Export” button on your Azure SQL database instance. For an on-premises database, a simple backup will do.

To restore the database, simply “Import Data-Tier Application” and import the bacpac file from Azure, or do a standard database restore (if production was on-prem).

However, there are additional steps to make this restored Commerce database work with Sitecore locally. Effectively, we need to use SQL to do the same things that unchecking the catalog from the Content Editor is doing.

SQL details

These additional steps are necessary because the Commerce database stores the parent’s (in this case “Catalogs”) Sitecore Id in the JSON of the Catalog Entity, as well as in the Mappings table. You can’t simply uncheck in the box in the Content Editor, since the Sitecore id of the “Catalogs” content node in this environment won’t match the one in production.

The below SQL should be run against your local “shared” Commerce database.

First, we need to get Commerce’s “UniqueId” for the catalog we need to unmap. Next, we get the entity JSON for the catalog, and set the “ParentCatalogList” property to an empty string. We then update the entity in the CatalogEntity table with the new JSON. Finally, we delete the row from the mappings table.

BEGIN TRANSACTION

DECLARE @catalogName     NVARCHAR(50),
        @catalogUniqueId UNIQUEIDENTIFIER,
        @catalogEntity   VARCHAR(max)

SELECT @catalogName = 'MyCatalog'

SELECT @catalogUniqueId = uniqueid
FROM   [sitecore_commerce_storage].[catalogentities]
WHERE  id LIKE 'Entity-Catalog-' + @catalogName + '%'

SELECT @catalogEntity = entity
FROM   [sitecore_commerce_storage].[catalogentity]
WHERE  uniqueid = @catalogUniqueId

SET @catalogEntity = JSON_MODIFY(@catalogEntity, '$.ParentCatalogList', '')

UPDATE [sitecore_commerce_storage].[catalogentity]
SET    entity = @catalogEntity
WHERE  uniqueid = @catalogUniqueId

DELETE FROM sitecore_commerce_storage.mappings
WHERE  entityid LIKE 'Entity-Catalog-' + @catalogName + '%'

SELECT entity
FROM   [sitecore_commerce_storage].[catalogentity]
WHERE  uniqueid = @catalogUniqueId

COMMIT TRANSACTION

Once the above is complete (it should run sub-second), you will go to the Content Editor in Sitecore (see above), and expand the nodes until you see your “Catalogs” node, and recheck the checkbox next to your catalog name.

There are some drawbacks to this approach, as there have been with all approaches I have proposed in this series:

• The production database may contain sensitive data (customer data, orders, etc.) that you don’t want (or cannot have) on a developer’s workstation. This cannot be overstated, especially considering PCI compliance and European privacy laws.
• The product database likely has a LOT more data in it than is needed for lower environments (for example, thousands (tens of thousands?) of orders.

However, there are also some significant advantages to this approach:

• By definition, the production data is complete and fully representative of the test data required to run every test case in your organization.
• Restoring a production backup, and running the above SQL will be considerably faster (except in the case of the smallest Commerce sites) than loading the database with the data needed using the Commerce APIs.

I hope that you have enjoyed this series about loading data into your Sitecore Commerce environments. If you have any questions, leave a comment below or get in touch.