Hacker News Reader

Comment by juskrey | Hacker News Reader

Years pass, now decades, yet SQLite is still the most pleasant DB to work with.

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?

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.

It lives in your process. It has no network features. It's not a DBMS.

People have built layers over it though

Dqlite exists - it's SQLite with some of these feature added. https://dqlite.io/

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.

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?

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

Also WAL mode isn't going to work on network filesytems.

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?".

You could always have a lock file alongside the main to signal to other instances of the app that the file's in use. A bit janky, but workable.

That's no worse than what applications often already do when they use a simpler file format.

But you also have to worry about SQLite (reasonably) refusing to operate because it tries to make a locking call and gets an error response. I think there are options to turn that off, though.

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).

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.

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? :)

Reply on news.ycombinator.com