Making a full-text search module that works on both desktop and mobile (Pt. 1)
Tags: sqlite3, reactnative, electron
Published: 2018-12-14 11:24:42

---
title: Making a full-text search module that works on both desktop and mobile (Pt. 1)
published: true
description: My work progress note on making a FTS module for my app
tags: SQLite3, React Native, Electron
---

I'm a solo developer of [Inkdrop](https://inkdrop.app/), 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](https://blog.inkdrop.info/the-roadmap-of-inkdrop-vol-3-a32fc0cc42d).
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](https://github.com/nolanlawson/node-websql) to have a compatibility between mobile (React Native) and desktop (Electron).
It depends on [node-sqlite3](https://github.com/mapbox/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!

```js
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](https://github.com/requery/sqlite-android).
But according to this page: [sqlite - SQLite3 FTS4, MATCH and Android - Stack Overflow](https://stackoverflow.com/questions/6339022/sqlite3-fts4-match-and-android)
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](https://pspdfkit.com/blog/2018/leveraging-sqlite-full-text-search-on-ios/)
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](https://pouchdb.com/guides/changes.html) to sync the index for FTS.

Try `UPDATE` first, if failed, then `INSERT`.

```js
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.