[FEATURE]: queryMetadata with SQLite relational queries

[rkistner]

Feature hasn't been suggested before.

• I have verified this feature I'm about to request hasn't been suggested before.

Describe the enhancement you want to request

For PowerSync, we're using Drizzle with a custom SQLite adapter, based on Drizzle's sqlite-core. In general we support running read queries concurrently with a write by utilizing WAL mode and a connection pool, and we're now making this work with our Drizzle adapter as well.

The main difficulty is that we need to know upfront whether a query is a read-only query, or could write to the database. We can't use the SQLiteExecuteMethod (run/all/get) for this, since you can have db.insert().returning()-style write queries that use all. Luckily, with the added cache support in #4447, there is now a queryMetadata field for prepared queries that includes the query type (select/update/delete/insert), and this appears perfect for this use case.

Now the only issue is that while this is currently set for db.select() queries, it is not set for db.query relational queries. The same limitation is also mentioned for the cache.

Is it feasible to set the same queryMetadata for these relational queries? Or is there perhaps a different way we can distinguish these queries from writes? It seems like most write queries do set the queryMetadata, but I can't tell whether that gives 100% of write queries, and when in doubt it's safer to assume the query can write.

A simple solution could be to just add queryMetadata: { type: 'select', tables: [] } here, but I'm not sure what the impact would be of that empty tables array, and couldn't see a quick way to populate it correctly.

For reference, our implementation using queryMetadata is here: powersync-ja/powersync-js#765