Create an Auto-Incrementing Sequence Field¶

On this page

Synopsis
Considerations
Procedures

Synopsis¶

MongoDB reserves the _id field in the top level of all documents as a primary key. _id must be unique, and always has an index with a unique constraint. However, except for the unique constraint you can use any value for the _id field in your collections. This tutorial describes two methods for creating an incrementing sequence number for the _id field using the following:

Use Counters Collection
Optimistic Loop

Considerations¶

Generally in MongoDB, you would not use an auto-increment pattern for the _id field, or any field, because it does not scale for databases with large numbers of documents. Typically the default value ObjectId is more ideal for the _id.

Procedures¶

Use Counters Collection¶

Counter Collection Implementation¶

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."
   }
)

You can verify the results with find():

db.users.find()

The _id fields contain incrementing sequence values:

{
  _id : 1,
  name : "Sarah C."
}
{
  _id : 2,
  name : "Bob D."
}

`findAndModify` Behavior¶

When findAndModify() includes the upsert: true option and the query field(s) is not uniquely indexed, the method could insert a document multiple times in certain circumstances. For instance, if multiple clients each invoke the method with the same query condition and these methods complete the find phase before any of methods perform the modify phase, these methods could insert the same document.

In the counters collection example, the query field is the _id field, which always has a unique index. Consider that the findAndModify() includes the upsert: true option, as in the following modified example:

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

   return ret.seq;
}

If multiple clients were to invoke the getNextSequence() method with the same name parameter, then the methods would observe one of the following behaviors:

Exactly one findAndModify() would successfully insert a new document.
Zero or more findAndModify() methods would update the newly inserted document.
Zero or more findAndModify() methods would fail when they attempted to insert a duplicate.

If the method fails due to a unique index constraint violation, retry the method. Absent a delete of the document, the retry should not fail.

Optimistic Loop¶

In this pattern, an Optimistic Loop calculates the incremented _id value and attempts to insert a document with the calculated _id value. If the insert is successful, the loop ends. Otherwise, the loop will iterate through possible _id values until the insert is successful.

Create a function named insertDocument that performs the “insert if not present” loop. The function wraps the insert() method and takes a doc and a targetCollection arguments.

Changed in version 2.6: The db.collection.insert() method now returns a WriteResult object that contains the status of the operation. Previous versions required an extra db.getLastErrorObj() method call.
```
function insertDocument(doc, targetCollection) {

    while (1) {

        var cursor = targetCollection.find( {}, { _id: 1 } ).sort( { _id: -1 } ).limit(1);

        var seq = cursor.hasNext() ? cursor.next()._id + 1 : 1;

        doc._id = seq;

        var results = targetCollection.insert(doc);

        if( results.hasWriteError() ) {
            if( results.writeError.code == 11000 /* dup key */ )
                continue;
            else
                print( "unexpected error inserting data: " + tojson( results ) );
        }

        break;
    }
}
```
The while (1) loop performs the following actions:
- Queries the targetCollection for the document with the maximum _id value.
- Determines the next sequence value for _id by:
  - adding 1 to the returned _id value if the returned cursor points to a document.
  - otherwise: it sets the next sequence value to 1 if the returned cursor points to no document.
- For the doc to insert, set its _id field to the calculated sequence value seq.
- Insert the doc into the targetCollection.
- If the insert operation errors with duplicate key, repeat the loop. Otherwise, if the insert operation encounters some other error or if the operation succeeds, break out of the loop.

Use the insertDocument() function to perform an insert:

var myCollection = db.users2;

insertDocument(
   {
     name: "Grace H."
   },
   myCollection
);

insertDocument(
   {
     name: "Ted R."
   },
   myCollection
)