SQL vs MongoDB: A Mental Model Shift

If you already know SQL, MongoDB will look familiar in places and completely alien in others. The vocabulary maps over reasonably well – a database is still a database, and you still query, insert, update, and delete – but the underlying model is different enough that copying your relational instincts straight over will get you into trouble. This lesson builds the mental model you need: what a document really is, how relationships work without foreign keys, and where MongoDB’s flexibility helps you and where it can hurt you if you’re not careful.

Overview: How the models differ

A relational database stores data as rows in tables, where every row in a table has the exact same columns, defined up front by a schema (CREATE TABLE). To represent a one-to-many relationship – a customer with many orders – you split the data into two tables and connect them with a foreign key, then reassemble them at query time with a JOIN.

MongoDB stores data as documents in collections. A document is a BSON object (Binary JSON – JSON’s data model extended with extra types like ObjectId, native Date, Decimal128, and binary data) that can contain nested objects and arrays directly. Two documents in the same collection are not required to have the same fields at all. Instead of splitting a customer and their orders across two tables and joining them back together, you can often just embed the related data as a nested array inside one document, because MongoDB documents aren’t limited to flat key-value pairs the way rows are.

This is the single biggest mental shift: SQL normalizes data to avoid duplication and guarantee consistency; MongoDB often denormalizes (embeds) data to match how it’s actually read. Neither approach is “more correct” – they’re optimized for different things, and MongoDB gives you the choice to embed or reference on a case-by-case basis, which SQL’s rigid table structure doesn’t really allow.

Terminology map

SQL concept MongoDB equivalent
Database Database
Table Collection
Row Document
Column Field
Primary key _id field (auto-generated as an ObjectId if you don’t supply one)
Foreign key + JOIN Embedded sub-document/array, or a reference field joined with $lookup
CREATE TABLE schema No schema required by default; optional JSON Schema validation per collection
Index Index – same underlying idea (mostly B-tree structures)
Transaction Single-document writes are always atomic already; multi-document ACID transactions are available when needed

Syntax: the same operations, different shapes

Every CRUD operation you know from SQL has a direct mongosh equivalent. The filter/update arguments are plain JavaScript objects, and operators are string keys starting with $.

SQL mongosh
SELECT * FROM users WHERE age > 25; db.users.find({ age: { $gt: 25 } });
INSERT INTO users (...) VALUES (...); db.users.insertOne({ ... });
UPDATE users SET status='active' WHERE id=1; db.users.updateOne({ _id: 1 }, { $set: { status: 'active' } });
DELETE FROM users WHERE id=1; db.users.deleteOne({ _id: 1 });
  • Filter document – the first argument to find/updateOne/deleteOne, equivalent to a SQL WHERE clause.
  • Update document – the second argument to an update call; must use update operators like $set, $inc, or $push (a bare replacement object replaces the whole document).
  • Projection – an optional second argument to find selecting which fields to return, similar to listing columns after SELECT.

Examples

Example 1: Schema flexibility

In SQL, every row in users must have the same columns:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  age INT
);

INSERT INTO users (name, email, age) VALUES ('Ava Patel', 'ava@example.com', 29);

In MongoDB, two documents in the same collection can have different fields entirely:

db.users.insertOne({
  name: "Ava Patel",
  email: "ava@example.com",
  age: 29
});

db.users.insertOne({
  name: "Leo Kim",
  email: "leo@example.com",
  age: 34,
  social: { twitter: "@leok", github: "leok" }
});

Output:

{ acknowledged: true, insertedId: ObjectId('66b1f1a2c9d4e10012a34567') }
{ acknowledged: true, insertedId: ObjectId('66b1f1a2c9d4e10012a34568') }

Leo’s document has a nested social object that Ava’s doesn’t have, and no ALTER TABLE was needed. That flexibility is real, but it’s a double-edged sword – see Common Mistakes below for what happens when nobody keeps the shape consistent on purpose.

Example 2: Querying, and whether MongoDB uses an index

db.users.find({ age: { $gt: 25 } }).sort({ age: 1 }).limit(5);

Output:

[
  { _id: ObjectId('66b1f1a2c9d4e10012a34567'), name: 'Ava Patel', age: 29, ... },
  { _id: ObjectId('66b1f1a2c9d4e10012a34568'), name: 'Leo Kim', age: 34, ... }
]

This chains find, sort, and limit exactly like real JavaScript method calls, because mongosh is JavaScript. Now check how MongoDB actually executed it:

db.users.find({ age: { $gt: 25 } }).explain("executionStats");

Output:

{
  winningPlan: { stage: 'COLLSCAN' },
  executionStats: { totalDocsExamined: 2, totalKeysExamined: 0 }
}

COLLSCAN means MongoDB scanned every document in the collection – fine at 2 documents, disastrous at 2 million. Add an index on age and re-check:

db.users.createIndex({ age: 1 });
db.users.find({ age: { $gt: 25 } }).explain("executionStats");

Output:

{
  winningPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', indexName: 'age_1' } },
  executionStats: { totalDocsExamined: 2, totalKeysExamined: 2 }
}

Now the query planner used the age_1 index (IXSCAN) instead of scanning the whole collection. Get in the habit of running explain("executionStats") on any query against a large collection.

Example 3: Joins vs. embedding/referencing

In SQL, an order and its customer live in separate tables and are reassembled with a JOIN:

SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.id = 1001;

MongoDB gives you two options. Since order line items are always read together with the order and don’t grow without bound, embed them directly:

db.orders.insertOne({
  customer: { name: "Ava Patel", email: "ava@example.com" },
  items: [
    { sku: "SKU-100", qty: 2, price: 24.99 },
    { sku: "SKU-207", qty: 1, price: 79.99 }
  ],
  total: 129.97,
  status: "placed"
});

Output:

{ acknowledged: true, insertedId: ObjectId('66b1f234c9d4e10012a34600') }

But the customer record itself is shared across many orders, so instead of duplicating the customer’s full profile into every order, reference it by _id and join with $lookup when you actually need the combined view:

const customer = db.customers.findOne({ email: "ava@example.com" });

db.orders.insertOne({
  customerId: customer._id,
  items: [{ sku: "SKU-100", qty: 2, price: 24.99 }],
  total: 49.98,
  status: "placed"
});

db.orders.aggregate([
  { $match: { status: "placed" } },
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
    }
  },
  { $unwind: "$customer" }
]);

Output:

[
  {
    _id: ObjectId('66b1f234c9d4e10012a34601'),
    customerId: ObjectId('66b1f1a2c9d4e10012a34567'),
    items: [ { sku: 'SKU-100', qty: 2, price: 24.99 } ],
    total: 49.98,
    status: 'placed',
    customer: { _id: ObjectId('66b1f1a2c9d4e10012a34567'), name: 'Ava Patel', email: 'ava@example.com' }
  }
]

The rule of thumb: embed data that’s read together and bounded in size (an order’s own line items); reference data that’s large, shared across many parents, or grows without bound (a customer referenced by thousands of orders).

How it works step by step

For the query example, MongoDB parses the filter document, asks the query planner to evaluate candidate plans (scan the collection, or use one of the available indexes), runs a short trial of each candidate plan, and picks the winner – that’s the winningPlan you saw in explain(). With no index on age, the only candidate is a full collection scan; once age_1 exists, the planner can seek directly into the index’s sorted key range instead of reading every document.

For the $lookup aggregation, think of it as a pipeline of stages that documents flow through one at a time, each stage transforming the stream before passing it to the next: $match first filters orders down to just the placed ones (ideally using an index on status), $lookup then performs, for each remaining order, an equality lookup against customers and attaches the matches as an array field, and $unwind flattens that one-element array into a plain object. This is conceptually the same execution order as a SQL engine applying WHERE before JOINing and before the final projection – filtering early keeps the expensive stages working on less data.

Common Mistakes

Mistake 1: Comparing an ObjectId field to a plain string

_id and reference fields like customerId are stored as the BSON ObjectId type, not strings. A string pulled from a URL param will never match unless you convert it:

// Wrong - customerId is stored as ObjectId, this string never matches
db.orders.find({ customerId: "656f1c2e8b3f9a1234567890" });

Output:

[]

Fix it by wrapping the string with new ObjectId(...) before querying:

db.orders.find({ customerId: new ObjectId("656f1c2e8b3f9a1234567890") });

Output:

[ { _id: ObjectId('66b1f234c9d4e10012a34601'), customerId: ObjectId('656f1c2e8b3f9a1234567890'), total: 49.98, ... } ]

Mistake 2: Recreating a fully normalized SQL schema and joining everything

Coming straight from SQL, it’s tempting to split every relationship into its own collection the way you’d split tables, then chain $lookups to reassemble them on every read:

// Wrong - three separate joins on every single read, just to render one order
db.orders.aggregate([
  { $lookup: { from: "order_items", localField: "_id", foreignField: "order_id", as: "items" } },
  { $lookup: { from: "products", localField: "items.product_id", foreignField: "_id", as: "items.product" } },
  { $lookup: { from: "customers", localField: "customer_id", foreignField: "_id", as: "customer" } }
]);

Output: correct data, but three joins executed for every order on every read – this scales poorly and throws away the reason you chose a document database.

Since an order’s line items are small, bounded, and only ever read with the order, embed them, and reserve $lookup for the one relationship (the shared customer) that actually benefits from being referenced:

db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $unwind: "$customer" }
]);

Output:

[ { _id: ObjectId('66b1f234c9d4e10012a34601'), items: [ { sku: 'SKU-100', qty: 2, price: 24.99 } ], customer: { name: 'Ava Patel' } } ]

Best Practices

  • Design documents around how your application reads the data, not around normalized relational theory – ask “what do I fetch together on one screen?” first.
  • Embed data that’s read together and naturally bounded in size; reference (and $lookup) data that’s large, shared across many documents, or grows without limit.
  • Always create an index for fields you filter or sort on, and confirm it’s used with explain("executionStats") before shipping a query against a large collection.
  • Convert string ids from URLs or API payloads with new ObjectId(idString) before querying an ObjectId field.
  • If your application needs guaranteed field shapes, add JSON Schema validation on the collection – MongoDB won’t enforce columns for you the way SQL does.
  • Reach for a multi-document transaction only when multiple documents (possibly across collections) truly must succeed or fail together – a single document write is already atomic on its own.

Practice Exercises

  • Take a normalized SQL schema of customers, orders, and order_items tables and design a MongoDB document model for it. Decide, and justify, what you’d embed versus reference.
  • Insert a handful of documents into a products collection with prices, then write a query that finds products priced between two values, sorted by price. Run explain("executionStats") on it before and after adding an index, and note the stage name change.
  • Given an orders collection with a customerId reference field, write an aggregation pipeline that joins in the matching customer document and returns only orders over a given total. Explain why a plain find() can’t do this join on its own.

Summary

  • Tables become collections, rows become documents, and columns become fields – but documents in the same collection don’t need identical fields.
  • SQL normalizes to avoid duplication; MongoDB often denormalizes (embeds) to match read patterns, and lets you choose per relationship.
  • There’s no automatic JOIN – you embed related data directly, or reference it and combine it explicitly with $lookup in an aggregation pipeline.
  • Without an index, find() falls back to a full collection scan (COLLSCAN); always verify index usage with explain().
  • Schema flexibility is a feature for evolving applications, not a license to let field names and shapes drift inconsistently – use validation when structure matters.