Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps (electric-sql.com)
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 Preguiça, 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.
172 comments
[ 3.7 ms ] story [ 342 ms ] threadI was wondering: what is the difference between VLCN and ElectricSQL?
Thank you!
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
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?
With Electron / Tauri it's the same as the browser. You're running in Chromium / native WebView on the front-end side.
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).
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.
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.
[1] https://docs.powersync.co/
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?
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
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?
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...
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.
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.
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
What's the alternative in cases where I need more advanced checking before doing an INSERT/UPDATE that is not possible in SQL?
This is usually done in the backend. The frontend is not a trusted environment.
So, I'd just send a request to the backend, perform the checks, modify the data in Postgres and then it'd sync to the clients?
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/
You can only be genuinely excited if you don't need to add a link to your product when talking about it. This one was actually your most interesting reply, I shouldn't have posted about the high amount of mention to your own competitor project here.
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.
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.
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.
* 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!
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.
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?
Thanks. I was thinking a few simple rules, like last outside change wins, or monitoring changes and recording the time. But that wasn't realistic.
A separate service still seems like a big win, but I'll have to assume there are similar reasons for it being tightly tied to Postgres.
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.
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.
Also is there a way to transition an existing Postgres data base into using electicsql?
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
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...
We’d definitely be interested in chatting through the reactivity model you use if you’d be up for it.
ElectricSQL uses CRDTs for merging changes, via the Electric backend. PowerSync records changes per transaction on the client and lets the developer write changes to their database, which can be customized with custom write logic, validations, and access control.
ElectricSQL uses “shapes” (currently a WIP feature) defined on the client to partition data, while PowerSync uses server-side “sync rules”. The sync rules approach allows data to be transformed and filtered using basic SQL queries, which we expect to scale well (benchmarks pending). PowerSync guarantees consistency over all synced data, while it appears like ElectricSQL only guarantees consistency within each shape*. PowerSync also automatically adds and removes sync buckets when changes are made to either the sync rules or data, while ElectricSQL likely* requires you to subscribe and remove shapes yourself.
ElectricSQL streams schema migrations to the client. Part of the PowerSync design philosophy is that each client version should have a static schema. PowerSync syncs “schemaless” data, then applies a client-side schema (typically a mirror of the Postgres schema, but can be customized) on top of that using SQLite views.
*We’re making some assumptions about ElectricSQL where the documentation is not clear - please correct us if we got something wrong.
We maintain consistency and integrity across shape boundaries.
This is perfect to create super fast offline first apps like linear.app, with Postgres
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!
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.
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.
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.
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.
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).
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...