WatermelonDB: Infinite List Questions, Memory Usage

@Kenneth-KT You’re right, my suggested workaround has real flaws, which depending on the exact use case may be fatal. To be clear: I’m not against adding SQL-level limiting/ordering to WatermelonDB, I just don’t need it for my use cases, so I’m not going to drive the implementation of it. But if this is key to you, I would highly encourage you to consider contributing those, even in a limited form. I can offer guidance. And yes, @azizhk’s PR is another useful workaround for large lists. Using raw SQL queries is also a great option, if you have a good way to know when to trigger re-fetch. Yes, records fetched using SQL are still 🍉 objects and you can observe them normally.

@azizhk

wanted to ask you what is the cache bursting strategy in 🍉?

None. Observable objects are cached on Query, Relation, but only for as long as they’re observed. And Collection has a cache of { id : Record } objects. Once object with given ID is fetched, it’s going to stay there forever.

My initial plan was to implement a two-level cache, keeping Records in a Map for the first X (100? 200?) records, and then move oldest records to a WeakMap, so that they’re cached only as long as some piece of the app actually keeps a reference to it. If memory usage is important to your app, try contributing this improvement - it may help a lot if you’re dealing with a lot of Records

Do you think that complete handling is necessary for a PR to be accepted?

No, as long as you clearly convey that the API may change or not fully work, e.g. Q.experimentalSortBy().

I didn’t have much luck with JOINing tables with raw SQL queries, so I guess it’s not supported, or maybe there was something wrong with the syntax I used, however it’s not documented so I’m not sure. Could you clarify if it’s possible and if it’s not at the moment should I open an issue about it ?

You can do any SQL which results in a list of records. (SQLs that result in a number or a list of numbers or something like that are not supported). You can do JOINs - check out tests for encodeQuery, you can see what built in join queries produce for SQL.

Please don’t open issues for unimplemented features – but please do send improvements in pull requests 😃

Would it make sense to have it in a different API for it in order not to conflict with the normal API ? Something like mango-queries rxdb has?

No, I don’t like the idea of maintaining two different query syntaxes. I think the current query builder can be extended to add limits and order

Hi @radex, So we’ve been working on some POCs. We wanted to know your opinions. Wondering if it would be easy for 🍉 to provide only a few columns like id and the columns needed for sorting:

const cols$ = database.collections
  .get("table")
  .query(Q.where("name", Q.like(sanitizedInput)))
  .observeOnlyColumns(["id", "updated_at"])

It should follow the same functionality as observeWithColumns. Here cols$ is Observable<Array<[string, Date]>>

const sortedIds$ = cols$.pipe(
  map(rows => rows
    .sort((a, b) => a[1] - b[1])
    .map(r => r[0])
  )
)

Then we sort these on the basis of the columns in JS, and pass it to the FlatList along with a RenderItem Function which returns a component wrapped using withObservables to query the model based on id.

Would this solve the problems of #113 and #15 while still being reactive? This would help reduce our memory footprint.