Hello,
how do you specify a location in some cloud service (say, Dropbox) so that your SQLite DB is available both from the iMac and the MacBook?
Thank you
Using databases on cloud services like Dropbox or iCloud is a really nice way to make the databases corrupt.
3 Likes
What is the distance between these two computers ?
(are-they in the same Room / House / Building ?)
maybe you can use a nas at your router for your own “cloud” solution?
it would be a central place without replicate the db.
if you access a cloud file it usually do
copy the file local, after change it copy (sync) it back to cloud via web api.
According to the documentation (that is if you read it):
Network Access
SQLite is not a server database and according the SQLite developers should not be placed on a shared network drive for access by multiple client apps as this can lead to database corruption
1 Like
But, you can use AirDrop (on both computers) to get the most recent copy, so you can work on one OR the other computer with the latest db (you are in charge of what is the latest copy to work with).
Normally, they are in the same house.
Sync may be needed when I’m away from home
NAS could be a solution but do I understand I have to manually copy the DB back and forth?
Is there any way or any place it can be done automatically?
if you have a NAS, then on most of them you can install postgresql on it.
it would be a REAL multiuser database then without risk of corruption…
it is possible at least on synology or qnap nas.
it is a network drive, it not good but for a small sqlite db why not.
my idea was a single origin of data.
the client dll would access it direct. no copies of file from and to.
at mac pc you can add this network drive after login automatically.
I know this won’t be a popular subject but… SQLite database corruption issues are way overblown. If you have a modern OS that has functioning file locking, which most do, I found it near impossible to corrupt an SQLite on a network drive even with simultaneous clients pounding away on the database. I have done extensive testing on this and no matter what I did I could not create a single corruption on a database due to multiple clients trying to access or write to the DB - numerous years of testing on Win10.
It really is very simple. When SQLite does a write to the DB it attempts to lock the file at the OS level until the operation is complete. If another client tries to write to the DB at the same time, you will get an error that the file is locked. It is YOUR job to handle that error - either retry, abort, etc. but make no mistake, you will get a file locked error when two processes try to write at the same time.
This is the same technique used for any type of file on a network (Excel, Word, or any other document), if one user opens it, the other user gets a read-only version and notified.
No modern OS I know of has broken file locking “primitives”. If they do, they should not be used for anything.
From the link above
# File locking problems
SQLite uses file locks on the database file, and on the write-ahead log or WAL file, to coordinate access between concurrent processes. Without coordination, two threads or processes might try to make incompatible changes to a database file at the same time, resulting in database corruption.
## 2.1. Filesystems with broken or missing lock implementations
*SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular.
If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result.*
This is a legitimate disclaimer for SQLite, but should not be construed that SQLite can’t be used in a multi-user environment.
I can see that everyday. a 30 users shared drive with excel or word document, every day I get a corrupted document you must restore from a backup. every day.
otherwise it never happens …
That is not how SQLite works. If you want to use SQLite from a single storage, you have to create an app that works as a middleware, that way that app is the only accessing the file and your client app access the data using an api. An example of this kind of middleware is CubeSQL. Otherwise, just use a propper database server like MySQL or PostgreSQL
Well, reading the whole topic… Yes, the WAL is a feature that uses file locking to have provide a multi-user environment as log as: “All processes using a database must be on the same host computer” , with a remark on "WAL does not work over a network filesystem"
What OS is this? We do this thousands of times a day on our system and never have reports of corrupted files. We’re on Win10 and Windows server.
seems if you only have microsoft products it’s working better
I mainly have macos, some windows 10 and a qnap NAS as a server.
no corruption with the macos finder, one per day with word or excel and 30ish users.