I'm a solo developer of Inkdrop, a Markdown note-taking app for programmers which supports macOS, windows, linux, android and iOS. It is built on top of Electron for desktop platforms and React Native for mobile platforms.
I'm currently working on rebuilding full-text search feature as I declared in our roadmap.
This article is my work progress note on making it.

To implement a full-text search feature, I would like to try using FTS of SQLite3. FTS is a full-text search engine for SQLite3. There are multiple versions: FTS3, FTS4 and FTS5. I'm gonna try FTS5.

Use node-websql to have a compatibility between mobile (React Native) and desktop (Electron).
It depends on node-sqlite3 so it works well on Electron apps.

First, I would like to try this module out from nodejs.

Trying FTS5

It worked like a charm out of the box!

const openDatabase = require('websql')

var db = openDatabase('mydb.db', '1.0', 'description', 1)

db.transaction(txn => {
  txn.executeSql('DROP TABLE IF EXISTS notes_fts')
  txn.executeSql(
    'CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(note_id, body)'
  )
  txn.executeSql(
    'INSERT INTO notes_fts (note_id, body) VALUES (:note_id, :body)',
    [1, 'Hello, world!']
  )
  txn.executeSql(
    'SELECT * FROM `notes_fts` where body MATCH :keyword',
    ['hello OR hoge'],
    function(tx, res) {
      for (let i = 0; i < res.rows.length; ++i) {
        console.log('hit:', res.rows.item(i))
      }
    }
  )

  txn.executeSql(
    'SELECT * FROM `notes_fts` where body MATCH :keyword',
    ['worl*'],
    function(tx, res) {
      for (let i = 0; i < res.rows.length; ++i) {
        console.log('hit2:', res.rows.item(i))
      }
    }
  )
})

It supports forward matching, OR operator and even NOT operator...that's awesome.

How about mobile platforms?

Android doesn't support FTS5 by default

Seems like it needs to install requery/sqlite-android: Android SQLite support library.
But according to this page: sqlite - SQLite3 FTS4, MATCH and Android - Stack Overflow
FTS4 can be used since API >= 11.
Confirmed it works in emulator!

iOS supports FTS5 by default

That's a good news. According to this article: Leveraging SQLite Full-Text Search on iOS | Inside PSPDFKit
FTS5 is supported from iOS 11.

Difference between FTS5 and FTS4

Overall, the FTS5 module has much better performance than its predecessors. However, it is missing some functionality when compared to FTS4, in that the auxiliary functions to extract information about matches aren’t as fully featured. Functions like matchinfo() and offsets() do not exist, and the snippet() function is not as useful as in FTS4.

So, the conclusion is to go with FTS4. But if possible, it would be better to switch to FTS5.

Sync index with pouchdb

Inkdrop's database is implemented with PouchDB.

Use Changes feed to sync the index for FTS.

Try UPDATE first, if failed, then INSERT.

txn.executeSql(
  'UPDATE notes_fts set body = :body where note_id = :note_id',
  ['Hello, world!', 1],
  (txn, res) => {
    if (res.rowsAffected === 0) {
      console.log('The row does not exist. Trying inserting.')
      txn.executeSql(
        'INSERT INTO notes_fts (note_id, body) VALUES (:note_id, :body)',
        [1, 'Hello, world!'],
        (txn, res) => console.log('inserted:', res)
      )
    }
  }
)

Or, it would be good to SELECT first to check if the row exists because SELECT query is smaller.

To be continued.