A NoSQL Conversation

This cropped up recently. It's part of a "replace Mongo with Relational DB" conversation.

I'm going to elide the conversation down to five key points. The three post-hoc nonsensical ideas, and the two real points.

What's (to me) very telling is that someone else published the five reasons in this order. As if they larded three on the front. Or included the two at the end out of guilt because they were avoiding the real issues.

Relational Queries are Desired. "the only way to find [the documents] would be to write a query that literally trolls through the entire database in order to find the most recent values". 

I beg to differ. "Only way" is a strong statement. Mongo has indexes. To suggest that they don't exist or don't work is misleading. The details of the use case involved searching by date. It's possible to contrive a database that does bad searches by date; the implication being that Mongo couldn't do date matching or something. 

To Enforce Constraints and Schema. "It is still possible for the application layer to ensure the constraint, but that relies on every single point in the application code enforcing it – a single error can lead to inconsistent data". 

This runs perilously close to the "what if some bonehead bypasses the API and hacks into the database directly?" question. Which is isomorphic to "what if all corporate governance disappeared tomorrow?" and "what if an evil genius hacks all our database drivers?"

Lack of Document-Oriented Access Patterns.  "If there are more complex access patterns (like reading certain fields from many records, or frequently updating single fields within a record) then a document-oriented database is not a good fit"

That's nonsense. Mongo has field-level updates. There was one example of a long-running transaction that appeared to be mis-designed. I suggested that an improved design might be less complex and expensive than rewriting the API's and moving the data.

  

Desire to Utilize [Relational DB]

More Support Available for [Relational DB]

Clearly, these last two are the real reasons. Everything above looks like post-hoc justification for the real issue. 

We're not sure we like Mongo. 

My point in the conversation was not to talk them out of making a switch. The last two reasons included the kind of compelling rationalization that can't be disputed.  The best I could do was to challenge the errors in the first three reasons so that everyone could be honest about the change. It's not technical. It's organizational.

SQL Hegemony and Document Databases

A surpassingly strange question is this: "How do I get the data out of MongoDB into a spreadsheet?"

The variation is "How can we load the MongoDB data into a relational database?"

I'm always perplexed by this question. It has a subtext that I find baffling. The subtext is this "all databases are relational, right?"

In order to ask the question, one has to be laboring under the assumption that the only difference between MongoDB and a relational database is the clever sticker on your laptop. Mongo folks have a little green Mango leaf. Postgres has a blue/gray elephant.

This assumption is remarkably hard to overcome.

THEM: "How can we move this mongo data into a spreadsheet?"
ME: "What?"
THEM: "You know. Get a bulk CSV extract."
ME: "Of complex, nested documents?"
THEM: "Nested documents?"
ME: "Mongo database documents include arrays and -- well -- subdocuments. They're not in first normal form. They don't fit the spreadsheet data model."
THEM: "Whatever. Every database has a bulk unload into CSV. How do you do that in Mongo?"
ME: "You can't represent a mongo document in rows and columns."
THEM: (Thumping desk for emphasis.) "Relational Theory is explicit. ALL DATA CAN BE REDUCED TO ROWS AND COLUMNS!"
ME: "Right. Through a process of normalization. The Mongo data you're looking at isn't normalized. You'd have to normalize it into a relational table model. Then you could write a customized extract focused on that relational model."
THEM: "That's absurd."

At this point, all we can do is give them the minimal pymongo MongoClient code block. Hands-on queries seem to be the only way to make progress.

from pymongo import MongoClient
from pprint import pprint
with MongoClient("mongodb://somehost:27017") as mongo:
    collection = mongo.database.collection
    for document in collection.find():
         pprint(document)

Explanations seem to wind up in a weird circular pattern where they keep repeating their relational assumptions. Not much seems to work: diagrams, hand-waving, links to tutorials are all implicitly rejected because they don't confirm SQL bias.

A few days later they call asking how they are supposed to work with a document that has complex nested fields inside it.

This could be the beginning of wisdom. Or it could be the beginning of a lengthy reiteration of SQL Hegemony talking points and desk thumping.

THEM: "The document has an array of values."
ME: "Correct."
THEM: "What's that mean?"
ME: "It means there are multiple occurrences of the child object within each parent object."
THEM: "I can see that. What does it mean?"
ME: (Rising inflection.) "The parent is associated with multiple instances of the child."
THEM: "Don't patronize me! Stop using mongo mumbo-jumbo. Just a simple explanation is all I want."
ME: "One Parent. Many Children."
THEM: "That's stupid. One-to-many absolutely requires a foreign key. The children don't even have keys. Mongo must have hidden keys somewhere. How can I see the keys on the children in this so-called 'array' structure? How can expose the underlying implementation?"

The best I can do is show them an approach to normalizing some of the data in their collection.

from pymongo import MongoClient
from pprint import pprint
with MongoClient("mongodb://your_host:27017") as mongo:
    collection = mongo.your_database.your_collection
    for document in collection.find():
         for child in parent['child_array']:
              print( document['parent_field'], child['child_field'] )

This leads to endless confusion when some documents lack a particular field. The Python document.get('field') is an elegant way to handle optional fields. I like to warn them that they should not rely on this. Sometimes document['field'] is appropriate because the field really is mandatory. If it's missing, there are serious problems. Of course, the simple get() method doesn't work for optional nested documents. For this, we need document.get('field', {}). And for optional arrays, we can use document.get('field', []).

Interestingly we sometimes have confusion over {} for document and [] for array. I chalk that up to folks who are too used to very wordy SQL and Java. I save the questions for my next book on Python.

At some point, the "optional" items may be more significant than this. Perhaps an if statement is required to handle business rules that are reflected as different document structures in a single collection.

This leads to yet more desk-thumping. It's accompanied with the laughable claim that a "real" database doesn't rely on if statements to distinguish variant subentities that are persisted in a single table. The presence of SQL ifnull() functions, case expressions, and application code with if statements apparently doesn't exist. Or -- when it is pointed out -- isn't the same thing as writing an if statement to handle variant document subentities in a Mongo database.

It appears to take about two weeks to successfully challenge entrenched relational assumptions. Even then, we have to go over some of the basics of optional fields and arrays more than once.

MongoDB and Schema Validation

One part of the MongoDB value proposition is being freed from the constraints of a database schema.

There's a "baby and bathwater" issue here. While a schema can become a low-value constraint, we have to be careful about throwing out the baby when we throw out the bathwater. A schema isn't inherently evil. A schema that's hard to modify can become more cost than benefit.

When working with document databases like MongoDB or CouchDB, we're freed from the constraints of a schema.

But.

Do we really want the kind of freedom that can devolve to anarchy?

Or.

Do we want some kind of constraint checking capability to provide some additional run-time assurance that the applications are using the database properly?

Read this http://realprogrammer.wordpress.com/tag/json-schema/ and this http://www.litixsoft.de/english/mms-json-schema/.

My thesis is that some schema validation may have some value.

My plan is this.

1. Define the essential collections for the various documents using ordinary document design practices.

2. For each document class, we'll have two closely associated collections:

• The primary collection, call it it "class" because it matches one of the application classes.
• An additional "class.schema" collection. This collection will contain JSON-schema documents. See http://json-schema.org for more information.
• For audit, and sequential key generation, we may have some additional associated collections.

Because JSON schema documents have a "$schema" field, we can replace the "$" with "\uFF04" the "FULLWIDTH DOLLAR SIGN" character when saving the JSON-schema document into a MongoDB database. We can do the inverse operation when finding the schema documents in the database.

3. Use a tool like https://github.com/Julian/jsonschema to validate the schema. The document-level validation could be embedded in the application for each transaction. However, it seems better trust the code and the unit testing of the code to enforce schema rules. We'd use this validation periodically to check the schema. Significant events should include a validation pass. For example, before and after any schema changes. This way we can be sure that things are continuing to go properly.

It would be strictly an additional layer of checking.

Cool stuff I saw at MADExpo

A random list of cool things.

1. HTML5.  Start now.  It's supported (more or less) by all browsers if you add appropriate shims.  Start with sites like http://www.html5rocks.com/en/ and continue reading.  It's largely arrived and there's no compelling reason to delay.
2. JavaScript.  I'm not a fan of the language.  However.  It's clearly here to stay. It's part of many important technologies (like CouchDB).  HTML5 shims (or shivs) are necessary.  Flex (and other browser plug-in languages) are effectively dead.  JavaScript is all that's left.
3. Redis.  Wow! Is that cool?  Rather that fart around trying to get outstanding performance out of a clunky old RDBMS, use a simple, high performance data store.
4. MongoDB.  Now that I've seen it, I have a vague notion of places where Mongo is better and places where Couch might be better.  In 80% of the application space, both are fine.  But there's a 20% where we might be able to split a hair and leverage the slightly different feature sets.

I don't build mobile apps.  But if I did, I think I'd start with Appcelerator Titanium before switching to a "native" development environment.  The idea that several standard features are readily accessible with a convenient development environment is pleasant.

I finally laid eyes on an Arduino.  Let me simply say that it looks like dangerous, dangerous fun.  I think I'll be hanging around at Radio Shack a lot.