SaaS CMS has officially launched! Learn more now.

Query Commerce Database


How to get product and variant details using sql query directly from commerce database?

Would like to know related tables also.

Edited, Feb 05, 2020 7:01

Not recommended and not documented. Why do you want to do it? :)

Feb 05, 2020 7:52
Janakiram - Feb 05, 2020 8:03
My client requirement is such, need to find a way to generate a query.
Joel Yourstone - Feb 05, 2020 8:06
You have customer requirements that dictate which way you fetch your data? Why not use the APIs for it?

We don't disclose database schema, so basically it's blackbox to you. One of the reason is that maintaining database backward compatible is a very tedious task. Your script might stop working in the next update. Also you are missing all the cache.

What do you want to do with the product and variant details and why do you want to use direct database query? With proper fetching/caching strategy you can get adaquate, if not very good performance from well tested and well documented APIs.

Feb 05, 2020 8:12
Janakiram - Feb 05, 2020 9:19
Most probably the client wants this data to use in Business Intelligence.
Joel Yourstone - Feb 05, 2020 10:09
Then it sounds like you should build a product export that exports the data you want to export to the BI system :)
Quan Mai - Feb 05, 2020 10:10
that does not explain why do you want to use direct database query

@Janakiram : Just my 2 cents, I think you can achieve this using the Commerce API. You can use the API to traverse across the catalog and get all the information you require, you could leverage the data any way you want (Push it into a table in a DB, reports). 

Edited, Feb 05, 2020 23:25

Hi Janakiram

You can read from the commerce database, if you like. But you will be on your own, and you need to dig into the tables yourself, because the databases are not documented or supported.

That being said, I recommend that you query the APIs and push the necessary data to your client's BI database. You can do so regularly (with a scheduled job) or when changes occur (hook into content events).

Feb 06, 2020 6:29

The recipe 1.2 can be helpful

Feb 06, 2020 8:11
Quan Mai - Feb 06, 2020 8:13
Janakiram - Feb 06, 2020 8:55
Hi Quan, thanks for your reply. I am aware that read_sample and I have already created a job based on that. My client requirement is all about fetching the data instantly and one time use only. That's why they are insisting for a query.
Trying to convince them.
Quan Mai - Feb 06, 2020 9:00
if they are doing some BI then why it's important to be "instantly" ?
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.