Comment by TedDoesntTalk
5 years ago
Agree, but it also side-steps some complex topics like sharding, replication, high-availability, etc. I’m not suggesting those topics should be addressed by SQLite; that simplicity is an asset.
Has anyone used SQLite remotely over a network?
It lives in your process. It has no network features. It's not a DBMS.
People have built layers over it though
I think the main reason SQLite doesn't have them is because it's not a goal for the project. SQLite would be monumentally more complex if it supported networking, sharding, etc. If you need those use cases then perhaps it's a good idea to switch to a more scalable DBMS. My favorite is PostgreSQL. If I'm building something which will have user-generated content then I always pick Postgres. Otherwise, SQLite.
I am working on a flash card app and specifically did not want to have to create a SaaS to support cross-device data sync, so have opted instead to create a journal of the data mutations on each device and having those (i.e. it's CRDT) uploaded to a simple file store (like Dropbox) for each device to share. Each device stores its own local data using SQLite, but does a merge on all the journaled commands to stay up to date.
So, basically I rolled my own sync solution that just happens to use SQLite. It's worth noting that I started this project by storing the data in my own format (JSON, at first) and quickly realized it was growing too large and was taking too long to serialize/deserialize. I'm very glad to have ended up using SQLite instead because it is super easy to use and has been reliable.
You can use rqlite for that:
https://github.com/rqlite/rqlite
You can have a single over-the-network database or a cluster of replica dbs (through Raft consensus).
When Subversion changed their working copy format to rely on an SQLite database, I had a number of problems using working copies on network file systems.
No doubt this was an indication that the network file system was incorrectly configured, but I think the fact that it worked in practice with the file-based format and didn't with the SQLite format is a strike against the idea of using SQLite for what the user sees as saving a file.
SQLite recommends against sharing it on NFS for multiple access because locking is broken on NFS for all kinds of files, not just SQLite.
> But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.
Source: https://www.sqlite.org/faq.html#q5
Fair enough, but that means if someone's following the advice in the article ("It deserves your consideration as the standard file format on your next application design") they need to consider "will our users want to use our files on network filesystems?".
2 replies →
Also WAL mode isn't going to work on network filesytems.
Working copies on network file systems are a horrible idea. You detected problems because you were using SQLite, but are you sure you didn't have silent corruption with older Subversion versions?
If you need sharding and replication, just use a different database. SQLite is not intended to solve those problems.
As for high-availability, isn't a single file on your disk the most available thing there is? :)
Dqlite exists - it's SQLite with some of these feature added. https://dqlite.io/