Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

electric-sql.com

Post reply on HN
Print view View original

161–170 of 173 posts

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#161

Post by rmbyrro »

thruflo wrote: post #14
koolba wrote: post #9

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 arb…

Hey, You can see our database rules spec here: https://electric-sql.com/docs/api/ddlx We haven't implemented it all yet but you can see the intention / direction. It's similar to RLS but adapted for the context. Connections are authenticated by signed JWT: https://electric-sql.com/docs/usage/auth We also auto-generate a type-safe data access client from the electrified subset of the Postgres schema. This applies type…

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?

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#162

Post by luxcem »

canadiantim wrote: post #32

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 bu…

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.

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#164

Post by vbalegas »

luxcem wrote: post #162
canadiantim wrote: post #32

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 bu…

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.

;)

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#166

Post by irq-1 »

kiitos wrote: post #122
irq-1 wrote: post #107

Earlier quoted context omitted.

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…

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.

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#168

Post by prepor »

prepor wrote: post #167

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

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#169

Post by ochiba »

lifty wrote: post #156
ochiba wrote: post #106

Earlier quoted context omitted.

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.

Re: Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps

#170

Post by hruzgar »

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?

Post reply on HN