Mobile app with locally stored, synchable, searchable read-only content. Sqlite?

Question

I'm working on a (Cordova iOS/Android) mobile app with <100MB worth of slowly-changing, read-only (to the user) data that needs to be searchable (full text and facets). Coming from a web background, we'd usually serve the data from a web API and database. However, we'd like the app usable in offline mode. Given that the database could fit comfortably on the device and we don't need to be real time updates for most of our datasets, I would like to look into what we could do to pre-position the content on the device. We're a subscription service, so this data is behind a paywall (which I believe limits our options with Core Spotlight/Firebase App Indexing).

Given that whatever we come up with has to be cross-platform, I've focussed on Sqlite (possibly a custom built one bundled with the app). It has full-text search and we can filter by related facet data.

Here's the approach I'm investigating:

1. Daily/hourly job to rebuild the content set from upstream data.
2. Reindex new data in the FTS tables.
3. Save the sqlite database in web accessible bucket.
4. Notify apps of a new content set.
5. They download changes (or the full database).

Is there an existing service or library that does this already?

Fossil seems like it does a lot of this, but it's not widely used. I can't find a case where it's been used as a content repository on a mobile app. Also, we don't need to push changes back up from the device, so I don't know if we'd be taking on a lot of overhead for a feature we don't need.

Most other services that provide synchronization and offline (AppSync, Firebase) also seem to be overkill for our needs and more focussed on being multi-writer, real-time backends.

Answer 1

I had to research this very same problem recently and the two options I came up with were

1. Couchbase Lite
   • Commercial
   • NoSQL
   • Local full text search
   • Built in conflict resolution and syncing with Couchbase
2. SQLite
   • Free and open source
   • SQL
   • Local full text search (you only get fts4 by default but as you say you can compile and bundle a version with fts5)
   • Conflict resolution and sync is up to you

I went with option 2 (SQLite) because I didn't have complicated sync requirements, it's a hobby project and the data is pretty slow moving. From my evaluation of Couchbase though I'd definitely recommend considering it.

Neither Firebase nor AppSync provide full text search offline.