Reapplying Users and Related Content to Database During Pipeline Promotion

Question

I was on a dev ops call this afternoon and am wondering about the viability of a proposed solution for pushing changes to a Craft product through a dev pipeline.

For context, this is for a big company with big security/scalability concerns, and their leadership team was thinking through this.

The idea is that dev and content changes would be applied to a database (Postgres in this case, if it matters) in a low environment (e.g., dev.ourproduct.com). When content or technology changes, a build pipeline would then transfer the entire database (along with whatever requisite code) to a higher environment (e.g., prod.ourproduct.com). During the migration, we would capture the user generated data rows/tables in the higher environment (e.g., users tables, user groups, any custom tables we use to capture user activity in production etc), and then "reapply" this live content and its corresponding data to the recently promoted/imported database.

The obvious challenge I'm struggling with is that we'd need to reliably track and map the "element" and related objects from the outgoing database into the incoming database. I've only ever used the craftcms api for generating users and elements, is this something that could be leveraged for this case and at scale, with at least tens of thousands users, perhaps more? From what I've read over the years, manually attempting to insert elements/rows/relations into a CraftCMS is dicey business.

My reaction to the proposal was "ooh boy". Has anyone traveled this road before or have thoughts about this? Thanks!!

Answer 1

I started this out as a comment but it got too long. Maybe not a complete answers, but some thoughts on your proposed solution.

I think the key point to contest (when talking to your management) is this:

We had presented migrations as a solution here already, but the above is the current focus of exploration.

What's important to realize is that by rolling your own content merging/propagation strategy you're still using migrations. You're just not using any of the proven, useful tools for performing migrations. I feel like I can hear the conversation where somebody went "But can't we just¹ …" and the proposed solution was merging some part of the content from a pre-prod environment with the production environment. But where are the boundaries between those? Are they even crystal clear?

It's not as simple as grabbing a couple of tables (the content and the user data, respectively) from each database and merging them together. For one, content is always split across multiple tables. Many fields store content in their own dedicated tables, so what if you're using one of those fields on both regular entries (coming from pre-prod) and user accounts (coming from production). So you have to find a way to merge those, while retaining all sorts of constraints on the database level. By sidestepping Craft's API and going directly to the database, you basically have to ensure you know all the ins and outs of how and where Craft stores data and handle all edge-cases that Craft already handles for you. Of course, you'll also have to maintain your custom solution whenever a new Craft version breaks something in your migration code.

In terms of talking to management – I'd start by talking about risks and benefits. What is the risk of updating content on a production environment? Some internal inconsistencies while changes are being made? On the other hand, what's the risk of rolling your own migration script with difficult rules for merging content? Accidentally dropping important user data? High maintenance costs for constant updates? It's just not worth it.

Better solutions

I'd say what you really want is a way to export content from your pre-prod environment to production in a predictable way. Exporting is even built into Craft. Importing could be done with the Feedme plugin. If you want to get fancier than manual imports/exports, you could build a small plugin with a button that just pushes content from one environment to the other (target, authentication keys etc can come from environment variables). This way, you could still update content across the entire side in one atomic step, but you could use Crafts API and keep your sanity.

Another approach: Craft 3 separates content from config and the deployment/migration processes are clearly geared towards that separation. So if migrating content alongside code and configuration is an important requirement – how about using another CMS that is better suited for that task? Statamic has a git plugin that allows you to track content changes in git. And there are other platforms that allow you to separate production data from site content/copy, in particular headless platforms like Strapi. Maybe one of those would work better for your usecase.

¹ https://signalvnoise.com/posts/439-four-letter-words