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.

Conceptually sounds like this is what Firebase, Couchbase Lite, and Mongo Reach do in the NoSQL world.

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?

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

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...

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.

This is awesome. I love the pattern, and the way you’ve built for this as a first-class concern is great.

You could store a SQLite database in a blob column for the user, though this would be an affront to $DEITY.

One cool feature would be to encrypt local SQL database with the user's key before syncing. The provider of the PostgreSQL database would have zero-access encryption while still having full SQL capabilities on the client.

;)

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

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?

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.

Since you are here, I would love to understand what are the conceptual and architectural differences between ElectricSQL and PowerSync.

ElectricSQL stores additional data in the source PostgreSQL database, while PowerSync stores this in a separate data store in its sync service. Because of this, ElectricSQL requires schema changes and has some schema restrictions on the source database.

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.

Both these assumptions are incorrect.

We maintain consistency and integrity across shape boundaries.

Thanks for the clarification

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!

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.

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?

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.

> you can't like "fold in" arbitrary changes to a CRDT without following the rules.

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.

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.

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.

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.

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.

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).

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 all-but 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.

Agree with all of these points. Well put.

[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

Oh, thanks for this, I hadn't heard of RFC 9420 until now and it looks very interesting.

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.

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?

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.

> 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.

> 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

I understand it'd be necessary to implement all auth and check rules in SQL queries using roles and the CHECK statement.

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 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/

Can you please stop posting as much about PowerSync on a Show HN thread centered on ElectricSQL?

You are really taking the expression `shameless plug` to another level in this thread, aren't you?

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.

> Genuinely excited about this space

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.

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.

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.

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...

Row-level permissions also very useful - e.g. I am allowed to update my own profile, but not someone else's.

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?

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 Preguiça 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.

Congrats.

How does this deal with schema changes? We have a solution where old clients can store objects that have new fields unknown to the client. Then when the clients updates the data is visible.

Do you have client libraries for mobile? Swift & Kotlin?

Does it support partial sync and fetch when you need an “archived” item? In my experience you only want the “working set” of data on the client. Not everything.

Cool work! CRDTs are the solution to this.

It works similarly: migrations flow as part of the replication stream and clients apply them immediately, so that new data fits the local schema. New columns and etc might not immediately be exposed to the client app, but everything will works for old clients.

You can learn more about our support for migrations here: https://electric-sql.com/docs/usage/data-modelling/migration...

Native clients will be coming a bit later.

You can use the shapes to control what data syncs to the device: https://electric-sql.com/docs/usage/data-access/shapes

I was also going to ask about mobile clients, then saw your comment.

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.

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.

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.

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.

Can you talk about the dataset sizes you support? Your example linear app has 112 rows, could you support 1000? 10K? 100k? 1M?

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.

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.

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.

We wrote a bit about SQLite implementations and associated performance considerations for web here: https://www.powersync.co/blog/sqlite-persistence-on-the-web

You’re saying you have partial sync already working for browser sized datasets say 5k rows which are an arbitrary subset of server records which are much larger? What limitations are there on the query criteria for client-synced records, can the subset be expressed as an arbitrary join? How do you implement local first on arbitrary join?

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/

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...

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.

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

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

Congratulations to James and the team! I reached out to James when they were searching for early adopters for their V2 version, which focuses on PostgreSQL. Our primary business involves developing Line of Business (LOB) applications. We decided to transform our LOB applications into low/no-code platforms to empower our customers with extensive customization needs to be self-sufficient.

We assessed nearly every new data solution available and ultimately chose Electric. We recognized that the combination of SQLite's convenience on the client-side and PostgreSQL's flexibility on the server-side is a potent foundation for our work.

I've spent years grappling with the challenges of the local-first (lo-fi) approach, particularly when using the PouchDB <-> CouchDB combination to support vital offline features in our Point of Sale tools. While not every feature aligns with the lo-fi concept (even though real-time synchronization status on the client can significantly reduce the need for numerous API end points), having PostgreSQL on the backend grants us access to a wealth of existing tools and support built around PostgreSQL. This assurance provides us with peace of mind, knowing that we won't encounter insurmountable obstacles, and any challenges we face will be no more daunting than those encountered in traditional server-based API programming paradigms.

I sincerely hope that ElectricSQL achieves the recognition it deserves and can implement many of the ideas outlined in their roadmap documentation. These ideas have the potential to overcome the initial hurdles that have hindered the widespread adoption of the lo-fi approach.

In the meantime, we're having a great time developing an opinionated, React-based low-code tool (internally, we refer to it as "Visual Basic using React"). We're looking forward to leveraging some of the outstanding work done by James' team thus far!

I just looked at the size of Sqlite in the browser as sql.js compared to WASM Sqlite in the broswer. sql.js seems to be over 2MB and WASM sqlite in just 900k, is that right? If so then does this make WASM Sqlite quite a good contender to replace things like Alasql in the browser (which is around 400k). Anyway views on this ?

Did I just break their conflict-free sync?

https://i.imgur.com/MiEWHA9.png

Thank you for playing with our demos and finding this issue!

We'd welcome very much if you could reproduce the steps and report an issue: GH: https://github.com/electric-sql/electric/issues Discord: https://discord.com/channels/933657521581858818/107968886985...

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.

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!

Hmm, yes, seems like we might need to bust a cache key there :)

Fixed!

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.

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?

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

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.

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.

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!

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!

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.

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).

Congrats to the team. I think it is particularly impressive (and a bit scary) that DDL changes are also synchronized to the clients automatically!

I see OPFS being mentioned a few times by team members. I could not find any estimates on the website, but could any of the folks in the know share any details? I work on an Electron-based text editor which uses the filesystem to manage text files. Something like this would really be useful to me if it supported plain text files.

Product looks excellent, and your website is gorgeous. Kudos!

It looks like the "Switch from X" buttons don't go anywhere.

Also noticed this! Seems like missing `href` on the "Find out how »" buttons

Do you have any plans on releasing an Ecto Client? Basically What I would like to do is deploy my phoenix app at the edge, and have a postgresql database centralized in some other region. Let's say for example my centralized postgresql is in us-west-2 and the client running in ap-southeast-1.

Would something like this be possible?

Will be keeping an eye on this. Are there plans for Swift/Kotlin clients for native iOS/Android apps?

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.

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...

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.

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.

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.

> 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.

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.

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?

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.

I get that which is why I was curious how ElectricSQL got this to work because SQL databases generally do have a "correct" state.

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?

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.

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.

Congrats! I've been using it for a while, it is indeed great!

Congrats to the team. Very nice project.

I'm interested in your calculation for compute cost savings. You have savings of 40k$ for 2k queries per second. I'm currently using GCP CloudSQL (Postgres) and have > 2k requests per second (for tables with billions of rows) for 200$/month. Is there a scenario that I'm overlooking?

Edit: Saw the explanation on your website. Apparently you're comparing it to AWS Aurora which charges for the amount of queries.

Yup, it's not the most sophisticated of cost calculator, obviously there's a wide variation in cost savings from reducing query and compute load and the reduction is app specific.

edit: we based some of the calculator variables on https://fauna.com/blog/compare-aws-aurora-serverless-v2-arch...

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!

Unrelated to Electric Clojure (https://electric.hyperfiddle.net/)

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

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.

Because synchronizing data with multiple users updating data in a distributed manner then reconciling it is not trivial.

This has long been a dream data stack of mine. Best of luck.

Wow, awesome project and team you've got here! I've been dreaming of something like this for years, so it's super cool to see you guys making it happen for everyone.

Quick question about the business side of things: I saw you're Apache 2.0 and VC-backed. So what's the plan for making money down the line?

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.

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).

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".

> '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.

The docs indicate[1] that electric's syncing logic, "shapes", are handled transactionally. How would electric handle an initial sync of 500mb or an initial sync of 10gb?

  [1]: https://electric-sql.com/docs/usage/data-access/shapes#data-loading

Apps with large dataset have their own characteristics: they are more tolerant to slower initial sync. We can resume replication at point-in-time, so even if a shape takes long to load, we can resume replication at the point we started synching the shape.

We will be working on the engineering to handle transferring large shapes.

Pretty interesting, what's the benefit of using a check expression rather than row level security?

We do row (and column) level security, the check expression provides an optional additional granularity for controlling which roles can make certain writes

Congrats to the team. I’ve tried https://rxdb.info/ and it wasn’t funny at all to do the remote replication (PG) and to deal with conflicts. I do need to check this one out!

Would this work as a tool to sync a central server's parts of your postgres data to your distributed webapp backends local-copy, as opposed to all the way to user front-end. i.e. nodejs instead of the browser.

Or is there a better tool for that?

yes, ElectricSQL works for webapps with nodejs: https://electric-sql.com/docs/integrations/drivers/server/no...

I am extremely, extremely excited about this.

I was wondering: what is the difference between VLCN and ElectricSQL?

Thank you!

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

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?

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.

How do you deal with schema migrations, and software updates (SQLite or Postgres)?

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?

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?

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.

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?

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

Is there a server-side validation layer to guard against users bypassing client-side validations and business logic that are not explicitly enforced in the database schema?

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?

This (and powersync) are genuinely so exciting for the react-native ecosystem. This really is the missing piece of the puzzle that we've been waiting for.

Wow! Congrats, this is an amazing project, it makes me interested in developing something just to use it!

This looks incredible. I can certainly see this gaining traction with increased support for other DBs and clients. Great work from these folks!

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.

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.

Any specific reason it wouldn't work with Angular as well (noticed it was not listed in the Frontend frameworks section)?

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.

What does the local client side look like? Could it be wrapped in a node daemon to sync to a local SQLite file?

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.

Ah that's fantastic.

This is incredible. Could this be used for analytics? something like timescaledb for powering dashboards?

> Writes automatically cause any relevant live queries to update.

How do you decide what queries are relevant?

https://github.com/electric-sql/electric/blob/main/clients/t...

  // Once we have electric, we then establish the data change
  // notification subscription, comparing the tablenames used by the
  // query with the changed tablenames in the data change notification
  // to determine whether to re-query or not.
  //
  // If we do need to re-query, then we use the saved function to reuse the query

So it re-runs queries on all updates in tables from the query.

How does this scale? For example, can you shard user DB's across multiple PGSQL clusters?

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).

Congrats on the launch! How does the sync between Postgres / SQLite works?

Does it support partial sync ?

I haven't checked the details yet but could this be used in react native?

Is the SQLite embedded in the browser?

It is in Firefox.

The SQLite team has been working on making WASM support first class, so should work anywhere WASM does.

How do you access the SQLite database in Firefox? Is there docs you can point me to?

Ok, so are you saying that currently Electric SQL only works with Firefox, but NOT with Google Chrome?

Can't speak to that, only that Firefox embeds SQLite. WASM support should make it ubiquitous in browsers.

Is MySQL support on your roadmap?

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.

Amazing!

Congrats to the Team!!!

Congrats to the team

demo request: TodoMVC. No cheating!

postgresql has conflict-free multi-master sync now? That's incredible work, can't believe I missed it!

https://www.researchgate.net/publication/359578461_Continuou... has entered the chat

That's a great reference. Thank you for sharing.