What's the best way to host a sqlite database on the network?

Question

linux

It is necessary to place the sqlite database on a resource that is connected over the network.
Now I use sshfs (the choice is random) and I encounter frequent database breakdowns, which so far have been cured.
Maybe some other protocol (smb, nfs something else) will bring more stability to my life? Only one host connects to a network resource.
PS: I know that my such scheme is not recommended, but in my case there is no choice.

Answer 1

>Maybe some other protocol (smb, nfs something else) will bring more stability to my life?
Will not bring

Answer 2

You understand that you don't actually connect to the database over the network, but download its file to yourself, and then upload it back? Naturally, if there are problems with the network, the file will deteriorate.
Either the database server or the web server (accessing that database locally) can save you.

Answer 3

No way. SQLite is basically not designed to work over the network.
The network SQL database must support one or another transaction isolation mechanism. Read about ACID

Answer 4

Based on the comments above. In your case, there is no way to decide exactly from the point of view of your Wishlist. Possible ways out of the situation:
- add (or hire a developer) software that works with the database on the client-server architecture.
- run the software that works with the database directly on the server where the database is located, and already cling to the server via vnc \ rdp
- solve with a crutch, a conditionally small script that connects to the server, pulls the database file to the local machine, starts the software, after closing the software, the script unloads the file db back to the server and if all is successful deletes the local copy of the db.
- smb\nfs won't solve the problem but should work a little better than sshfs.

Answer 5

Try SMB.
But the documentation explicitly warns that problems are possible when working over the network, and this cannot be completely avoided.
https://sqlite.org/lockingv3.html#how_to_corrupt

Answer 6

It is interesting that SMB over the Internet, according to the results of my test, gives performance 1.5 times lower than sshfs both in terms of speed and eps.
Not very important, but just unexpected