Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Bi-directional sync between Postgres and SQLite

powersync.com

Post reply on HN
Print view View original

41–50 of 107 posts

Re: Show HN: Bi-directional sync between Postgres and SQLite

#41

Post by kobieps »

fractalnetworks wrote: post #38

Cool! Reminds me of Fractal Database, https://github.com/fractalnetworksco/fractal-database PoC Django ORM replication engine that supports master-master replication in a decentralized context. With a Django based solution you can sync between any DB Django supports so MySQL Postrgres SQLite is easy peasy. Disclaimer: I am the author and haven't written the README yet, but cool to see similar stuff nonetheless.

Cool to learn about your project, thanks for sharing!

Do you expose the CDC log over Django? Or how do you listen for changes.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#42

Post by ochiba »

vlovich123 wrote: post #35
ochiba wrote: post #32

Earlier quoted context omitted.

> In the given design, am I understanding correctly that this means that a local commit could be seen as “committed” by the user but then later the server rejects it because of conflicts right? > it does mean that you could lose data if the application author doesn’t handle that well in their local code or the application server yeah? Yes, this is correct and the backend developer needs to make sure that conflicts ar…

Yeah I figured that given the static nature of declaring the sync rules ahead of time, dynamic control over what is synchronized may be tricky. You mention using a flag column, but wouldn’t that mean that you’re generating writes for reads to update that flag/timestamp? Or can you choose to have extra local columns that aren’t aren’t part of the replication data?

Note that dynamic control from the client-side over what is synced is currently supported to an extent via token parameters from the client, and this will potentially be expanded in the future.

> You mention using a flag column, but wouldn’t that mean that you’re generating writes for reads to update that flag/timestamp?

There may be other ways to solve this, but the solution that came to mind was:

- Set a "last accessed at" timestamp on the client when the user opens a specific item. This would sync to Postgres.

- Have a recurring task on the server-side that updates items in Postgres based on "last accessed at" and sets a flag that causes an item to be de-synced for that user once the elapsed time exceeds some threshold

Persistent local-only columns are not currently supported. Local-only tables are currently supported.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#43

Post by kobieps »

maxfurman wrote: post #37
alfor wrote: post #30

I am looking for something like this. We develop IoT devices, thousand of linux computer running behind customer routers and have a central server with web admin, all in python. I wish i could have "syncronized objects", something were a change in a device would be reflected back to the server, web interface even better and in the other direction too, a way to see the whole system as one thing, not a hodge podge of a…

I'm reminded of RealmDB, which I think Mongo bought at some point

Yeah they did acquire Realm

Re: Show HN: Bi-directional sync between Postgres and SQLite

#45

Post by matharmin »

zlg_codes wrote: post #39
ochiba wrote: post #6

Earlier quoted context omitted.

Co-founder (Conrad) here. SQLite does indeed have a narrow set of types. Timestamps in Postgres are converted to text in SQLite in a format that is compatible with ISO8601 and SQLite's functions. Type conversions are documented here: https://docs.powersync.com/usage/sync-rules/types

I see you're using ISO8601 and including timezone data, which seems best for generalizing. Somewhat related: if you were storing UNIX timestamps that only need the resolution of a day and will always be UTC, would you use an integer for the UNIX time or just a shortened ISO8601 without H:M:S data? I've been trying to decide on this for a feature in a tool I'm making. In either case, SQLite has the ability to convert…

Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable.

I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negative timezone.

Julian day is another option, but support for it is not quite as universal. If efficiency is important, that's the format I'd use.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#46

Post by matharmin »

vlovich123 wrote: post #27

Since the company seems to be answering questions I’ll give a few that come up for me. Congrats on the launch btw. In the given design, am I understanding correctly that this means that a local commit could be seen as “committed” by the user but then later the server rejects it because of conflicts right? I guess it’s all application defined in that you could build your application in such a way as to show enqueued b…

> Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient? Eg could you support browser LocalStorage instead of WASM SQLite or is there some piece of functionality of SQLite you’re relying on?

SQLite is supported practically everywhere, so that's our first choice. We also modelled our sync rules on SQLite's type system and functions to a large extent.

However, there is nothing technical tying is to SQLite. The sync protocol works purely with JSON, and doesn't impose any restrictions on how the data is stored or queried.

For the JourneyApps platform where PowerSync was originally used, we actually have an implementation on top of IndexedDB for browsers, which ends up being more lightweight. It needs more work before we can expose it as a general library, so we just started with SQLite for now.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#47

Post by fractalnetworks »

kobieps wrote: post #41
fractalnetworks wrote: post #38

Cool! Reminds me of Fractal Database, https://github.com/fractalnetworksco/fractal-database PoC Django ORM replication engine that supports master-master replication in a decentralized context. With a Django based solution you can sync between any DB Django supports so MySQL Postrgres SQLite is easy peasy. Disclaimer: I am the author and haven't written the README yet, but cool to see similar stuff nonetheless.

Cool to learn about your project, thanks for sharing! Do you expose the CDC log over Django? Or how do you listen for changes.

By leveraging Django's db transaction machinery in concert with Django's post save signals we send full representations to a user configurable replication target.

We provide a Matrix (protocol) replication target implementation but system's architecture was designed to be transport and database agnostic.

Our goal is to make it easy for developers to build offline-first decentralized applications for private / permissioned networks.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#48

Post by kobieps »

bullebak wrote: post #13

It is amazing how often I sit with crappy or no connectivity, even in today's day and age, and so if it were easier for more apps to be designed to work offline first that would truly be amazing. Exciting development PowerSync team, good luck and Godspeed!

Yeah it's a shocker that even major apps like Apple's Weather aren't local first. Lots of room for improvement.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#49

Post by halfcat »

alfor wrote: post #30

I am looking for something like this. We develop IoT devices, thousand of linux computer running behind customer routers and have a central server with web admin, all in python. I wish i could have "syncronized objects", something were a change in a device would be reflected back to the server, web interface even better and in the other direction too, a way to see the whole system as one thing, not a hodge podge of a…

Turso might be a fit

https://turso.tech/

Re: Show HN: Bi-directional sync between Postgres and SQLite

#50

Post by vlovich123 »

matharmin wrote: post #45
zlg_codes wrote: post #39

Earlier quoted context omitted.

I see you're using ISO8601 and including timezone data, which seems best for generalizing. Somewhat related: if you were storing UNIX timestamps that only need the resolution of a day and will always be UTC, would you use an integer for the UNIX time or just a shortened ISO8601 without H:M:S data? I've been trying to decide on this for a feature in a tool I'm making. In either case, SQLite has the ability to convert…

Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable. I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negati…

What’s the ambiguity issue with Unix time stamps? Unix time stamps are expressed as a UTC offset always so I’m not sure how time zones come into play / what parsing might involved.
Post reply on HN