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.
Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
21–30 of 173 posts
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#22Earlier quoted context omitted.
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…
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.
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.
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#23Congrats 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.
Thanks :) and yup, definitely, we were digging into the typed-sql repo just the other day.
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#24Is 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 arb…
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…
> 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.
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#25Congrats 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!
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#26Congrats 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.
Thanks :) and yup, definitely, we were digging into the typed-sql repo just the other day.
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.
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#27Earlier quoted context omitted.
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.
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 pointe…
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.
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#28Will be keeping an eye on this. Are there plans for Swift/Kotlin clients for native iOS/Android apps?
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#29Congrats to the team. I think it is particularly impressive (and a bit scary) that DDL changes are also synchronized to the clients automatically!
Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
#30Are 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.