Using the command sqlite.open(PATH, [BUSY_TIMEOUT=500]), I need to access an SQLite database on another server.
How do I access the remote PATH to connect to SQLite?
You can’t use sqlite over network. Use scp or something else to copy over the database file from the other server to the kumo server periodically. I have a static file server that listening on my private network, serving the sqlite db file. A cron job on the kumo server periodically downloads the file from there.
You could try something like sshfs, and I’m sure a pile of caveats go with that.
OK, I am trying to implement something else and needed a way to share information from the application server with Kumo, and ended up doing some more research on using sqlite over network. From what I understand, using sshfs/nfs is not “safe” as they don’t support file locking, which means that you will very quickly end up with a corrupted database if you have multiple processes writing to the database concurrently.
That being said, if you’re planning for Kumo to only read from the database, you can mount the nfs/sshfs in read-only mode. But then - and I’m not 100% sure about this - because the application server that’s writing to it is most probably not doing it sequentially (e.g. multiple threads can write at the same time), and the filesystem doesn’t support file locking, you’d end up having to implement the safety in your own application code (using mutexes to make sure that only one thread can write at a time).
At this point I’m questioning whether it’s worth going through all these hoops to get sqlite to work - nfs/sshfs adds some network latency, and on the application side you’ll need to think carefully about what you’re doing, and probably face some performance penalty if you write to the db often (because having to sequentialize the writes given the db file lives on a fs without file locking). Might make more sense to use vault (and accessing the vault over network probably has the same order of latency as accessing sqlite over sshfs, but at least you don’t need to worry about concurrency), or just write simple APIs and have kumo call them in lua to get the data.
I think I’m going to go with writing the simple API and call it from the lua config files, as in my case the query that needs to be run is very simple and fast, and I’ll be calling the API over a private network, which in my benchmarks (not done in lua) means that I’ll have the response within 5 to 10ms.
@free-spirited-yorksh @wise-yaffle would love to hear your thoughts.
@original-baboon I’d look into the various projects that implement distributed SQLite.
I saw rqlite, but it’s essentially sqlite over http which the kumo bindings don’t support (and it’s just simpler to write my own api instead of adding a new service that I’d need to maintain).
Also saw LiteFS, but it seemed too complex for my usecase.
Some use cases are better for a relational DB, but those are on the backburner until someone needs one enough to sponsor it.
unless the data is really really large, I would suggest the simplest option of dumping the source data into eg: json files and pushing those to each kumo node. You can use memoize wrapped around the kumo json loading function to manage the lifecycle of that data, so that you’re not parsing json all the time.
The advantages of this are:
- Simplicity
- Resilience; no network deps directly in your configuration lookup path
- Easy to scale to any number of nodes using either push or pull based deployment. You can chef/ansible/etc the data onto the node if you like.
The disadvantages are:
- Higher latency for noticing changed config. Lower bound is whatever interval you are using to recreate and distribute the json files. vs. “instant” if you are querying your config service/datasource in real time on every lookup
- Either slightly higher memory cost (if the data is large and you are using memoize), or lookup latency (if the data is really large and you are not using memoize: you spend more time json parsing)
Unless your data is really large or you truly need very low latency to pick up changes, those disadvantages are unlikely to matter.