Want help generating ideas for adding full-text search

[SuperSonicHub1]

Hello again! For my Mastodon client, I want to implement full-text search so that people can easily find statuses and accounts stored in stored. This is not as easy as I thought it would be due to search indexes implemented in JavaScript varying in quality, features, and the structure of their indexes. I am going to leave here a few libraries I've been investigating and the problems I've been having with them; feel free to find anything I missed.

Library Requirements

• I should be able to add, update, and remove documents at runtime
• Stemming (if you want to throw in fuzzy search as well, feel free, but fuzzy search isn't a replacement for actual ranking functions)
• Index serialization should be easy to upsert into TinyBase

Issues with TinyBase

TinyBase doesn't really have a listener for knowing if individual rows in a table have been added, updated or deleted. My mind goes to SQLite's CREATE TRIGGER. I think something like below would be a good solution to the problem:

enum RowChange {
  Add, 
  Update,
  Delete
}

type RowChangeListener = (
  store: Store,
  tableId: Id,
  rowId: Id,
  changeType: RowChange,
  newRow: Row | null,
  oldRow: Row | null
) => void

interface Store {
  addRowChangeListener(tableId: IdOrNull, listener: RowChangeListener): string
}

Another issue is that our ID system is somewhat incompatible with the wider JavaScript ecosystem. For us, tables look like {[rowId: Id]: Row}, while most expect a table to look like (Row & {id: string})[]. Of course, we can resolve this with something like:

function convert (table: Table): (Row & {id: string})[] {
  return Object
    .entries(table)
    .map(([id, row]) => {
      return {...row, id}
    })
}

But then there's the concern of having id be a column in your row and then overwriting it; likely sweating small stuff here.

MiniSearch

MiniSearch is pretty nice, but its serialization index is not very normalized, and it duplicates data already in our database in storedFields. If you used a few tables to store everything and recreated the index at runtime, you could overcome these issues, but it wouldn't necessarily be trivial.

interface SerializedIndexEntry {
    [key: string]: number;
}

type AsPlainObject = {
    documentCount: number;
    nextId: number;
    documentIds: {
        [shortId: string]: any;
    };
    fieldIds: {
        [fieldName: string]: number;
    };
    fieldLength: {
        [shortId: string]: number[];
    };
    averageFieldLength: number[];
    storedFields: {
        [shortId: string]: any;
    };
    dirtCount?: number;
    index: [string, {
        [fieldId: string]: SerializedIndexEntry;
    }][];
    serializationVersion: number;
};

search-index

This library is quite powerful. It supports custom backends, but I'm not sure if TinyBase, a relational database built on top of JSON datatypes, is a good fit for a key-value store which holds binary blobs.

[beorn]

There are a bunch of others that are popular:

https://npmtrends.com/@leeoniya/ufuzzy-vs-@orama/orama-vs-elasticlunr-vs-flexsearch-vs-fuse.js-vs-lunr-vs-minisearch-vs-search-index

I found benchmarking lists are also usedful, e.g., flexsearch's and uFuzzy's. uFuzzy's even has a way to try each of the listed search indexes.

[stelcodes]

Would it be technically possible to add a search keyword function to "TinyQL" to return only rows matching a text search on one or more columns?

There could also be a search_fuzzy variant as well.

Maybe a local search index could be generated as a method on the Tinybase store object upon creation?

This seems like a problem that would best be solved in Tinybase internally. It would be an incredible addition IMO.

[jamesgpearce]

I always had ideas for search (if only to be able to run a search feature on the docs website!) thinking it might be something like the Indexes API. It hasn't surfaced as a priority but I'd love to get back to something fun like that soon!