Database Instructions
Three databases, three very different ideas of what a database is. SQLite is a single file your program opens directly. PostgreSQL is a server that many programs talk to at once, in SQL. MongoDB is a server too, but it stores JSON-like documents instead of rows. Each guide below takes you from nothing installed to your own data stored and queried, on Linux, macOS, or Windows, and every command block has a copy button.
Pick a database, or do all three in order.
The order below goes from least to most setup. SQLite has no server, so you're writing SQL within a minute. PostgreSQL adds a server and a login system, called roles, that you need to understand before it will let you in. MongoDB adds a different data model on top of a server. The SQL you learn in the SQLite guide carries straight over to PostgreSQL.
SQLite3 Complete Guide
Start here if you're new to SQL. Install SQLite, then learn it level by level: beginner, intermediate, and advanced SQL, with a textbook explanation on every step and a quiz at the end.
PostgreSQL Complete Guide
Install PostgreSQL 18 on Windows, macOS, or Linux, then work through 19 lessons from beginner to advanced: psql, tables, joins, aggregates, indexes, transactions, roles, JSONB, and backups, with a quiz at the end.
MongoDB Complete Guide
Install MongoDB 8.3 on Windows, macOS, or Linux, then work through 19 mongosh lessons from beginner to advanced: documents, queries, arrays, aggregation, indexes, validation, transactions, access control, and backups, with a quiz at the end.
Three tools, three different jobs.
| SQLite | PostgreSQL | MongoDB | |
|---|---|---|---|
| Where the data lives | One file on disk | A server process that owns a data directory | A server process that owns a data directory |
| Shape of the data | Tables of rows | Tables of rows, plus JSON columns | Collections of JSON-like documents |
| Query language | SQL | SQL | JavaScript-style method calls |
| Shell | sqlite3 | psql | mongosh |
| Default port | none, no network | 5432 | 27017 |
| Good first use | A desktop app, a small website, a script's scratch data | A web app with many users writing at once | Records whose fields vary from one to the next |
How much each one can hold
All three can store far more than most projects will ever need. The limits that matter in practice are the size of a single record, the size of a single stored file, and how many programs can write at the same moment. These figures are from each project's official limits page.
| SQLite | PostgreSQL | MongoDB | |
|---|---|---|---|
| Files on disk | One file per database; a connection can attach up to 10 more (125 at most) | A data directory; each table is split into 1 GB segment files | A data directory; one file per collection and one per index |
| Largest database | About 281 TB | Unlimited | No hard limit; bounded by the filesystem, then by sharding across servers |
| Largest table or collection | Bounded by the database size | 32 TB per table | No hard limit |
| Largest single row or document | About 1 GB (the default maximum string or BLOB length) | 1 GB per field; large values are moved out of the row automatically | 16 MB per document |
| Storing files (images, PDFs) | As a BLOB, up to about 1 GB each | As bytea, up to 1 GB each | Up to 16 MB inside a document; bigger files go in GridFS, which splits them into chunks |
| Columns or fields | 2,000 columns per table (default) | 1,600 columns per table | No column limit; documents can nest 100 levels deep |
| Writers at the same moment | One at a time; readers carry on meanwhile | Many, with row-level locking | Many, with document-level locking |
| Connections | No server; each program opens the file itself | 100 by default (max_connections), raised with a setting or a connection pooler | Thousands; limited mainly by the operating system |
A single SQLite file handles many gigabytes comfortably. What pushes a project to PostgreSQL or MongoDB is usually concurrency, when many programs or users need to write at the same moment, or the need to reach the database over a network from several machines.
Pros and cons
- Nothing to install or run: the database is one file you can copy, email, or back up.
- Very fast for one program reading and writing on the same machine.
- Built into Python, PHP, Android, iOS, and every major browser.
- Ideal for learning SQL, prototypes, desktop and mobile apps, and small websites.
- Only one writer at a time, so busy multi-user apps queue up.
- No network access or user accounts; programs must be on the same machine as the file.
- Flexible typing accepts wrong-typed values unless the table is declared
STRICT. - Fewer built-in features for large-scale reporting and replication.
- Many users can read and write at once, safely.
- Strict types and constraints keep bad data out.
- Rich features: transactions, JSONB, full-text search, window functions, extensions such as PostGIS.
- Free and open source, with a very large community and hosting on every cloud.
- A server to install, configure, secure, and back up.
- Roles and login rules take some learning, as the guide's first steps show.
- Schema changes need planning on very large tables.
- Scaling writes beyond one server needs extra tools or services.
- Flexible documents: records can differ, and fields can be added without a migration.
- Arrays and nested objects map directly onto the objects in your code.
- Built-in replication, and sharding to spread data across many servers.
- A powerful aggregation pipeline for reports.
- No schema by default, so typos and inconsistent fields slip in unless you add validation.
- Joins with
$lookupare clumsier than SQL joins. - Transactions need a replica set, and each document is capped at 16 MB.
- The Community edition's license (SSPL) is not considered open source by the OSI, so some Linux distributions don't package it.
A file, a server that speaks SQL, and a server that stores documents. Choosing between them is choosing a shape for your data.