“Emails must be unique” sounds like a validation rule you could write in a few lines of code: look the email up, and if it isn’t there, insert. That code passes every test you write and fails in production the first time two sign-up requests arrive within a few milliseconds of each other. A unique index moves the rule into the database, where it holds no matter how many writers race.
Examples are mongosh for MongoDB 7 and 8 and are illustrative; no live server was available while writing.
The race in find-then-insert
// both requests run this at nearly the same moment
if (!db.users.findOne({ email: "ana@example.com" })) {
db.users.insertOne({ email: "ana@example.com", name: "Ana" })
}Request A checks: no user. Request B checks: no user. A inserts. B inserts. You now have two accounts with the same email, two password hashes, and a support ticket. The check and the insert are separate operations, and nothing stops another write from landing between them. Adding a transaction doesn’t reliably fix it either, because the “find” of a document that doesn’t exist yet doesn’t create a conflict for the other insert.
Let the index enforce it
db.users.createIndex({ email: 1 }, { unique: true })
db.users.insertOne({ email: "ana@example.com", name: "Ana" })
db.users.insertOne({ email: "ana@example.com", name: "Ana again" })MongoServerError: E11000 duplicate key error collection: app.users index: email_1 dup key: { email: "ana@example.com" }The second insert fails atomically, no matter how close in time the two were. Your code can now drop the pre-check (or keep it only for a friendlier early message) and handle the error:
try {
await users.insertOne({ email, name })
} catch (err) {
if (err.code === 11000) {
return res.status(409).json({ error: "An account with this email already exists." })
}
throw err
}Check err.code === 11000 (and, if the collection has several unique indexes, err.keyPattern to see which one fired). Don’t parse the message text.
Creating the index on an existing collection that already has duplicates fails with the same E11000 error, listing one offending key. Find all the duplicates first:
db.users.aggregate([
{ $group: { _id: "$email", n: { $sum: 1 }, ids: { $push: "$_id" } } },
{ $match: { n: { $gt: 1 } } }
])Then decide how to merge or rename them. That’s a business decision, not a script you run blindly.
Missing fields count as null
Suppose phone numbers are optional but must be unique when present:
db.users.createIndex({ phone: 1 }, { unique: true })
db.users.insertOne({ email: "ana@example.com" }) // ok
db.users.insertOne({ email: "ben@example.com" }) // E11000 ... dup key: { phone: null }A unique index stores a missing field as null, so the second user without a phone collides with the first. This surprises nearly everyone the first time. The fix is a partial index that only includes documents that actually have a phone:
db.users.createIndex(
{ phone: 1 },
{ unique: true, partialFilterExpression: { phone: { $type: "string" } } }
)Using $type: "string" rather than $exists: true also excludes documents where someone stored phone: null explicitly. A sparse unique index (sparse: true) solves the missing case too, but it still indexes explicit nulls; partial indexes are the more precise and recommended option.
Case and formatting
Ana@Example.com and ana@example.com are different strings, so a plain unique index allows both. Two fixes:
Collation. Create the index with a case-insensitive collation:
db.users.createIndex(
{ email: 1 },
{ unique: true, collation: { locale: "en", strength: 2 } }
)Strength 2 compares base letters and accents but ignores case. The catch: queries must specify the same collation to use the index, for example db.users.find({ email: "ANA@example.com" }).collation({ locale: "en", strength: 2 }). A query without it won’t use this index for matching.
Normalised field. Store emailLower (lowercased and trimmed at write time) with a plain unique index, and query on that. It’s simple, it’s obvious to every developer reading the code, and it doesn’t depend on remembering a collation. Many teams prefer it for that reason.
Whatever you choose, decide normalisation rules deliberately. Should ana+news@gmail.com be the same as ana@gmail.com? Gmail says yes, the email standard says no. A unique index enforces exactly the value you give it, nothing more.
Compound and sharded uniqueness
A unique compound index makes the combination unique:
db.memberships.createIndex({ orgId: 1, userId: 1 }, { unique: true })A user can join many orgs, and an org has many users, but the same pair can’t appear twice. This is the right way to model “no duplicate membership”, and it also gives you the index for “is user X in org Y?”.
On a sharded collection, a unique index must be prefixed by the shard key (or be the shard key), because each shard only checks its own documents. Global uniqueness on a field outside the shard key needs another approach, such as a small unsharded collection that holds just the claimed values.
Upserts and bulk inserts
Unique indexes also make upserts safe. Two concurrent updateOne({ email }, { $setOnInsert: {...} }, { upsert: true }) calls can both find no document and both try to insert. With a unique index on email, one wins and the other gets E11000; retrying it once finds the new document and updates it instead. MongoDB retries some of these conflicts internally when the filter is an equality on the unique key, but without the index you can simply end up with two documents, so the index is what makes the pattern correct.
With insertMany, the ordered option decides what a duplicate does to the rest of the batch. Ordered (the default) stops at the first duplicate, leaving later documents unwritten. With ordered: false, MongoDB inserts everything it can and reports every duplicate in writeErrors, each with its index in the batch. For imports where some records may already exist, ordered: false plus inspecting the errors is the usual pattern.
Interview practice
A multi-tenant SaaS needs usernames to be unique within a tenant, case-insensitively, and the username is optional for service accounts. Write the index and explain each choice. What does the sign-up code do when the constraint fires?
Answer
The rule is “unique per tenant”, so the index is compound on { tenantId, username }. Case-insensitivity means either a collation or a normalised field; I’d store usernameLower alongside the display version, because every query path then works without remembering a collation. Optional usernames mean documents without the field must not collide on null, so the index needs a partial filter.
db.accounts.createIndex(
{ tenantId: 1, usernameLower: 1 },
{ unique: true, partialFilterExpression: { usernameLower: { $type: "string" } } }
)Queries for a username must include tenantId and usernameLower with a string value, which the login path does anyway, so the planner can use the partial index.
In sign-up code, I’d attempt the insert directly and catch error code 11000. If err.keyPattern includes usernameLower, return a 409 with “That username is taken in this workspace.” I wouldn’t do a pre-check for correctness, though I might keep a lookup for a live “username available?” hint in the form, knowing it’s only advisory. If the collection is later sharded on tenantId, this index stays valid because it’s prefixed by the shard key.