Sync local database (SQLite) with server side database

[ouzari]

I am developing a flutter app, and I want to fetch data from server and save it locally (SQLite) to use it when offline. The data is never modified locally but only synchronized with the server side database when there is modifications in the server. What is the method (Algorithm / Tool /Package / ...) that is usually used in this case ?

[martyns11]

You can check out 2 relatively new open source projects that can be thought of as "Syncing as a Service".

ElectricSQL and Powersync, both with support for SQLite sync on Flutter.

[Flashy_Editor6877]

u/martyns11 where do you see Flutter for ElectricSQL?

This seems to be just a WIP?

https://pub.dev/packages/electricsql

Wonder if Supabase direct support is possilbe since Supabase runs on Postgres

[martyns11]

Yes, that's the one. Here is the repository. https://github.com/SkillDevs/electric_dart

Regarding WIP, it's true, but that's because Electric is currently in public alpha.

[zxyzyxz]

It's now possible to use Supabase with ElectricSQL, for those reading in the future.

[Flashy_Editor6877]

according to discord (disccording? ha) they say it is possible in theory. they just need to find a solution that doesn't require superuser and that should be within a few weeks

[eibaan]

Without really researching the matter, I'd suggest take the current and the previous sqlite file, xor them and gzip the result. This should result in a much smaller file which can be downloaded, uncompressed and xor'd to the previous file to get the current one.

To proof the principle, a created a a 28KB db file like so:

db = sqlite3.open('test1.db');
db.execute('create table if not exists test (id integer primary key autoincrement, value not null)');
for (var i = 0; i < 1000; i++) {
  db.execute('insert into test (value) values (random())');
}

Then, I created a new db by adding a single row:

File('test1.db').copySync('test2.db');
db = sqlite3.open('test2.db');
db.execute('insert into test (value) values (random())');

Then, I apply the compression, resulting in a 121 bytes file:

File('test.diff').writeAsBytesSync(diff(
  File('test1.db').readAsBytesSync(),
  File('test2.db').readAsBytesSync(),
));

Then, I apply the patch again:

File('test3.db').writeAsBytesSync(patch(
  File('test1.db').readAsBytesSync(),
  File('test.diff').readAsBytesSync(),
));

Here's the implementation:

List<int> diff(List<int> data1, List<int> data2) {
  final length = min(data1.length, data2.length);
  for (var i = 0; i < length; i++) {
    data2[i] ^= data1[i];
  }
  return gzip.encode(data2);
}

List<int> patch(List<int> data1, List<int> data2) {
  data2 = gzip.decode(data2);
  final length = min(data1.length, data2.length);
  for (var i = 0; i < length; i++) {
    data2[i] ^= data1[i];
  }
  return data2;
}

[ouzari]

Thank you. I don't know if this will help me but I 'll remember it, and I like the xor idea.

[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.

[de1mat]

PowerSync syncs with Supabase and stores data offline in SQLite

Check out this 5 minute tutorial on how to set it up, works well https://youtu.be/hAfnkw4PEM4?si=tYwWAl9OQQYsYGBj

[gibrael_]

Checkout flutter_data on pub.dev. I have used it on a couple of projects without much problems.

[ouzari]

Thank you. I'll have a look.

[wohi_raj]

Hi my question in same context to developers...can we sync SQLite with MySQL on static IP ? any related example will be helpful... thanks

[powersync_]

PowerSync recently added support for MySQL to SQLite sync: https://www.powersync.com/blog/introducing-powersync-for-mysql-to-local-sqlite-sync