Node.JS with NoSQL or SQL?

Something I'm not quite understanding, I've been doing some research for databases for Node.JS and almost everyone is recommending NoSQL type databases like MongoDB or CouchDB to use with Node.

But reading further I see that NoSQL is not that great for relational data... But that's where I'm getting confused: Most business applications (or social networking type apps) have relational data.

For example, let's say I'm building an application in Node.JS for a school that has students, teachers, classes. You can see there's alot of relationships in there. Would you still recommend using NoSQL?

MongoDB is often paired with Node.js because of their shared asynchronous nature, and because of the easy use of javascript JSON objects with the JSON based mongoDB document structure. That said, mongoDB has its shortcomings, particularly when complex reporting must be done. One of the better explanations I have found is that replicating the easy data access in mongo would require a join in mysql, however if you were to want to join the data a different way it would be rather straightforward in SQL, but much more complex in Mongo.

Take a look at mongoose for node. It allows you to define models for an express application. You can use .populate() to effectively join on what would be a foreign key on an RDBMS system.

http://mongoosejs.com/docs/populate.html

var mongoose = require('mongoose')
  , Schema = mongoose.Schema

var PersonSchema = new Schema({
  name    : String,
  age     : Number,
  stories : [{ type: Schema.Types.ObjectId, ref: 'Story' }]
});

var StorySchema = new Schema({
  _creator : { type: Schema.Types.ObjectId, ref: 'Person' },
  title    : String,
  fans     : [{ type: Schema.Types.ObjectId, ref: 'Person' }]
});

var Story  = mongoose.model('Story', StorySchema);
var Person = mongoose.model('Person', PersonSchema);

Saving refs

Saving refs to other documents works the same way you normally save objectids, just assign an ObjectId:

var aaron = new Person({ name: 'Aaron', age: 100 });

aaron.save(function (err) {
  if (err) return handleError(err);

  var story1 = new Story({
    title: "Once upon a timex.",
    _creator: aaron._id    // assign an ObjectId
  });

  story1.save(function (err) {
    if (err) return handleError(err);
    // thats it!
  });
})

Population

So far we haven't done anything special. We've merely created a Person and a Story. Now let's take a look at populating our story's _creator:

Story
.findOne({ title: /timex/ })
.populate('_creator')
.exec(function (err, story) {
  if (err) return handleError(err);
  console.log('The creator is %s', story._creator.name); // prints "The creator is Aaron"
})