Optimizely Commerce database querying 101

2021-09-24 in OptimizelyEpiserver

Howdy y'all!

When working in projects based on Optimizely (formerly Episerver) Commerce there is often a need to look deep within the database structure to gather information or troubleshoot issues. Having worked with this structure for the past ten years I feel that I could dump some of the "usual suspects" on the interwebs for other developers to copy paste.

When querying production data, use WITH(NOLOCK) or SET TRANSACTION LEVEL READ UNCOMMITTED to avoid locks as we most likely do not really care about dirty reads etc when doing these things.

Below I may refer to the concepts of content types and meta class interchangably. Meta class is the old school concept that still very much lives in the database schema, while in code it is referred to as ContentType and is handled very similar to "real" content types in the CMS.

As always, avoid querying default database entities from production code directly as they may change between versions.

The structure

There are a lot of tables, views and procedures in the commerce database, most of which I will not dive into in this post. The most important (or basic) ones from my viewpoint are listed below. Note that some views are generated based on defined content types and your milage may vary.

The key to remember is that catalog and order entities are based on the meta data plus system, which will map an entity table (e.g. CatalogEntry) to meta properties (CatalogEntryEx/CatalogEntryEx_Localization) mapped on primary key in the former table to ObjectId column in the others.

Catalog entitites

  • CatalogEntry The most basal data for all entries in the catalog
  • CatalogEntryRelation The relation between entries, e.g. product and variation
  • CatalogNode The equivalent basal data for nodes in the catalog
  • CatalogNodeRelation Secondary relations for nodes, the primary node is set on each node explicitly
  • NodeEntryRelation The relations between nodes and entries
  • CatalogEntryEx_ProductContent A view containing property values for the ProductContent content type
  • CatalogEntryEx_ProductContent_Localization A view for the CultureSpecific property values for the ProductContent content type
  • CatalogEntryEx_VariationContent A view containing property values for the VariationContent content type
  • CatalogEntryEx_VariationContent_Localization A view for the CultureSpecific property values for the VariationContent content type

Orders

  • OrderGroup The base table for orders, for legacy carts, this is also used
  • OrderGroup_PurchaseOrder Property values for purchase orders
  • OrderGroup_PurchaseOrder_Localization CultureSpecific property values for purchase orders
  • OrderForm Base table for order forms, typically there is one form per order but can be multiple if required/needed
  • OrderFormEx Property values for forms
  • OrderFormEx_Localization CultureSpecific property values for forms
  • LineItem Base table for order lines
  • LineItemEx Property values for lines
  • LineItemEx_Localization CultureSpecific property values for lines
  • SerializableCart Table for ongoing carts, soft modelled on JSON

Sample queries

Getting variation/sku information

Get entry and parent entry data for each language based on SKU code. Note that this example is trivial and expects only one meta class for product and variation.

DECLARE @SkuCode NVARCHAR(255) = '12345';
SELECT *
FROM [CatalogEntry] ce
    JOIN [CatalogEntryEx_VariationContent] v ON v.ObjectId = ce.CatalogEntryId
    JOIN [CatalogEntryEx_VariationContent_Localization] vl ON vl.ObjectId = ce.CatalogEntryId
    JOIN [CatalogEntryRelation] cer ON cer.ChildEntryId = ce.CatalogEntryId
    JOIN [CatalogEntry] ce2 ON ce2.CatalogEntryId = cer.ParentEntryId
    JOIN [CatalogEntryEx_ProductContent] p ON p.ObjectId = ce2.CatalogEntryId
    JOIN [CatalogEntryEx_ProductContent] pl ON pl.ObjectId = ce2.CatalogEntryId AND pl.Language = vl.Language
WHERE ce.Code = @SkuCode;

Getting order data

Get order data for a specific TrackingNumber/OrderNumber. Given a basic setup with default purchase order, form and line item meta classes.

DECLARE @TrackingNumber NVARCHAR(255) = '12345';
SELECT *
FROM [OrderGroup] og
    JOIN [OrderGroup_PurchaseOrder] po ON po.ObjectId = og.OrderGroupId
    JOIN [OrderGroup_PurchaseOrder_Localization] pl ON pl.ObjectId = og.OrderGroupId
    JOIN [OrderForm] f ON f.OrderGroupId = og.OrderGroupId
    JOIN [OrderFormEx] fe ON fe.ObjectId = og.OrderGroupId
    JOIN [OrderFormEx] fl ON fl.ObjectId = og.OrderGroupId
    JOIN [LineItem] li ON li.OrderGroupId = og.OrderGroupId
    JOIN [LineItemEx] lie ON lie.ObjectId = li.LineItemId
    JOIN [LineItemEx_Localization] liel ON liel.ObjectId = li.LineItemId
WHERE po.TrackingNumber = @TrackingNumber;

Getting even more order data

In the above example excluded shipping/shipment and payment data. Of course this could be included as well. Payment method is typically extended with meta classes and could be queried on that level too, omitted here for brevity.

DECLARE @TrackingNumber NVARCHAR(255) = '12345';
SELECT *
FROM [OrderGroup] og
    JOIN [OrderGroup_PurchaseOrder] po ON po.ObjectId = og.OrderGroupId
    JOIN [OrderGroup_PurchaseOrder_Localization] pl ON pl.ObjectId = og.OrderGroupId
    JOIN [OrderForm] f ON f.OrderGroupId = og.OrderGroupId
    JOIN [OrderFormEx] fe ON fe.ObjectId = og.OrderGroupId
    JOIN [OrderFormEx] fl ON fl.ObjectId = og.OrderGroupId
	JOIN [Shipment] s ON s.OrderFormId = f.OrderFormId
	JOIN [ShipmentEx] se ON se.ObjectId = s.ShipmentId
	JOIN [ShipmentEx_Localization] sel ON sel.ObjectId = s.ShipmentId
	JOIN [ShippingMethod] sm ON sm.ShippingMethodId = s.ShippingMethodId
	JOIN [OrderFormPayment] ofp ON ofp.OrderFormId = f.OrderFormId
	JOIN [PaymentMethod] pm ON pm.PaymentMethodId = ofp.PaymentMethodId
    JOIN [LineItem] li ON li.OrderGroupId = og.OrderGroupId
    JOIN [LineItemEx] lie ON lie.ObjectId = li.LineItemId
    JOIN [LineItemEx_Localization] liel ON liel.ObjectId = li.LineItemId
WHERE po.TrackingNumber = @TrackingNumber;

Getting (serializable) cart based on property value

Using the new-ish SerializableCart table is a bit more cumbersome (or easier depending 🤷‍♂️). The example queries are bit more advanced than previous examples but should be a nice starting point for querying on the data that matters to you.

Say that you have a property (Cart_IsAwesome) on carts that you would like to query on. The table has just a couple of columns: CartId, CustomerId, Name, MarketId, Created, Modified and Data. The last one is the target for our query as it contains the JSON representation of our cart state.

In my example we have something similar (but not pretty printed) in the Data column for a SerializableCart row.

{
	"Id": 1,
	"Created": "2021-09-24T11:37:54.003Z",
	"Currency": "SEK",
	"CustomerId": "00000000-0000-0000-0000-000000000000",
	"Forms": [ // Stripped down for readability
        {
            "Shipments": [
                {
                    "LineItems": [
                        {
                            "Code": "12345",
                            "DisplayName": "Acme Floor Polish",
                            "Quantity": 1.0
                        }
                    ]
                }
            ]
        }
    ],
	"MarketId": "DEFAULT",
	"MarketName": "Default market",
	"PricesIncludeTax": true,
	"Modified": "2021-09-24T11:38:12.7381036Z",
	"Name": "Default",
	"Notes": [],
	"OrderLink": {
		"OrderGroupId": 1,
		"Name": "Default",
		"CustomerId": "00000000-0000-0000-0000-000000000000",
		"OrderType": "EPiServer.Commerce.Order.Internal.SerializableCart, EPiServer.Business.Commerce, Version=13.21.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7"
	},
	"OrderStatus": 4,
	"Properties": {
		"Cart_IsAwesome": {
			"$type": "System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
            "$value": true
		}
	},
	"IsTaxTotalUpToDate": true,
	"TaxTotal": 1.00
}

If you haven't queried JSON in SQL Server before it's doable but I find it a bit cumbersome and restricted. Look up JSON_QUERY, JSON_VALUE and OPENJSON on your favorite SQL Server documentation website for more details on how and why.

SELECT *
FROM SerializableCart c
WHERE JSON_VALUE([Data], '$.Properties.Cart_IsAwesome."$value"') = 'true'

Even though the JSON property is a boolean we query based on the string representation in this case. Also note that the $value property needs to be wrapped in quotation marks.

Say that you instead would like to query for line items in JSON format for the first shipment of the first form in carts matching the above criteria, then something like the below would do the trick.

SELECT JSON_QUERY([Data], '$.Forms[0].Shipments[0].LineItems')
FROM SerializableCart c
WHERE JSON_VALUE([Data], '$.Properties.Cart_IsAwesome."$value"') = 'true'

And taking it even further, we can define the expected structure of a LineItem and make it into a table. Thus we can get the included line items in the returned carts.

SELECT c.CartId, LineItem.*
FROM SerializableCart c
	CROSS APPLY
        OPENJSON([Data], '$.Forms[0].Shipments[0].LineItems')
		WITH (
				Code NVARCHAR(255) '$.Code',
				DisplayName NVARCHAR(255) '$.DisplayName',
				Quantity DECIMAL(38, 9) '$.Quantity'
		) AS LineItem

The collections for properties are available on the other objects in the JSON as well. Having just the one form and shipment makes it easier ($.Forms[0].Shipments[0]) than opening the JSON and query each element in the collection for instance. A more simplistic approach could use LIKE but now we have expanded our toolbox!

Wrapping up

So that's all for today. Variations on the above (using aggregates, CTEs etc) are good tools both in development and production. Be careful when accessing production databases though. All info and queries delivered as-is, I take no responsibility for your actions. Check, double and triple check!

Cheers!