Request Module proposal: DB migration assistant

[Rob]

It occurs to me that a module that records all alterations to DB schema would be useful for the migration of DBs from a staging/development environment to live or vice-versa.

I was thinking that, at present, if I work on my site locally (LAMP stack on my laptop etc) and then need to push changes live that it is no problem from a code point of view (I use git for source control, comfortable with that process) but that DB schema changes may be a bit more of a pain.

I can, of course, just recreate the fields and templates manually on the live site but it's not exactly convenient or intuitive!!

I have not checked, but if there are hooks into the DB parts of the core that make changes to the schema for new fields, template structure etc then it shouldn't be too hard to record those changes into a log that will therefore be a series of SQL commands that can be run to update the DB schema on another installation.

I'm slightly lacking the time to do this myself, but I suspect that there are talented module devs here who could do this in no time, assuming the hooks are in place.

An ex-colleague wrote a plugin for another CMS which did the same thing and it was very helpful indeed.

[Adam Kiss]

Hey Rob, that would be awesome - I strongly feel that there really is a hole, when it comes to database syncing. While I probably won't pick this up (I'm not one of those talented developers), is there any possibility of linking us (or the one that will pick this up) to that ex-colleagues code? (in case it's open source, or he is willing to share it with PW community, if you're in contact).

[Rob]

Hey Rob, that would be awesome - I strongly feel that there really is a hole, when it comes to database syncing. While I probably won't pick this up (I'm not one of those talented developers), is there any possibility of linking us (or the one that will pick this up) to that ex-colleagues code? (in case it's open source, or he is willing to share it with PW community, if you're in contact).

Hi Adam.

I think the code is on github somewhere and I could probably find it, but it's so specific to the CMS in question that it really wouldn't be relevant here. What we need to figure out is whether the hooks in question are available in the core and then tap into them to basically log every DB schema change.

I know from previous talks with Ryan that basically any function in the core can be made hook-able, but that there are performance implications in many cases so it has to be carefully considered.

That said, in this case it has nothing to do with page dispatch/render and is purely linked to back-end activity so it shouldn't affect site performance.

I'm sure he'll chime into this discussion when he gets the chance!

[ryan]

I think that I can add the necessary hooks to support this. Overhead with high traffic functions is always a concern with hooks, but did recently figure out a way to do it in a way that avoids any overhead unless/until a hook is actually active. So it should be possible to make something like $db->query() hookable. I will take a closer look at this.

[Adam Kiss]

Ryan, haven't you previously mentioned that you made something like versioning before?

I would really love to have something like this:

You have local/live version; you make a snapshot of a live version, and push the changes to local version. You work on the local, disregarding the live version. When you're finished, you'll take new snapshot of the live and merge it into local version. Now you can push your new local dump live and you're in sync.

I think this comes into play not only with external editors (who can't work with your local version, obviously), but also to other automated things out of your control: I for instance have visit tracking code on section of website, because I later recalculate lists with this data. Also, when you have new comments and such on the website, you'll like to merge this with your local (possibly outdated version).

Only other way to do this (as far as I know) is to do all the dev on your local version, do live->local db dump, freeze live, do local changes and push local->live. And while it might sound simple when written, it really feels cumbersome...

[ryan]

Web sites aren't just databases, but files too (which may be version tied to the database), so I don't see anything that operates just on SQL queries as being realistic. Versioning is one thing (which can be done), but migrating in the manner you mentioned is something else. I don't think you should ever consider "live" as something that can be frozen while you go to work on dev, though I guess that depends on the case. I know I couldn't freeze any of my live sites. Migrating file-based assets (templates, js, css, etc.) is a pretty simple matter. But migrating databases gets more complicated. Future tools for migrating data in ProcessWire will likely take a web-service approach (two sites talking and sharing JSON) rather than one involving sharing of database queries.

[Adam Kiss]

Ryan,

the freeze isn't the full time while you work on dev, I mean rather something like preparing everything you need locally and then:

• discard local db changes
  
• live->local db push
  
• recreate needed db changes (as in merge local+live)
  
• local->live
  

This could be done in matter of minutes, if well prepared (for instance, every new 'local' page is created through one script, for instance) and I think freezing anything for few minutes is possible (even on ecommerce sites, 10 minutes can't hurt your sales that much short of work day hours).

Of course, this involves rather unnecessary logistics, and something more elegant should be in PW

[neildaemond]

What if every time PW made a change to the database schema, afterwards it asks if you want to record that change. Then PW could ask you to add this change to a 'batch' or create a new batch to add to.

while each batch would represent all the changes made in the new site version, on the server each 'batch' could be a folder containing the php scripts which implement the same database schema changes, in the same order (by date/time).

run those scripts on the production site and you should be caught up.

[edit: well, I guess the content won't be caught up. But at least things like fields and modules and stuff. Page content would still need to be added to the live site. maybe a 'save and record' button when editing a page? ]

would something like that be implementable?

[ryan]

I think it sounds good in theory. But it also might be problematic in reality. DB schema changes are often the result of other things going on in the system that have some other representation whether by modules, template files, page files, etc. We couldn't just record DB schema changes and then play them back somewhere else because the source of those DB schema changes might not exist on the other system. I will keep thinking about this though, as it's definitely interesting.

[Hari KT]

I recently came accross http://www.rubyrep.org/ .

Seems an interesting one.

Development of an open-source solution for asynchronous, master-master replication of relational databases that is ridiculously easy to use database independent

Currently supports PostgreSQL and MySQL.

[ryanscherler]

Hey all,

I wanted to see if there was any progress on this feature. I have recently had to use Wordpress on a project or two and while I can't really stand WP, there is an awesome plugin for syncing local and remote DB's (really any DB to another):

"Migrate DB Pro" - https://deliciousbrains.com/wp-migrate-db-pro

Something like this would be AWESOME to have for ProcessWire and really help with continuous integration workflows (Git does a great job of file sync, but DB is still a pain). Any thoughts on next steps for a module like this?

Thanks!

[adrian]

https://github.com/adrianbj/ProcessMigrator

and since you are coming from Wordpress, you might enjoy this plugin module for taking WP XML exports and importing them into PW:

https://github.com/NicoKnoll/MigratorWordpress

They are still possibly not quite release worthy yet, but with a little more testing, hint hint we might be ready to add them to the modules directory.

[ryanscherler]

Excellent! I will look into this module and give it a try. Thanks!

[Jonathan Lahijani]

Hey all,

I wanted to see if there was any progress on this feature. I have recently had to use Wordpress on a project or two and while I can't really stand WP, there is an awesome plugin for syncing local and remote DB's (really any DB to another):

"Migrate DB Pro" - https://deliciousbrains.com/wp-migrate-db-pro

Something like this would be AWESOME to have for ProcessWire and really help with continuous integration workflows (Git does a great job of file sync, but DB is still a pain). Any thoughts on next steps for a module like this?

Thanks!

The WP Migrate DB Pro plugin doesn't do versioning (like Ruby on Rails DB Migrations) or content deployment (merging), but rather a complete database replacement of one database to another, which would be problematic on sites that are already live and whereby the live site and staging site have different content.  The RAMP plugin is what does selective content deployment.

The current ProcessWire equivalents are a bit manual:

• Can export and then import fields (native PW feature)
• Can export and then import templates (native PW feature)
• Can export and then import HannaCode (native module feature)
• Can export and then import FormBuilder forms (native module feature)
• The ProcessMigrator module does both field and template export/import along with the ability to export and import actual content.  So in my eyes, it's the currently the analogous plugin to RAMP.

It would be very slick ProcessMigrator could "connect" a staging site and a live site so that content deployment could be one click.  Would be a great premium feature!

[Jonathan Lahijani]

Another plugin that's similar to RAMP:

https://github.com/stenberg/content-staging