MongoDB: handling auto-incrementing model id's instead of Mongo's native ObjectID

Question

Due to a management decision, we are using userId for the users collection, postId for the posts collection, and topicId for the topics collection, instead of '_id' for each collection as the unique identifier.

This causes a few problems getting started - one of the problems I have encountered is with upserts -

Using Mongoose, we have a schema that restricts userId to be a unique value - but when doing an update on a user model, with upsert set to true, MongoDB appears to only look at the ObjectIds of a collection to see if the same one exists - it doesn't check to see if a model already exists with the same userId - therefore Mongo does an insert instead of an update.

let me illustrate this with some data:

let's say the user's collection has one document:

{
_id:'561b0fad638e99481ab6d84a'
userId:3,
name:'foo'
}

we then run:

User.update({userId:3},{"$set":{name:'bar'},{upsert:true},function(err,resp){

if(err){

   // "errMessage": "insertDocument :: caused by :: 11000 E11000 duplicate key error index: app42153482.users.$userId_1  dup key: { : 3 }",
}

});

one would think that MongoDB would find the existing document with userId:3 and udpate it, so there must be something I am doing wrong since it's giving me the duplicate key error?

Answer 1

Typically the default value ObjectId is more ideal for the _id. Here, in this situation you can either override the default _id or you can have your own field for id(like userId in your case).

Use a separate counters collection to track the last number sequence used. The _id field contains the sequence name and the seq field contains the last value of the sequence.

Insert into the counters collection, the initial value for the userid:

db.counters.insert(    {
      _id: "userid",
      seq: 0    } )

Create a getNextSequence function that accepts a name of the sequence. The function uses the findAndModify() method to atomically increment the seq value and return this new value:

function getNextSequence(name) {
   var ret = db.counters.findAndModify(
          {
            query: { _id: name },
            update: { $inc: { seq: 1 } },
            new: true
          }
   );

   return ret.seq;
}

Use this getNextSequence() function during insert().

db.users.insert(
   {
     _id: getNextSequence("userid"),
     name: "Sarah C."
   }
)

db.users.insert(
   {
     _id: getNextSequence("userid"),
     name: "Bob D."
   }
)

This way you can maintain as many sequences as you want in the same counter collection. For the upsert issue, check out the Optimistic Loop block in this link Create an auto-increment sequence field.

The second approach is to use a mongoose middleware like mongodb-autoincrement.

Hope it helps.