I am building a service that includes a web server and a client app, both storing data in SQLite databases. In the client app, I would like to periodically check the server for updated data.

For this purpose, the dbhash program seems very helpful for identifying changes on the server that require the client to do a new sync with the server. That is, I can fetch the latest dbhash from the server, compare it to the latest dbhash on the client, and very efficiently confirm that the data is in sync.

My question is, is there a smart method built in to SQLite to sync the data when that is required? One solution I am considering is sending the server the latest dbhash from the client and requesting a JSON payload containing all the updates since that dbhash. But I am not sure how that would handle deletions and that sort of thing.

Anyway, any guidance is much appreciated. Thanks.

Is it ill-advised to just pass raw SQL queries from the server to the client that would bring the client into sync with the server?

This might be what you're looking for, convergent, replicated, sqlite.

I tried a Goggle search on

sync sqlite dbs

Interesting results.

After thinking more about this, I have settled on a solution to try. If you have questions or suggestions, please share them!

Firstly, I will run the SQLite dbhash program every time the server changes its database, and make the resulting hash available via one of the server's API endpoints. That way, the client/app can quickly check if its data is out of date by fetching the server dbhash and comparing that to the client's own dbhash.

If the dbhashes on both server and client match, then the databases are in sync, and there is nothing to do.

However, if the dbhashes on the server and client do not match, then the client will be able to fetch the whole db file from the server to replace the current client db file. This could be made more efficient by only fetching the new data, but I do not anticipate the data being very big (at most a few kilobytes), and I do not expect it to change very often, so I think it should be reasonably performant to just fetch the whole file in case of an update.

Thoughts?

That sounds right to me.

Going to the work of being able to determine the new data and send just that would be premature optimization.

Gerry

Thanks! If I do that, I suppose that I could store the latest log table rowid on the client, and then when I update from the server, fetch any entries after that, right?

That is a good idea. Better still, whenever you sync, store the last rowid on both the client and the server. Your sync process can start by checking to see they're the same, and produce an error if they aren't. This would prevent problems if someone restored an old backup on one or other computer without realising this would mess up the update process.

This arrangement could be used (with small changes) with any situation where only one computer can make changes to a database but many computers need to have copies of it. It does not work at all where more than one computer can make changes.

One advantage of a system like this is that it's multiplatform, and involves SQLite calls but no OS calls. You don't have to worry about Windows/Unix file permissions, interpreting date formats, etc..

I'm not sure I understand your point about an old backup messing up the update process, particularly when you say, "Your sync process can start by checking to see they're the same, and produce an error if they aren't." Do you mean generating an error if the client rowid is newer than the server rowid? (Since that should never happen). That might happen if the server is restored from an old backup.

However, if I'm understanding correctly, it should be a normal case that the server rowid is newer than the client rowid, which should trigger an update of the client from the server.

Is that different than how you're thinking about it?

You got it right. It is a check to see that nobody messed with either computer. If everything's normal, the two numbers match. If someone restored an old backup on one computer but not the other, the two won't match, and an update on the client machine will produce a database with bad data in it.

The sync process writes the same rowid -- the current maximum rowid of the log table -- to a file or table on both the client and the server. The following sync process can read them both and check to see that they're still the same. If not, it shouldn't proceed with the sync process because it will produce incorrect data.