WooCommerce products are stored as custom post types. Each product will have 1 row in the
wp_posts table. and the post type will be “product.”
The product details will be stored in the
wp_postmeta table. This table is organized by
post_id matching the ID of the post and multiple key/value pairs.
Any meta_keys that begin with an underscore “_” are meant to be hidden from view on product pages.
How to find products in the database
In WP Dashboard go to the Products menu then find the product you want to review. Hover your mouse over the product to find the ID.
Access the website database using phpMyAdmin or SQLite and run the following SQL command using the product ID.
Note: your WordPress installation might have a unique table prefix like “wp_wW32_”. Check your table names and update the sql accordingly.
In this example the ID is 264.
This sql statement will retrieve the product’s single row.
select * from wp_posts where ID = 264;
This sql statement will retrieve the product’s meta. Multiple rows will be returned.
select * from wp_postmeta where post_id = 264;
Can WooCommerce products be edited directly in the database?
Yes, products can be edited in the database, but some product meta might be tricky to change.
A single value like “_sku” is easy to edit because the meta_value stores the SKU as a string.
Other meta that is more complex might be stored as serialized data. WPEngine describes serialized data as “just a fancy way of saying ‘to list data in a specific order.”
Product attributes is stored in a meta_key of “_product_attributes” as serialized data. If this field is edited directly and one character is incorrect the entire meta might not unserialize correctly and will cause errors on the site.
Knowing where WooCommerce stores products in the database is good to know when you’re troubleshooting issues, or just to know your store better.
If you have any questions or need help bulk refunding orders in WooCommerce Get in Touch, or ping Grow Development on Twitter!