Electric (Postgres sync engine) beta release(electric-sql.com)
electric-sql.com
Electric (Postgres sync engine) beta release
https://electric-sql.com/blog/2024/12/10/electric-beta-release
71 comments
It doesn't always work, but I like the SQLite model: the core offering is free and open source, but enterprises can pay for things like
* Professional support, including on-prem hosting when applicable
* Additional features that enterprises care about (encrypted databases, SSO)
* Compliance documentation/certifications
* Professional support, including on-prem hosting when applicable
* Additional features that enterprises care about (encrypted databases, SSO)
* Compliance documentation/certifications
How big of a business is SQLite?
Maybe SQLite isn't that big, but Red Hat makes billions of dollars of annual revenue. GitLab is a public decacorn (or was one yesterday, anyways). Those are good businesses, and I'm pretty sure a good chunk of us run software from both of these.
It’s a one man company run by Richard Hipp.
Big enough to keep the developers happy to keep working on it. That is all that matters.
This is how DuckDB is structured too:
DuckDB Labs: The core contributors. Instead of developing features that will be behind a paywall, they provide support and consulting.
DuckDB Foundation: A non-profit that ensures DuckDB remains MIT licensed.
0 - https://x.com/thisritchie/status/1797962367571239309
DuckDB Labs: The core contributors. Instead of developing features that will be behind a paywall, they provide support and consulting.
DuckDB Foundation: A non-profit that ensures DuckDB remains MIT licensed.
0 - https://x.com/thisritchie/status/1797962367571239309
FWIW SQLite has 3 developers, and I don't think it's even full-time for all of them.
This can work because SQLite is deliberately a very small yet very high impact project.
Very few projects (unfortunately) can boast that.
This can work because SQLite is deliberately a very small yet very high impact project.
Very few projects (unfortunately) can boast that.
What if the DRM/license was based around offering binaries built with 8/16/32/64 bit limits in data types and max records per table, each being its own edition and priced accordingly? Eg yearly license of $8/160/3,200/640,000.
It's very creative! LOL
But in practice most my tables that are uncapped end up with IDs are 64bits and i suspect not being the only one.. 32b is in fact quite small ~ 4B rows.
And so you would pay for the largest tier as it sounds like you have big data needs? ;P Whereas my company--which only had tens of millions of users and millions of dollars a year in revenue--certainly never had any tables with more than 4 billion rows... (not that I think this licensing model works or makes any sense at all, to be clear).
This is really amazing as is their standalone pglite project, which is a WASM port of Postgres. One surprise for me though is that Electric is a read only replica. In particular, “Electric does read-path sync. It syncs data out-of Postgres, into local apps and services. Electric does not do write-path sync. It doesn't provide (or prescribe) a built-in solution for getting data back into Postgres from local apps and services.” https://electric-sql.com/docs/guides/writes
We hit on exactly the same pattern for our product and it works really well.
We use Hasura as the read engine. That updates a graph of mobx objects that drive the ui. We apply updates directly to those objects so the ui updates immediately. The mutations are posted back to a Python api that applies them to the db.
I’ve looked at Electric because we’ve had to recreate some of what they do to interface with Hasura. At the moment it’s a non-starter because we use pg views to shape the data for the frontend.
We use Hasura as the read engine. That updates a graph of mobx objects that drive the ui. We apply updates directly to those objects so the ui updates immediately. The mutations are posted back to a Python api that applies them to the db.
I’ve looked at Electric because we’ve had to recreate some of what they do to interface with Hasura. At the moment it’s a non-starter because we use pg views to shape the data for the frontend.
Have you looked into something like Materialize? That’d give you realtime updates over even materialised views, works to back Hasura etc.
Hadn’t seen that one. Thanks for the heads up. It’s the sort of thing that I could see being useful in a bunch of places. We have a number of hand rolled things to deal with similar issues.
Might not be a perfect fit here because some of the views are session context dependent (but could maybe still be workable).
Might not be a perfect fit here because some of the views are session context dependent (but could maybe still be workable).
Would love to read more about how you have mobx structured here. I have a similar graph of mobx objects for something I’m building but haven’t come up with an ergonomic sync story yet.
Mine is simple but relies on a few cheap tricks (like generating uuids in the frontend and manually linking relationships in an order where replaying on the db is going to work). Happy to walk you through it if you want to message me.
I listened to https://www.localfirst.fm/18 recently from Electric-SQL. One of the things James mentioned was that Electric lets you use commodity CDNs for distributing sync data, which takes the load off your main Postgres and servers.
This seems like a good pattern, but of lower value for a SaaS app with many customers storing private data in your service. This is because the cache hit-rate for any particular company's data would be low. Is this an accurate assessment, or did I misunderstand something?
This seems like a good pattern, but of lower value for a SaaS app with many customers storing private data in your service. This is because the cache hit-rate for any particular company's data would be low. Is this an accurate assessment, or did I misunderstand something?
Hey, one of the things here is to define shapes that are shared. If you imagine syncing a shape that is that user’s data then it may be unique. But if you sync, say, one shape per project that that user has access to and a small shape of unique user data then you get shared cache between users who have access to each project.
It’s worth noting that Electric is still efficient on read even if you miss the CDN cache. The shape log is a sequential read off disk.
It’s worth noting that Electric is still efficient on read even if you miss the CDN cache. The shape log is a sequential read off disk.
I'm curious on how you'd configure this. Is it common (and safe) to let a cdn cache private data for authenticated users?
Say Jira used electric, would you be able to put all tickets for a project behind a cdn cache key? You'd need a cdn that is able to run auth logic such as verifying a jwt to ensure you don't leak data to unauthorized users, right?
Say Jira used electric, would you be able to put all tickets for a project behind a cdn cache key? You'd need a cdn that is able to run auth logic such as verifying a jwt to ensure you don't leak data to unauthorized users, right?
Yup, you can put an auth proxy in front of the CDN, for example using an edge worker.
See the auth guide: https://electric-sql.com/docs/guides/auth
Some CDNs also validate JWTs, so the CDN can be the proxy part of the Gatekeeper pattern (in the guide).
See the auth guide: https://electric-sql.com/docs/guides/auth
Some CDNs also validate JWTs, so the CDN can be the proxy part of the Gatekeeper pattern (in the guide).
Another option too for scaling reads is just putting an nginx in your cluster.
Electric itself is quite scalable at reads too so for a SaaS use-case, you might not need any http proxy help.
Electric itself is quite scalable at reads too so for a SaaS use-case, you might not need any http proxy help.
Informing recent podcast episode with the Electric SQL founder, https://www.localfirst.fm/18.
Related:
Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps - https://news.ycombinator.com/item?id=37584049 - Sept 2023 (171 comments)
Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps - https://news.ycombinator.com/item?id=37584049 - Sept 2023 (171 comments)
Is it fair to say that if you have a high read\low write service, you could use this as a sort of local cache (with the added benefit that it also handles staleness through the sync) on the service itself, to reduce latency from not just calls to Postgres but other caches like Redis?
Right exactly — a sync engine like Electric maintains replicas of data wherever you like. So natural places are web apps, mobile apps, backend services, etc. Here's an example showing how to sync a table from PG to Redis https://github.com/electric-sql/electric/blob/main/examples/...
It's dramatically faster to read from a local copy of data vs. sending the query to Postgres and can eliminate a lot of network and db load as well.
It's dramatically faster to read from a local copy of data vs. sending the query to Postgres and can eliminate a lot of network and db load as well.
Thanks!
Just curious. They have Google listed as user. Why would a company like that be interested given that they’ve already any world class db in-house along with the expertise?
Yeah, I'm gonna need a citation on Google's usage of ElectricSQL, considering that the link points to Firebase Data Connect.
Ok, so PGLite is used in the Firebase Data Connect's emulator: a test utility, not even the client library.
It's a bit disingenuous to lump it in the same sentence and try to mislead user that Google's adopting Electric.
It's a bit disingenuous to lump it in the same sentence and try to mislead user that Google's adopting Electric.
From https://firebase.google.com/docs/data-connect/data-connect-e...:
> Firebase Data Connect provides you with a local emulator for end-to-end prototyping as well as continuous integration and continuous deployment (CI/CD) flows
Developers at Google have chosen PGLite for those qualities so that users of Firebase Data Connect had better CI/CD workflows. It wouldn't be fair to say this is an insignificant use case.
> Firebase Data Connect provides you with a local emulator for end-to-end prototyping as well as continuous integration and continuous deployment (CI/CD) flows
Developers at Google have chosen PGLite for those qualities so that users of Firebase Data Connect had better CI/CD workflows. It wouldn't be fair to say this is an insignificant use case.
hey want to get more developers on their platform and some ctos (like me) try to avoid vendor lockin. for that reason I avoid firebase. I certainly wouldn't mind setting one of these up on gcp with google's postgres offerings though
The ElectricSQL concept of 'shapes' is interesting. Right now, a shape is just a single table with some column filters and a where clause. The shape is defined on the fly in the query string. Electric does single table sync of those 'shapes' a lot like Supabase realtime.
I work as part of the team building Ably LiveSync, a competitor in this space. Our postgres connector[0] works in the opposite way by taking rows from an 'outbox' table and fanning them out to subscribed clients over websockets in realtime.
Here's the thing; for any meaningfully complicated data in a relational database the data is likely to be normalised across tables with relations, and there are going to be joins. So I'm really curious how people are making single-table-sync work. (Maybe this is where Electric imagine the future of shapes, solving for joins).
In LiveSync we do it the other way around, instead of having a live-query style subscription to a table (like ElectricSQL), we listen for someone writing a row to the 'outbox' table and that row is automatically sent to subscribers. This means that you're writing your denormlised data directly to the outbox and it's being sent on to clients, rather than writing your normalised data to tables but being limited by single table sync. Or worse, your clients having to subscribe to multiple sync streams and trying and stitch the data back together. We opted to have the write-side insert the denormalised data, rather than having the read side have to stitch normalised data back together.
Electric will at some point try and solve multi table sync, which isn't easy given how the Postgres replication protocol works. It's also not easy to imagine how shapes (which are defined on the fly right now in the query string) will adapt to multiple tables. There's going to be a tradeoff between a complex query string trying to stitch the normalised tables back together, or a 'shape' becoming an entity in Electric which would define how to stitch the normalised tables back together (which you would have to CRUD manage, and update in Electric every time your schema changed).
[0]: https://ably.com/docs/livesync/postgres
I work as part of the team building Ably LiveSync, a competitor in this space. Our postgres connector[0] works in the opposite way by taking rows from an 'outbox' table and fanning them out to subscribed clients over websockets in realtime.
Here's the thing; for any meaningfully complicated data in a relational database the data is likely to be normalised across tables with relations, and there are going to be joins. So I'm really curious how people are making single-table-sync work. (Maybe this is where Electric imagine the future of shapes, solving for joins).
In LiveSync we do it the other way around, instead of having a live-query style subscription to a table (like ElectricSQL), we listen for someone writing a row to the 'outbox' table and that row is automatically sent to subscribers. This means that you're writing your denormlised data directly to the outbox and it's being sent on to clients, rather than writing your normalised data to tables but being limited by single table sync. Or worse, your clients having to subscribe to multiple sync streams and trying and stitch the data back together. We opted to have the write-side insert the denormalised data, rather than having the read side have to stitch normalised data back together.
Electric will at some point try and solve multi table sync, which isn't easy given how the Postgres replication protocol works. It's also not easy to imagine how shapes (which are defined on the fly right now in the query string) will adapt to multiple tables. There's going to be a tradeoff between a complex query string trying to stitch the normalised tables back together, or a 'shape' becoming an entity in Electric which would define how to stitch the normalised tables back together (which you would have to CRUD manage, and update in Electric every time your schema changed).
[0]: https://ably.com/docs/livesync/postgres
In my experience, the "outbox" approach means a lot more manual work for developers. It requires developers to create a message for every type of change they want to sync to the client, and then also interpret that on the client. ElectricSQL's Shapes does a lot more work to keep the shape in sync between the client and the server, reducing the need for the developer to do that work.
You're right that "single-table sync" does have its limitations. At PowerSync we effectively support one level of "joins", and even then it's often not enough for more complex schemas. An older version of ElectricSQL did also actually have multi-table shape sync support, but I believe doing that at scale proved to be difficult.
One solution to this is often denormalizing data - either adding more denormalized columns in the existing table, or creating new tables dedicated to sync data. Conceptually, keeping these tables up to date is not that different from writing updates to an outbox table.
I'm also interested in seeing what Zero comes up with in the space. They seem to have solved doing multi-table query sync, but it remains to be seen how well that works in practice.
You're right that "single-table sync" does have its limitations. At PowerSync we effectively support one level of "joins", and even then it's often not enough for more complex schemas. An older version of ElectricSQL did also actually have multi-table shape sync support, but I believe doing that at scale proved to be difficult.
One solution to this is often denormalizing data - either adding more denormalized columns in the existing table, or creating new tables dedicated to sync data. Conceptually, keeping these tables up to date is not that different from writing updates to an outbox table.
I'm also interested in seeing what Zero comes up with in the space. They seem to have solved doing multi-table query sync, but it remains to be seen how well that works in practice.
We have a good solution in mind. Check back in a few months!
Eagerly awaiting the React Native compatible version. https://zerosync.dev/ is the main alternative I'm considering.
PowerSync (https://www.powersync.com/) is also a great alternative, and my preferred choice. SDK's available for React Native, Flutter, JS, Swift, Kotlin etc
I've heard about this one as well. I'm not sure why it seems to have less "hype" than Zero or ElectricSQL. I'm going to play with it this weekend. Thanks for sharing.
Electric works with Expo / RN now.
PGlite is coming — we have a new WASI build that is the basis for native mobile support. (It’s working in dev, but still needs some more polishing and bindings).
PGlite is coming — we have a new WASI build that is the basis for native mobile support. (It’s working in dev, but still needs some more polishing and bindings).
Sorry should have mentioned that I need PGlite or SQLite support. I am very much looking forward to PGlite on Expo/RN!
As are we :) btw checkout LiveStore if you haven’t already:
https://expo.dev/blog/local-first-application-development-wi...
It’s not fully released yet but it is now open to GitHub sponsors and it’s very cool.
https://expo.dev/blog/local-first-application-development-wi...
It’s not fully released yet but it is now open to GitHub sponsors and it’s very cool.
Oh this is very cool as well. Thank you for sharing. I hadn't heard of LiveStore.
Any thoughts on Flutter support? I believe that existed in the previous version of Electric.
What are you using for your WASI runtime?
A couple of things here:
- the WASI build is targeting WASI snapshot preview 1, and so should work in any compatible runtime.
- for the web, currently we use Emscripten, but we are considering moving to a single WASI build there as well. We'll be writing our own JS WASI shim if we do. Having full control of the JS code will help to solve some of the problems we've faced with Emscripten.
- we are also exploring a route to native where we take the WASI build and decompile it back to C. This seems a little mad, but it makes it possible to compile (with any tool chain) a native binary with a very minimal WASI-like api that can be linked to from any app. It essentially end up a little like the SQLite amalgamated header file as a build route. It's very experimental, and we haven't committed to it yet, but it looks like it may work.
- the WASI build is targeting WASI snapshot preview 1, and so should work in any compatible runtime.
- for the web, currently we use Emscripten, but we are considering moving to a single WASI build there as well. We'll be writing our own JS WASI shim if we do. Having full control of the JS code will help to solve some of the problems we've faced with Emscripten.
- we are also exploring a route to native where we take the WASI build and decompile it back to C. This seems a little mad, but it makes it possible to compile (with any tool chain) a native binary with a very minimal WASI-like api that can be linked to from any app. It essentially end up a little like the SQLite amalgamated header file as a build route. It's very experimental, and we haven't committed to it yet, but it looks like it may work.
This is interesting. Can you go into a bit more detail on why the WASM decompiling would be helpful as opposed to just using the native codebase?
Dynamic linking on iOS is complex, and Android also brings some toolchain complexities. It would be possible to do a native build and link it, and that is a route we are also exploring, but a single C file that can be linked with any existing toolchain would simplify things for users.
It also allows us to implement a VFS layer underneath PGlite in a native mode. So things like the in-memory VFS, or a custom VFS, would be possible.
We have not committed to one route or the other yet.
It also allows us to implement a VFS layer underneath PGlite in a native mode. So things like the in-memory VFS, or a custom VFS, would be possible.
We have not committed to one route or the other yet.
You should check out Triplit as well, we have React Native support[0] and the best Typescript integration by far
0. https://www.triplit.dev/docs/frameworks/react-native
0. https://www.triplit.dev/docs/frameworks/react-native
Another one I have to play with. Thanks for sharing.
I may be misunderstanding, but does this boil down to essentially a partial read replica?
It's way more powerful than that, since every active user session can have its own shaped & subsetted subscription to live data. See https://electric-sql.com/demos/proxy-auth for an example of how you could build a simple authorization proxy that allows each client session to build a customized long-poll of their own subset of relevant data, directly connecting to the Electric engine, and https://electric-sql.com/demos/react for how simple the frontend code can be.
I fondly remember the days of Meteor (before the pivot to Apollo), where you'd give up SQL and in return be able to give every user a real-time live-updating data model, kept in sync with a secure subset of the central MongoDB database. Now, you don't need to give up the SQL part, nor are you locked into an entire ecosystem. We're going to see really cool things built on this.
I fondly remember the days of Meteor (before the pivot to Apollo), where you'd give up SQL and in return be able to give every user a real-time live-updating data model, kept in sync with a secure subset of the central MongoDB database. Now, you don't need to give up the SQL part, nor are you locked into an entire ecosystem. We're going to see really cool things built on this.
It's closer to Firebase on Postgres, in my opinion.
Isn't that just Supabase? I don't think Electric has much to do with PaaS like Firebase or Supabase.
[deleted]
[deleted]
I tried using this a bit in an earlier version, when there was an option to have a real SQL client library/ORM, and you declared what part of the schema would exist client side. Is there plans to bring this back?
The part where you could use drizzle client side was really what interested me, I don't want to bother learning another new query language apart from SQL.
The part where you could use drizzle client side was really what interested me, I don't want to bother learning another new query language apart from SQL.
Hey, it's possible to use PGlite on the client, along with Drizzle. Our "Linearlite" example show this (albeit without Drizzle) with a "write through the database" pattern to sync back to the server.
Live demo: http://linearlite.examples.electric-sql.com
Code: https://github.com/electric-sql/electric/tree/main/examples/...
Live demo: http://linearlite.examples.electric-sql.com
Code: https://github.com/electric-sql/electric/tree/main/examples/...
Is this basically a read-only partial replica? I wonder if it would be possible to sync the changes back to the upstream.
Caught a few typos on your site as well fyi, https://triplechecker.com/s/153718/electric-sql.com
Caught a few typos on your site as well fyi, https://triplechecker.com/s/153718/electric-sql.com
Hey this is great to see, congrats on the launch!
I've been researching 'local first' solutions like electric recently and tried out powersync, triplit and instant for now. All three of these solve for both reading and writing to databases, with offline support.
Wondering if you have plans to support writes too.
I've been researching 'local first' solutions like electric recently and tried out powersync, triplit and instant for now. All three of these solve for both reading and writing to databases, with offline support.
Wondering if you have plans to support writes too.
Check out our guide on writes — https://electric-sql.com/docs/guides/writes
There's a variety of valid patterns for writes & we don't want to be prescriptive about how you do them. We aim instead to help you easily use Electric with your preferred/required write pattern.
It's possible we'll write our own write library but it's equally likely we'll continue to find it better to partner with other projects building excellent libraries (like we are with https://livestore.dev/).
There's a variety of valid patterns for writes & we don't want to be prescriptive about how you do them. We aim instead to help you easily use Electric with your preferred/required write pattern.
It's possible we'll write our own write library but it's equally likely we'll continue to find it better to partner with other projects building excellent libraries (like we are with https://livestore.dev/).
Super exciting! I've been following LiveStore development, and been looking forward to a beta launch. I didn't know Electric was sponsoring LiveStore.
This looks very interesting. Do you have any plans to implement a postgres extension, so that a client postgres can natively sync shapes from an upstream postgres? Very similar to your pglite replica, but for native postgres.
Is there any software that let me make graphical user interface to a connected database, allows me to make data visualizations, all things automatic and interactive?
Like a node editor or spreadsheet?
In theory Retool would be this, but it's clunky for the interface designer - namely, it assumes you might have different custom read and write paths, so it makes you jump through hoops in setting up a Cell Change Handler linked to a Query that writes to the database. See: https://community.retool.com/t/auto-save-table/16044
Airtable's interface builder is more reasonable in that it ensures that you're always looking at real-time data, and saves happen and propagate to all users whenever you change a character - but this requires that the canonical data live in Airtable. And setting up sync with external systems is clunky. I'm hopeful that people build better tools on top of Electric, because it's ridiculous that we live in 2024 and we don't have interfaces like this out of the box.
Airtable's interface builder is more reasonable in that it ensures that you're always looking at real-time data, and saves happen and propagate to all users whenever you change a character - but this requires that the canonical data live in Airtable. And setting up sync with external systems is clunky. I'm hopeful that people build better tools on top of Electric, because it's ridiculous that we live in 2024 and we don't have interfaces like this out of the box.
Sounds like PowerBI
I am going to try this, it looks very interesting
[deleted]
Super excited!
Why ?
Efforts like electric make development of local/offline-first apps easier which (IMO) is generally a good thing - although it appears they're moving towards more a generic sync system.
Before these sorts of projects, you'd have to roll your own custom sync engine which I've found to be surprisingly difficult when you factor in multiple devices.
Before these sorts of projects, you'd have to roll your own custom sync engine which I've found to be surprisingly difficult when you factor in multiple devices.
My concern with DB startups is always the business model. There’s a massive tension between open source and a sustainable business which is much more prevalent with deeply technical products. So either it’s too open and dies because aws eats their lunch or it’s too closed to be useful for self-hosting, or too complex to self-host, or priced too high for small players. What’s the strategy to build/grow the business in the short-medium term?