If I merge local only data into a powersync raw table, will syncs replace all that data?

[galacticgibbon]

Context: I use views derived from some local tables and some powersync raw tables heavily for polymorphic queries, using UNION ALL. UNION ALL seems to break indexes, so the queries using these can't be well optimised.

One way around this for me is to merge the tables, and only trigger powersync_crud for the non-local rows. This might work if powersync doesn't replace all the rows in a table when it downloads to client, is that what would happen?

[rkistner]

Do you have details on how UNION ALL breaks the indexes? I'd expect that indexes should still work for filters, but may be less efficient with ORDER BY ... LIMIT.

It is technically possible to have local-only and synced rows in the same raw table, but it's your app's responsibility to manage it. The SDK only uses the "put" and "delete" queries you provide for raw tables (and support for providing a query to "clear" the table also being rolled out soon) - no other queries would be run against your table automatically. If you go this route, I'd recommend adding an additional column to indicate whether or not the row is being synced, which you can use both as a filter in the powersync_crud trigger, and to queries you provide to the raw table definition. For example:

mySchema.withRawTables({
  // The name here doesn't have to match the name of the table in SQL. Instead, it's used to match
  // the table name from the backend database as sent by the PowerSync service.
  todo_lists: {
    put: {
      sql: 'INSERT OR REPLACE INTO todo_lists (synced, id, created_by, title, content) VALUES (true, ?, ?, ?, ?)',
      params: ['Id', { Column: 'created_by' }, { Column: 'title' }, { Column: 'content' }]
    },
    delete: {
      sql: 'DELETE FROM lists WHERE id = ? AND synced = true',
      params: ['Id']
    }
  }
});

[galacticgibbon]

Awesome 👍