[HN Gopher] Show HN: ElectricSQL, Postgres to SQLite active-acti...
___________________________________________________________________
Show HN: ElectricSQL, Postgres to SQLite active-active sync for
local-first apps
Hi HN, James, Valter, Sam and the team from ElectricSQL here.
We're really excited to be sharing ElectricSQL with you today. It's
an open source, local-first sync layer that can be used to build
reactive, realtime, offline-capable apps directly on Postgres with
two way active-active sync to SQLite (including with WASM in the
browser). Electric comprises a sync layer (built with Elixir)
placed in front of your Postgres database and a type safe client
that allows you to bidirectionally sync data from your Postgres to
local SQLite databases. This sync is CRDT-based, resilient to
conflicting edits from multiple nodes at the same time, and works
after being offline for extended periods. Some good links to get
started: - website: https://electric-sql.com - docs:
https://electric-sql.com/docs - code: https://github.com/electric-
sql/electric - introducing post: https://electric-
sql.com/blog/2023/09/20/introducing-electri... You can also see
some demo applications: - Linear clone: https://linear-
lite.electric-sql.com - Realtime demo: https://electric-
sql.com/docs/intro/multi-user - Conflict-free offline:
https://electric-sql.com/docs/intro/offline The Electric team
actually includes two of the inventors of CRDTs, Marc Shapiro and
Nuno Preguica, and a number of their collaborators who've pioneered
a lot of tech underpinning local-first software. We are privileged
to be building on their research and delighted to be surfacing so
much work in a product you can now try out.
Author : samwillis
Score : 419 points
Date : 2023-09-20 13:46 UTC (9 hours ago)
(HTM) web link (electric-sql.com)
(TXT) w3m dump (electric-sql.com)
| koolba wrote:
| Is there any validation or authorization for changes being merged
| back into the root database?
|
| In a traditional client / server model, the server has an
| opportunity to validate each request and optionally reject it.
| The lower level you go with the sync protocol (data changes vs
| high level requests) the more difficult that becomes.
|
| Have you addressed that and, if so, how? What prevents a
| malicious client from send arbitrary data streams to get synced
| into the root database?
| ochiba wrote:
| The route that we went with for PowerSync (disclosure: co-
| founder) is to allow to define your own function for handling
| writes, where you would use your (presumably existing) backend
| application API to persist the writes to Postgres. Therefore
| you can handle any validation/authZ/business logic for writes
| in your backend layer.
|
| The PowerSync client SDK still handles the queueing and
| retrying of writes so that they can be automatically retried if
| network connectivity is not available -- whenever there is a
| retry, your callback function is called. (As a result of this
| approach, your write function should be idempotent; we commend
| using GUIDs or UUIDs generate client-side for primary keys)
|
| Similar to Electric, PowerSync also uses JWT for auth against
| your backend.
|
| There are some architecture diagrams explaining this on our
| docs root here: https://docs.powersync.co/
| meiraleal wrote:
| You are really taking the expression `shameless plug` to
| another level in this thread, aren't you?
| ochiba wrote:
| Genuinely excited about this space and it's what I'm
| focused on full-time so definitely have thoughts to share.
| I am wary of self-promotion. I do want to contribute things
| that I feel are relevant to the discussion, since I assume
| folks would be interested to see different
| patterns/approaches around local-first/offline-first
| architecture.
| FrancoisBosun wrote:
| Not the author, but PostgreSQL has constraint triggers that can
| run procedures / functions on insert/update/delete, to
| allow/reject a given row or statement. That would be one way to
| confirm that a given update from a client is valid, from the
| POV of the application.
| koolba wrote:
| The situation I'm considering is data that matches the
| referential integrity and check constraints of the database,
| but is malicious. For example syncing a "salary update to
| $1M" for yourself into the source database.
| thruflo wrote:
| Yup, this is what's addressed by write permissions. You can
| express who can set salaries and column level rules to
| validate input values.
|
| When it comes to concurrency problems like not spending
| money twice, the plan is https://electric-
| sql.com/blog/2022/05/03/introducing-rich-cr...
| robertlagrant wrote:
| Row-level permissions also very useful - e.g. I am
| allowed to update my own profile, but not someone else's.
| thruflo wrote:
| Hey,
|
| You can see our database rules spec here: https://electric-
| sql.com/docs/api/ddlx
|
| We haven't implemented it all yet but you can see the intention
| / direction. It's similar to RLS but adapted for the context.
|
| Connections are authenticated by signed JWT: https://electric-
| sql.com/docs/usage/auth
|
| We also auto-generate a type-safe data access client from the
| electrified subset of the Postgres schema. This applies type-
| level write validation and will apply general write validation
| when we get there.
|
| James.
| doctorpangloss wrote:
| > It's similar to RLS but adapted for the context.
|
| I'm visiting the docs.
|
| > ...This must have at least a user_id claim, which should be
| a non-empty string matching the primary key UUID of the
| authenticated user...
|
| You should probably strike this from your docs. It sounds
| like you are still figuring out how this should work.
|
| The "right" answer is to author an OIDC authentication module
| for Postgres and license it in a way that Amazon will steal
| it for the purposes of actual adoption; or try to add it to
| PGBouncer as a proxy, converting JWTs in the password field
| into "set local user.id = ..." and similar in a preamble for
| every sql statement.
|
| Projects like postgrest have been down this journey and spell
| out their solutions. It's all a little vague with Supabase,
| there isn't anything magical about their authorization
| approach either, but I wouldn't keeping looking to them as
| the gold standard implementation for web backend stuff.
|
| Anyway, none of this exists in SQLite, which is a huge pain
| in the butt. SQLite looks promising, and then it's missing
| all this stuff, because it doesn't make any sense for SQLite
| to have row level security or JWTs or whatever. That's the
| issue isn't it? Like why not step back and say, "is SQLite
| right for me, if it's not only missing all these features I
| need, but also they will never be added to SQLite because it
| doesn't make _sense_ for SQLite to have them? "
|
| Separately, when I visit https://electric-
| sql.com/docs/intro/local-first, it's ironic, the local first
| says its latency is 3ms, but because it had to load all this
| code and stuff, it took longer than 237ms of the "cloud-
| first" box for that 3ms number to even appear. I've been here
| before, I was a Meteor developer, I am cognizant of the
| tradeoffs and what this measurement is saying. There's no
| such thing as a free lunch.
| kiwicopple wrote:
| > Projects like postgrest have been down this journey and
| spell out their solutions. It's all a little vague with
| Supabase
|
| Just in case it's not entirely clear: supabase is just
| PostgreSQL + PostgREST. We contribute to + maintain
| PostgREST, so if it works with PostgREST it also works with
| Supabase.
|
| > there isn't anything magical about their authorization
| approach either
|
| I 100% agree with this, and that's intentional. We don't
| want to do anything special here, we want our solutions to
| be as interoperable as possible with existing approaches
| LocalPCGuy wrote:
| Any specific reason it wouldn't work with Angular as well
| (noticed it was not listed in the Frontend frameworks section)?
| samwillis wrote:
| Not at all, it's perfectly possible to use it with Angular.
| While we haven't built any specific helpers for Angular, like
| we have for React, our TypeScript client is perfectly useable
| from any JS/TS framework or project.
| nathancahill wrote:
| What does the local client side look like? Could it be
| wrapped in a node daemon to sync to a local SQLite file?
| icehaunter wrote:
| Hey, one of the devs here! JS library already works with
| Node via `better-sqlite3` library. We provide a way to set
| up listeners to data changes, or you can just start up a
| node client and do some work periodically on the synced
| data.
| agg23 wrote:
| Are there plans for a true native driver (not JS)? That's kind of
| a dealbreaker in the same groups that are looking for offline
| first.
| paulharter wrote:
| There will be other drivers. We are exploring ways to support a
| wide range of os/platforms but this is a little way off, for
| now we are concentrating on the js client.
| obeavs wrote:
| Huge congrats to the team. I reached out to them four or five
| months ago as we explored offline-first frameworks, and its
| awesome to see how far they've come in a short time.
|
| We didnt find anyone else who was tackling the tie between
| client-side SQLite, an open source CRDT/sync layer, and Postgres.
| We found a few who were attempting to manage this in a closed
| source way, but it didn't make sense to give up control of the
| server/auth, and every other fully open source solution was
| SQLite -> SQLite, not Postgres.
|
| Great stuff from some killer engineers.
| roncesvalles wrote:
| Conceptually sounds like this is what Firebase, Couchbase Lite,
| and Mongo Reach do in the NoSQL world.
| zubairq wrote:
| Is the SQLite embedded in the browser?
| pstuart wrote:
| It is in Firefox.
|
| The SQLite team has been working on making WASM support first
| class, so should work anywhere WASM does.
| cpursley wrote:
| Neat, this is the pattern I've been thinking about for a while
| now. Also glad to see this is Elixir based and that Jose is
| involved.
|
| I've been using https://github.com/cpursley/walex (basically a
| fork of cainophile via a fork for subabase) to listen to Postgres
| changes in Elixir.
|
| Sequin is also doing some neat stuff in this space as well:
| https://blog.sequin.io/all-the-ways-to-capture-changes-in-po...
| pgm8705 wrote:
| Awesome! I'm looking forward to trying this out. Currently I get
| this functionality by using PouchDB on the client with a CouchDB
| sever. Then on my API server I have some janky code in a cron job
| to sync changes from CouchDB to PostgreSQL.
| samwillis wrote:
| Hey, I work at Electric,
|
| The CouchDB/PouchDB pattern is how I originally got interested
| in local first, they are such a good tool, but having the full
| power of Postgres and then SQLite on the client, I believe, is
| a real game change.
|
| Sounds like Electric could be a really good fit for your use
| case. If want any advice join the Discord and we are happy to
| help out.
| jsaramago wrote:
| Congrats to the team
| addisonj wrote:
| Congrats on the launch and interesting project! I have a lot of
| questions :)
|
| * I am by no means a crdt expert, but from my tinkering I have
| come to the opinion that the upside of the consistency does come
| at the cost of ease of understanding how state ends up how it
| does, which can bleed into user facing issues if how a crdt
| converges conflicts with intuition. A lot of that seems like an
| education and design problem and I am curious how you are
| thinking of talking that? Do you plan on some support for server-
| first writes for situations where crdt might not fit?
|
| * from the docs it seems like adding electric to a table does a
| migration to add new columns / new tables, I assume, to support
| the crdt, but my other knowledge of crdts is that it can be
| expensive in terms of size, especially for preserving history. I
| will have to poke at it to learn more, but I do wonder where you
| think you _shouldn 't_ enable electric?
|
| * I am curious at the commercial model? Are you going full cloud
| service like Supabase/neon? Or just host the elixir component?
|
| I will keep poking, but really really interesting!
| thruflo wrote:
| Hey, thanks :)
|
| We have a Discord if you fancy chatting at more length!
| https://discord.electric-sql.com
|
| Re: CRDTs and intuition, yup, there's trade offs. We are
| working hard to deliver a solid model to code against. So we
| have finality of local writes and standard relational integrity
| guarantees. You can read a bit more about some of techniques
| here https://electric-sql.com/blog/2022/05/03/introducing-rich-
| cr...
|
| Re: electrify yes, we create a shadow table and some operation
| log tables etc. So there is some storage amplification but it's
| of the order of 2x (we use operational based CRDTs and we
| garbage collect the operation log). We see the fit being with
| standard OLTP workloads, mainstream relational apps. We don't
| target high volume data ingestion, etc.
|
| Re: commercial model, we're designed for open source self-host.
| We see Electric as a drop in sync layer, not as a full stack
| backend-as-a-service. Lots of options but concentrating on
| product for now.
| irq-1 wrote:
| Wouldn't making a backend service be better all around? All
| CRDT data could be stored elsewhere. The service could be
| easily updated and changed, and without changes the database.
| Any database could be used. Could have LDAP integration and
| other services.
|
| I'm sure I don't understand CRDTs. Can they be used with
| database changes that are not part of the CRDT history? That
| is, can they be added to an existing database with other
| clients?
| kiitos wrote:
| A CRDT is a discrete entity/object that has to follow a set
| of rules for reads, writes, and storage -- including, but
| not limited to, rules about how to maintain history. You
| can certainly mix-and-match CRDTs with other entities in
| the same database, but you can't like "fold in" arbitrary
| changes to a CRDT without following the rules.
| 3523582908 wrote:
| I am extremely, extremely excited about this.
|
| I was wondering: what is the difference between VLCN and
| ElectricSQL?
|
| Thank you!
| thruflo wrote:
| Hey,
|
| James here, one of the co-founders of Electric.
|
| Both projects are doing active-active CRDT-based sync. Our
| focus is on sync via Postgres and on compatibility with
| existing Postgres-backed applications. So you can drop Electric
| onto an existing Postgres-backed system and it works with your
| existing data model.
|
| There's also quite a lot of difference in the development
| model, how we handle migrations, shape-based partial
| replication, etc. And we're not focused on p2p sync -- for us,
| everything goes through Postgres.
|
| Hope that helps -- you can read a bit more about the system
| design here: https://electric-
| sql.com/docs/reference/architecture
| ako wrote:
| How do you deal with schema migrations, and software updates
| (SQLite or Postgres)?
| 3523582908 wrote:
| Thank you for the help! It is quite useful. Another question
| while I still have you:
|
| On the clientside, is SQLite running in a separate thread? Or
| is it running on the main thread? Is this the same or
| different for an electron app vs running in the browser?
| thruflo wrote:
| With wa-sqlite in IndexedDB mode we're main thread. In OPFS
| mode it's a worker thread. With the mobile drivers you go
| over a native bridge.
|
| With Electron / Tauri it's the same as the browser. You're
| running in Chromium / native WebView on the front-end side.
| malablaster wrote:
| I remember pitching this idea to a team of colleagues about 11
| years ago. People weren't ready for it and I never took it
| anywhere. I really hope this works like my dreams!
| CuriouslyC wrote:
| Pretty interesting, what's the benefit of using a check
| expression rather than row level security?
| paulharter wrote:
| We do row (and column) level security, the check expression
| provides an optional additional granularity for controlling
| which roles can make certain writes
| jtmarmon wrote:
| Congrats on the launch, looks like a great product.
|
| Out of curiosity - the demo on the site suggests the local first
| latency is like 10-40 ms. I would think reading/writing to SQLite
| locally would be on the order of 1ms or less. Why is that?
| samwillis wrote:
| Hey, I work for Electric,
|
| Quite right, the latency should be low single figure ms. There
| is actually a slight bug in how that latency is calculated on
| the demo and includes an extra render tick, I was digging into
| it earlier but didn't get a chance to push a fix yet.
| Exoristos wrote:
| When `brew install postgresql` is too hard.
| [deleted]
| LAC-Tech wrote:
| postgresql has conflict-free multi-master sync now? That's
| incredible work, can't believe I missed it!
| victorbjorklund wrote:
| Nice. When I looked at this last some months ago it only
| supported multi-tentant setups (where a whole db/table is synced)
| but seems like it now suports row based users, correct?
| thruflo wrote:
| Hey, yup, a big part of the new release is a proper system for
| dynamic partial replication, as per https://electric-
| sql.com/docs/usage/data-access/shapes
| roblh wrote:
| Very cool. I haven't quite worked out how, but I wonder if you
| could use this for writing really fast integration tests somehow
| for CI workflows, using some subset of real (or I guess more like
| curated) data. Also love that the sync layer is Elixir, what a
| great language.
| lucgagan wrote:
| Can you delve a bit deeper into how WASM works? Specifically, how
| heavy is the WASM module? Also, is there a specific use-case
| you've seen where this shines particularly brightly?
| samwillis wrote:
| Hey, I work for Electric.
|
| The WASM module is just a standard build of SQLite, about 1.1mb
| - there are many landing pages 10x that size - and something
| that we think is easily justifiable for the types of apps that
| people can build with Electric.
|
| In terms of use cases, there are numerous, broad-ranging
| possibilities - anything from a replacement for GraphQL or a
| REST API in your existing stack, all the way up to building
| large collaborative apps. Our intention is that you can build
| collaborative tools similar to Linear, and we do actually have
| a demo of this sort of thing (http://linear-lite.electric-
| sql.com), but it's certainly not limited to that.
| tantaman wrote:
| You can get that size down to 600KB using brotli compression
| fwiw. Maybe even a bit smaller using Roy's latest tricks that
| no longer require asyncify'd builds of SQLite.
| hultner wrote:
| I have a side project where this would fit perfectly. I've for a
| database in Postgres and my plan have been to sync the data into
| a local SQLite for a react-native app which needs to work offline
| and with flaky connections, to then later sync it back into PG. I
| have never gotten around to actually build the sync and it looks
| like this could solve that problem.
|
| Does this play nice with RLS? I'm running everything on Postgrest
| at the moment.
| thruflo wrote:
| Hey, cool and yup, that's exactly the kind of setup we aim to
| support and we have drivers for both Expo and React Native.
|
| Re: RLS, not quite, you'll need to port your rules to our DDLX
| syntax https://electric-sql.com/docs/api/ddlx -- which is not
| fully implemented yet (we're working on right now, will be
| available shortly).
| hinkley wrote:
| I'm going to have to check this out.
|
| I still maintain that we need an interchange standard for write
| ahead logs, but tools like this are a step in the right
| direction.
|
| I would like to point out thought why you may be underselling
| this. Local-first yes, but that dynamic can also apply to edge-
| networked apps (at least before everyone started equating edge
| networking with Lambda).
| gregzo wrote:
| We've been chatting with the ElectricSQL team for a few weeks,
| super responsive and open to adapting to our use case. We're
| building an offline-first, mobile-first app and have high hopes
| for this project!
| pgt wrote:
| Unrelated to Electric Clojure (https://electric.hyperfiddle.net/)
| switz wrote:
| This has long been a dream data stack of mine. Best of luck.
| nine_zeros wrote:
| Amazing!
| srameshc wrote:
| It took me a while to understand the usecase but this is a very
| good solution for apps that are read intensive, wonder why this
| pattern isn't very common.
| brigadier132 wrote:
| Because synchronizing data with multiple users updating data in
| a distributed manner then reconciling it is not trivial.
| canadiantim wrote:
| I want to be able to have a Postgres database as the central
| source of truth for all data and user accounts, but then have
| each users private content to be siloed and synced to their own
| SQLite database which they alone have access to (maybe even
| they're have one SQLite file on server and one SQLite file on
| phone or etc.). Is this possible with electricSQL? I remember
| looking at it a year ago or so and was excited but not sure if
| worked for my use case. Great work tho looks really good and very
| much in line with what I'd like to be able to do.
|
| Also is there a way to transition an existing Postgres data base
| into using electicsql?
| thruflo wrote:
| Hey,
|
| This is the exact pattern we target :)
|
| Drop Electric onto an existing Postgres data model and use the
| Shape-based sync to manage dynamic partial replication of
| subsets of data between central Postgres and local SQLites:
| https://electric-sql.com/docs/usage/data-access/shapes
|
| James
| appplication wrote:
| This is awesome. I love the pattern, and the way you've built
| for this as a first-class concern is great.
| aidos wrote:
| Congrats on the launch. I've been keeping an eye on these
| sorts of tools for a while and had a look at this after it
| was mentioned on HN the other day. Looks great.
|
| We currently use Hasura subscriptions to pull data to the
| frontend (effectively select * from table where account_id =
| X and updated > recently). We then funnel changed rows into
| mobx objects so we have a lovely object graph to work with.
|
| I'm imagining doing the same with electric so I guess you'd
| use notify to hear that there has been a change, then figure
| out if it's in a table you care about and then select the
| updated records from that to merge into mobx?
|
| Basing that off what I see in here:
| https://github.com/electric-
| sql/electric/blob/main/clients/t...
| thruflo wrote:
| Interesting, I don't know enough about MobX to be sure but
| yes, you can use the notifier to subscribe to data change
| notifications and query to get the changed rows.
|
| We'd definitely be interested in chatting through the
| reactivity model you use if you'd be up for it.
| ochiba wrote:
| This is also the pattern targeted by PowerSync (disclosure: co-
| founder) -- selectively syncing scoped data for the user from
| Postgres to their own client SQLite database. Sync Rules are
| used to define which data is synced to which users:
| https://docs.powersync.co/usage/sync-rules
| afavour wrote:
| This is the fifth comment I've counted plugging Powersync in
| this thread. I guess it's the nature of the startup hustle
| game but this level of promotion in someone else's Show HN
| thread feels a little distasteful. Maybe you could post your
| own Show HN?
| ochiba wrote:
| I hear you, thanks for sharing your thoughts and for the
| suggestion. Noted. I didn't mean to just plug -- I thought
| I was contributing to the discussion and that some people
| may find another implementation relevant/interesting.
| candiddevmike wrote:
| You could store a SQLite database in a blob column for the
| user, though this would be an affront to $DEITY.
| vladstudio wrote:
| You might want to update the github star counter on your homepage
| :-) It said 333 during my visit, while on Github itself it's 1.3K
| already. Congrats!
| thruflo wrote:
| Hmm, yes, seems like we might need to bust a cache key there :)
| thruflo wrote:
| Fixed!
| mrgalaxy wrote:
| Wow, this is exactly what I've been searching for! I knew this
| was possible but am too busy trying to build web apps to spend
| time learning how to do stuff like this.
|
| I notice this is still a 0.X version. How comfortable are you
| with people using this in production? Are there any success
| stories so far? And if it's not production ready, is there a
| roadmap I can check out?
|
| The company I work for is in the process of planning a complete
| rework of our app and right now is the time for us to choose
| technologies. I so very much want to use this or something like
| this.
| thruflo wrote:
| Hey, very cool :)
|
| We don't recommend production use right now. There's a roadmap
| page here https://electric-sql.com/docs/reference/roadmap -- we
| particularly need to flesh out shapes, permissions and
| validation.
|
| Happy to chat (e.g. on our Discord) if you'd like a bit more
| detail / looking at whether Electric could fit into your tech
| pans.
| mrgalaxy wrote:
| Thanks for the roadmap, I should have just looked around the
| site a bit more, I would have found it.
|
| And, I may take you up on a chat after I've had a
| conversation with my team! At the very least, I've joined the
| Discord and I'll definitely be following you guys closely.
| ochiba wrote:
| > I so very much want to use this or something like this.
|
| Since you mentioned this -- you could also take a look at
| PowerSync which is similar (disclosure: co-founder)
| https://docs.powersync.co/ - Currently beta suitable for
| production use.
| mrgalaxy wrote:
| Yes, saw your other comment! ;) I will definitely be checking
| this out as well. I am so glad folks are finally creating
| real solutions in this space.
| LAC-Tech wrote:
| Some very smart people working on this - Annette Bieniusa comes
| to mind, who has done a lot of research on CRDTs.
|
| Excited to take a closer look.
| crubier wrote:
| Oh wow, I've been wanting this for a long time, to the point that
| I started making it myself.
|
| This is perfect to create super fast offline first apps like
| linear.app, with Postgres
| jchw wrote:
| I saw this recently and I am really excited for, hopefully, a
| renaissance in local-first apps; it's been quite a long time
| coming. That said, it seems like there's still _a lot_ of
| problems to work on in this space and it 'll take a while for
| different approaches and implementations to approach their local
| maxima.
|
| I am curious about encryption. Assuming that ElectricSQL is
| handling essentially all of the syncing, is it possible to build
| applications that use end-to-end encryption for some of their
| state? I am wondering specifically because CRDTs seem to simplify
| E2EE a lot since well, any client that can write can resolve
| conflicts any time. I haven't looked very deeply yet as I've been
| very busy, but to me that's the main thing I've been really
| curious about. I know you can at least do this with Y.js which
| helpfully provides a way to use symmetric encryption for WebRTC
| synchronization, but this obviously is a whole lot more than
| that.
| klabb3 wrote:
| You can always encrypt local data even if the metadata is not
| encrypted. But that restricts it to the device itself by
| default, not even your other devices on the same account can
| read it.
|
| If you want encrypted and shared with other devices or users
| you have to surface key management to users which is very
| difficult to do. Signal and a few others do this but they're
| very bespoke to the specific business logic. To provide a
| general purpose abstraction layer is still an open problem,
| afaik.
| jchw wrote:
| I'm not asking for the problem of key derivation to be solved
| by ElectricSQL per se, just wondering if it can handle E2E
| encrypted data in any way. That said, I disagree with how
| difficult it is: there are a lot of approaches that are not
| too ridiculous. For example, a simple approach to E2EE is to
| use a PAKE like SRP to do password authentication, then since
| the password is kept secret, you can use a normal KDF to
| derive a symmetric key from the password. From here, you can
| e.g. store symmetric and public key matter on a server or
| synced across your clients, encrypted using the password/KDF.
| If this sounds familiar, it's exactly how password managers
| work and the main downsides are that it requires password
| authentication (can't use any auth mechanism that doesn't
| somehow discretely convey a secret to the client) and the
| forgot password situation is more complicated (if you have no
| backups of the key, you can't recover any data. However,
| that's not an unreasonable compromise in exchange for E2EE.)
|
| Just like TLS, it'd probably be bad if most people
| implemented SRP from scratch. That said, I did write my own
| implementation of SRP-6a (a variant of SRP based on the RSA
| cryptosystem) in TypeScript and I found it fairly simple to
| do. There are also PAKEs that provide even better security
| properties than SRP-6a, but good implementations of them are
| still lacking for now.
|
| What Matrix does seems pretty simple, it just has a lot of
| moving parts and nuance; the underlying keys are essentially
| sent directly between clients after a key exchange is
| performed and verified out of band to ensure there is no
| eavesdropping and that the two clients are connected to who
| they think they are.
|
| As far as I know, Signal sidesteps key management almost
| entirely and only allows one logged in device, and everything
| else must proxy through it. That's pretty lame, though I
| understand the stakes are very high to get it 'right' and
| keeping the moving parts to a minimum was likely high
| priority.
| klabb3 wrote:
| I don't disagree with your technical assessment per se but
| I would would not label any of this "pretty simple", except
| for special purpose apps with technically competent users.
|
| Academically it's in a decent state, but tech- and UX wise
| I'd say it's immature. It virtually penetrates all layers
| of a normal stack (including layer 8). For apps it's at
| least possible but the web is very tricky as a platform for
| anything e2ee without absurd usage requirements.
|
| The tech choices that remain after deciding on an e2ee
| model are severely limited and language dependent, and
| spans deeply across persistence layers, software updates,
| authn, authz, social graphs, moderation etc etc. And even
| so, there are many subtleties in terms of privacy and
| information leakage that are often necessary compromises
| for features that many apps take for granted today. Without
| any social aspects it's significantly easier (eg PAKE is
| best suited for for interactive/online two-party
| operations).
| jchw wrote:
| I do agree that the UX of a lot of E2EE software is
| pretty bad, and if you wanted to make e.g. a chat
| application, then yeah, it's not necessarily "simple". In
| practice it can become quite a mess. And honestly, I
| think a lot of apps have just done a poorer job than what
| would've been possible without much hassle because it's
| relatively new territory for a lot of developers.
|
| On the other hand though, we're talking in the context of
| local-first apps, and the set of constraints imposed by
| local-first apps already does limit you substantially. If
| anything, local-first is more complicated in a lot of
| regards; in my mind, the worst part about E2EE is trying
| to keep track of all of the different keys you wind up
| needing to wrangle, whereas with local-first, usually,
| you're guaranteed to run into CRDTs first-thing, which
| impose a whole bunch of limitations on the structure of
| data and the operations you can perform on that data. It
| is possible to share things in a local-first app, but
| there are limitations to how much a CRDT-based app can
| scale for a single document.
|
| And that's why arguably, it's one of the perfect places
| to add encryption. If you're already talking about apps
| that deal with small-scale documents and that necessarily
| do most of the work on the client, you may as well
| encrypt it. All of the clients are "equal" as far as the
| data structure goes and the server(s) mainly exist to
| sync to; it's perfect.
|
| When thinking about the E2EE UX of local-first apps, I
| think of password managers and not chat applications. I
| think it's closer to the former than the latter in terms
| of constraints.
| westurner wrote:
| [IETF] "RFC 9420 a.k.a. Messaging Layer Security" does key
| revocation and rebroadcast fwiu
| https://news.ycombinator.com/item?id=36815705
|
| W3C DID pubkeys can be stored in a W3C SOLID LDPC:
| https://solid.github.io/did-method-solid/
|
| Re: W3C DIDs and PKI systems; CT Certificate Transparency
| w/ Merkle hashes in google/trillian and edns, keybase pgp
| -h, blockchain-certificates/cert-verifier-js,:
| https://news.ycombinator.com/item?id=36146424
| https://github.com/blockchain-certificates
| jchw wrote:
| Oh, thanks for this, I hadn't heard of RFC 9420 until now
| and it looks very interesting.
| vmfunction wrote:
| Thank you for saying this. I can see this will help app
| developers to make more apps that can work in airplane mode.
| More and more we need apps like that. Why do we need internet
| for an app to work locally on my computer. The whole google,
| meta, apps are just annoying to require the user to be online
| to work.
| samwillis wrote:
| Absolutely, we are really keen to support an encryption
| pattern. We are at the design phase, the current thinking is to
| have a hook enabling developers to provided their own
| encryption callback so that you can have unencrypted data in
| the local database. Essentially encrypting and decrypting, on
| and off the sync layer.
| boundlessdreamz wrote:
| 1. "Find out how" under "Switch from REST" on your homepage is
| not working.
|
| 2. Is there a way to disable auto resolving of conflicts? i.e to
| let the user know of the conflict?
| steida wrote:
| Congrats! The world needs more local-first software, but many
| find a complete switch hard and practically impossible, so
| reusing existing Postgres DB is an excellent idea. I suppose
| there is a space for more solutions; that's why Evolu takes a
| more radical and clean approach with complete privacy by default
| without opt-out. Also, a dependency on third-party servers should
| be limited as much as possible, so that's why Evolu Server is
| super simple and generic for all Evolu clients. Another Evolu
| feature is the simplicity of the code. CRDT shouldn't be a black
| box only people with Ph.D. can understand. So, if anyone is
| interested in another approach, check
| https://github.com/evoluhq/evolu.
| alooPotato wrote:
| Can you talk about the dataset sizes you support? Your example
| linear app has 112 rows, could you support 1000? 10K? 100k? 1M?
| ochiba wrote:
| Relevant -- PowerSync [1] (similar to Electric; disclosure: co-
| founder) is currently designed to efficiently sync around 1M
| rows, or roughly 1GB of uncompressed data. More would be
| possible, but performance may degrade. In the future we plan on
| pushing it to handle larger data volumes.
|
| [1] https://docs.powersync.co/
| samwillis wrote:
| OP here, I work for Electric,
|
| Electric is designed to support partial sync, and so you don't
| have to sync your whole dataset. (Note that this is feature is
| under development and not yet public)
|
| There are limitations on how much data a browser will store for
| an individual site, so the number of rows you can sync will
| depend on the shape of your dataset. Finally there are also
| some performance considerations with WASM SQLite, this is
| something the SQLite team are working on in collaboration with
| the browser developers, particularly with the development of
| the new OPFS apis which we plan to support as they mature.
|
| So, thousands of rows are definitely viable, and we have that
| working with our own internal development tests. Hundreds of
| thousands or millions may cause issues right now, but are
| something we do want to support.
| slashdev wrote:
| I wish they didn't remove sqlite from browsers "because it's
| not an open standard". It was one very important thing in
| common between mobile and web, and now it's clunky and slow
| on web when running over WASM. I don't see how that was in
| any way good for users.
| tln wrote:
| Yeah but SQLite doesn't protect against unlimited CPU use
| and has historically had issues where "malicious queries"
| cause crashes -- not OK for a browser to surface to
| scripts.
|
| https://www.sqlite.org/cves.html
|
| > Almost all CVEs written against SQLite require the
| ability to inject and run arbitrary SQL.
|
| > The advertised consequence of most CVEs is "denial of
| service", typically by causing a crash through a NULL
| pointer dereference or a division by zero, or similar.
|
| > But if an attacker can already run arbitrary SQL, they do
| not need a bug to cause a denial of service. There are
| plenty of perfectly legal and valid SQL statements that
| will consume unlimited CPU, memory, and disk I/O in order
| to create a denial-of-service without requiring help from
| bugs.
|
| > Hence, the mere fact that an attacker has a way to inject
| and run arbitrary SQL is in and of itself a denial-of-
| service attack. That the arbitrary SQL might also tickle a
| bug in SQLite and cause a crash is not a new vulnerability.
| slashdev wrote:
| JavaScript can do that too. Web browsers defend against
| DoS attacks in the browser by popping up an alert asking
| if you want to kill the tab process or not. I don't see
| why sqlite can't be run under that model as well.
| ochiba wrote:
| We wrote a bit about SQLite implementations and associated
| performance considerations for web here:
| https://www.powersync.co/blog/sqlite-persistence-on-the-web
| tantaman wrote:
| Congrats on the launch!
|
| Looking forward to seeing more people trying out this
| architecture.
|
| Still hoping we can find some time to collaborate on reactivity,
| tree-sql, typed-sql or some such other effort in the near future.
| thruflo wrote:
| Thanks :) and yup, definitely, we were digging into the typed-
| sql repo just the other day.
| tantaman wrote:
| Awesome.
|
| I've made quite a bit of progress on incremental view
| maintenance for SQLite which I'd like to share in the near
| future if that's a problem you're also dealing with.
| chaxor wrote:
| This is a great project.
|
| Also, it reinforces that fact that everyone should pronounce SQL
| as 'squeal'.
|
| 'S.Q.L.' is far too many syllables, and 'sequel' isn't much
| better. 'Squeal' is just one syllable, so obviously better.
|
| Also, I love how this project now has me singing "OO girl, shock
| me like an electricSQL".
| CogitoCogito wrote:
| > 'S.Q.L.' is far too many syllables, and 'sequel' isn't much
| better. 'Squeal' is just one syllable, so obviously better.
|
| I think that "sequel" and "squeal" have basically the exact
| same consonant and vowel sounds just in different orders.
|
| I mean I know you're making a bit of a joke here, but I'm
| having fun saying the two words and picking apart the sounds.
| rococosbasilisk wrote:
| Congrats to the team. I'm on the team over at
| https://powersync.co and have been following this space for a
| while.
|
| PowerSync is also a plug-in sync layer. The biggest difference I
| see is in Electric's use of CRDTs, where we don't rely on them
| and instead use server reconciliation.
|
| As a team that's been working on online/offline sync for just
| over a decade, it's great to finally see more products that
| enable offline-first architectures!
| vekker wrote:
| Wow, this is awesome! I've been "waiting" for a project like this
| for years - having to roll out my own syncing solution for my
| local-first app until now.
|
| I have yet to dive deeper in the docs and examples, but does
| anyone know how easy it would be with Electric to provide some
| kind of client-side transformations (encryption/decryption) of
| specific fields or entire rows in your SQLite db before syncing?
| A major reason to go local-first, is often privacy, and the idea
| that the user owns their data...
|
| Also, it's unfortunate permissions haven't been implemented yet;
| https://electric-sql.com/docs/usage/data-modelling/permissio...
| If I understand correctly, that means in a multi-user system,
| everyone will sync everyone else's data?
|
| And too bad my favorite stack (Angular + Nest.js backend) isn't
| supported, but I guess it should still be straightforward to
| integrate the client-side and run the sync service in the
| backend. I might try it if I have a weekend...
| vbalegas wrote:
| Valter from Electric here,
|
| Handling encryption/decryption of user data is one of our
| priorities. We're thinking along the lines of adding hooks for
| transforming data as you're seeing it.
|
| We're getting there on permissions implementations. If you want
| to do row-based filtering based on user_id today, you can add a
| special column 'electric_user_id' to your table:
| https://electric-sql.com/docs/reference/roadmap#shapes
|
| We plan to integrate Electric with popular web frameworks:
| https://electric-sql.com/docs/integrations/frontend. To
| integrate with Angular, the client should work out of the box.
| What you'd need is to provide is the code for reactivity, which
| should not be a lot. Reach out on Discord and we can have a
| chat about it.
| mnahkies wrote:
| Are you / have you considered leveraging postgres' row level
| security for this (row filtering)? Feels like a natural fit
| for ensuring the right subset of data gets to the right users
| https://www.postgresql.org/docs/current/ddl-rowsecurity.html
|
| E2E encryption would also be nice to have, it could be worth
| having a look at https://www.etesync.com/ for inspiration
| vbalegas wrote:
| You definitely can do row-level filtering (and a lot more)
| using DDLX[1], an extension we made to Postgres language
| that allows expressing flexible permission rules in a data-
| centric way.
|
| [1] https://electric-sql.com/docs/api/ddlx
| meiraleal wrote:
| EletricSQL is very cool, congratulations and thanks for the work!
|
| I was thinking about using it for an app that I'm developing but
| decided to not ship a 2mb binary to every user.
|
| And that led me to discover that IndexedDB is actually quite
| interesting.
|
| Any plans to integrate EletricSQL with IndexedDB?
| thruflo wrote:
| Hey, thank you :)
|
| We currently use IndexedDB for the virtual filesystem
| underneath the wa-sqlite [1] WASM SQLite driver in the browser.
| But yup, that means a ~1.1MB WASM download.
|
| We'll definitely stay with SQLite in the client. One of our key
| principles is full SQL support (any Postgres supported SQL on
| the server, any SQLite supported SQL on the client). So we
| won't go pure IndexedDB without a database on top.
|
| There's a lot of cool stuff going on with OPFS and WASM SQLite
| atm, so the technical options are evolving fast.
|
| [1] https://github.com/rhashimoto/wa-sqlite
| jwells89 wrote:
| Will be keeping an eye on this. Are there plans for Swift/Kotlin
| clients for native iOS/Android apps?
| thruflo wrote:
| Hey, yup it's definitely a medium term objective.
|
| The team at SkillDevs are maintaining a Daft/Flutter client at
| https://github.com/SkillDevs/electric_dart
|
| Plus we have a thread to extract the core client-side
| replication component to Rust to be able to compile for
| multiple targets.
| revenga99 wrote:
| This is incredible. Could this be used for analytics? something
| like timescaledb for powering dashboards?
| caiocodes wrote:
| Congrats! I've been using it for a while, it is indeed great!
| pdhborges wrote:
| Congrats to the team. I think it is particularly impressive (and
| a bit scary) that DDL changes are also synchronized to the
| clients automatically!
| mjadobson wrote:
| I am fully on the offline-first bandwagon after starting to use
| cr-sqlite (https://vlcn.io), which works similar to ElectricSQL.
|
| I thought the bundle size of wasm-sqlite would be prohibitive,
| but it's surprisingly quick to download and boot. Reducing
| network reliance solves so many problems and corner-cases in my
| web app. Having access to local data makes everything very snappy
| too - the user experience is much better. Even if the user's
| offline data is wiped by the browser (offline storage limits are
| a bit of a minefield), it is straightforward to get all synced
| changes back from the server.
| tantaman wrote:
| Yeah, 1mb of WASM != 1mb of JS.
|
| WASM is much faster to start up since you don't have all the
| parsing and compiling overhead.
| declan_roberts wrote:
| I love this project. It's 2023 and I'm still surprised how many
| projects require an actual database running someplace to work
| locally.
|
| If the solution is to fire up a docker image with your app, then
| stop! Why not just use sqlite first?
| ThinkBeat wrote:
| I get dizzy even trying to envision all the edge cases and
| challenges making an active - active sync between two different
| database engines, over what would be tens of thousands (hundres?)
| of simultaneous connection to clients via a non-reliable, expect
| network disconnect, and reconnect from a different location from
| the same client.
|
| If you have managed to pull this off in a way that reliably
| scales and reliably keeps all the data updated and uncorrupted I
| am mighty impressed. My hat off for you folks.
|
| How do you handle transactions and rollbacks?
| thruflo wrote:
| Hey, thanks :) and yup, lots of complexity and engineering.
| We're lucky because we're building on a lot of research and
| previous work.
|
| The model is Transactional Causal+ Consistency (TCC+). This has
| been formally proven to be the strongest possible consistency
| mode for an AP database system.
|
| We have a page on literature here https://electric-
| sql.com/docs/reference/literature, which includes the Cure
| paper http://ieeexplore.ieee.org/document/7536539/ on TCC+ and
| the highly available transactions paper
| https://dl.acm.org/doi/10.14778/2732232.2732237
|
| Annette Bieniusa, Nuno Preguica and Marc Shapiro from our team
| are authors of the Cure work (amongst many other things!).
|
| Re: rollbacks, our model is to provide finality of local-
| writes: https://electric-
| sql.com/docs/reference/architecture#local-w... -- which means
| you don't have to write rollback handlers.
| orixilus wrote:
| Is MySQL support on your roadmap?
| thruflo wrote:
| Potentially, but not for a while at least.
|
| Technically it's possible to support different backend
| databases. But we want to focus on getting Postgres right
| first.
| brigadier132 wrote:
| I'm curious, CRDTs are good for having data converge in the same
| state but not necessarily ensuring that the state it converges to
| is "correct". How does this solve that?
| roncesvalles wrote:
| You can't guarantee "correctness" (if you define that as zero
| information loss and/or external causal consistency) in any
| multi-leader system partly because of unsynchronized clocks and
| partly because, well, you have to deal with the conflict
| somehow. You can think of CRDT as a tool to _minimize_ relevant
| information loss (ideally to zero) when two concurrent writes
| are converged.
| gunapologist99 wrote:
| How does this scale? For example, can you shard user DB's across
| multiple PGSQL clusters?
| thruflo wrote:
| We cover this a bit in the architecture page: https://electric-
| sql.com/docs/reference/architecture
|
| Basically Electric is horizontally scalable. You can run
| multiple Electric sync services on top of Postgres and then
| obviously many clients connecting to the Electric. We're
| focused on running on top of a single Postgres right now (which
| we take query load off).
___________________________________________________________________
(page generated 2023-09-20 23:00 UTC)