<p>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. </p>

<p>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.</p>

<p>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. </p>

<p>I think that this method is best to use, unless you want to get in deep, or wait for a possible Craft feature solution.</p>

<p>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.</p>

<p>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.</p>

<p>Some of the difficulties:</p>

<ul>
<li>you'd have to discover what tables are related, and copy over the
related records also for the data set you're interested in
transferring.</li>
<li>Reverse engineering screens available in the more expensive versions
of paid tools could help with this, but it's still a write-down
process: they don't directly create the queries for data transfer.</li>
<li>Worse, as with almost any system, Craft isn't entirely consistent in
using defined foreign keys for related data. So you have to 'know'
rather than tool-aided discover the needed tables. Sometimes those
are easy enough to guess, but...</li>
<li>There are some 'floating' tables it may be hard to guess about. For
example, the migrations table. I found that had extra records on on
one of two relatively related sites, and it was probably because of
the initial craft version point installed on each. But to be sure?</li>
<li>finally, but not small, consider consistency. Are you able to be
certain keys for data won't overwrite or otherwise supplant those on
a live database? Consider especially any generated keys, whether
simple unique names or autoincremented or autoincrement
variable-derived.</li>
</ul>

<p>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.</p>

<p>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.</p>

<p>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. </p>

<p>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</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

CC BY-SA 3.0
2014-06-14T22:01:15.800Z
Just to note I cut out of this commentary on schema sync vs. data sync, reasons for wanting to not use any kind of completely automated scripting tool for data merge, etc.. You'll realize these if you've liked to understand databases well enough to get involved with this less than full data transfer idea.
narration_sd
CC BY-SA 3.0
2014-06-15T02:48:34.427Z
Wow, thank you for the detailed response. I'm going to leave this open a bit longer before awarding the bounty, but this was very helpful.
Eric Ressler
CC BY-SA 3.0
2014-06-15T03:59:08.677Z
Great, Eric, and nice to hear. Just to underline further, I later downloaded the Enterprise demo of Navicat, and its Entity-Relation diagrammer could find and show the foreign key relationship for Categories and Category Groups; indeed it's there. That Toad didn't recognize it may be a lesson in getting what you pay for, but I'd rather think it's just a measure for the limits of what we can be more or less sure of in modern complex systems, hence how we approach working on them...
narration_sd
CC BY-SA 3.0
2017-01-25T13:54:22.610Z
`tl;dr` a bounty highly deserved.
Penguin9
CC BY-SA 3.0
2014-06-14T21:48:45.847Z
2014-06-14T22:02:16.413Z
2014-06-14T22:02:16.413Z
narration_sd
<p>Note: This method is not for the faint of heart and might not be for everybody, but can be useful in some situations.</p>

<hr>

<p>One thing we do (at <a href="http://airtype.com" rel="nofollow noreferrer">Airtype Studio</a>, 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: </p>

<p><img src="https://i.sstatic.net/DvC0o.png" alt="aws architecture"></p>

<p>This type of architecture is commonly referred to as an n-tier architecture. You can read more about it here: <a href="https://stackoverflow.com/a/312197/3053214">https://stackoverflow.com/a/312197/3053214</a>. 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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>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.</p>

<p>So really, a request for us goes something like this:</p>

<ol>
<li>The user requests <a href="http://website.com/about" rel="nofollow noreferrer">http://website.com/about</a></li>
<li>The web tier makes a GET request to the APP tier to get the about
page info. The environment of the web tier is sent over with the GET
request (either as a custom header or a query string parameter).</li>
<li>On the app tier, Craft reads the criteria pass from the web tier and
gets the content that matches the criteria (entry, environment,
etc).</li>
<li>The app tier returns a json response to the web tier.</li>
<li>The web tier handles presenting the view, or template (which is a
non-Craft template).</li>
</ol>

<p>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.</p>

<p>A couple of thoughts about all of this:</p>

<ol>
<li>Having a standardized, RESTful Craft API would make something
like this a lot easier. As described above, creating that app tier
API is up to the individuals creating the site. This can lead to
inconsistencies in the API if not done with care.</li>
<li>Having environment support natively on elements would be ideal,
rather than a custom field.</li>
<li>The method described above opens up a ton of avenues for Craft.
Since your Craft has an API now, the tech that drives the "web tier"
can change. It could be .NET app, Android app, or iPhone app. Just
think long and hard about exposing the Craft API via javascript. You
should probably have your javascript hit an endpoint on your web
tier that queries the app tier if you are really going for security.</li>
</ol>

CC BY-SA 3.0
2014-06-20T21:57:36.263Z
Thanks for the response Wes, this is helpful for larger projects.
Eric Ressler
CC BY-SA 3.0
2015-10-19T21:29:11.543Z
You can use Element API to create JSON API for your entries (and other element types) https://github.com/pixelandtonic/ElementAPI
Christian P
CC BY-SA 3.0
2014-06-20T14:02:10.683Z
2014-06-20T14:02:10.683Z
2017-05-23T12:38:53.957Z
Wes Rice
<p>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.</p>

<p>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.</p>

<p>Certainly a git-style DB would be awesome for this, at least in concept. Not sure what would it actually look like in practice.</p>

CC BY-SA 3.0
2014-06-12T01:09:27.347Z
2014-06-12T01:09:27.347Z
cballenar
<p>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.</p>

<p>The only CMS that has any kind of solution that I know of to this problem is Wordpress with this plugin <a href="https://deliciousbrains.com/wp-migrate-db-pro/">https://deliciousbrains.com/wp-migrate-db-pro/</a></p>

CC BY-SA 3.0
2014-06-13T17:29:01.370Z
We used this plugin extensively when we built WordPress sites. I wonder if anyone is able/willing to create something similar for Craft?
Eric Ressler
CC BY-SA 3.0
2014-06-17T07:38:28.467Z
While I haven't personally used it, this tool seems to be even more powerful: http://crowdfavorite.com/ramp/.

The way it saves individual edits as "migrations", rather than copying over an entire database, seems like it would be closer to a true "version controlled" database.
shanecavaliere
CC BY-SA 3.0
2014-06-13T07:00:58.673Z
2014-06-13T07:00:58.673Z
jnowland
<p>I found <a href="https://dmlogic.com/blog/database-migrations-for-wordpress-expressionengine-and-craft">a great blog post</a> where the author uses <a href="https://phinx.org/">Phinx</a> and MySQL Query Log to create database migrations. I summarized its content into 10 easy steps:</p>

<p><strong>Step 1</strong></p>

<p>Enable the MySQL Query Log on your dev environment by editing your mysql <code>my.cnf</code> file. </p>

<p>Set the <code>general_log</code> key to <code>1</code> and <code>general_log_file</code> to <code>/var/log/mysql/mysql.log</code></p>

<p><strong>Step 2</strong></p>

<p>Take a snapshot of your current db: </p>

<p><code>mysqldump -u dbuser -p dbname &gt; dumpfile.sql</code></p>

<p><strong>Step 3</strong></p>

<p>Empty your query log file:</p>

<p><code>&gt; /var/log/mysql/mysql.log</code></p>

<p><strong>Step 4</strong></p>

<p>Extract only the relevant queries from the log: </p>

<p><code>cat mysql.log | grep -E '[[:space:]]+[[:digit:]]+[[:space:]]Query' | grep -ivE 'Query([[:space:]])+(/*.**/)?(start|select|set|show|commit|rollback|use)' &gt; mymigration.sql</code></p>

<p>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”.</p>

<p><strong>Step 5</strong> <em>only required once per project</em></p>

<p>Assuming that you already have <a href="https://getcomposer.org/">Composer</a> installed on your dev environment, run the following commands to install Phinx:</p>

<p><code>composer require robmorgan/phinx</code> then <code>composer install --no-dev</code></p>

<p>Create a folder in your project directory called <code>migrations</code> with adequate permissions (should be writable).</p>

<p><strong>Step 6</strong></p>

<p>Make a migration using phinx:</p>

<p><code>vendor/bin/phinx create FooBarFeatureMigration</code></p>

<p>The last argument is whatever you want to name the migration. A new class file should show up in your <code>migrations</code> folder.</p>

<p><strong>Step 7</strong></p>

<p>Open the migration class file and change the <code>change()</code> function to <code>up()</code>.</p>

<p><strong>Step 8</strong></p>

<p>It's time to manipulate the contents of "mymigration.sql" into useful PHP. Wrap every query with <code>$this-&gt;query("");</code> and put the whole lot inside the <code>up()</code> function in the migration class file.</p>

<p><strong>Step 9</strong></p>

<p>Rollback the database to how it was before the change:</p>

<p><code>mysql -u dbuser -p dbname &lt; dumpfile.sql</code></p>

<p><strong>Step 10</strong></p>

<p>Run the migration:</p>

<p><code>vendor/bin/phinx migrate</code></p>

<p>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.</p>

<p><a href="https://dmlogic.com/blog/database-migrations-for-wordpress-expressionengine-and-craft">Check out the complete article here</a>.</p>

<p>I use this workflow with Git, Deploybot and a DigitalOcean droplet on a fairly large site (~10k entries). It works like a charm.</p>

CC BY-SA 3.0
2016-05-13T02:59:07.107Z
This is an interesting idea. A couple questions: How well does this work when you're developing a larger addition for a site? How much have you managed to automate the process beyond what's described? Have you run into any migration sync issues when working with other developers?
Michael
CC BY-SA 3.0
2016-05-24T17:46:28.820Z
@Michael I'm the only developer. But I commit every migration to git so It would be easy for anybody else to pull and run `vendor/bin/phinx migrate`. Step 8 is the longest part. I just don't have time right now to fully automate this process.
jlcharette
CC BY-SA 3.0
2016-03-15T17:31:50.963Z
2016-03-15T17:38:45.463Z
2016-03-15T17:38:45.463Z
jlcharette
<p>I'm currently implementing a workflow that's very efficient for us. </p>

<p>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.</p>

<p><strong>Craft CMS with a multi-environment config:</strong></p>

<ul>
<li>LOCAL (Local Machines) localhost:3000</li>
<li>DEVELOPMENT (On server for developers only) dev.mysite.com</li>
<li>STAGING (On server, for client review) staging.mysite.com</li>
<li>PRODUCTION (Live Website) mysite.com</li>
</ul>

<p><strong>Private Git repo using Beanstalk(BS):</strong></p>

<ul>
<li>Development (Deploys automatically on Push)</li>
<li>Staging (Deploys manually from BS)</li>
<li>Production (Deploys manually from BS)</li>
</ul>

<p><strong>Developers connect to a remote DB on Dev</strong></p>

<ul>
<li>During initial development, LOCAL and DEV environments share a database that is hosted on the server.</li>
<li>Local environments connect remotely to the DB. This is configured in the /config/db.php</li>
<li>This allows multiple developers to edit the DB and for content creators to simultaneously create content on the dev environment.</li>
</ul>

<p>After initial development, we will version the DB utilizing Backup Pro:
<a href="https://mithra62.com/projects/view/backup-pro" rel="noreferrer">https://mithra62.com/projects/view/backup-pro</a></p>

<p><strong>Here is where the DB versioning magic comes in:</strong></p>

<ol>
<li><p>If changes need to made to the PRODUCTION DB, initiate a planned content freeze.</p></li>
<li><p>Make a Backup of the DB</p></li>
<li><p>Sync your local environment with the Production DB. This can be done manually, or using Backup Pro after editing the config settings.</p></li>
<li><p>Edit the config file to connect to the DB locally.</p></li>
<li><p>Make all needed changes to the DB locally.</p></li>
<li><p>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.</p></li>
<li><p>Commit, and push to Dev.</p></li>
<li><p>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).</p></li>
</ol>

<p>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.</p>

<p>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. </p>

<p>Note:</p>

<ul>
<li><p>Always, make backups.</p></li>
<li><p>Another nice feature of Backup pro, is that you can set up cron jobs to make daily backups for you.</p></li>
</ul>

CC BY-SA 3.0
2016-06-01T16:19:43.457Z
2016-06-01T16:25:38.817Z
2016-06-01T16:25:38.817Z
SteveO
<p>This came up today, so I figured I'd post for completeness. This is how I handle it:</p>

<p><a href="https://nystudio107.com/blog/database-asset-syncing-between-environments-in-craft-cms" rel="nofollow noreferrer">Database &amp; Asset Syncing Between Environments in Craft CMS</a></p>

CC BY-SA 4.0
2018-05-30T20:22:23.600Z
With this method it seems you will need **SSH access** on all your servers ie: dev, staging, production, so this won't work for all types of hosting like some shared hosting out there.
Radmation
CC BY-SA 4.0
2018-05-31T03:38:16.977Z
I can't imagine working on an environment without ssh access... I'd recommend: [How Agencies & Freelancers Should Do Web Hosting](https://nystudio107.com/blog/web-hosting-for-agencies-freelancers) -- there's really no reason for shared hosting these days.
andrew.welch
CC BY-SA 4.0
2018-05-31T16:44:17.703Z
Hahah, apparently you get real lucky with your clients!! In the world I live in, sometimes clients have existing hosting that they are INSISTENT on keeping/using despite our words of better judgement. I am working on a project right now in Craft and I do not know where the final site will be hosted, I just know that we are not hosting the site and the client has existing hosting they want to keep, therefore I cannot guarantee using .sh files will work.
Radmation
CC BY-SA 3.0
2017-10-27T05:22:35.777Z
2017-10-27T05:22:35.777Z
andrew.welch
<p>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.</p>

<p>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.)</p>

<p>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.</p>

<p>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.</p>

CC BY-SA 3.0
2014-06-21T23:07:44.920Z
This appears to be the most straight forward and effective workflow currently for most setups.
Eric Ressler
CC BY-SA 3.0
2015-10-15T21:47:48.533Z
This has so far been completely safe (just not foolproof) on a complicated AWS setup where production Craft upgrades are carefully committed and coordinated. We've just made it very clear that nobody's ever allowed to initiate a Craft system update from within the production control panel.
Matt Stein
CC BY-SA 3.0
2017-09-06T21:26:52.880Z
"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." That would be fantastic.
Anders
CC BY-SA 3.0
2014-06-12T01:01:39.153Z
2014-06-18T20:01:46.577Z
2014-06-18T20:01:46.577Z
Jeremy Gimbel
<p>This issue becomes especially difficult when there are multiple environments, with multiple developers working on different features that affect the data structure. </p>

<p>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.</p>

CC BY-SA 3.0
2014-06-12T00:45:50.977Z
Too bad I can only upvote the question once.
Matt Stein
CC BY-SA 3.0
2014-06-12T02:07:48.943Z
Agreed Matt. The holy grail as mentioned by Eric is also something I would desperately love.
Peter Tell
CC BY-SA 3.0
2014-06-12T02:21:35.660Z
If it's true that P&T is working on a solution for this it would be amazing! It would be a major game changer IMO.
Natetronn
CC BY-SA 3.0
2014-06-12T03:40:12.927Z
I've seen Brandon and Brad mention they have something in mind but I don't think it will be anything we see until Craft 3.
Eric Ressler
CC BY-SA 3.0
2014-06-14T16:24:05.587Z
Historical moment! First bountied post!
nicael
CC BY-SA 3.0
2014-06-15T03:32:48.010Z
It's very exciting to see a bounty on this question... but I'm concerned that no answer will truly be sufficient. The question itself is very open-ended: _" ... it'd be great to hear how the community is handling this currently."_ Several people have already weighed in with viable solutions, and it's been mentioned that Pixel & Tonic may have a long term solution planned. I'm curious @EricRessler, what criteria would you require in an answer which deserves that bounty?
Lindsey D
CC BY-SA 3.0
2014-06-16T04:23:14.737Z
Hi Lindsey, thanks for the question. I've never participated in a SE before this, so if I'm using features incorrectly I apologize. The reason I offered the bounty is because I wanted to encourage more answers as clearly there is a lot if community interest as to how people are handling this. As to how I'm going to award the bounty, I'll probably select the best answer based on a combination of most effort / most informative.
Eric Ressler
CC BY-SA 3.0
2014-06-17T19:01:06.757Z
No problem, no need to apologize. :) I was just pointing out the subjectivity of the question (and answers), and was wondering if there was something specific you were looking for. Thanks for responding!
Lindsey D
<migration><deployment><environments><database>
What's the best practice for handling data migration and organization across development environments?
# What's the best practice for handling data migration and organization across development environments?

(Asked by: username_0 on 2014-06-12)

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.

**Tags:** migration, deployment, environments, database

> **Comments:**
> **username_1**: Too bad I can only upvote the question once.  
> **username_2**: Agreed Matt. The holy grail as mentioned by Eric is also something I would desperately love.  
> **username_3**: If it's true that P&T is working on a solution for this it would be amazing! It would be a major game changer IMO.  
> **username_0**: I've seen Brandon and Brad mention they have something in mind but I don't think it will be anything we see until Craft 3.  
> **username_4**: Historical moment! First bountied post!  
> **username_5**: It's very exciting to see a bounty on this question... but I'm concerned that no answer will truly be sufficient. The question itself is very open-ended: _" ... it'd be great to hear how the community is handling this currently."_ Several people have already weighed in with viable solutions, and it's been mentioned that Pixel & Tonic may have a long term solution planned. I'm curious @EricRessler, what criteria would you require in an answer which deserves that bounty?  
> **username_0**: Hi Lindsey, thanks for the question. I've never participated in a SE before this, so if I'm using features incorrectly I apologize. The reason I offered the bounty is because I wanted to encourage more answers as clearly there is a lot if community interest as to how people are handling this. As to how I'm going to award the bounty, I'll probably select the best answer based on a combination of most effort / most informative.  
> **username_5**: No problem, no need to apologize. :) I was just pointing out the subjectivity of the question (and answers), and was wondering if there was something specific you were looking for. Thanks for responding!  

---

## 8 answers

### Answer by username_18 on 2014-06-12. Score: 50 | Accepted answer

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.

> **Comments:**
> **username_0**: This appears to be the most straight forward and effective workflow currently for most setups.  
> **username_1**: This has so far been completely safe (just not foolproof) on a complicated AWS setup where production Craft upgrades are carefully committed and coordinated. We've just made it very clear that nobody's ever allowed to initiate a Craft system update from within the production control panel.  
> **username_19**: "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." That would be fantastic.  

---

### Answer by username_6 on 2014-06-14. Score: 19

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:

* you'd have to discover what tables are related, and copy over the
  related records also for the data set you're interested in
  transferring.
* Reverse engineering screens available in the more expensive versions
  of paid tools could help with this, but it's still a write-down
  process: they don't directly create the queries for data transfer.
* Worse, as with almost any system, Craft isn't entirely consistent in
  using defined foreign keys for related data. So you have to 'know'
  rather than tool-aided discover the needed tables. Sometimes those
  are easy enough to guess, but...
* There are some 'floating' tables it may be hard to guess about. For
  example, the migrations table. I found that had extra records on on
  one of two relatively related sites, and it was probably because of
  the initial craft version point installed on each. But to be sure?
* finally, but not small, consider consistency. Are you able to be
  certain keys for data won't overwrite or otherwise supplant those on
  a live database? Consider especially any generated keys, whether
  simple unique names or autoincremented or autoincrement
  variable-derived.

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.

> **Comments:**
> **username_6**: Just to note I cut out of this commentary on schema sync vs. data sync, reasons for wanting to not use any kind of completely automated scripting tool for data merge, etc.. You'll realize these if you've liked to understand databases well enough to get involved with this less than full data transfer idea.  
> **username_0**: Wow, thank you for the detailed response. I'm going to leave this open a bit longer before awarding the bounty, but this was very helpful.  
> **username_6**: Great, Eric, and nice to hear. Just to underline further, I later downloaded the Enterprise demo of Navicat, and its Entity-Relation diagrammer could find and show the foreign key relationship for Categories and Category Groups; indeed it's there. That Toad didn't recognize it may be a lesson in getting what you pay for, but I'd rather think it's just a measure for the limits of what we can be more or less sure of in modern complex systems, hence how we approach working on them...  
> **username_7**: `tl;dr` a bounty highly deserved.  

---

### Answer by username_8 on 2014-06-20. Score: 14

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](http://airtype.com), 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:

![aws architecture](https://i.sstatic.net/DvC0o.png)

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:

1. The user requests <http://website.com/about>
2. The web tier makes a GET request to the APP tier to get the about
   page info. The environment of the web tier is sent over with the GET
   request (either as a custom header or a query string parameter).
3. On the app tier, Craft reads the criteria pass from the web tier and
   gets the content that matches the criteria (entry, environment,
   etc).
4. The app tier returns a json response to the web tier.
5. The web tier handles presenting the view, or template (which is a
   non-Craft template).

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:

1. Having a standardized, RESTful Craft API would make something
   like this a lot easier. As described above, creating that app tier
   API is up to the individuals creating the site. This can lead to
   inconsistencies in the API if not done with care.
2. Having environment support natively on elements would be ideal,
   rather than a custom field.
3. The method described above opens up a ton of avenues for Craft.
   Since your Craft has an API now, the tech that drives the "web tier"
   can change. It could be .NET app, Android app, or iPhone app. Just
   think long and hard about exposing the Craft API via javascript. You
   should probably have your javascript hit an endpoint on your web
   tier that queries the app tier if you are really going for security.

> **Comments:**
> **username_0**: Thanks for the response Wes, this is helpful for larger projects.  
> **username_9**: You can use Element API to create JSON API for your entries (and other element types) https://github.com/pixelandtonic/ElementAPI  

---

### Answer by username_10 on 2014-06-12. Score: 11

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.


---

### Answer by username_11 on 2014-06-13. Score: 10

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/>

> **Comments:**
> **username_0**: We used this plugin extensively when we built WordPress sites. I wonder if anyone is able/willing to create something similar for Craft?  
> **username_12**: While I haven't personally used it, this tool seems to be even more powerful: http://crowdfavorite.com/ramp/.  The way it saves individual edits as "migrations", rather than copying over an entire database, seems like it would be closer to a true "version controlled" database.  

---

### Answer by username_13 on 2016-03-15. Score: 10

I found [a great blog post](https://dmlogic.com/blog/database-migrations-for-wordpress-expressionengine-and-craft) where the author uses [Phinx](https://phinx.org/) 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](https://getcomposer.org/) 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](https://dmlogic.com/blog/database-migrations-for-wordpress-expressionengine-and-craft).

I use this workflow with Git, Deploybot and a DigitalOcean droplet on a fairly large site (~10k entries). It works like a charm.

> **Comments:**
> **username_14**: This is an interesting idea. A couple questions: How well does this work when you're developing a larger addition for a site? How much have you managed to automate the process beyond what's described? Have you run into any migration sync issues when working with other developers?  
> **username_13**: @username_14 I'm the only developer. But I commit every migration to git so It would be easy for anybody else to pull and run `vendor/bin/phinx migrate`. Step 8 is the longest part. I just don't have time right now to fully automate this process.  

---

### Answer by username_15 on 2016-06-01. Score: 6

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:**

* LOCAL (Local Machines) localhost:3000
* DEVELOPMENT (On server for developers only) dev.mysite.com
* STAGING (On server, for client review) staging.mysite.com
* PRODUCTION (Live Website) mysite.com

**Private Git repo using Beanstalk(BS):**

* Development (Deploys automatically on Push)
* Staging (Deploys manually from BS)
* Production (Deploys manually from BS)

**Developers connect to a remote DB on Dev**

* During initial development, LOCAL and DEV environments share a database that is hosted on the server.
* Local environments connect remotely to the DB. This is configured in the /config/db.php
* This allows multiple developers to edit the DB and for content creators to simultaneously create content on the dev environment.

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:**

1. If changes need to made to the PRODUCTION DB, initiate a planned content freeze.
2. Make a Backup of the DB
3. Sync your local environment with the Production DB. This can be done manually, or using Backup Pro after editing the config settings.
4. Edit the config file to connect to the DB locally.
5. Make all needed changes to the DB locally.
6. 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.
7. Commit, and push to Dev.
8. 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.


---

### Answer by username_16 on 2017-10-27. Score: 3

This came up today, so I figured I'd post for completeness. This is how I handle it:

[Database & Asset Syncing Between Environments in Craft CMS](https://nystudio107.com/blog/database-asset-syncing-between-environments-in-craft-cms)

> **Comments:**
> **username_17**: With this method it seems you will need **SSH access** on all your servers ie: dev, staging, production, so this won't work for all types of hosting like some shared hosting out there.  
> **username_16**: I can't imagine working on an environment without ssh access... I'd recommend: [How Agencies & Freelancers Should Do Web Hosting](https://nystudio107.com/blog/web-hosting-for-agencies-freelancers) -- there's really no reason for shared hosting these days.  
> **username_17**: Hahah, apparently you get real lucky with your clients!! In the world I live in, sometimes clients have existing hosting that they are INSISTENT on keeping/using despite our words of better judgement. I am working on a project right now in Craft and I do not know where the final site will be hosted, I just know that we are not hosting the site and the client has existing hosting they want to keep, therefore I cannot guarantee using .sh files will work.  

---

