Limitations

SQLite is excellent within its envelope. Beyond that, pain comes from concurrency, deployment surface, and scale — not missing SQL features for most apps.

| Limitation | Practical impact | |------------|------------------| | Single writer | Many concurrent writers queue; WAL helps readers only | | Network filesystems | Locking corruption risk — use local disk | | No built-in HA | Replication is app-layer (LiteFS, rqlite) or export | | Limited ops tooling | No roles, no server-side connection pooling | | Type affinity | Flexible typing can hide app bugs without strict mode |

					PRAGMA journal_mode = WAL;   -- readers don't block writer as much
PRAGMA synchronous = NORMAL; -- balance durability vs speed (know the risk)
PRAGMA busy_timeout = 5000;  -- wait on lock instead of instant SQLITE_BUSY
				

Respect `SQLITE_BUSY` with retries and short transactions. For multi-instance server workloads with heavy write contention, PostgreSQL or MySQL is the safer default.

On interviews: state the single-writer rule, NFS warning, and when you would refuse SQLite for a backend service.

Common pitfalls: multiple app servers writing one SQLite file; Docker volumes on network storage; treating type affinity as full dynamic typing without validation; no backup while app holds locks.

The trade-off is radical simplicity for embedded and edge workloads versus hard ceilings on write parallelism and centralized operations.

Checklist:

• Confirm single-writer or accept retry/backoff.
• Keep database files on local filesystems.
• Set WAL, busy_timeout, and backup strategy.
• Re-evaluate when write QPS or HA requirements grow.