There are a number of situations where being able to conveniently analyse the differences between two database installations is extremely handy.
For example, you have a problem with your production system, but all you know about it is that the problem lies somewhere in the database, but WHERE in the database? It worked fine yesterday, so if you compare the current database with yesterday’s backup, most likely you’ll find out what the problem is.
Additionally, it could be time to release an upgrade to the production system, but there are new configuration parameters required. The upgrade can be be performed with a calm confidence when you can easily compare against the staging server and clearly see which parameters need to be introduced, changed or removed.
Or worse – an accidental update has removed vital configuration from the system, and it’s now crashing. You knew you should have taken a back up, but there just wasn’t time, and besides it was just a trivial change – what could possibly go wrong? But now, all you can do is sit and stare at your screen in horror and gulp down the bitter taste of regret. Unless of course, you can compare the differences and quickly identify which settings have been lost, and carry out an informed repair before anyone realises your terrible blunder! (Of course, this NEVER HAPPENS on production systems, right? :-) )
More generally, being able to see how your data is changing is very powerful. Not only does it let you gain insight to your data, but it also provides confident control over the changes when you can see them explicitly and edit them directly.
In this post, I’m going to show you how we can achieve this using the 3T Data Compare & Sync tool, an excellent data comparison and sync GUI app for MongoDB from 3T Software Labs.
Performing a diff
First of all, the app can be downloaded from here. It’s available for Windows, Mac and Linux.
Start the app, click the ‘New comparison’ tool bar button, and then we can select source and target collections to compare. Our comparison at this stage looks like this:
Click ‘Select source connection’. If this is the first time you’ve used the app, you’ll be presented with the Connection Manager and can create a new connection there. If you’ve already created a connection, you’ll be able to simply select it.
Now click ‘Select target connection’ and similarly select or create the target connection.
The difference between the source and target connections is that it is the documents from the source connection that will be compared to documents from the target connection. The source and target can be the same if need be.
OK, now we have you source and target connections selected. In the image above, we’re comparing source connection ‘Secured DB 1’ against target connection ‘Secured DB 2’.
Choosing what to compare
Now, let’s select the specific collections we want to compare. To do this, simply drag the source database on top of the target database. Voilà! All collections with the same name in the source and target databases are automatically selected for comparison.
Of course, it’s not mandatory that collections need to have the same name. For example, if you need to compare ‘my_collection’ with ‘my_collection_backup_20140528’, simply drag the source ‘my_collection’ on top of the target backup collection and it will be added to the comparison.
If you want to remove a particular pair of collections from the selection, simply right click it, either in the connection trees at either side or in the comparison table at the bottom, and select ‘Remove from comparison’.
Running the analysis
OK, now that we’ve selected what we want to compare, hit ‘Run comparison’ in the tool bar. When the run is complete, we’re first presented with an overview screen.
In the image above, we can see the comparison took a rather nippy 171 milliseconds, and found five differences in total – three differing documents, one document that only appeared on the source side and one other document that appeared only on the target side.
The overview page also shows exactly what was compared. This is useful if we save the results to come back to them later, by which time the comparison configuration may have been updated. The overview page of the results provides a permanent record of what was actually compared.
Seeing the differences
OK, now it’s time for the main course! Click on the ‘Differences’ tab.
Differing documents are highlighted in yellow, source-only documents in green and target-only in red, though in nice pastel shades that are easy on the eye.
Let’s expand a document.
Here we can the same highlighting is used within a document too – yellow shows fields that are different, green shows fields that exist in the source document only and red shows target only fields. Fields that are identical are shown in the normal alternating blue and white lines (or whichever colours are normal for your particular OS).
Document level synchronisation
We realise our source-only document should indeed be on the target, so we right click ‘Copy Document to Target’. We are prompted with confirmation of the document we wish to copy as well as the source and target locations, to be sure we are performing the sync we intend.
Alright, now we realise the target-only document is old, and should no longer be present on the target. No problem, we simply right click and select ‘Delete Document’.
Field level synchronisation
OK, we’ve done some syncing at document level, now we sync individual fields. We see that the ‘job: lawyer’ field is missing in the source document and should be copied over. Simply right click and select ‘Copy Field to Source’ if you clicked on the target side or ‘Copy Field from Target’ if you clicked on the source side.
It comes to our attention that ‘Rue de Rivoli, 12a’ on the target side is incorrect, but instead should be ‘12, Rue de Rivoli’ as it is on the source side. Right click it, and as the field exists on both source and target we have ‘Copy Field to Source’ and ‘Copy Field from Target’ options.
Note that we’ve just performed a bi-directional sync on the same document, in that some changes were copied from the target document to the source and other changes from the source to the target. Pretty neat, eh?
Synchronisation via direct in-place editing
Alright, we’re done with the veg and potatoes, let’s centre in on the steak!
We notice that on the target side, ‘age’ is a Double when it should be an Integer. You could copy it across, but you’d prefer just to edit it in-place. Then go ahead – full editing in-place is possible. Right click it, select ‘Edit Value / Type’ and change the type to Integer.
It then catches our eye that ‘cherbourg’ on the source side should be capitalised. For such a small change you don’t even want to invoke the right click menu. You don’t need to, simply double click the value and edit it directly, the diff status is updated in real time.
Full editing power, directly in your sync view. Excellent!
It’s been a pleasure
That concludes our journey through how to diff MongoDB, and I hope it’s left you a little bit more powerful, MongoDB diff-wise, than when our journey first began. :-)