On SQL, it’s simple as hell. Well, if you got enough experience :3

CREATE TABLE thing (
id int PRIMARY KEY,
stuff varchar(20)
);

INSERT INTO thing (id,stuff) VALUES (45, "soz") ON DUPLICATE KEY UPDATE stuff = VALUES(stuff);

It’s done.

MongoDB as you must have heard already, it is NOT a relational database. Is it better than traditional ones? I am not going to start a flame war here. However note that I’m a hardcore SQL and ACID-compliance fan.

MongooseJS is a sign that NoSQL will eventually be a reinvention of today's relational databases.

— Nayar (@NayarJoolfoo) October 11, 2015

MongooseJS is used to validate data before storing in MongoDB. MongoDB is kinda dumb on its own which is not necessarily a bad thing as program logic is supposed to validate data and MongoDB developers can concentrate on making fast storage and reading capabilities. Let’s have some hands on with Mongoose:

var mongoose = require('mongoose');
mongoose.connect('mongodb://localhost/mycars');

var Car = mongoose.model('Car',{
  make: String,
  color: String,
  engine: {
    fuel : String,
    capacity: Number	
  },
  link: { type: String, unique: true}
});

As you can see, we described the characteristics with which e.g. we describe cars. Note that it differs from MySQL in the sense that you the fields can be further expanded as compared to the strict 2 dimensional tables.

Let’s create JavaScript car objects which we will store in the database.

var nayar_car = new Car ({
  make: "Maruti Suzuki :(",
  color: "purple",
  engine : {
    fuel : "petrol",
    capacity: 1000
  },
  link : "http://example.com/car/1"
});

Easy right? Let’s save it in a Mongo database

nayar_car.save(function(err){
  console.log(err);
});

Voila! No need for any SQLs.

The above will not work in case I repeat the insert with the same link as Car.link is supposed to be unique. You’d get an error like this:

E11000 duplicate key error index: mycars.cars.$link_1 dup key: { : “http://example.com/car/1” }

To make it work in an Insert or Update fashion, we modify the save code like this:

nayar_car.save(function(err){
  if(err && err.code == 11000){
    nayar_car._id = undefined;
    Car.update({link: nayar_car.link},nayar_car,{},function(err1,no){});
  }
});

Note that i had to unset the Car._id. It’s very important.

I’m not sure that I’m liking MongoDB/NoSQL way of doing things. I simply love SQL too much.

Refs:
http://stackoverflow.com/questions/21638982/mongoose-detect-if-document-inserted-is-a-duplicate-and-if-so-return-the-exist
https://jira.mongodb.org/browse/SERVER-340
http://docs.mongodb.org/manual/reference/method/db.collection.update/#upsert-parameter
http://stackoverflow.com/questions/21728879/mongodb-on-duplicate-key-update-behaviour
https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html