"unsafe use of virtual table" when creating triggers for sqlite full text search (fts)

I am attempting to make use of SQLite's full text search (FTS) extension in an external content table by following the official documentation. I am running sqlite version 3.42.0 from the Arch linux package sqlite, currently on version 3.42.0-1.

The schema is as follows:

-- Create main data table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Create fts index table
CREATE VIRTUAL TABLE users_fts USING fts5 (
    content='users',
    content_rowid='id',
    name
);

-- Create insert trigger
CREATE TRIGGER users_fts_insert AFTER INSERT ON users
BEGIN
    INSERT INTO users_fts (rowid, name) VALUES (new.rowid, new.name);
END;

-- Insert some sample data
INSERT INTO users (rowid, name) VALUES
(NULL, 'John Smith'),
(NULL, 'John Doe'),
(NULL, 'Jane Doe');

When running the above in sqlite3, the INSERT step fails with the error message:

Parse error near line 21: unsafe use of virtual table "users_fts"

If I comment out the trigger block it successfully creates both tables and inserts values into the main table. Curiously, the FTS table is also seemingly created with matching rows, and the rows appear to be kept synchronized across various INSERT/UPDATE/DELETEs, although the actual FTS functionality via MATCH does not work (e.g. SELECT * FROM users_fts WHERE users_fts MATCH 'John' returns no rows).

As I don't want to have to deal with manually keeping the two tables in sync, I would really prefer to have an automatic solution based on triggers such as the above. Is the issue in how I am setting up the trigger, or in how I am configuring the FTS table? Or perhaps there's some hidden limitation in SQLite FTS that precludes this kind of trigger?

Any help here would be greatly appreciated!

Solution

This is a side effect of the TRUSTED_SCHEMA pragma being set to false.

The pragma was added in Sqlite 3.31, and is documented as defaulting to true, but 3.42 now has the sqlite3 shell program disable it by default. It appears to be a side effect of a new option, --unsafe-testing. Without that option being present, several settings including this one are toggled from their defaults.

Happily, connections to databases in user code aren't affected, just ones made with the standard sqlite3 program. So you can invoke it with --unsafe-testing, or run the following command after starting the shell (Every time; it's not persistent):

PRAGMA trusted_schema=1;