The typical use case would be with the sqlite in the browser?
I thought that it was deprecated for a lack of diversity of implementation.
Show HN: Bi-directional sync between Postgres and SQLite
31–40 of 107 posts
Re: Show HN: Bi-directional sync between Postgres and SQLite
#32Since 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…
> 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 are handled appropriately (I went into a bit more detail on this in my other comment in reply to langarus which you may have seen)
> So presumably the powersync service channel is for synchronization of Postgres -> local SQLite. Is that right?
Yes, this is correct.
> And the replication logic in powersync - is that essentially accomplishing horizontal sharding of the database for reads?
Assuming I understood this question correctly — yes, the PowerSync Service handles the complexities of dynamic partial replication of the database to different users. In our announcement blog post we wrote a bit more about the trade-offs and design considerations: https://www.powersync.com/blog/introducing-powersync-v1-0-po... (see section "A scalable dynamic partial replication system")
> Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient?
We do currently use a few different features of SQLite, but something that we are considering is making the client-side more database agnostic and potentially supporting more local database options (details TBD).
> Finally, do you have any support for lazy local hydration / eviction? Eg if I have a Google docs like application, is it synchronizing my entire account at all times or does it support pulling a document and evicting LRU documents when some local storage limit is exceeded?
It is possible to accomplish some of this kind of functionality using PowerSync's Sync Rules. It should be possible to design the Sync Rules such that flags are set (e.g. based on LRU) which would trigger certain rows to be synced/un-synced.
I think my co-founder (matharmin) may also want to weigh in with more detail on some of the answers. We are based in different timezones so we may reply with more information in a few hours.
Re: Show HN: Bi-directional sync between Postgres and SQLite
#33The typical use case would be with the sqlite in the browser? I thought that it was deprecated for a lack of diversity of implementation.
Are you perhaps thinking of Web SQL? Web SQL was deprecated but Wasm SQLite with IndexedDB or OPFS as the underlying storage layer is gaining more adoption. https://developer.chrome.com/blog/from-web-sql-to-sqlite-was...
PowerSync can currently be used in web browsers (powered by Wasm SQLite) and also in Flutter and React Native, for which native SQLite libraries are used.
Re: Show HN: Bi-directional sync between Postgres and SQLite
#34I 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…
This is an interesting use case and sounds like it could be a fit.
What stack is the software that runs on the Linux devices?
Also feel free to shoot me an email if you'd like to discuss the use case privately (email in bio) or chat on Discord https://discord.gg/powersync
Re: Show HN: Bi-directional sync between Postgres and SQLite
#35Since 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…
> 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?
Re: Show HN: Bi-directional sync between Postgres and SQLite
#36I 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…
It's called RMM and there are tons of products that do exactly this. Most will be geared towards MSPs, but that's likely right up your alley as you probably want to silo customers into their own sites.
Re: Show HN: Bi-directional sync between Postgres and SQLite
#37I 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
Re: Show HN: Bi-directional sync between Postgres and SQLite
#38Cool! 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.
Re: Show HN: Bi-directional sync between Postgres and SQLite
#39I dig it, super cool idea! What about timestamps, though? SQLite doesn't support them.. does it end up being restricted to the lowest common denominator between the two databases in terms of supported types/functionality?
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 back and forth, so it'd really be the exported JSON containing the data whose readability would be affected more than anything.
Re: Show HN: Bi-directional sync between Postgres and SQLite
#40Earlier quoted context omitted.
SQLite has five types, documented here: https://www.sqlite.org/datatype3.html null, integer, real, text, blob It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html
JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another. See https://sqlite.org/json1.html#value_arguments
The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.