Executing SQL synchronously via JSI

[scarlac]

A few other discussions have touched on this, but in transitioning from Redux(-persist) to WatermelonDB, it would be extremely helpful to be able to temporarily execute raw SQL queries synchronously. This allows a gradual transition to proper model-based/Q-based WatermelonDB queries.

This is roughly what I've come to so far:

// Where the database is set up and initialized:
export const adapter = new SQLiteAdapter({ ... });

// Where the synchronous query is needed:
const _db = adapter._dispatcher._db;
const res = _db.unsafeQueryRaw(`SELECT id, bar FROM foo`, []);
console.log('sql', res);

Any guidance on how to do it more smoothly / catch any pitfalls? So far I've been trying to reverse engineer it all but the amount of adapter code makes it unclear what code I'm actually calling and the need for decoding/encoding parameters, results, caching, etc.

I found that simply calling _db.query(...) was an issue because the result gets cached so the second the the query runs, the resultset turns into a JSI cache "id" instead of the actual data.
(Ideally a public API would be great, but I understand the reasoning from another issue where this was requested)

Some specific questions to get started:

• How do I get the cached result back?
• Is there a way to access the adapter's _db without using the private fields like this?

Any guidance would be helpful.

[scarlac]

I'm sharing my solution, as it may be handy for others doing a transition. It assumes the availability of:

1. const adapter = ... referring to the SQLiteJSI adapter
2. const schema = ... referring to the appSchema definition

/**
 * Execute a raw SQL 'SELECT' query to the SQLite database.
 * You must always start with 'SELECT *' (all columns) to allow hydration (relation data => object)
 * SELECTs are safe, but UPDATE, DELETE, and INSERTs must follow up with a call to rawClearCache()!
 * otherwise data goes stale in other places
 * @param table Table name, used as part of the cache key (keyed as tableName@rowId)
 **/
export function rawQuery<T = any>(table: string, sql: string, args: any[] = []) {
  const _db = (adapter as any)._dispatcher._db;

  // Call adapters/sqlite/makeDispatcher/index.native's SqliteJsiDispatcher's "this._db" directly
  // (it calls directly into native using JSI)
  // Call @nozbe/watermelondb/adapters/sqlite's adapter (aka dispatcher) directly
  // WatermelonDB uses a queue and a 'dispatcher'. The db adapter dispatches to the queue,
  // and then the adapter later picks up each query, processes it, and calls the callback
  const res = _db.queryAsArray(
    table,
    sql,
    args,
  );
  const decoded = decodeQueryResult(res);
  const sanitizedResults = sanitizeQueryResult(decoded, schema.tables[table]);

  const collection: any = database.collections.get(table);
  const out = collection._cache.recordsFromQueryResult(sanitizedResults);

  return out as T[];
}

And combined with a cache clearing hack, it can be helpful:

export function rawClearCache(table: string, ids: string[]) {
  const _db = (adapter as any)._dispatcher._db;

  // Clear native cache by faking a "batch update" that does nothing
  const CACHE_BEHAVIOR = -1; // -1 means 'delete' from cache
  const ops: any[] = [];
  ids.forEach(id => {
    ops.push([CACHE_BEHAVIOR, table, `UPDATE "${table}" SET id = ? WHERE 1 = 0`, [[id]]]);
  });
  _db.batchJSON(JSON.stringify(ops));

  // Clear JS object cache
  const collection: any = database.collections.get(table);
  collection._cache.unsafeClear();
}

[PEZO19]

@scarlac
Thanks for your sharing your solution!
May I have some update on that? Anything you run into / a better versions you are using since?

For others, I'd like to add these imports to make your solution work:

import decodeQueryResult from '@nozbe/watermelondb/src/adapters/sqlite/makeDispatcher/decodeQueryResult/index'
import { sanitizeQueryResult } from '@nozbe/watermelondb/src/adapters/common'

I made it work for myself like that, with id instead your proposed *:

// somewhere where I do need synchronous/blocking access to db data
const synclyFetchedModels = rawQuery(`myTable`, `SELECT id FROM myTable WHERE id = 'recordId'`) // only 1 model in array in that case
rawClearCache(`myTable`, ['recordId'])

[PEZO19]

I'd also like to add, that this also works for me based on @radex's comments:

let data_to_fill_blockingSynchronously

const myQuery = undefined // your arbitrary Query
database.adapter.underlyingAdapter.unsafeQueryRaw(myQuery.serialize(), (raw_result)=>{
  // sanitization / cache things might come here...?
  data_to_fill = raw_result
})

I like this one better due to better UX (Query API instead raw SQL) and it seems to me somehow it does not mess up caches so no need to reset.

Please let me know if I am missing something.
(Also wondering about use cases regarding .underlyingAdapter.unsafeQueryRaw vs .underlyingAdapter.unsafeExecute?)

[radex]

@scarlac

db.get('table')._unsafeFetchRaw(query, results => { ... })

is the simplest (but undocumented and without any guarantees of API stability) way of synchronously getting results for a raw sql query

[radex]

No, synchronous under JSI

[PEZO19]

@radex is there any way (even undocumented) to

1. synchronously WRITE to db (eg. with jsi) - eg. can _unsafeFetchRaw be abused for that?
2. having a subscription which emits/updates in the same "event cycle" like the WRITE in point 1?

I am wondering if that's possible for such scenarios OR for simple sync writing/observing key-value pairs it's better to introduce something like mkkv (+ custom reactive wrapper).

[radex]

@PEZO19 Yes, you can use adapter's unsafeExecute to execute arbitrary SQL - https://github.com/Nozbe/WatermelonDB/blob/master/src/adapters/type.js#L22

Observation is executed synchronously so if you mess around with it you should be able to achieve that, but there isn't necessarily a good API for it exposed, depending on what exactly you're looking for

[PEZO19]

Thanks @radex! However, is there any not-too-abusing way to fetch the underlying db (maybe with underlyingAdapter) synchronously, without any involvement of any Promise on the js side? (unsafeExecute returns a Promise, which is not good enough for me for my use case, as I'd like to "block" entirely.)

[radex]

right, underlyingAdapter is the way (the Promise-based API is a convenience/legacy/compatibility thing)