Searched for a while with no avail, but please point me in the right direction if I missed it…
Wondering the best practices for including SQL scripts in the trellis/ansible deployment process (possibly with relation to Wordpress’ dbDelta for schema updates) and additionally how to revert those database changes on rollback.
The SQL scripts might include schema and/or data changes. Ideally a specific script could be configured to execute on the next/specific deployment but never again.
Trellis doesn’t handle database updates by default.
What you’re describing sounds like database migrations and seeding, something that is not included with WordPress, and not something I’ve ever actually seen happen with it. But holy crap could WP use it, however it’s a very developer centric feature so nothing that will ever be added into WP core.
Many people once they get into a multiple developer project, have only a couple options that I’ve seen used successfully. I think that generally, developers need to be in communication about who’s doing what with the database.
More so than migrations/seeding/syncing, we need to normalize/alter old data to work with newly optimized functionality (though maybe those can be handled via a migration process). Conceptually, I feel most comfortable being able to run straight queries for those types of tasks. I.E. on deployment run a sets of sql scripts.
The big part I didn’t see covered is rollback - after making all these changes to the database, how would one easily revert to a prior state? I figured something along the lines of making a backup of the DB before deploy, which could be imported on rollback might be a solution, but hoped there is a more graceful/automated method.