SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with
fopen().
Situations Where SQLite Works Well
- Embedded devices and the IoT: Because it requires no administration, SQLite is perfect for devices without expert human support: cellphones, set-top boxes, game consoles, drones, medical devices, and robots. It thrives at the edge of the network.
- Application file format: SQLite is often used as the on-disk file format for desktop applications. The traditional File/Open operation calls
sqlite3_open(). This approach gives improved performance, reduced complexity, and improved reliability. - Websites: SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). Any site that gets fewer than 100,000 hits/day should work fine with SQLite. The SQLite website itself runs on SQLite.
- Data analysis: Use the command-line shell or simple scripts (Python, R, etc.) to import CSV data and analyze large datasets. The resulting database is a single, portable file.
- Cache for enterprise data: Many applications use SQLite as a local cache of relevant content from an enterprise RDBMS. This reduces latency, network load, and allows the app to work offline.
- Server-side database: For application-specific servers, SQLite can be faster than a client/server RDBMS. The server serializes requests, and separate database files can be used for different domains (sharding).
- Data transfer format: Because an SQLite database is a single, cross-platform file, it is an excellent container for transferring content between systems.
- File archive / data container: The SQLite Archive format can be a substitute for ZIP or Tarballs, offering incremental updates and richer metadata.
Situations Where A Client/Server RDBMS May Work Better
- Client/Server Applications: If many clients need to access a central database over a network, a client/server engine is a better choice. SQLite works over network filesystems, but latency and buggy file-locking implementations can cause issues.
- High-volume Websites: For write-intensive websites or sites requiring multiple servers, a traditional client/server database is recommended.
- Very large datasets: While SQLite supports databases up to 281 terabytes, filesystems may impose smaller limits. For datasets in the terabyte range, a client/server engine that spreads content across multiple files and volumes is a better fit.
- High Concurrency: SQLite allows only one writer at any instant in time. While write transactions are very fast, applications that require high write concurrency may need a different solution.
Checklist For Choosing The Right Database Engine
- Is the data separated from the application by a network? → Choose client/server.
- Many concurrent writers? → Choose client/server.
- Data approaching terabyte-scale? → Choose client/server.
- Otherwise? → Choose SQLite!
For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. It is fast, reliable, and requires no configuration or maintenance. It keeps things simple. SQLite "just works".