MongoError: E11000 duplicate key error collection ??

#database #mongodb #experiencelearning #wfh

TL;DR

I've encountered the issue sometime in my developer path. So I decided to write down my experience and to firstly note to myself and secondly to help other developers/engineers who are a newbie in this topic.

If you only need the fix, you can skip to the last part of this article!

One of the most advantages of using NoSQL databases is the flexible feature that allows us to update the number of
fields and their data types any time we want.

I'm using Nodejs and Mongoose driver to connect to MongoDB. And in the very beginning phase of a project development
life and this help us to able to update collections and their fields.

However, I encountered an error.

MongoError: E11000 duplicate key error collection: companies index: code_1 dup key: { code: null }

What happened here?

To understand, I would go further a bit with Indexing and unique keys in Databases.

Database Indexes

Wikipedia defines:

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without
having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more
columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

If you want to query a field or set of fields without iterating all the entries in a table/collection, you can create indexes for these fields.

To understand more how indexes are created & organized to support efficient queries, you can check atthis

However, the cost for indexes is not cheap, especially in the world of big data nowadays. Because to make queries on indexed fields efficient, these indexes need to be stored in a fast query memory (for example RAM). So, be careful when you want to add an index for a field, some factors should be put on the table to have good enough decisions: are data queried frequently? the user behaviors? regions that data are stored? etc

And last but not least, an indexed field may be a non-unique value for each entry in the column. For example, you can have an index on the field "Region" where we can have multiple users in the same region.

Unique keys

In the real-life, there are use cases that we want to limit the appearance of one or a set of values of factors. For example, you want there is only 1 email that is used to register per user, no more.

So, unique keys help you to achieve this constrain by defining the rule in the schema. For example in mongoose:

const userSchema = new Schema(
  {
    email: {
      type: String,
      index: true,
      unique: true,
    },
    name: String,
  }
);

You can see that the email attribute is unique. With this, you cannot add more than one user with the same email. If developers violate the rule, Mongodb will throw errors. This helps us preventing developer mistakes.

Again, you can check more at wikipedia

When Mongo DB schema become out of date with Mongoose schema

Come back to the beginning error, let's me show you the mongoose schema that was defined by code:

const companySchema = new Schema(
  {
    location: String;
    name: String,
  }
);

Look good! right?

But what makes the error?

MongoError: E11000 duplicate key error collection: companies index: code_1 dup key: { code: null }

Let's check a bit:

I used mongo client by querying with the command line to check the existing keys for the companies collection. And the result was:

> db.companies.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "project-name.companies"
        },
        {
                "v" : 2,
                "unique" : true,
                "key" : {
                        "code" : 1
                },
                "name" : "code_1",
                "ns" : "project-name.companies",
                "background" : true
        }
]

Yeah! There is an established index field for code and it is set to unique. And once an index is set, it is there until you remove it, and the rule unique is still there also.

And the reason is the schema was modified due to the product business has changed. The previous schema was:

const companySchema = new Schema(
  {
    code: {
      type: String,
      index: true,
      unique: true,
    },
    location: String;
    name: String,
  }
);