Database Questions - Remote & Merging

trellis
1

Hello!

I’ve been reading old topics such as this one (about remote SQL databases), and was curious if this is still the method most people follow.

The short version of our current task is that we have a hosted site using trellis on AWS (using two EC2s, one for Production & the other for Staging), and we are moving everything over from AWS to Azure. While doing this, I want to start using a Remote/Hosted DB on Azure, that way we can scale up with high availability if we need to (we intend for our site to be used on multiple continents). If it’s just a matter of changing DB_HOST to point at the IP of our configured DB instance on Azure, and then re-provision the VM on Azure, then no problem!

But I have another question…

What is the currently agreed upon ‘best practice’ to work between multiple developers and making sure their databases don’t produce merge conflicts? With our current setup, as mentioned, we currently use a Production VM and a Staging VM, each with their DBs stored on the VM Volume; and we use WP Migrate Pro to push and pull from one to the other. We have ran into some merge conflicts (read: overwriting), and want to avoid that happening again (maybe setting some internal rules such as “first pull all new user details from production and then push from staging” kind of stuff). I know that Delicious Brains were also working on mergebot, but killed it late last year, so I’m definitely looking for your guys methods!!!

Would you use two separate hosted DBs, one for Production and one for Staging, and then do pushes/pulls between them?
Would you have one centralized (hosted) Production database and then have Staging/Dev environments use their own local ones?

Thanks, looking forward to your feedback! =D

2

I have also been using an AWS setup for a while alongside a remote Aurora RDS which was good. Also using Kinsta more recently.
Here’s my flow (working alongside in-house devs):
1 DB for local dev on our office network server which myself and my colleagues share. Working on the same project, we all use the same DB so we have no issues.
Once we’ve finished build, we deploy code and DB to staging (also using Migrate DB Pro) and then from that point on we never push the DB up. Only code. Same situation for when deploying to production.

So my rule of thumb is: code goes up, DB comes down. Never push DB changes.

The only times I find myself having to deal with DB changes needing to go up is with ACF, but fortunately we commit our JSON to our repos and use the sync feature to get fields updated.

Hope that’s helpful!

Edit:
You asked about having separate databases. Definitely have a separate database per environment. Local doesn’t need a remote DB though, just a local one.

1 Like
3

Thank you for the insight!! It is quite helpful :slight_smile:

Are you handling the multiple environments with custom Ansible playbooks? =]

4

Don’t have anything special between environments and really for the parity between them you shouldn’t really have any playbooks that will make environments differ from each other.

5

1 DB for local dev on our office network server which myself and my colleagues share. Working on the same project, we all use the same DB so we have no issues.

More on this setup / foreseeable issues:

You might want to sync uploads as well. Otherwise, you will see other colleagues’ uploaded files become 404.
Possible solutions:

  • WP Offload S3
  • mount a network file system
  • or, just tolerate the 404s

There are chance you don’t want to use a office-shared network DB, especially when developing with custom DB tables, DB query caching and WP cron.

How things could go wrong:

  1. you developing a new wp cron job (not yet pushed, source code only exist on your vagrant VM)
  2. you scheduled it
  3. your vagrant VM saves it into the shared DB
  4. colleagues’ vagrant VM picks up the new cron event from the shared DB
  5. colleagues’ vagrant VM wants to execute the cron event
  6. colleagues’ vagrant VM can’t find the corresponding classes/functions
  7. Boom! Cron job failed

In such case, using a local DB on the VM is a better choice.

Would you have one centralized (hosted) Production database and then have Staging/Dev environments use their own local ones?

Are you handling the multiple environments with custom Ansible playbooks?

To avoid production/staging parity, treat your staging servers as the production ones:

  • use the same services, i.e: both of them use Azure SQL DB, but 2 different DB instances
  • use the same source code
  • use the same Ansible playbook, i.e: same provision/deploy workflow
  • use the same config, i.e: keep staging.php empty
  • only differences are the API keys, e.g: PayPal sandbox on staging but PayPal live on production

How about debugging on staging?

Enabling WP_DEBUG on staging servers sometimes useful and unavoidable. Rather than messing with the production-like staging servers, set up a special environment for such uses:

  1. set up a new DB instances on Azure
  2. clone these files:
  3. edit them, i.e: enable verbose logging, error reporting, xDebug, etc
  4. provision & deploy

Note: Although setting up new Trellis environments is undocumented, I don’t see any issues doing so.

Here’s my flow (working alongside in-house devs):

P.S.: @codepuncher and I are colleagues.

3 Likes
6

Thank you so much for this whole post, it has been very helpful in contextualizing a lot of our process moving forward =D It is very much appreciated, and thanks again for you having taken the time to write it!!!

1 Like