Roots Discourse

Preserve WooCommerce data during db sync

#1

I’m having trouble coming up with a solution for what I imagine must be a fairly common problem, and I’m interested in hearing how others have handled it. We have a WooCommerce site that we manage with Trellis. The general workflow is:

  1. Make content edits on staging
  2. Use Migrate DB Pro to push database changes from staging to production

The problem starts when we capture new e-commerce transaction data on the live site. Pushing the staging database to production overwrites this data, and I’m trying to come up with a strategy for preserving it. So far I’ve identified three options:

  1. Trigger an export of WooCommerce data before every sync, then re-import the data post-sync
  2. Use a filter with Migrate DB Pro to ignore WooCommerce fields during sync
  3. Use the WooCommerce api to sync transaction data back to staging in realtime

Option 1 seems cumbersome and error-prone. I’m not sure Option 2 is possible, due to the fact that WooCommerce doesn’t store order and product data in unique tables, but lumps them in as custom post types in the wp_post and wp_postmeta tables. Option 3 seems decent from a functional standpoint but it’s not trivial to set up and I haven’t found a single instance of anyone actually going this route.

I’ve found very little documentation (official or otherwise) on how to best handle this. Anyone have a solution?

#2

Our approach has always been that once a site has launched, you don’t push DB content to production. If content needs to move from staging to production you move it manually (i.e. you copy-and-paste), or you use a tool (i.e. http://www.wpallimport.com/export/) to move just the content you’ve updated from staging to production.

1 Like
#3

@alwaysblank that makes sense. Unfortunately in our case the site is undergoing frequent content changes. We have multiple editors adding/removing/updating/reviewing content on staging and we push content changes multiple times a week. It really requires a database sync.

#4

I don’t know the details of your situation, but at least in my experience trying to somehow automate that process is not a solved problem w/r/t to WordPress and its database. There might be ways you could structure a site to be more receptive to automated DB merges, but it’s still going to be very difficult. It sounds like this site is already in production, which probably limits your options, but if I was looking at a project similar to what you’re describing I’d probably consider doing one of the following:

  • Separate sites for WooCommerce and whatever the rest of your site is, so you can push updates to the non-WooCommerce part w/o interfering w/ WooCommerce. You could deploy the same theme to both, and use REST API stuff to communicate between sites.
  • Don’t use WooCommerce (we just use Shopify for almost everything these days for reasons like this).
  • Do your content editing/removing/updating on production, and use WordPress’s own tools for content management, approval, etc.
  • Mange non-WooCommerce content outside of the WordPress ecosystem, i.e. store your content in flat files that are parsed into content on deploy or on view.
#5

Thanks @alwaysblank, I appreciate your thoughts on this. You are correct, this site has been in production for a while now. If we ever end up restructuring I would certainly consider the options you outlined, knowing what I know now. It’s frankly surprising to me that WooCommerce and WordPress can’t easily coexist in a basic staging → production workflow like ours. I will probably end up attempting either Option 2 or 3 above and see how it goes.

#6

For me, after the site has been released to production (the first time) and populated with content, its content is now authoritative. Never something is pushed to production again (only in rare case of restoring a backup maybe). Content is then only tranferred “downards”, from production to staging and development.

Why can’t in your case the production site be used for content entry?
Do you want to review content changes first? This could be implemented in production, too.

The development and staging environments are meant for testing code (themes and plugins), using representative content.

#7

@strarsis there are a few reasons why managing content directly in production is not ideal in our case. For one, we sometimes have outside partners that must review content before it goes live. These are often non-technical people who are reviewing entire microsites’ worth of content. In order to have them review unpublished content in production, we’d have to set them up with their own WP access, or use a plugin like Public Post Preview to set all of the relevant pages as preview-able before sending the list of links to the partner for review. Given the difficulties of getting busy stakeholders to review at all, we want to avoid introducing additional barriers as much as possible.

Having content editors working directly in production also introduces more risk for error (for example content being published prematurely).

Still, it’s a good point and it’s given me another option to consider. I may end up going this route if I can show that this is a natural method of preserving our WooCommerce data that outweighs the drawbacks I mentioned above.

#8

@needle would you consider to add tow step publishing into your workflow. Something like two persons needs to approve the publishing of a Post.