Sitecore Commerce Databases Explained

Summary

This post is a quick high-level summary, describing how Sitecore Experience Commerce 9 (SXC) data is persisted in its databases and how to read and understand those database records. Below are some of the details on Commerce databases and tables, a quick summary which might be useful to Development and QA teams, starting on SXC 9 and need a quick walkthrough before diving deep into SXC9 documentation.

How Commerce Engine data is stored in Commerce Databases

With SXC9 using SQL Server (or SQL Azure) for data storage, the database schema is not relational - it looks more like an object database with simple table structure, featuring a limited number of fields and cross-references. Persisted Entity objects are getting serialized in JSON format and stored along with their unique IDs and a few additional fields (more on those fields below). When an Entity object is getting retrieved from the database, its JSON data gets de-serialized into its object representation. This approach has a number of benefits, particularly flexibility to support any kind of objects without schema changes and simplicity of database schema, as well as great read and write performance.

How to read Commerce data directly from Commerce databases

Since JSON is used all over the place to format, a good JSON formatter is a must-have tool. Many good JSON parsers can be found online (e.g. this or this one). Notepad++ has a good plugin called "JSON Viewer", which can be installed via "Plugins -> Plugin Admin" menu. (Spending a few minutes learning keyboard shortcuts for this plugin can save quite a bit of time in the future :))

The sections below explain where various Commerce Entities and Settings are stored in tables of Commerce "Global" and "Shared" databases. For the most part, all tables have identical schema and hold mostly the same set of columns with a few exceptions, such as Version column, which applies to some, but not all kinds of entity records. More on this below.

Global and Shared Databases

To put it simply, Global DB is used for application Settings and Policies. Shared DB stores SXC application data. (More details can be found in this blog post). Names of Global and Shared databases depend on your install script. Usually, they will end with "_Global" and "_Shared", for example, "SitecoreCommerce9_Global" and "SitecoreCommerce9_SharedEnvironments".

Database Schemas

For the most part, all tables follow the same schema and hold pretty much the same set of columns, with the exception of Version column, which only applies to some, but not all kinds of entity records. Global and Shared DBs use the same table schema, but tables are used differently where some tables are used and one DB and empty/unused in another and some are populated with data in both DBs but holding different sets of data. The below sections explain which tables are used in each database and which data is stored in each of these cases.

Commerce Database Schema

Sample result set from the following query: SELECT TOP (1000) * FROM [dbo].[CatalogEntities] in Commerce Shared database.

Commerce Database Typical Schema

And What do those columns in the above table mean?

Id: This is a unique entity ID where the left part is the prefix, based on entity type and the right part is based on the actual entity ID or name. For example in Entity-SellableItem-AW149 15 the "Entity-SellableItem: would mean it's a sellable item and "AW149 15" is an ID of the product represented by this entity. Id is unique, but the row representing it in the table is not unique due to potentially multiple versions of the given entity persisted in the table.
EnvironmentId: Specifies which Commerce environment any given entity belongs to
Version: This is used by SXC to track object changes internally
EntityVersion: Entity version in SXC is similar to item version in Sitecore. For example, version 1 can be published and visible on the web and version 2 is being updated by the merchandiser or content editor and is not published yet. When version 2 is published, it will replace the previously published version 1 on the site. More details on SXC versioning can be found here.
Published: When set to 1, then the system will treat an entity as published, which usually means that the given item should become visible on the site. Only one item version can be published at any time, so when a Commerce Product or a Category is displayed on the web and has multiple published versions than the one, the largest EntityVersion value will be considered to be the latest.

Global Database

Global DB store SXC application configuration settings and policies. The Commerce bootstrap process reads system configuration data from JSON config and policy files in the file system and saves it into Global DB to read it from the database later on. If changes to such settings are made in the file system, then bootstrap needs to be repeated or the system will ignore it.

Here's where important data is stored in Global DB (most other tables in Global DB are empty and currently not in use):

CommerceEntities: Serialized environment settings and policies is where configuration data is getting copied to and from configs and policy files in the file system when Commerce Bootstrap is executed.
CommerceLists: SXC Managed lists listing SXC Environments, associated with a given instance of Global DB. Each environment would usually have its own Authoring, Minions and Ops roles, where web application instances~~,~~ running those roles can work with multiple environments simultaneously. For example, an out of the box commerce install would come with Habitat and AdventureWorks environment samples. When it comes to Authoring, Minions, and Shops web apps, each would be associated with both Habitat and AdventureWorks.
Versions: Version of SXC system, connected to given DB (e.g. 9.2).

Entity JSON structure

All Commerce entity objects inherit from Commerce Entity base type and can have a list of Component and Policies, stored in Components and Policies collections respectively. Components are the main and very flexible way of customizing/extending Commerce entities, allowing adding custom properties without having to change database schema or any of the data access logic. Here's Sitecore documentation on Commerce Entity. Policies are meant for various configuration settings~~,~~ related to a given entity. When persisted Commerce Entity is getting serialized into JSON, "Components" and "Policies" are turning into root paths for respective object collections. In order to lookup entity data in Commerce database, one can find entity record by ID and evaluate its entity JSON. Below is an example of a very simple Commerce Entity. Complex ones can have much more data, but will follow a similar structure when it comes to main properties, Components, and Policies.

Commerce Entity JSON

Shared Environments database

Shared Database stores application data, various commerce entities, representing Catalog items (Categories, Sellable Items), Customers, Carts, Orders and relationships between them. Below is the list of tables in Shared DB and what's stored in them:

CartsLists: SXC Managed lists, linking different customer carts together
CatalogEntities: All items and relationships in all catalogs, defined in all Commerce environments. This includes all catalog definitions, categories, sellable items, relationships between catalog entities. (e.g. Categories to Sub-Categories, Sellable Items to their parent Categories, Sellable Items to Bundles, etc.)
CatalogLists: SXC Managed lists, linking various Catalog items together
CommerceEntities: Unlike Global DB, where CommerceEntities holds various application settings and policies, that come from config files, in Shared DB this table is populated with various Commerce and Shop settings, which come from "Commerce Control Panel" items defined in Sitecore, which can be found under "/sitecore/Commerce/Commerce Control Panel" content path.
CommerceLists: SXC Managed lists, linking various entities together
ContentEntities: Sitecore content items related to Commerce, mostly related to "Commerce Control Panel" and Storefront(s) settings
ContentLists: SXC Managed lists, linking various Sitecore content items together
Mappings: One to one links/relationships between Commerce entities, e.g. Sellable Item to parent Category or Sub-Category to its parent Category
OrdersEntities: When an order is submitted on the site then Customer's shopping cart is getting converted into order record, which is what this table holds.
OrderLists: SXC Managed lists, linking various SXC order entities together
PricingEntities: SXC Pricing - related records for Sellable Items. One product can have multiple pricing points defined for different situations.
PromotionEntities: Records related to SXC Promotions
PromotionLists: SXC Managed lists, linking Commerce promotions together
Versions: Same as in Global DB, the version number of SXC, e.g. 9.2

References:

Sergey

Sitecore Technology MVP & Architect

Type