This is hugely underrated in my opinion: it’s a very competent search engine.
It also ships as part of the Python standard library, so if your machine has Python installed you have a high quality search engine ready to use without installing anything else.
Yes you're exactly correct. They were worried I'd had my laptop compromised or stolen but after a little bit of back and forth I think they just thought it was funny and restored my access
I have been using it as my only searching engine for my blog in years and it’s much better than anything else I’ve thrown at it. For bonus points, peewee ORM supports it directly.
Unfortunately it still works very poorly for languages with compound words (German, Swedish, Finnish) where you would usually want to surface search results if someone uses the second or third part of the compound word, otherwise the user has to enumerate the (often too many) variants. Meilisearch doesn’t support suffix search and their tokenizer can’t handle these languages.
For instance searching for ‘___missilestrike’ in Kyiv would ideally surface all incidents, regardless of launch mode (surface-to-surface, air-to-surface…), range, propulsion, warhead, or guidance system. In these languages a number of these would form the prefix of that compound word.
it's more about the tech it uses... it's oriented toward docker based deploy and is made in rust so maybe it can be ported to iOS, I'd have to spend time forking the packaging. looks like it's geared toward server environments
I'd go as far as claiming that the WASM port of SQLite with FTS5 enabled makes SQLite the best general purpose client-side search engine currently on the market. And with the option to further customize it with custom tokenizers and auxilliary functions, you can do pretty advanced stuff with it, rivalling Lucene (not completely though, e.g. FTS5 does not store offsets, so highlighting is more expensive than in Lucene).
I've been trying something similar, but using https://github.com/mmomtchev/sqlite-wasm-http to stream the database over http for a SPA without a backend. It's actually able to do searches (for queries that aren't super short) without downloading the entire FTS table.
I once tried to use sql.js [1] on a static site for full text search. It worked, but the resulting database size for that site was too large for the web, even with things like detail=none and content='' applied, and requiring the user to download a database each time was just no go. (I guess things should work better for sites with less content or those not requiring a trigram tokenizer.)
I switched to Pagefind [2] afterwards before finding out a sql.js-httpvfs [3] fork of sql.js that removes exactly the need to fully download a database (with HTTP range requests). I haven't got the chance to test sql.js-httpvfs out though, but it looks pretty sound and could be much more flexible than Pagefind. (Previously discussed at https://news.ycombinator.com/item?id=27016630 .)
Does anyone have successfully worked with Non-English text with FTS5 in Sqlite? I could not find any reference for German, e.g. and the default stemming does not seem to work properly (given some short tests).
> Does anyone have successfully worked with Non-English text with FTS5 in Sqlite? I could not find any reference for German, e.g.
We use it in the Fossil SCM project and users have reported success with Chinese and Russian, so it presumably works fine with any European/Germanic language.
> and the default stemming does not seem to work properly (given some short tests).
The Porter Stemmer is documented as only being useful for English.
It has pretty much the same support for other languages as most text mining tools and Elasticsearch via the snowball stemmer: https://github.com/abiliojr/fts5-snowball
Should work well for German, I’m using it with Nordic languages.
Does anybody know how to make it work with UUID as primary keys? I got stuck on that a while back and it randomly corrupts when using rowid. Couldn't figure out a solution other than migrate my schema to use a sequential integer instead.
We are running FTS5 with a bunch of sqlite databases for an internal search tool, one with 15m records (we sharded that one, one shard per core), others with 200k-2m records. Speed is very fast -- the biggest dbs are hundreds of ms response time, but others are sub 10 or tens of ms. By _far_ the simplest search option for a small organization where a lot of the data is already in a SQL db, and we don't have the resources to stand up and maintain an ES cluster.
I’m interested in the sharding approach myself but those hundreds of ms sound a bit slow.
Do you have to do anything special to guarantee each sqlite instance executes in parallel with the others on its own core? Does the language you’re calling them from support concurrency or parallelism?
I just did some tests and the perf is actually worse than that — but it’s actually still perfectly fine for our use cases.
- 15m row db will return a search for an extremely common term in ~3s (9m rows have that term);
- When adding terms, so that only ~200k rows have the combination, response is ~2s
- That 15m db was in 16 shards. (I’m actually not sure why it should be so slow; doesn’t actually make sense given that a single 1m row db is not that slow, might look into it but not a huge priority)
- For a ~200k row db, search is ~150ms for three terms that appear in ~1.5k entries
- Obviously the network io is the slower piece anyway
This perf is fine for our use case — a research system for internal users. Certainly worth the trade-off of not having to deal with a more complex database like elasticsearch, or even PG with the tantivy extension (which we might switch to some day).
For sharding — we only shard the huge one. To make sure bm25 rankings are still _roughly_ the same as they would be if we did not shard, we just randomised the rows assigned to the num_core shards. It’s a 16 core machine so 16 shards.
We know/ensure each db file gets its own core because we use aioprocessing in python. It handles both multicore and async. Running a query with htop shows all the cores light up (unlike with unsharded).
It’s all about trade-offs in the end. We cache the first search then pre cache the next page (and so on) so the user only has to wait 2-3s when searching the big db for that initial search and after that it’s pretty snappy. Most searches are on much smaller dbs (thousands to hundreds of thousands of rows) and results there are often 10ms or something. No user complaints so far.
FTS5 works great. The only issue I had is that the syntax for the queries is quite irregular. Had problems both with understanding it exactly and translating to it from the usual format of space delimited keywords, exact strings in quotes, etc.
I wish it would have an API instead where I could put the parts of the query exactly (as nodes in a tree) without the need to translate to this irregular syntax.
Has anyone tried this out on mobile? I have a list of app ideas that all center around shipping a big database to a device and allowing complex querying while offline.
It is slower than Realm in some benchmarking (I forget if it’s reads or writes) and might be less memory friendly (Realm does lazy evaluation). You need to use some third party open source to make sqlite faster by precompiling model definitions, which I didn’t bother with. I currently only use it for fts5 and Realm for other needs, but would like to try using it for more.
I also recommend looking at https://skip.tools which has cross platform SQLite for iOS and Android (you write your app in swift and SwiftUI and it generates the Android project and kotlin code)
Btw I use GRDB in my iOS/macOS app here: https://reader.manabi.io Manabi Reader, a Japanese learning app. I use SQLite for dictionary searches which works ok for Japanese only because I’m only searching dictionary expressions and not sentences
Not sure if I'm holding it wrong, but when I played with it I nowhere got results that are anything remotely like a proper search engine (thinking of Algolia, Elasticsearch, etc.).
It was good at finding the text I was searching for but in terms of ranking them, it felt like it needed a lot of post processing.
Not by default, no. But you could kind of implement it by providing a custom tokenizer that emits multiple terms for the same position in the document, with different variants of the same token. This would not be "proper" fuzzy search, but might be enough depending on the use case.
I don't think that will work for me, since I needed something that can handle mistakes in the words, like Du'ha to duha etc, Rahman to rehman, basically whatever looks closest.
One thing you could do: FTS5 has the `fts5vocab` virtual table [1] that has all the terms. You could provide a user-defined function that computes the levenshtein distance between your query terms and the terms in that table, obtain candidate terms that way and build a big query that searches for all those lexically close terms.
I can confirm an approach like this works in practice.
Although instead of levenshtein I use spellfix (maybe it uses that under the covers? not sure). If there is no match from the first search, I use the sqlite spellfix extension [0] to find matches. Then feed those candidates into the terms.
Ah, that's great, didn't know about that :-) It seems to use a similar edit-distance algorithm under the hood, and the docs explicitely mention integration with the FTS extension, so this is probably the way to go!
It also ships as part of the Python standard library, so if your machine has Python installed you have a high quality search engine ready to use without installing anything else.
I have a CLI tool (and Python library) for working with it here: https://sqlite-utils.datasette.io/en/stable/cli.html#configu...