Ask HN: Using SQLite for server/client web apps?
SQLite was not designed for the server/client model. However, the simplicity and speed of SQLite makes it appealing for small-to-medium dynamic websites.
True client/server databases like PostgreSQL bring with them advanced capabilities but also complexity in installation and maintenance.
Below are some extracts are from the SQLite FAQ: https://sqlite.org/whentouse.html
---
Websites
...The SQLite website uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Server-side database
...Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.
---
So, can SQLite work with dynamic sites that require writing to the database for many users at the same time?
- Has anyone had success doing this? How did you achieve it?
- Conversely, did anyone ditch SQLite and adopt a different client/server database e.g. PostgreSQL, MySQL etc?