Show HN: Bi-directional sync between Postgres and SQLite (powersync.com)
Today we’re launching PowerSync, a Postgres<>SQLite bi-directional sync engine that enables an offline-first app architecture. It currently supports Flutter, React Native and web (JavaScript) using Wasm SQLite in the browser, with more client SDKs on the way.
Conrad and I (Ralf) have been working on our sync engine since 2009, originally as part of a full-stack app platform. That version of the system is still used in production worldwide and we’ve learnt a lot from its use cases and scaling. About a year ago we started on spinning off PowerSync as a standalone product that is designed to be stack-agnostic.
If you’d like to see a simple demo, check out the pebbles widget on the landing page here: https://www.powersync.com/
We wrote about our architecture and design philosophy here: https://www.powersync.com/blog/introducing-powersync-v1-0-po...
This covers amongst other things how we designed the system for scalable dynamic partial replication, why we use a server authority architecture based on an event log instead of CRDTs for merging changes, and the approach to consistency.
Our docs can be found here: https://docs.powersync.com/
We would love to hear your feedback! - Ralf, Conrad, Kobie, Phillip and team
106 comments
[ 2.8 ms ] story [ 101 ms ] threadWhat 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?
Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package.
[0] https://www.sqlite.org/different.html
[1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...
See https://sqlite.org/json1.html#value_arguments
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.
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.
In the case of my project, the goal is to have UTC-only timestamps/dates, and since the resolution I'm using it for is a calendar day, time zone doesn't matter. I will still need to be explicit about it in my code when converting.
Or maybe edge cases like historical timezone changes, leap seconds?
And when parsing those values, you could do for example (JS) `new Date(ts*1000).getDay()`. Of course that's not correct - you need to use the UTC methods. But it's easy to miss, and may pass all your tests until you switch the timezone.
Those aren't massive issues - you just have to be careful with the parsing and serialization, and you need to be careful in any case. It just explains my personal preference.
I could see it being more convenient if you're just dumping the raw values without any transformation or you don't know what data type is stored in a column, but otherwise the difference seems largely one of taste and not so much relative error rates?
However, with a string date you can bypass the Date class completely in many use cases.
My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.
Once again, these are all preventable issues - just use UTC everywhere. I've just seen issues like that happen enough that I prefer never converting between dates and timestamps if I can avoid it.
Oh man, that sounds ugly, and an invitation for disaster. Lots of off-by-ones.
In my project's case, I also need to do math with them for analytical purposes. It's a game collection manager, so it's tracking the day you bought, beat, and 100%d a game.
I derive how long it took to beat a game by subtracting the UNIX timestamp of the purchase date from the beaten date's timestamp, then divide by (60*60*24) to measure days. It's handy to show games in the backlog and sort by purchase date, too, so you can target the games that've been sitting the longest. There are plans to graph out a collection in terms of its path from new to beaten to completed in a Gantt-style chart or something else fun.
But yeah, I just thought to ask because in JSON I and others can read `2023-12-01`, but not `1701417600`. SQLite and Python can do conversions for the calculations, and Python even has the timedelta module that might make my comparisons easier to do or slightly more accurate.
Edit: asterisks
Let me define schema with drizzle and use its generated objects to interact with db. Postgres driver for server SQLite driver for client
Working with a local database (e.g. SQLite) means that apps feel instant to use because of low latency, and remain functional when the user’s network connection is unreliable or offline. Also, offline-first typically offers built-in real-time collaboration by automatically syncing data in the background.
There's 1-minute demo video here of a Flutter "To-Do List" app showing some of these concepts: https://www.youtube.com/watch?v=VTx5ViRe3HY (this demo uses a Supabase Postgres backend for simplicity)
Meta had an interesting article a few years back about how they rewrote the Messenger app to use SQLite: https://engineering.fb.com/2020/03/02/data-infrastructure/me...
I actually need a local first, disconnected framework. What many people don’t realize is that even in the US, there are many parts without any network connectivity like our parks and preserves. Rangers still need to make queries in those areas.
https://watermelondb.dev/docs/Sync/Intro
Exciting development PowerSync team, good luck and Godspeed!
The PowerSync Service is currently a hosted cloud service. A self-hostable version is coming soon.
The PowerSync Service will move to an open-core model in 2024: An open-source version will be available (self-hosted) as well as a non-open-source version with premium features (available as both self-hosted and hosted cloud service)
The client SDKs are currently open-source (Apache 2.0 license)
I'm asking in terms of ease of hosting & restrictions, whether it would be super hard like supabase self hosting + a lack of features or like appwrite where self hosting is very easy and you get most things in self hosted version as well?
- A paid self-hosted version, full-featured: This will be released in the next couple of months.
- An free open-source self-hosted version, with core features: This will be released within 2024.
We will aim to make the open-source version easy to self-host and want to make sure that it has a valuable set of core features.
The submission link https://powersync.com/ results in ERR_ADDRESS_UNREACHABLE
You linked the proper landing page here: https://www.powersync.com/
Your web infrastructure doesn't redirect root visitors to www (or pass those requests on to your web server)
Is SQLite reverted to postgress and thus the changes lost?
If the server can automatically resolve an error or conflict, it would return a 2xx response and the client's state will be updated to match the server's authoritative state.
If the conflict/error cannot automatically be resolved, there are a few options:
- The server can return a 2xx response but record information about the conflict that allows a user to resolve it (e.g. prompt a user to manually resolve the conflict — either the end-user or administrator for example)
- The server can save details in a dead letter queue
More details are documented here:
https://docs.powersync.com/architecture/consistency
https://docs.powersync.com/usage/lifecycle-maintenance/handl...
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 but uncommitted changes differently from the checkpoint, but 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? Not a critique because I think that’s true even for anything handrolled, but just making sure I understand the model.
Also, I’m a little unclear how it’s tied to postgres and what the powersync service is doing. The docs say that you fetch a JWT from your application to talk to the service but then it says that all writes are mediated by your own application code. So presumably the powersync service channel is for synchronization of Postgres -> local SQLite. Is that right? And the replication logic in powersync - is that essentially accomplishing horizontal sharding of the database for reads? 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?
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 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.
> 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.
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.
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 api, calls, update and so on.
Is there an elegant way to do this?
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
Then the web admin run docker container, we have a python backend on the device with fastapi as the base and another with vue.js for the frontend (touchscreen)
Then the server is fastapi/mongodb/vue.js.
We have a continuous websocket from the python program to server and another one from web dashboard to our server admin.
Lot's of moving parts, lot's of problems, not a lot of cohesion and structure.
I think Amazon and Google both deprecated it since then, not sure how Azure's is going.
Realm itself is amazing, fast and intuitive, and the bidirectional sync to MongoDB just works. The only downside is the messy and confusing web admin interface.
https://turso.tech/
I thought that it was deprecated for a lack of diversity of implementation.
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.
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.
Do you expose the CDC log over Django? Or how do you listen for changes.
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.
[1] https://electric-sql.com/
As a sibling comment says, PowerSync actually wrote up a comparison here https://www.powersync.com/blog/electricsql-vs-powersync
Aside from Electric being open source and PowerSync a proprietary service, the primary difference is in the programming model on the write path.
Electric provides finality of local writes. So once a non-malicious write is accepted locally, it is final and won’t be rejected by the server. This simplifies the programming model and means you don’t have to code for rollbacks.
PowerSync is a server authoritative system. Local writes are tentative and can be rejected at the server. You run an API on the write path and write logic to handle conflicts and rollbacks.
The different approaches come with different trade offs, both operationally and in terms of the programming model.
On the topic, if interesting, we have a list of alternative projects here: https://electric-sql.com/docs/reference/alternatives
Congrats, I'm so looking forward an idea or project where I can use electricsql
Supporting joins is in development, but I'm not yet clear on whether the current dev branch on it goes far enough to support access control use cases.
There's a hack in place that's supposed to help - you can define an electric_user_id on the table - but that isn't actually usable in the majority of use cases, because most ACL cases include records where multiple users can access it. I did explore using views, but electricsql doesn't currently support postgres views.
(if I'm wrong or missed something in electricsql, I'd love to be corrected, as it looks like an exciting project otherwise)
The key features for us on this are:
1. permissions https://electric-sql.com/docs/usage/data-modelling/permissio... which are defined using DDLX rules, authorise data access and can be used to filter data 2. shapes https://electric-sql.com/docs/usage/data-access/shapes which are the main, more expressive way to control what data syncs on and off the local device, including where clauses, joins, include trees, etc.
These are both in development and due soon. From your comment, I think you’ve seen the shapes branch with where clauses and include trees already working, for example.
In the meantime, the shapes API over syncs the full table. This is temporary and obviously suboptimal but it means you can develop today using the shape APIs and still filter data you display using local queries. Then when the proper functionality lands, the sync will become more fine grained and optimal without your app code needing to change.
Hope that makes sense. We’re very much not a full table sync system. Our role is to provide the best possible model for controlling dynamic partial replication (and to maintain integrity across replication boundaries).
Also see the other comments regarding our current status and plans around open source.
For the To Do app example, let's say two clients start with an empty database and each creates a new todo:
Client A creates [id:1, todo:"Buy milk"]
Client B creates [id:1, todo:"Buy cheese"]
"PowerSync does not perform any validation that IDs are unique. Duplicate IDs on a client could occur in any of these scenarios:
- A non-unique column is used for the ID.
- Multiple table partitions are used, with the same ID present in different partitions.
- Multiple data queries returning the same record. This is typically not an issue if the queries return the same values (same transformations used in each query)."
Very happy with PowerSync. The docs are detailed and well thought out, and the technology is solid. One can see it builds on a decade of sync experience from the Journey team.
https://www.youtube.com/watch?v=Gj8ov0cOuA0
Difficulties quickly come in if you want to: 1. Persist large amounts of data on the client. 2. Keep the data in sync incrementally (too much data to re-download every time). 3. Keep the data up-to-date in realtime (streaming changes). 4. Keep the data consistent, especially across multiple tables / types.
Many "offline" solutions are actually closer to caching rather than offline-first, which introduce all the issues associated with cache invalidation.
Also, if you're just working with a single table, you might get by with just using updated_at timestamps and soft deletes to be able to get incremental changes from the server. Even then you need to be careful with consistency issues, e.g. making sure timestamps are always in order and without duplicates. And if you start adding more tables, the complexity quickly increases.
The difficult parts are mostly related to keeping the local data in sync with the server, whether that uses SQLite, IndexedDB or some other database.
How similar is this to MeteorJs minimongo collections?
Does this provide an architecture where a client subscribes to a collection and the server keeps track of which data the client has and only sends minimal update messages?
Is this ideal for intense real-time applications (ex:Chat)?
Is self-hosted a future option?
> Does this provide an architecture where a client subscribes to a collection and the server keeps track of which data the client has and only sends minimal update messages?
It is similar in that PowerSync also supports local queries and real-time/streaming updates. However, I believe minimongo / MeteorJS uses an in-memory database, while PowerSync fully persists the data, allowing full offline capabilities.
The PowerSync client does subscribe to incremental changes, similar to MeteorJS (although the client keeps track of the state, not the server).
> Is this ideal for intense real-time applications (ex:Chat)?
Yes, PowerSync will work well for that. One caveat is that PowerSync may have slightly more latency than some other real-time systems (in the order of tens to hundreds of milliseconds), in favor of consistency and offline persistence of data. This means PowerSync will not be suitable for time-sensitive games as an example.
> Is self-hosted a future option?
Yes - see other comments for details on self-hosting and our open-source plans.
> Writes are made directly to your Postgres database via the Electric sync service (bypassing your backend application),
https://electric-sql.com/docs/reference/architecture#primary...
https://electric-sql.com/docs/reference/architecture#data-fl...
The docs explain how the Electric sync service publishes a logical replication stream that the Postgres database subscribes to. The Electric sync service is responsible for validating permissions based on the DDLX configuration.
We have had some requests / discussions around adding hooks to the sync service that will support custom logic on the write path (as per https://github.com/electric-sql/electric/discussions/565). This seems like a good idea but they don't exist yet.
In my opinion it is quite underestimated giving its various cross platform and cross product sync capabilities. My first experiences with it are quite promising.
It appears to be primarily aimed at server-to-server replication. It does support Android and iOS, but I don't see much documentation on that. Do you know if it works in practice to implement offline-first applications?
I haven't used Capacitor but I will soon. Is it possible to use the web SDK in a mobile Capacitor app or is there something about the webview browser environment that breaks the normal functioning of Powersync on the web?