How MongoDB is different from a SQL database

If you are familiar with MySQL or PostgreSQL, a SQL database stores rows in tables. You add and read those rows with SQL:

SELECT model, year FROM cars WHERE year = 2010

INSERT INTO cars (model, year) VALUES ('Fiesta', 2010)

The table has a schema. Every row has the same columns, and you change that list with a migration such as ALTER TABLE cars ADD COLUMN color TEXT.

SQL is rather old. IBM developed it in the early 1970s. It started as SEQUEL, for System R, and it became an ANSI standard in 1986. It’s a battle-tested technology.

Under the NoSQL umbrella we put databases that do not use SQL as their query language. MongoDB is one of them.

MongoDB is a document database. The unit of data is a document, not a row. Documents live in a collection, the closest thing MongoDB has to a table.

The document holds the object

In a relational database a car and the work done on it want two tables. The services table points back at the car with a foreign key, and a query joins them.

In MongoDB the same car can be one document, services included:

{
  model: 'Fiesta',
  year: 2010,
  services: [
    { date: ISODate('2024-04-02'), shop: 'North Garage' },
    { date: ISODate('2025-11-18'), shop: 'North Garage' }
  ]
}

You store it from mongosh:

use garage

db.cars.insertOne({
  model: 'Fiesta',
  year: 2010,
  services: [
    { date: ISODate('2024-04-02'), shop: 'North Garage' }
  ]
})

And you read it with a query object:

db.cars.find({ year: 2010 })

In mongosh and in the Node.js driver that query is JavaScript. Other official drivers build the same query as objects in their own language. The operators and the aggregation pipeline are still a query language. They are passed as data, where SQL would be a string.

BSON, not JSON text

MongoDB stores the document as BSON, a binary format. Printed, it looks like JSON. BSON adds types JSON does not have: ObjectId, Date, 32-bit and 64-bit integers, Decimal128, and binary data.

A plain JavaScript number is stored as a BSON double. year: 2010 in the insert above is a double, not an integer. When you need an integer, pass Int32(2010). Money should be Decimal128 (Decimal128('19.99')), because a double cannot represent every decimal cent.

If you omit _id, the driver adds an ObjectId. _id is the primary key. It is unique in the collection, MongoDB puts it first, and you cannot change it after the insert.

Two size rules shape the model:

• One document cannot be bigger than 16 mebibytes (16,777,216 bytes).
• Field names are stored in every document, so long names add up across a large collection.

Flexible schema

Documents in one collection do not have to share fields. One car can have color and the next can leave it out. People call that schemaless. Flexible schema is the better name. The shape lives in your application until you also enforce it in the database.

Schema validation does the enforcing, usually with a JSON Schema on the collection. After you add the rules, inserts and updates have to match. By default MongoDB rejects the invalid write. You can instead allow the write and log a warning, which is useful while you clean old documents. The rules don’t have to cover every field. Require model and year, and leave room for the rest.

A rule that says year is bsonType: 'int' rejects the year: 2010 from the insert above, because that value was stored as a double. Use Int32(2010) in the write, or validate it as a number.

In SQL, adding a column is a migration, and every row has the column afterwards (often as NULL until you fill it). In MongoDB, new documents can carry a new field while old documents don’t. Every read has to tolerate both until you backfill.

Joins and transactions

SQL expects you to split data and join it back. The planner is good at that when the keys are indexed.

MongoDB expects you to store together the data you read together, so the services come back in the same read as the car.

When two collections really are separate, the aggregation stage $lookup does a left outer join and puts the matches in an array. It works on sharded collections since MongoDB 5.1. An unindexed $lookup on a large collection is slow, and a page that runs several of them is doing relational work in the wrong place. If the queries you care about are joins, use a SQL database.

A write to one document is atomic. Updating the car and appending a service inside that document either both happen or neither does. Embedding is what removes a lot of transactions.

Multi-document transactions exist for a change that has to cover several documents or several collections. They run on a replica set or a sharded cluster. A standalone mongod does not support them. The driver opens a session, you pass that session into each operation, and the whole transaction commits or rolls back.

I would keep data that must change together in one document, and use a transaction when forcing it into one document would mix unrelated records.

Which one I would use

I would use a SQL database when the records point at each other and the questions are not fixed yet. Orders, accounts, invoices, and the reports someone asks for next month. Constraints and joins are the point.

I would use MongoDB when one read should return one object with its nested data, and I already know that access pattern. A product with its variants, or an event stored with the payload the app always loads beside it.

You design a schema either way. In SQL the database holds you to it. In MongoDB you hold yourself to it, and you add validation on the fields a bad write would corrupt.

Pitfalls

A collection where every document has a different shape makes every reader a special case. Pick one shape, and let a few fields be optional.

An array that grows without a limit will hit 16 mebibytes, and updates get expensive earlier, because MongoDB rewrites the document as it grows. A handful of services fits in the car document. A GPS point every second does not. That log belongs in its own collection.

find({ year: 2010 }) on a large collection with no index on year reads every document. Same failure as a SQL query with no index. Add the index for the fields you filter and sort on.

Want me to talk about your product? You can sponsor this site.