4 ms·

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

TedDoesntTalk 6y 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?
- trashburger 6y ago
  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.
- easytiger 6y ago
  It lives in your process. It has no network features. It's not a DBMS. People have built layers over it though
- rlpb 6y ago
  Dqlite exists - it's SQLite with some of these feature added. https://dqlite.io/ https://dqlite.io/
- mjw1007 6y ago
  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.
  - HelloNurse 6y ago
    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?
  - m_sahaf 6y ago
    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 https://www.sqlite.org/faq.html#q5
    - catalogia 6y ago
      Also WAL mode isn't going to work on network filesytems.
    - mjw1007 6y ago
      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?".
      - yellowapple 6y ago
        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.
        mjw1007 6y ago
        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.
- polyrand 6y ago
  You can use rqlite for that: https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite You can have a single over-the-network database or a cluster of replica dbs (through Raft consensus).
- allenu 6y ago
  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.
- sdinsn 6y ago
  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? :)

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?

trashburger 6y ago
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.
easytiger 6y ago
It lives in your process. It has no network features. It's not a DBMS. People have built layers over it though
rlpb 6y ago
Dqlite exists - it's SQLite with some of these feature added. https://dqlite.io/ https://dqlite.io/
mjw1007 6y ago
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.
- HelloNurse 6y ago
  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?
- m_sahaf 6y ago
  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 https://www.sqlite.org/faq.html#q5
  - catalogia 6y ago
    Also WAL mode isn't going to work on network filesytems.
  - mjw1007 6y ago
    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?".
    - yellowapple 6y ago
      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.
      - mjw1007 6y ago
        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.
polyrand 6y ago
You can use rqlite for that: https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite You can have a single over-the-network database or a cluster of replica dbs (through Raft consensus).
allenu 6y ago
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.
sdinsn 6y ago
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? :)