Raw tables: mandatory untyped column for migration suggestion

[galacticgibbon]

I've been using raw tables for a while now and they work well. However, I'm now adding a column to my schema and I noticed that there's not a clean way to go about this yet. I was thinking one solution to this could be for raw tables to have a mandatory untyped column which powersync throws everything into where a column can't be found yet.

Essentially then this works in a similar way to ps_untyped and can be migrated from down the line.

[galacticgibbon]

Or even just a separate table managed by powersync, doesn't need to be a column on your own table.

[rkistner]

It's not released yet AFAIK, but we added support for exactly that (a "rest" column) in the core extension recently:
powersync-ja/powersync-sqlite-core#158. It's opt-in rather than mandatory (which could be a problem if you realize too late you should have added it before doing migrations), but it does essentially what you describe if you do use it.

Also see the general migration strategy ideas in this doc - any comments there would be appreciated.

[galacticgibbon]

Yeah I like this approach!

The only thing I wonder is if users need to add 'rest' to their own raw tables. Perhaps that could be managed internally by powersync e.g. ps_rest__RAWTABLENAME. Maybe there's a small dx improvement there.

I remember this comment that Simon made and it sounded nice - the bit about defining tables with 'remaining_data BLOB'. My impression was that it would lead to a similar migration free experience as the schemaless views, while having the performance of raw tables. I'm wondering if this avenue has been explored already? #333 (comment)

[rkistner]

The remaining_data idea is essentially the same thing that is implemented with thr "rest" column now.

You're right that doing this automatically in a separate table would save on the effort of configuring this manually, but it could also add some overhead versus storing it in the same table. There could be an option to do that automatically but only if the table doesn't contain a "rest" column.

Do also note the other migration options listed in the doc - someone of them also get very close in terms of outcomes, without any need to store those extra columns upfront.

[galacticgibbon]

I think I can do powersync_trigger_resync(); in my case since my new column is nullable. Is that the same as UPDATE ps_buckets SET last_applied_op = 0

[simolus3]

In terms of sync behavior, I believe it is. We would probably also update the sync status in a trigger_resync() function which SET last_applied_op = 0 doesn't.