101

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.

  • 5
    Too bad I can only upvote the question once. – Matt Stein Jun 12 '14 at 0:45
  • 1
    Agreed Matt. The holy grail as mentioned by Eric is also something I would desperately love. – Peter Tell Jun 12 '14 at 2:07
  • 4
    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 Jun 12 '14 at 2:21
  • 1
    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 Jun 12 '14 at 3:40
  • 1
    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 Jun 15 '14 at 3:32
49

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.

  • This appears to be the most straight forward and effective workflow currently for most setups. – Eric Ressler Jun 21 '14 at 23:07
  • 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. – Matt Stein Oct 15 '15 at 21:47
  • "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 Sep 6 '17 at 21:26
17
+50

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.

  • 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 Jun 14 '14 at 22:01
  • 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 Jun 15 '14 at 2:48
  • 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 Jun 15 '14 at 3:59
  • tl;dr a bounty highly deserved. – RaisingAgent Jan 25 '17 at 13:54
11

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:

aws architecture

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.
9

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.

8

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/

  • 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 Jun 13 '14 at 17:29
  • 1
    While I haven't personally used it, this tool seems to be even more powerful: 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 Jun 17 '14 at 7:38
8

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.

  • 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 May 13 '16 at 2:59
  • 1
    @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. – JCharette May 24 '16 at 17:46
4

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.

2

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

  • 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 May 30 '18 at 20:22
  • I can't imagine working on an environment without ssh access... I'd recommend: How Agencies & Freelancers Should Do Web Hosting -- there's really no reason for shared hosting these days. – andrew.welch May 31 '18 at 3:38
  • 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 May 31 '18 at 16:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.