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

11–20 of 173 posts

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

#11

Post by FrancoisBosun »

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…

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.

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

#12

Post by samwillis »

alooPotato wrote: post #6

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.

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

#13

Post by 3523582908 »

thruflo wrote: post #7
3523582908 wrote: post #2

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

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?

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

#14

Post by thruflo »

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-level write validation and will apply general write validation when we get there.

James.

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

#15

Post by koolba »

FrancoisBosun wrote: post #11
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…

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.

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

#16

Post by thruflo »

3523582908 wrote: post #13
thruflo wrote: post #7

Earlier quoted context omitted.

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

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.

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

#18

Post by thruflo »

koolba wrote: post #15
FrancoisBosun wrote: post #11

Earlier quoted context omitted.

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

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

#19

Post by slashdev »

samwillis wrote: post #12
alooPotato wrote: post #6

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…

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.

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

#20

Post by pgm8705 »

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.
Post reply on HN