This flexibility is convenient when only one application reads and writes to the table. But if multiple applications access the same tables, the lack of a strictly enforced schema becomes a liability. The same is true when using generic tools to process data in SQLite tables, because such tools don't know what type of data to expect. The column type may be X but the actual data may be of type Y.

O'Reilly's "Relevant Search" isn't the worst here, but you'll be porting/writing a bit yourself.

Is there a reason why something more custom built, like ParadeDB Community edition won't meet your needs?

I understand you're speaking about SQLite, while ParadeDB is PostgreSQL but as you know, it's non-trivial to get good search quality, so I'm trying to understand your situation and needs.

I very, very much did. I was using a Python package that used a lot of NumPy internally, and sometimes its return values would be Python integers, and sometimes they'd be NumPy integers.

The Python integers would get written to SQLite as SQLite integers. The NumPy integers would get written to SQLite as SQLite binary blobs. Preventing you from doing simple things like even comparing for equal values.

Setting to STRICT caused an error whenever my code tried to insert a binary blob into an integer column, so I knew where in the code I needed to explicitly convert the values to Python integers when necessary.

    with thing_key as (
    insert into item(key, description) values('thing', 'a thing') on conflict do nothing )

    insert into user_note(uid, key, note) values (123, 'thing', 'I like this thing') on conflict (uid, thing) do update set note = 'I like this thing');

Why is it a problem that it allows data that does not match the column type? SQLite is intended for embedded databases, where only your application reads and writes from the tables. In this scenario, as long as you write data that matches the column's data type, data in the table does match the column type.

Does anyone have any other favorite modern bloom-filter-based search solutions that dont need to store copies of all the documents in the search db? Ideally something that can run in WASM too so we can ship a tiny search index to the browser. I found https://github.com/tinysearch/tinysearch but haven't tried it yet.

https://www.sqlite.org/fts5.html#fts5_prefix_queries

All of the listed features except for strict tables and generated columns have been in SQLite for 10+ years, and those two are certainly not new. The JSON APIs were not made part of the standard distribution until 3.38 (2022-02) but were added in 3.9 (2015-10) and widely used long before they were upgraded from an optional extension to a core feature.

- Generated columns: 3.31 (2020-01)

- Strict tables: 3.37 (2021-11)

    SELECT payload->>'$.user.id' AS user_id FROM events ...

I just got hit badly by Dreamhost, which is still running MySQL 8.0. That version has a "JSON extension" which is almost totally useless. I designed something before discovering how different MySQL 8.4 and MySQL 8.0 are.