SQLite vs MongoDB: Documents, JSON, Joins and Transactions Compared

Ninad Pathak

October 7, 2026

Tables or documents? SQLite stores rows in tables and MongoDB stores JSON-like documents, so the same task looks different in each. In this article I’ll walk you through the two side by side, task by task, and when each one is the right pick.

TLDR

SQLite is a relational database in a single file, used as a library inside your program. MongoDB is a document database server that clients connect to.
Use SQLite when one application owns the data and you want SQL with joins and transactions, and no server to run.

Use MongoDB when many clients share data over a network and the data is naturally a nested document.
SQLite can store flexible data too: a JSON column with CHECK (json_valid(doc)), queried with ->> and indexed with an expression index.

What is SQLite?

SQLite is a relational database engine that runs inside your program and stores the whole database in one file. Its docs describe it this way: “SQLite does not compete with client/server databases. SQLite competes with fopen()” (SQLite: appropriate uses). You query it with SQL, and there’s no server to install or connect to.

What is MongoDB?

MongoDB is a document database that runs as a server. It “stores data records as BSON documents”, and those documents “are composed of field-and-value pairs” (MongoDB: documents). It also “uses a flexible schema model. By default, documents in a collection don’t need the same fields or data types” (MongoDB: schema validation).

SQLite vs MongoDB at a glance

The core differences fit in one table. Sources are linked in the sections below:

	SQLite	MongoDB
Runs as	A library in your process	A database server that clients connect to
Stores	Rows in tables, optionally with a JSON column	BSON documents in collections
Query language	SQL	find()/update methods and the aggregation pipeline
Joins	`JOIN`	`$lookup`, or embedded documents
Size limit	1 billion bytes per string or BLOB by default	16 MiB per document
Primary key	Any column you choose	`_id`, set automatically

MongoDB’s own mapping chart lines up the terms. A table corresponds to a collection, and a row to a “document or BSON document”. Table joins correspond to “$lookup, embedded documents” (MongoDB’s SQL to MongoDB mapping chart). On limits, MongoDB says “The maximum BSON document size is 16 mebibytes” (MongoDB: limits). SQLite’s default maximum string or BLOB length is 1 billion bytes, set at compile time (SQLite: limits).

Differences that matter

To show you how the two compare, I will use the people example from MongoDB’s mapping chart, with a JSON column for the flexible part. MongoDB was not run for this page. The official mongo:8 image refused to start on my machine’s Linux kernel, a known incompatibility that MongoDB tracks as SERVER-121912. So each MongoDB snippet below is labelled as taken from the MongoDB manual, and no MongoDB output is shown.

Flexible data: a JSON column vs documents

SQLite tables have fixed columns, but a JSON column gives you a flexible part. A CHECK (json_valid(doc)) constraint keeps it valid (SQLite: JSON functions):

sqlite> CREATE TABLE people (id INTEGER PRIMARY KEY, user_id TEXT NOT NULL UNIQUE, age INTEGER, status TEXT, doc TEXT CHECK (json_valid(doc)));
sqlite> INSERT INTO people (user_id, age, status, doc) VALUES ('abc123', 55, 'A', '{"name":{"first":"Ada"},"tags":["admin","beta"]}'), ('bcd001', 45, 'A', '{"name":{"first":"Bo"},"tags":[]}'), ('cde002', 22, 'D', '{"name":{"first":"Cy"}}');

sqlite> INSERT INTO people (user_id, age, status, doc) VALUES ('bad', 30, 'A', '{not json');
Error: stepping, CHECK constraint failed: json_valid(doc) (19)

In MongoDB, every record is a document, and the schema is flexible by default.

Filter with a comparison

sqlite> SELECT user_id, status FROM people WHERE age > 25;
user_id  status
-------  ------
abc123   A
bcd001   A

MongoDB equivalent from the MongoDB manual, not executed for this page (mapping chart):

db.people.find( { age: { $gt: 25 } } )

Query a nested field

SQLite reaches inside JSON with the ->> operator and a path:

sqlite> SELECT user_id, doc ->> '$.name.first' AS first_name FROM people WHERE doc ->> '$.name.first' = 'Ada';
user_id  first_name
-------  ----------
abc123   Ada

MongoDB equivalent from the MongoDB manual, not executed. MongoDB “uses the dot notation to access the elements of an array and to access the fields of an embedded document” (MongoDB: documents):

db.people.find( { "name.first": "Ada" } )

Index a field inside the document

Without an index, the nested query scans the whole table. An expression index on the same JSON path fixes that, and the query plan switches to the index:

sqlite> EXPLAIN QUERY PLAN SELECT user_id FROM people WHERE doc ->> '$.name.first' = 'Ada';
QUERY PLAN
`--SCAN people
sqlite> CREATE INDEX people_first_name ON people (doc ->> '$.name.first');
sqlite> EXPLAIN QUERY PLAN SELECT user_id FROM people WHERE doc ->> '$.name.first' = 'Ada';
QUERY PLAN
`--SEARCH people USING INDEX people_first_name (<expr>=?)

MongoDB equivalent from the MongoDB manual, not executed. “In order for a dot notation query to use an index, you must create an index on the specific embedded field you are querying, not the entire embedded object” (MongoDB: index an embedded field):

db.people.createIndex( { "name.first": 1 } )

Arrays

In SQLite, json_each() turns a JSON array into rows, one per element:

sqlite> SELECT p.user_id, t.value AS tag FROM people p, json_each(p.doc, '$.tags') t ORDER BY p.user_id, tag;
user_id  tag
-------  -----
abc123   admin
abc123   beta

In MongoDB, an array is an ordinary field value. The manual’s example document stores contribs as an array of strings (MongoDB: documents), and dot notation reaches its elements.

Add a field to one record

With a JSON column there’s no ALTER TABLE. json_set() adds the key to that row only:

sqlite> UPDATE people SET doc = json_set(doc, '$.join_date', '2026-10-06') WHERE user_id = 'bcd001'; SELECT user_id, doc FROM people WHERE user_id = 'bcd001';
user_id  doc
-------  ----------------------------------------------------------
bcd001   {"name":{"first":"Bo"},"tags":[],"join_date":"2026-10-06"}

MongoDB equivalent from the MongoDB manual, not executed. The mapping chart shows $set adding fields to existing documents with updateMany():

db.people.updateMany( { user_id: "bcd001" }, { $set: { join_date: new Date() } } )

Upsert

sqlite> INSERT INTO people (user_id, age, status) VALUES ('abc123', 56, 'A') ON CONFLICT(user_id) DO UPDATE SET age = excluded.age RETURNING user_id, age;
user_id  age
-------  ---
abc123   56

MongoDB equivalent from the MongoDB manual, not executed. With upsert: true, updateOne() “Creates a new document if no documents match the filter” or “Updates a single document that matches the filter”:

db.people.updateOne( { user_id: "abc123" }, { $set: { age: 56 } }, { upsert: true } )

Paging

sqlite> SELECT user_id FROM people ORDER BY user_id LIMIT 2 OFFSET 1;
user_id
-------
bcd001
cde002

MongoDB equivalent from the MongoDB manual, not executed. The mapping chart maps LIMIT 5 SKIP 10 to find().limit(5).skip(10):

db.people.find().sort( { user_id: 1 } ).limit(2).skip(1)

Joins: LEFT JOIN vs $lookup

sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id TEXT REFERENCES people(user_id), total REAL); INSERT INTO orders (user_id, total) VALUES ('abc123', 20.0), ('abc123', 5.5), ('cde002', 12.0);
sqlite> SELECT p.user_id, count(o.id) AS orders, coalesce(sum(o.total), 0) AS total FROM people p LEFT JOIN orders o ON o.user_id = p.user_id GROUP BY p.user_id ORDER BY p.user_id;
user_id  orders  total
-------  ------  -----
abc123   2       25.5
bcd001   0       0
cde002   1       12.0

MongoDB equivalent from the MongoDB manual, not executed. $lookup “Performs a left outer join to a collection in the same database”:

db.people.aggregate( [
  { $lookup: { from: "orders", localField: "user_id",
               foreignField: "user_id", as: "orders" } }
] )

MongoDB’s alternative is to embed the orders inside each person document, so no join is needed.

Transactions

SQLite “implements serializable transactions that are atomic, consistent, isolated, and durable” (SQLite: transactional). This transaction touches two tables:

sqlite> BEGIN; UPDATE people SET status = 'D' WHERE user_id = 'abc123'; INSERT INTO orders (user_id, total) VALUES ('abc123', -25.5); COMMIT; SELECT p.status, sum(o.total) AS balance FROM people p JOIN orders o ON o.user_id = p.user_id WHERE p.user_id = 'abc123';
status  balance
------  -------
D       0.0

For MongoDB, the manual says: “In MongoDB, an operation on a single document is atomic.” Because related data can live in one document, “multi-document transactions are not necessary for many practical use cases. For situations that require atomicity of operations on multiple documents, MongoDB supports transactions” (MongoDB: transactions).

When to use SQLite and when to use MongoDB

The choice comes down to who owns the data and what shape it has:

SQLite: an app or tool that owns its data, with no server to run or connection pool to manage.
SQLite with a JSON column: relational data with a few flexible attributes, as shown above.
MongoDB: many services and clients reading and writing over a network, with data that is naturally a nested document.

MongoDB: data whose shape varies a lot from record to record and is read as a whole document.

Conclusion

SQLite and MongoDB solve different problems. SQLite is a file your program opens, and MongoDB is a server your programs connect to. If your data fits one application and SQL, SQLite’s JSON support covers flexible fields and nested queries, and it can index them too. If many clients need a shared document store, MongoDB is built for that.