4 ms·
Ask HN: Using SQLite for server/client web apps?
Is anyone 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?
- blacksqr 5y agoSQLite is optimized for frequent reads, but not frequent writes. 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.
- ozzythecat 5y agoYou can’t do concurrent writes to SQLite, from what I understand. And as you mentioned, at the table level and not just row level. If you have multiple writes, presumably you’d have to move them to a queue, maybe with a dedicated writer thread. Ops post mentions serializing, which probably is saying this same thing. I’ve only used SQLite for some hacking/toy applications, so not sure if others have more insight. Would love to learn. I do see some advantages with SQLite. I imagine back up is super easy - just copy the whole file.
- manx 5y agoThis might be of interest: https://litestream.io/ https://litestream.io/
- croo 5y agoRelevant database check http://howfuckedismydatabase.com/sqlite/ http://howfuckedismydatabase.com/sqlite/
- pimbrouwers 5y agoMy best advice, try it! I have used it many times for server apps with great success, namely "forms-over-data" systems, some even with a large user base but low concurrency. 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