SQLite vs PostgreSQL vs MySQL

Three databases, three different jobs

The three databases are often discussed as if they're competitors. They're not — they answer different questions.

Property	SQLite	PostgreSQL	MySQL
Architecture	In-process library	Client/server	Client/server
Deployment	One file	Daemon + config	Daemon + config
Concurrent writers	One at a time (per file)	Many, MVCC	Many, MVCC
Concurrent readers	Unlimited (WAL mode)	Unlimited	Unlimited
Typing	Dynamic (or STRICT 3.37+)	Strict, rich types	Strict, fewer types
Replication	Add-on (Litestream/LiteFS/Turso)	Built-in streaming	Built-in (binlog)
JSON	JSON1 + JSONB (3.45+)	JSONB native	JSON native
Full-text search	FTS5 built-in	tsvector	FULLTEXT (InnoDB)
Auth model	File system	Roles + RLS	User accounts
Best fit	App's data layer	Multi-tenant systems	Web app DB tier

Principle: Choose by deployment shape, not by feature checklist. If your data must live where many machines can reach it, you need a server. If your data can live where the code runs, SQLite removes an entire tier of operational concern.

Modern SQLite has closed most of the feature gap with Postgres for app-level workloads — JSON, FTS, window functions, CTEs, generated columns, partial indexes, expression indexes, UPSERT, RETURNING. What it does not close is the multi-writer-across-machines gap, and it does not pretend to.

Code

Same SQL, three different engines·sql

-- All three databases happily run this exact statement
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name  TEXT NOT NULL
);

INSERT INTO users(email, name) VALUES ('alice@x.com', 'Alice');

SELECT id, email, name FROM users WHERE name = 'Alice';

-- The differences show up in the deployment story, not the SQL.

SQLite vs PostgreSQL vs MySQL

Three databases, three different jobs

Code

External links

Exercise

Progress

Comments 0