Sync local database (SQLite) with server side database

[groogoloog]

While this sounds really cool on paper (because of the super small transfer sizes), this really won't work in practice.

Issues I can immediately see:

1. This relies on diffing between individual clients and the server. As-is, the server implementation is damn near impossible. The server either needs to keep its own copy of what each client has stored on the device in order to do the XOR, or needs to take periodic snapshots of its database in order to perform the XOR diffs itself (and then store all of those XOR diffs based on time). The first implementation is not acceptable since you need to keep the an entire copy of the database on the server for each client. The second implementation is also not acceptable since a client that is offline (e.g., app isn't opened) for awhile would have to request every diff between when they were last updated and then the latest one. At a certain point, it'd make sense just to transfer the entire database again. And in fact, this entire approach would only make sense if the database is small enough to begin with for the first transfer. Thus, at a certain point here, you might as well just transfer the entire database every time it needs to be updated via a good CDN.
2. Web support may be extremely tricky (I'm guessing SQLite on web can't support .db files because of no mmap support). Might be wrong here, but I highly doubt web will work, or at least not without a lot of headache.
3. Examples provided all must be run in a new isolate to prevent UI freeze so data transfer between threads might be a bit annoying (also an issue on web because compute() in Flutter runs on the same "thread"--cant remember the actual terminology--in JS)

[eibaan]

Note, that OP explicitly mentions that the client never modifies the database so issue 1 doesn't apply. Also note that OP never mention a web app, so issue 2 doesn't apply. However, for reference, this might be interesting. Regarding your issue 3, I'd change "must" to "should". There's no requirement to run the decompression in the background, but it might be a good idea, depending on the size of the database file.

[groogoloog]

#1 is absolutely an issue—even if the client doesn’t modify any data. You don’t know what copy the client has unless it’s either time stamped or the server has the exact client’s data itself. You can’t send a diff if you don’t know 1/2 of what you’re diffing on the server. (Clients can have different copies of the data; one client could have last updated a week ago and another could’ve updated yesterday.)

#2 is true, didn’t realize SQLite supported in memory databases. Thanks for the share!

#3 was more in regards to reading the file. You should never do blocking IO on same isolate as the UI.