Device sync

[lukepighetti]

Hi there. Starting to think that flutter is missing a solid local SQLite option that also supports syncing with Postgres or SQLite server.

Drift feels like the ideal package to handle the client side.

Do you have any ideas on the shortest path to adding remote sync (foreground and background) to an app using drift?

The overall goal would be to prototype an app using drift but then have a linear migration path to robust device syncing.

What could be very interesting is using drift on clients and drift with SQLite in WAL mode on the server to enable a low headroom (but perfectly acceptable for most) app architecture hosted on something like fly.io

[davidmartos96]

Hello Luke,
I would like to share my experience on this topic as I've felt the same way as you for a long time.
In our team we've been developing an app for 3+ years which heavily uses Sqlite features via drift to be fully usable offline. Data sync has always been a really hard feature to integrate (conflicts, migrations...).

Last year we discovered ElectricSQL, which offers exactly what you are asking, a way to sync Postgres with SQLite through a type safe interface, all without limiting SQLite features on the client. The problem was that, at the time it was only compatible with Web technologies.
We really enjoyed the transparency and DX it offered, so because it is open source, we've evaluated how feasible it would be to port the Javascript client to Dart. It was very possible so we went ahead and did it. https://pub.dev/packages/electricsql

The client is built on top of the drift typed interface and it integrates with drift Stream queries for real time sync updates. Electric is still in open alpha, but the community is very active on Discord. As of today the unofficial Dart client is up to date with all the capabilities the official JS client offers, and we are actively maintaining it.

We are still integrating it with our app, but it really feels like magic, we didn't need to change at all our drift code to get the sync capabilities and conflict resolution working.

If you give it a try feel free to ask any questions 😄

Just to be clear, this is not a sponsored post. We are really enjoying this technology, and how it is shaping up. And being both Open Source and openly Self Host-able it is just the cherry on top 🙂

[lukepighetti]

This is wonderful news.

Can you please recommend a strategy for prototyping with Drift and then progressively adding Electric for device syncing? Or would you start with Electric from the beginning?

An ideal scenario would be specifying types in one place (ideally Drift / client side) and not having to duplicate that effort on the backend. Can you speak to this?

Thank you

[davidmartos96]

@lukepighetti You can pretty much decide. The only difference would be how the initial schema migration is defined:

• Starting with Electric + Drift
  You define the schema in a Postgres .sql file, and use the CLI to generate the client side Sqlite statements to create the schema, and bundle it in the app. Electric will automatically run those statements to create the schema in the client. This client generation would generate your Drift schema with the Dart syntax, without creating it by hand.

• Drift only
  You define the Drift schema (Dart syntax and Drift files) yourself and create it in the onCreate callback. You will later need to create an equivalent schema for Postgres and replace your original drift schema with the one generated by Electric. If you use special drift features like custom row classes, column renames,..., where you need to add drift annotations, know that you can customize how Electric generates the drift schema to add the same annotations as well.

The latter is pretty much what we are doing with our app, but starting from scratch it might be easier to generate the client + postgres in the same step (option 1). You can work fully offline in the app, it's just that the tables need to be created.

The team behind Electric is working on improving this area with a full offline development mode to add sync incrementally. Some improvements were recently made with the CLI.
I'm not sure at what stage that is, but you can ask the team over Discord.

[alterhuman]

I've tried Drift with Electric, have also talked with David on the dart electric repo.

There is another solution powersync which is similar in working to electric but is fundamentally a middleware between your SQLite and your Postgres db. While electric basically takes over complete control of your Postgres db, powersync is just a middleware.

I think instead of me explaining it, this link can explain it much better.

What I like is about electric is that it is open source & powersync is not (yet) but they are planning on moving to the open core model (which is open source + an optional hosted version provided by them with some extra features).

Performance wise both of them worked equally well for me although I did not test any of them with a lot of rows.

Overall, I found powersync to be a better option since it can be removed anytime as it's only a middleware. I can do anything I want in my supabase db and it simply reflects in the app. While in the app I just write to my local Sqlite db and it reflects automatically in the supabase db. Powersync also has official support & SDK for flutter while electric has an unofficial dart client. But @davidmartos96 has done a great job of maintaining it.

One thing I like is that dart electric works with drift which is great, powersync use another sqlite package in their SDK. And electric is open source.

[cahofmeyr]

Hi @alterhuman — I wanted to let you know that the PowerSync Open Edition has been released (self-hosted & source-available), in case you haven't seen news of the release yet: https://www.powersync.com/blog/powersync-open-edition-release

[alterhuman]

@cahofmeyr this is great! Thanks for notifying, starred the repo. Hope it gets great traction.

[simolus3]

Thanks for starting this discussion, this seems to be a very popular request. My stance is that drift should work well with synchronization services, but not provide its own.
You can use drift to talk to sqlite3 databases, you can use drift to talk to postgres, you can even share a lot of code when doing that. But implementing automatic synchronization between the two is still a monumental effort, and this is something that existing third-party solutions will be able to do a lot better.
That said, I'm happy to accept contributions to drift (or work on changes myself) that would reduce friction between drift and synchronization services further. If there's more information drift should provide at runtime, or other hooks to make it easier to generate code picked up by drift_dev, it's worth taking a look.

Perhaps a first step is to highlight Electric (and also powersync once there's a better way to use that with drift) on drift's documentation website, so that they're easier to find for drift users interested in syncing their database with a postgres server.

[hamishjohnson]

FYI powersync seems to have support with drift:

https://docs.powersync.com/client-sdk-references/flutter/flutter-orm-support

I haven't tried it yet but will do so in next month or so