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?
When SQL writes data, it does table-level locking; i.e. the whole table is inaccessible to any other write until the current write is complete. This is in contrast to e.g. Postgres, which does row-level locking; i.e. only the row currently being written is locked.
Still, SQLite is likely to be satisfactory for all but very high-load sites.
Some tips to make this work:
- use the latest version
- use write-ahead logging
- use synchronous execution model
- favor insert (and delete) over update
- favor many small, simple queries over larger, all-serving queries
- open and close connections quickly
- employ app level validation
- define a typed schema