Watermelondb: Raw Queries

[developerium]

Hi

Is there anyway we can run custom queries on database?

Is there anyway we can access database directly?

[bsr203]

I used to be able to connect to the sqlite database through https://github.com/facebook/stetho

you can see the instructions at https://facebook.github.io/react-native/docs/debugging#debugging-with-stetho-http-facebookgithubio-stetho-on-android

But, not sure what changed, I cannot see my WatermelonDB database though stetho anymore, but other sqlite dbs. I like to know how people normally does it as well.

[radex]

Is there anyway we can run custom queries on database?

Not currently. What exactly are you trying to achieve?

[developerium]

I want to make a report on several tables, a sum report on multiple joined tables.

[radex]

https://github.com/Nozbe/WatermelonDB/blob/master/src/adapters/sqlite/index.js#L162

this is the code that executes the query. The query will pass through encodeQuery to generate SQL. You could maybe extend Query or make a new RawQuery object or something like that to be able to pass raw SQL. Contributions welcome.

If you want a quick & dirty hack (but I warn you that it's very dirty, might not work in the future or at all, and you don't have observability, and it might be unsafe):

import { NativeModules } from 'react-native'
import { sanitizeQueryResult } from '@nozbe/watermelondb/adapters/common'

const tag = database.adapter._tag
const bridge = NativeModules.DatabaseBridge

const dirtyRecords = await bridge.query(tag, 'select from ...')
const rawRecords = sanitizeQueryResult(dirtyRecords, appSchema.tables['name_of_table'])

const records = database.collections.get('name_of_table')._cache.recordsFromQueryResult(rawRecords)

does this help?

[developerium]

I definitely need this feature (and the migration), can you give me some advice on how to implement this?
If I find the time, I like to contribute

[radex]

@MrTinyByte hey, still interested in contributing to this? Did you try out the hack I described above?

[developerium]

I'm currently on another project, but will try it whenever I get back to this

[psolom]

I was looking a solution for SQL order and limit support, and found the explanations why it's not an easy thing considering observables, e.g. #15. While this is true for observables there are cases when you just need to fetch some DB entity out of the observables context. My particular case is that I need to find first entity sorted by timestamp with fetch() method. If we can't get it with {Q} then raw queries would become a solution.

Thanks to @zobeirhamid we have PR #199 and, if it's ok, I would vote for it to be merged.

@radex take a look this PR please.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[sursma]

I am working heavy data intensive application, need often orderby limit, max etc. ops, either orderby and limit support or raw query support would be great!

[radex]

@sursma Take a look at the open PR :) It's been stuck for a while, maybe you can take it over and finish up so we can merge it?

[sursma]

@radex I wrote a wrapper on top of raw query syntax its working well for me :-)

const rawQuery = async (db, tableName, sqlQuery) => {
try {
const tag = db.adapter._tag
const bridge = NativeModules.DatabaseBridge
const dirtyRecords = await bridge.query(tag, sqlQuery)
const rawRecords = sanitizeQueryResult(dirtyRecords, mySchema.tables[tableName])
const records = db.collections.get(tableName)._cache.recordsFromQueryResult(rawRecords)
return records
} catch (err) {
return []
}
}

[psolom]

import { NativeModules } from 'react-native'
import { sanitizeQueryResult } from '@nozbe/watermelondb/adapters/common'

const tag = database.adapter._tag
const bridge = NativeModules.DatabaseBridge

const dirtyRecords = await bridge.query(tag, 'select from ...')
const rawRecords = sanitizeQueryResult(dirtyRecords, appSchema.tables['name_of_table'])

const records = database.collections.get('name_of_table')._cache.recordsFromQueryResult(rawRecords)

does this help?

Doesn't work anymore.
Can you force PR #199 please?

[radex]

@servocoder should work with minor changes. #199 has been sitting there for a while, nearly done. I do encourage you to take over the PR with the finishing touches discussed there and contribute if you'd like to have this available :)

[psolom]

I'd already have it done if I had time this :)
Anyhow I'm looking forward this feature

[radex]

shipped in 0.15