4 ms·

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 silo

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?

thruflo 3y ago
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 https://electric-sql.com/docs/usage/data-access/shapes James
- appplication 3y ago
  This is awesome. I love the pattern, and the way you’ve built for this as a first-class concern is great.
- aidos 3y ago
  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/typescript/src/frameworks/react/hooks/useLiveQuery.ts#L189C1-L189C1 https://github.com/electric-sql/electric/blob/main/clients/t...
  - thruflo 3y ago
    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.
ochiba 3y ago
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 https://docs.powersync.co/usage/sync-rules
- afavour 3y ago
  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?
  - ochiba 3y ago
    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.
    - lifty 3y ago
      Since you are here, I would love to understand what are the conceptual and architectural differences between ElectricSQL and PowerSync.
      - ochiba 3y ago
        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.
candiddevmike 3y ago
You could store a SQLite database in a blob column for the user, though this would be an affront to $DEITY.
luxcem 3y ago
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.
- vbalegas 3y ago
  ;)

thruflo 3y ago

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 https://electric-sql.com/docs/usage/data-access/shapes James

appplication 3y ago
This is awesome. I love the pattern, and the way you’ve built for this as a first-class concern is great.
aidos 3y ago
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/typescript/src/frameworks/react/hooks/useLiveQuery.ts#L189C1-L189C1 https://github.com/electric-sql/electric/blob/main/clients/t...
- thruflo 3y ago
  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.

ochiba 3y ago

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 https://docs.powersync.co/usage/sync-rules

afavour 3y ago
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?
- ochiba 3y ago
  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.
  - lifty 3y ago
    Since you are here, I would love to understand what are the conceptual and architectural differences between ElectricSQL and PowerSync.
    - ochiba 3y ago
      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.

candiddevmike 3y ago

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

luxcem 3y ago

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.

vbalegas 3y ago
;)