[HN Gopher] Show HN: Bi-directional sync between Postgres and SQ...
___________________________________________________________________
Show HN: Bi-directional sync between Postgres and SQLite
Hi HN, 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
Author : matharmin
Score : 328 points
Date : 2023-11-30 14:13 UTC (1 days ago)
(HTM) web link (powersync.com)
(TXT) w3m dump (powersync.com)
| metadat wrote:
| I 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?
| saurik wrote:
| SQLite has a number of functions to work with time but the
| underlying storage format is just going to be--depending on
| what precision/range of timestamp you want--a float or an
| integer... to the extent, of course, that SQLite has types at
| all (as it frankly doesn't, thereby making this question kind
| of moot).
| simonw wrote:
| 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
| bch wrote:
| > It has a historically cavalier attitude to enforcing them
| (which I believe it inherited from TCL)
|
| 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?...
| o11c wrote:
| 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
| kobieps wrote:
| The JSON1 stuff in SQLite is incredible. Very easy to
| work with for the most part.
| saurik wrote:
| Ah! I mean, only two years old, but OK ;P. Regardless: my
| point stands that you can trivially store and work with
| timestamps in SQLite as they are simply a real or integer
| or text; like, the idea that you need a special timestamp
| data type or you can't work with such values in an engine
| that only two years ago got support for checking the types
| at all and encourages you to just write code is highly
| strange.
| ochiba wrote:
| 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
| zlg_codes wrote:
| 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.
| matharmin wrote:
| 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.
| vlovich123 wrote:
| 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.
| zlg_codes wrote:
| I think he's referring to behavior that some software has
| when it encounters raw UNIX timestamps without TZ
| information. I recall seeing a change like that in
| Python's datetime module as of late, so that even if UNIX
| timestamps are UTC by default, it's more explicit.
|
| 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?
| vlovich123 wrote:
| By definition unix timestamps are UTC. Python's datetime
| module has a lot of footguns because naive timestamps can
| represent UTC, local time, or even a non-standard
| application-specific time. Python's aware timestamps just
| carry the tzinfo information along with the naive
| timestamp so that you can get to UTC from the abstract
| naive timestamp. It's a really overdesigned footgun of
| Python's datetime module more than anything, but again
| not unix timestamps which are always an offset from 1970
| midnight UTC.
| zlg_codes wrote:
| Cool, thanks for the clarification!
| matharmin wrote:
| You can do it in a consistent way, for example always
| store the timestamp at 00:00 UTC. But that's not obvious
| just by looking at the values, which is why it can be
| ambiguous.
|
| 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.
| vlovich123 wrote:
| Not sure I follow. What's the difference between `new
| Date(ts).getDay()` when ts is an ISO8601 string or a ms
| since UNIX epoch value. Pretty sure the result is the
| same and whether you need to use `getDay` or `getUTCDay`
| is a display issue that affects both variants.
|
| 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?
| joking wrote:
| when you do that, it will give you different days
| depending on your timezone. 1701388800 is 2023-12-01 in
| london, but still 2023-11-30 on new york.
| vlovich123 wrote:
| Sure, but that's also true for an iso8601 string unless
| you're extracting things directly. But also you're
| assuming that your iso8601 string is in local time which
| it may not be (eg string generated in London but accessed
| in New York). Localizing things to be time zone aware in
| a way that meets user expectations is hard because it's a
| squishy domain specific UX problem, not a technical one
| so arguing about it like a technical issue feels like a
| wrong approach.
| matharmin wrote:
| You're right that you'll have exactly the same issues if
| you use Date with a string.
|
| 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.
| zlg_codes wrote:
| > 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.
|
| 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
| random_kris wrote:
| Possible integration with drizzle orm ?
|
| Let me define schema with drizzle and use its generated objects
| to interact with db. Postgres driver for server SQLite driver for
| client
| ochiba wrote:
| It is on our near-term roadmap to support ORMs for the client-
| side SQLite database. Our goal with ORMs is to fit in with
| existing popular libraries on each platform (e.g. Flutter,
| React Native, etc.), rather than creating a PowerSync-specific
| ORM over all platforms. Drizzle is one of the integrations that
| we're looking at, along with Prisma and others.
| carstenhag wrote:
| Sorry, unfortunately I am a bit lost on what this does (on my
| phone, so couldn't see the demos on the website). I'm an android
| developer, but I am not sure what this would do. Do you have an
| example of an app/backend at which you can explain where
| Powersync helps?
| ochiba wrote:
| The goal is basically to help you build offline-first apps. In
| the offline-first paradigm, app code works directly with a
| client-side embedded database, which automatically syncs with a
| backend database in the background. This is in contrast to
| cloud-first apps which primarily use a cloud datastore via
| APIs.
|
| 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)
| JCharante wrote:
| Oh that's very interesting. I guess it's easier to deal with
| "only" sqlite than to have a store, keep track of mutations,
| and then upload/download the mutations when the device is
| reconnected.
| ochiba wrote:
| For sure, working only with a local SQLite database does
| simplify state management in many cases. For the most part
| you don't need an additional state management
| system/library.
|
| 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...
| farmeroy wrote:
| This looks great! I've been planning a project and I wanted
| exactly this functionality and started looking into running
| SQlite in the browser. Very excited to try this out
| chaostheory wrote:
| Thank you - it's great to have an alternative to electric-sql.
|
| 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.
| amit9gupta wrote:
| You can try Watermelon DB. It is local first disconnected
| framework. And it has a sync framework as well, but you have to
| create your own backend (using any DB) for syncing. Stable
| product and works great
|
| https://watermelondb.dev/docs/Sync/Intro
| jpbtavares wrote:
| Hi everyone, I worked with watermelonDB and recently switched
| my entire project to powersync. Watermelon has its
| limitations for an offline applicationFirst, if you really
| want to make use of the application completely offline, you
| will have to build a synchronizer like CRDT by hand and
| manage the request queues. With powersync all of this is
| managed by them, and with a simple code I can choose which
| information from the database I will sync for each user. In
| my first tests with WatermelonDB, synchronization proved to
| be unfeasible due to the amount of synchronized data. In
| short, Powersync has proven to be a wonderful tool that has
| allowed my company to move forward with offline services.
| whilenot-dev wrote:
| You can find more alternatives here: https://localfirstweb.dev/
| bullebak wrote:
| 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!
| kobieps wrote:
| Yeah it's a shocker that even major apps like Apple's Weather
| aren't local first. Lots of room for improvement.
| jmnicolas wrote:
| Why would you want a local first weather app? It's not very
| useful to have yesterday's weather or even from one hour ago.
| kobieps wrote:
| Great question and that's what you'd think. I spend a lot
| of time in the outdoors though, and on day 3 of a camping
| trip into an area with no signal, I'd rather have a stale
| prediction for the day 4 weather than no prediction.
| jmnicolas wrote:
| Got it, thanks.
| andyferris wrote:
| I couldn't tell whether I could integrate my SQLite app with my
| own Postgres database? E.g. is it free to self host, and the paid
| services are a convenience? Or the cloud hostage is mandatory?
| ochiba wrote:
| You can use any Postgres database as long as it supports
| enabling logical replication and PowerSync can connect to it.
| Details here:
| https://docs.powersync.com/usage/installation/database-setup
|
| The PowerSync Service is currently a hosted cloud service. A
| self-hostable version is coming soon.
| andyferris wrote:
| Ah thanks. Out of curiosity, will the self-hostable version
| be a paid product, or free to use? (No judgement, just want
| to understand the business model).
| ochiba wrote:
| The initial self-hostable version will be available as part
| of the paid service, but there is also a free open-source
| self-hostable version planned for next year:
|
| 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)
| alterhuman wrote:
| So that means the pricing of powersync cloud solution
| will be just a bit higher or similar to cost of self-
| host, just with extra features, correct?
|
| 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?
| johng wrote:
| Are you open to investors?
| ochiba wrote:
| The company behind PowerSync is profitable
| https://www.powersync.com/company and is not currently actively
| seeking investment
| unixhero wrote:
| Can I have a tshirt?
| ochiba wrote:
| Yes!
| unixhero wrote:
| :)
| rgrieselhuber wrote:
| Love to see this.
| JCharante wrote:
| Heads up:
|
| 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)
| ochiba wrote:
| That's strange, we are unable to reproduce this on our end --
| both URLs load correctly for our team. I'll shoot an email to
| the address in your bio to see if there's any details you could
| perhaps share that could help us debug. Thanks for letting us
| know :)
| langarus wrote:
| Great concept! What if the user is offline, makes updates to the
| local/embedded DB, but when it gets online there's an issue in
| syncing that prevents updating the postgress with the SQLite
| changes?
|
| Is SQLite reverted to postgress and thus the changes lost?
| ochiba wrote:
| The client does not update its state to the authoritative state
| of the server as long as there's pending writes present in the
| client's upload queue. If there is an error with uploading a
| write to the server (either a network error or the server
| returning an HTTP error response), the write will remain in the
| upload queue and the upload will be retried.
|
| 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...
| vlovich123 wrote:
| 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 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?
| ochiba wrote:
| > 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.
| vlovich123 wrote:
| 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?
| ochiba wrote:
| 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.
| matharmin wrote:
| > 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.
| alfor wrote:
| 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 api,
| calls, update and so on.
|
| Is there an elegant way to do this?
| ochiba wrote:
| 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
| alfor wrote:
| on the linux devices we have a web admin (made with node-red)
| that control the linux machine, network, config, updates.
|
| 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.
| bongodongobob wrote:
| 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.
| jddj wrote:
| Yeah, during the IoT hype there was a (supposed? I never
| tried it) solution to this from every cloud provider.
|
| I think Amazon and Google both deprecated it since then, not
| sure how Azure's is going.
| maxfurman wrote:
| I'm reminded of RealmDB, which I think Mongo bought at some
| point
| kobieps wrote:
| Yeah they did acquire Realm
| jinjin2 wrote:
| We have been using realm in production for a while and it
| seems to have steadily improved since moving to MongoDB
| (surprisingly for an acquisition). It is definitely best of
| class when it come to doing live sync between a backend and a
| local database.
|
| 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.
| redwood wrote:
| Out of curiosity, what kind of use case is yours?
| halfcat wrote:
| Turso might be a fit
|
| https://turso.tech/
| alfor wrote:
| 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.
| ochiba wrote:
| 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.
| fractalnetworks wrote:
| 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.
| kobieps wrote:
| Cool to learn about your project, thanks for sharing!
|
| Do you expose the CDC log over Django? Or how do you listen for
| changes.
| fractalnetworks wrote:
| 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.
| m9t wrote:
| This looks interesting! How does this compare to ElectricSQL[1]?
| Next to not using CRDTs.
|
| [1] https://electric-sql.com/
| prawnstar wrote:
| Hey there's a comparison of the two here
| https://powersync.com/blog/electricsql-vs-powersync
| thruflo wrote:
| Hey, James here from Electric. Congratulations to the PowerSync
| team :)
|
| 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
| matharmin wrote:
| Thanks for chiming in! ElectricSQL is great - we see it come
| up a lot in discussions since on the surface it solves the
| same problems (syncing between Postgres and SQLite), despite
| the details being very different. I love seeing all the
| innovation in the offline-first space!
| dkarras wrote:
| what happens if you go out of business?
| matharmin wrote:
| We've been in business for over 10 years, and the company
| (JourneyApps) is profitable - we're not going out of business
| any time soon.
|
| Also see the other comments regarding our current status and
| plans around open source.
| ricg wrote:
| How do you handle primary keys? Wouldn't auto-incrementing
| integers lead to duplicate keys since clients are not aware of
| each other?
|
| 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"]
| prawnstar wrote:
| Hi ricg thanks for the great question! We have a docs page that
| speaks to this https://docs.powersync.com/usage/sync-
| rules/client-id:
|
| "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)."
| matharmin wrote:
| To expand on what prawnstar said - it's exactly because of this
| that we recommend using uuids as the primary key (uuid v4
| specifically). While you can use integer ids, you need some
| workarounds such as pre-generating sets of unique ids per
| device.
| sgt wrote:
| Well done on the launch! I have been using PowerSync for several
| months already ( although our own app hasn't quite reached
| production yet, due to other reasons).
|
| 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.
| prawnstar wrote:
| Thanks for the feedback! Along with the docs we also have an
| active Discord that anyone is welcome to join for any queries,
| requests, discussions etc https://discord.gg/powersync
| mediumsmart wrote:
| Sorry for being a noob but is this "tech" similar to the rails
| offline video linked or am I misunderstanding? I don't get why
| you would need a service instead of building it into the
| webapp/site?
|
| https://www.youtube.com/watch?v=Gj8ov0cOuA0
| matharmin wrote:
| I just scanned through the video, but it seems to focus on
| persisting client-side changes locally when offline, which only
| covers one part of the bigger problem.
|
| 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.
| mediumsmart wrote:
| thank you for pointing that out, yes. I just saw the video
| uses indexedDB (probably suboptimal although mdn says its ok
| for storing complex data on the client) and also its just for
| 'bridging' no internet access when doing input on the device.
| thank you for taking the time to answer, I see a bit clearer
| now.
| matharmin wrote:
| IndexedDB is fine - just use some wrapped that makes it
| easier to work with. Our current SQLite implementation
| actually stores the underlying data inside IndexedDB -
| mostly due to a lack of better options (but that's busy
| changing with OPFS).
|
| 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.
| mariopt wrote:
| This seems really interesting and I do have some questions:
|
| 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?
| matharmin wrote:
| > 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?
|
| 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.
| belalw wrote:
| Looks a lot like a light version of ElectricSQL
| (https://github.com/electric-sql/electric). Why should I use
| PowerSync instead?
| ochiba wrote:
| A detailed comparison can be found here:
| https://www.powersync.com/blog/electricsql-vs-powersync
| jadbox wrote:
| Hi! I'm not familiar with either engine, but I'm confused
| with the text in the comparison (see below). Couldn't
| ElectricSQL be used within the backend to enforce policies or
| am I missing something?
|
| > Writes are made directly to your Postgres database via the
| Electric sync service (bypassing your backend application),
| ochiba wrote:
| ElectricSQL's architecture is documented here:
|
| 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.
| thruflo wrote:
| Yup, this is correct.
|
| 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.
| smartmic wrote:
| Let me throw SymmetricDS into the ring: https://symmetricds.org/
|
| 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.
| matharmin wrote:
| Thanks for the link, I haven't come across SymmetricDS before.
| The list of supported databases is impressive.
|
| 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?
| srameshc wrote:
| Thanks for sharing. Sometimes I feel had it not been for
| discussion and community at HN, I wouldn't find such stuff.
| accra4rx wrote:
| You stole my words! Thank you again for sharing this option.
| I just knew about big brands OracleGate , Qlik, IBM IIDR etc
| but this is interesting. I hope somebody shares the
| experience with the Pro version and how was the support
| experience.?
___________________________________________________________________
(page generated 2023-12-01 23:02 UTC)