Support for `readonly` streams

[BierDav]

I have the need that there are certain complex views (like in my case a transitive closure table that helps me resolving supervisors of a company member) which I only want to read in my app. I want to avoid having to write the same logic required for realtime data view and caching twice, so what about adding a readonly flag that you can set to streams which will behave in following ways different to conventional streams:

• Don't require an id column, because this only required to process changes
• Doesn't generate triggers for the views in the sqlite database, so that they basically natively become read-only
• Allows the usage of materialized views
• Maybe some of the other query constrains such as joins can be loosend up to, but a materialized view always can solve this usecase

To me it seams quite easy to implement actually especially when considering that it would only need some changes in the rust library.

[simolus3]

Don't require an id column, because this only required to process changes

We also use the id column to de-duplicate rows that might be part of multiple stream subscriptions at once (which, depending on how parameters are used, can happen unintentionally). So we need a stable id even when only considering downloads.

I like the idea of a "remote only" flag as an inverse localOnly on the client side to skip generating triggers. But there's a potential mismatch between setting this flag on a table and the stream (you could have a regular stream on a table and another readonly stream on the same table for instance).

[BierDav]

Your point with id makes sense, maybe in future support for composit primary keys could be considerd, but adding a surrogat key is a quite easy workaround for now.

Hmmm I'm not sure if I understood you correctly, but my thoughts were: For readonly streams we mustn't rely on the underlying table name, because ultimately when it comes to joins we don't have a reliable source for the table name anymore. Instead we can just use the stream name as table name, therefore we don't have a mismatch if there are other writeable subscriptions to the same table.

What so you think?

[rkistner]

Agreed with @simolus3 here - we can add support for a readOnly flag per table on the client.

This would be a client-side change - making data read-only doesn't simplify anything on the service. It means it won't change any of the current service limitations:

1. It can't be defined on the sync stream, since (1) it would have to be enforced on a per-table level (and you can have multiple sync streams for the same table), and (2) the client needs to know about the read-only restriction upfront, before even connecting to the service.
2. We still need an unique id per row for syncing. Note that with [raw tables]9https://docs.powersync.com/usage/use-case-examples/raw-tables#raw-sqlite-tables-to-bypass-json-view-limitations) you don't actually need to use this on the client, but you still need it defined as the service uses it to de-duplicate tables.
3. This won't affect the current lack of support for views and joins in sync queries (although this can be worked around using pg_ivm in some cases, and we have some plans to add built-in support for that in PowerSync).

Hmmm I'm not sure if I understood you correctly, but my thoughts were: For readonly streams we mustn't rely on the underlying table name, because ultimately when it comes to joins we don't have a reliable source for the table name anymore. Instead we can just use the stream name as table name, therefore we don't have a mismatch if there are other writeable subscriptions to the same table.

We still need a physical table name to write to on the client. If you want something other than the source table name, for example to get an unique name per stream definition, you can use a table name alias: SELECT * FROM some_table AS my_synced_table_name.

Also note that you can currently get similar behavior by filtering out changes in your uploadData function: The client would still be able to make the changes locally, but it would be reverted as soon as the next sync completes.

[BierDav]

Makes a lot of sense, thanks. I was just a bit confused at adopting because of the limitations of m:n:n connections. Now I built an automated solution for ivm views on postgres. But to be frankly it would be nice to get support for some basic join tasks in sync streams, because the need to create a new materialized view for each deep node table is not only waste of storage, but also in my opinon unneccessary complexity. Is there no way to analyze a query in a way to understand how rows are joined together and then listen for two or more tables for one query?

[BierDav]

I actually don't need this readOnly flag anymore, but you might just add it for feature completeness 🤷