I use git for version control. I have a development, staging and production environment. When I finish in development I push to staging for review by the client. When approved, I push changes from staging to production. That works fine as long as there is no database changes. What happens if I install modules via Magento connect on local development and it makes database modifications.
How would I push those changes up to the production server since the production server is always changing?
Edit:
I wrote two shell scripts. One that pulls the production database down to my development server, replaces base url with develpment url and updates my development db accordingly. It also leaves the production sql dump behind to be added to my git repo. I'm not really sure if it's beneficial to keep the raw dumps in source control but I'm going to try it out. The second scripts moves the development database up to staging and essentially performs the same operations as the first.
Now when it comes time to move to production I pull the updated production repo into the production server and allow magento to do it's thing. I also started using SQLYog recently and it has a database comparison wizard which will give me the differences in my development and production databases and allow me to merge the changes in selectively. It always creates a migration script that I added to source control as well. If anything goes wrong I can run the comparison to see if anything was missed.
Does this sounds like a decent workflow to you guys?
This is a common situation for developers. It's much easier to modify code and schema and be assured that all is well when there is a small codebase which is thoroughly understood and doesn't have too much flexibility for UI. Of course, this is not the case with Magento, which can be quite difficult to work into automated testing and continuous integration schemes. That said, there are some knowable, testable behaviors on which you can rely.
An Overview
When dealing with local development which is merged to production, one must be assured that the schema and data changes relevant to new or changed functionality are also applied when the filesystem is updated. This is actually how Magento itself works. Module configuration files can supply a version number and can configure setup resources. This information is used to enter into a schema & data modification workflow which results in version information being added to the database. It is the consistency between file-designated version number and database-registered version number that one can / the system can infer that the database is in the appropriate state given the files present.
This means that when the new/updated module files are merged to production and the necessary conditions are met (e.g. the config cache is invalid, etc.), the database upgrade should take place. Your (proper) concern is that this process might break based on remote server-level differences, remote data differences, etc. Without a tightly-regulated integration testing process, there is some overhead.
Plan of Attack: Pick the Right Strategy
The essential activity in this area is assessing the areas of module's impact on the database. This should be straightforward with any module which is worthy of being installed; check for any of the following:
global/resources xpath)global/resources xpath)For 1, simply review the structure and know that its effects on the database will be limited to the core_config_data table, and generally only once an admin has saved values via the GUI (noting that 1. below applies as well).
For 2 & 3, review the scripts which are set to be run. These can be divided into three general areas:
1. Configuration settings - look for setConfigData() and deleteConfigData() calls
2. Table additions and edits (new tables, adding columns, etc.)
3. EAV-related changes and additions; look for EAV setup resources
4. Non-EAV data changes: installation of new data or modification of existing data
It's a matter of feel & intuition, but gauging the level of impact on the db will allow you to determine if you should clone production data down to local dev and test the setup workflow locally, verifying it works okay, then pushing to production and re-checking (backing up always!). If the changes are wide-ranging, it may be best to take the site offline so that you can ensure that you won't lose order or customer data if you need to revert after a botched upgrade.