This issue becomes especially difficult when there are multiple environments, with multiple developers working on different features that affect the data structure.
The holy grail is a git-style distributed database setup, and I believe the Craft team is working on a solution that integrates with Craft Core. But it'd be great to hear how the community is handling this currently.
My general approach for this, not just with Craft, but with all CMSes is to have a shared database for development. It's not the most ideal solution, since there can be overwriting of work, but it at least prevents schema changes from having to be redone across multiple environments.
Once a site goes live, maintaining a dev environment alongside live becomes a bit more troublesome. My approach right now is generally to either make the changes in both places, or make the schema changes on live first and then copy the database back down to dev. (This of course requires that the changes not be anything that will break the current live site, and that both dev and live site are running the same version.)
On the upside, Craft does an awesome job of handling the schema changes that come with Craft updates. Basically the database version is checked against the file version, and the database changes can be reapplied on additional environments to update all the databases. This makes updates across environments lot easier than something like ExpressionEngine, which usually requires a multi-step copying of database down from live and back up to live.
In the future I'd love to see ways to encapsulate configurations in a migration similar to the updates, so that it's really just a matter of running the latest migrations to keep everything in sync.
Hmm. I started out having a feeling existing tools might be a best approach here. I soon found that even with the better of these, except for in-dev patching, we may want to wait for a Craft capability for transferring any less than a full dump of data in a Craft database. Syncing databases is a tricky problem, unless they are of exactly the same structural vintage and you want to take it all.
In Craft you have the strong issue of how much of its capability which we take for granted is based on accurate use of foreign keys, which must be handled correctly by pulling the data that relates this way into the sync or merge, if it is not a total replacement - more on this below.
If you want all current live data on a dev or staging database, you cam avoid the problems. It's very easy to do what that Wordpress plugin does: just take an sql dump of the live site, drop the dev database, and import the dump to replace your working data. This can be done with phpMyAdmin, much less pleasantly with MySQL Workbench, both free; or for as low as $10 you can have the base version of Navicat, Navicat Essentials, which is very slick, very fast, and with a free trial even at that price point. I think it's a good deal.
I think that this method is best to use, unless you want to get in deep, or wait for a possible Craft feature solution.
It does sound good indeed to just pull over fragments from a working or alternate dev database. It could certainly come in handy, especially as it wouldn't wipe out your own present partial work. I've had a very brief look at some current tools to try to see how they might support this.
Overall conclusion (tl:dr): unless you want to really understand all technical aspects of the Craft database, at least in the area you are working, syncing fragments, for example test data for a new development, is not a sensible option. Further, unless I miss my overview taken quickly, the assistance of even the expensive db tools is not sufficient for this.
Some of the difficulties:
I think it's not a small or unintended omission that even the expensive database tools quietly don't actually support automatic relational data merges. Just like text merges, you have to understand meaning to do it. The better ones have tools that can help you do that if very knowledgeable, but they don't automate using the information. You are on your own.
Can you do it anyway, merge in fragments from another development, or useful data from a live site? You can - with varying degrees of misperformance or destructiveness, wherever you don't know or remember all the important relationships or data details. Caveat emptor, but in principle you can do it, given you're replete with backups and ready to start over. Thus this would be a dev-only move, and if you think it will save you over just using Craft to enter the the new data, say from windows side-by-side.
If you want to have a play for yourself, I personally liked the look of Toad, which is entirely free, and possibly the nicest-implemented free tool to have seen, including its inside-integrated support and community system. Unusually, it's provided by Dell, as their free software contribution.
Toad operates a lot like a text diff-merge tool, and I had it ready to complete a chooseable merge of two databases in about five minutes from cold start -- if you didn't have the actual difficulties hinted at above for doing this correctly. Probably more, but that's what it felt like
Trying this will give you an idea of what you actually want in doing a merge. There's an amount of actual complexity in a database; it's not just a set of data lists to be conveniently manipulated by your framework's query builder.
Under Toad's Tools is what's called a Master-Detail Browser. This will build a pretty Entity-Relation diagram, showing you foreign key relations for a data area -- but ONLY when those have been defined. To see quickly an area where they aren't, try Categories and Category Groups - just an example to underline that you have to understand Craft's detail design, not just what a tool shows you.
Using this Master-Detail Browser with some understanding of the Craft area, you can probably write down a paper list of what tables need to be considered. Then you could use Toad's Data Compare to see differences in that nice diff mode for two databases, and choose the tables you want to include from the paper list. Toad will then generate an SQL script to make the transfer - which you would sensibly only try with a complete backup first taken of the original dev database.
It might help you to try a new template or plugin feature, but only with full awareness of the potential inaccuracy of what you are doing. After such a testbed, you'd start clean again, no doubt by loading a backup or transfer of a clean working database. Building your test data again by entering through Craft will be a good test that you've done things right in your new code, and in relation to that in Craft.
Perhaps that's just as true if Craft actually could provide a partial data export/transfer ability in future? It might be some measure on whether they should. Immediate desires may be popular, but not always the best way forward; it's why we do what is design.
As always, it's just one view here, and you may have another, but hoping it's useful, and that through comments and other solutions, we each may learn a thing.
Note: This method is not for the faint of heart and might not be for everybody, but can be useful in some situations.
One thing we do (at Airtype Studio, where I work) for larger installations is set up a couple of load-balanced pools of servers: an app tier and a web tier. In fact, the environment architecture is very similar to this:
This type of architecture is commonly referred to as an n-tier architecture. You can read more about it here: https://stackoverflow.com/a/312197/3053214. Basically, web servers can't talk to the database; Only the app servers can. This allows you to lock down who or how the app servers are accessed. It also provide a layer of separation between software that can be publicly attacked (the web tier) and the database, which inherently has advantages outside of the context of this question. Anyways, moving on.
So where does Craft fit into this big picture and more importantly, what does this have to do with migrating data across environments? I'm getting there, just bear with me.
We install Craft on the app tier and basically build an API (either by outputting json via twig which can get hairy, or building plugin to map routes and output json for us). On the web tier we have a php app (like Laravel, Yii, Slim, etc - we typically choose Laravel) that makes requests to and consumes the app tier API that Craft is controlling.
From a git standpoint, the web and app tiers have their own repos. They are considered two separate applications, each on their on track, although they work together as one. Each application has their own dev and staging environments. This allows us to point our web-dev environment to our app-production environment. I could have a whole convo on what a git workflow looks like in this situation, so if you're interested, hit me up.
To manage content across multiple environments, we don't actually migrate data at all. We elevate it. What I mean by this is all web tier apps typically point to the production environment of the app tier. It's no different than pulling data from a 3rd party source, like Twitter for example. You don't point your dev environment to Twitter's dev environment do you? No, you'd have your dev code point to Twitter's production environment. My main point is, don't feel like your web-dev environment has a 1 to 1 relationship with your app-dev environment. Just don't think about it like that.
On entries within Craft, we use a multi-select or checkbox custom field that contains a list of our environments. By checking which environments the content is allowed to be viewed in and querying Craft with the environment criteria, we can effectively limit where our content is allowed to show up.
So really, a request for us goes something like this:
Now I realize that many of you have no need for a load balanced, multi-tier environment. Not to mention that setting all this up as described above is a lot of work and in many situations is super overkill. However, you can apply some of the principles of what I'm describing to an environment that as a much smaller scale. For example, you could set up separate virutalhosts on the same server and build out web and app environments there.
A couple of thoughts about all of this:
I work with git for all my projects, I had to spend some time figuring out how to set up the folders and files, i.e.: what to gitignore. But it now works pretty well. All the core files are independent from the configuration/assets so it's pretty easy to work with once it's been setup.
Now, in regards to the database, I have been working with separate, development and a production, databases. This allows me to play with new ideas that I can simply get rid of by the end of the day. After lots of content has been added to production I'll copy the data from production to dev to update it; this can be annoying but it does the job. This of course does not account for major structural changes. The way I've dealt with something like that is just replicating the structure in production after testing it in Development. Not the most pleasant experience.
Certainly a git-style DB would be awesome for this, at least in concept. Not sure what would it actually look like in practice.
We currently handle this with database dumps from dev and taking screenshots of config screens and attaching them to git pull requests. There needs to be a better way though.
The only CMS that has any kind of solution that I know of to this problem is Wordpress with this plugin https://deliciousbrains.com/wp-migrate-db-pro/
I found a great blog post where the author uses Phinx and MySQL Query Log to create database migrations. I summarized its content into 10 easy steps:
Step 1
Enable the MySQL Query Log on your dev environment by editing your mysql my.cnf file.
Set the general_log key to 1 and general_log_file to /var/log/mysql/mysql.log
Step 2
Take a snapshot of your current db:
mysqldump -u dbuser -p dbname > dumpfile.sql
Step 3
Empty your query log file:
> /var/log/mysql/mysql.log
Step 4
Extract only the relevant queries from the log:
cat mysql.log | grep -E '[[:space:]]+[[:digit:]]+[[:space:]]Query' | grep -ivE 'Query([[:space:]])+(/*.**/)?(start|select|set|show|commit|rollback|use)' > mymigration.sql
The command translates as “Look in the log file, filter down to all lines starting with a query and then filter out any starting with an unwanted keyword. When you’ve got all that, save it to a file called mymigration.sql”.
Step 5 only required once per project
Assuming that you already have Composer installed on your dev environment, run the following commands to install Phinx:
composer require robmorgan/phinx then composer install --no-dev
Create a folder in your project directory called migrations with adequate permissions (should be writable).
Step 6
Make a migration using phinx:
vendor/bin/phinx create FooBarFeatureMigration
The last argument is whatever you want to name the migration. A new class file should show up in your migrations folder.
Step 7
Open the migration class file and change the change() function to up().
Step 8
It's time to manipulate the contents of "mymigration.sql" into useful PHP. Wrap every query with $this->query(""); and put the whole lot inside the up() function in the migration class file.
Step 9
Rollback the database to how it was before the change:
mysql -u dbuser -p dbname < dumpfile.sql
Step 10
Run the migration:
vendor/bin/phinx migrate
If you go back to Craft CP, you should see your new field applied. You can now add and commit the migration to git as normal.
Check out the complete article here.
I use this workflow with Git, Deploybot and a DigitalOcean droplet on a fairly large site (~10k entries). It works like a charm.
vendor/bin/phinx migrate. Step 8 is the longest part. I just don't have time right now to fully automate this process.
Commented
May 24, 2016 at 17:46
I'm currently implementing a workflow that's very efficient for us.
The trick is to use Backup Pro to "backup" locally and then "restore" once the site is deployed. This essentially version controls the DB without lots of manual DB dropping and importing. Communication is very important with this method as Devs and Stakeholders need to be aware of how the data is moving around.
Craft CMS with a multi-environment config:
Private Git repo using Beanstalk(BS):
Developers connect to a remote DB on Dev
After initial development, we will version the DB utilizing Backup Pro: https://mithra62.com/projects/view/backup-pro
Here is where the DB versioning magic comes in:
If changes need to made to the PRODUCTION DB, initiate a planned content freeze.
Make a Backup of the DB
Sync your local environment with the Production DB. This can be done manually, or using Backup Pro after editing the config settings.
Edit the config file to connect to the DB locally.
Make all needed changes to the DB locally.
Make a backup of the DB using Backup PRO -- This will put a backup of the DB in your project, and since your project is under VC, the database will be included in any commits.
Commit, and push to Dev.
Login to the DEV CMS and using Backup Pro's "restore" option, restore the DEV DB with the newish version (The one you just created using Backup Pro).
This process can be repeated for staging and production environments. That's the overview. In reality it's bit more involved, but that's the general concept that works for our team. It's also nice to have commit messages that explain what's been done to the DB.
If multiple Developers need access to work on the LOCAL DB, you can all connect remotely to a local DB, or agree to connect to the DEVELOPMENT DB.
Note:
Always, make backups.
Another nice feature of Backup pro, is that you can set up cron jobs to make daily backups for you.
This came up today, so I figured I'd post for completeness. This is how I handle it:
