[FEATURE]: queryMetadata with SQLite relational queries

### Feature hasn't been suggested before.

- [x] 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](https://orm.drizzle.team/docs/cache#limitations-that-are-temporary-and-will-be-handled-soon).

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](https://github.com/drizzle-team/drizzle-orm/blob/ad4ddd444d066b339ffd5765cb6ec3bf49380189/drizzle-orm/src/sqlite-core/query-builders/query.ts#L147-L161), 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: https://github.com/powersync-ja/powersync-js/pull/765