Testing iOS FTS5 Search: Tokenization, Escaping and Index Sync

A customer renames a ticket. The list shows the new title, but offline search still matches the old words. This kind of bug is easy to miss when a studio rotates between cloud Macs for builds: the project compiles and the database opens, yet the full-text index has drifted out of sync with the application table. Start with a fixed test dataset to verify the search contract before delivery. Testing against production data can provide additional observations, but it should not be the only acceptance criterion because the results are difficult to reproduce consistently.

Check the SQLite Runtime Actually in Use

Run sqlite3 :memory: 'SELECT sqlite_version();' in the cloud Mac terminal and record the version used for the test. Then try creating an FTS5 virtual table. If creation fails, stop there—do not mistake “no search results” for a tokenization problem.

The command-line tool only verifies the test environment. An iOS project may link against the system library or bundle its own SQLite, so you must also execute the same table-creation statement through the connection the app actually uses. Add this check to the test target and run it on the target operating system. SQL-only samples are useful for quick iteration on a Mac, but they cannot replace results from the mobile runtime.

Define Tokenization and Query Semantics

First, choose between “literal phrase search” and “advanced search syntax.” The former suits a standard ticket search box; the latter suits tools that explicitly support Boolean expressions. Do not mix both behaviors in a single input field without explaining the distinction.

unicode61 remove_diacritics 2 handles common Latin diacritics, but it is not a general-purpose Chinese tokenizer. At a minimum, the samples should cover case differences, diacritics, hyphens, quotation marks, and Chinese text without spaces. Passing English tests does not establish support for arbitrary word searches in Chinese.

SQL parameter binding and MATCH escaping operate at different layers. Parameter binding prevents input from becoming part of the SQL structure, but it does not disable MATCH's own query syntax. To construct a literal phrase, first double any internal double quotes, then wrap the result in a pair of double quotes:

func literalFTSQuery(_ text: String) -> String? {
    let trimmed = text.trimmingCharacters(in: .whitespacesAndNewlines)
    guard !trimmed.isEmpty else { return nil }
    return "\"" + trimmed.replacingOccurrences(of: "\"", with: "\"\"") + "\""
}

Bind the returned value to WHERE ticket_fts MATCH ?; do not concatenate it into SQL. “Literal” still involves tokenization here—it does not mean byte-for-byte substring matching. For empty input, return an empty result set or the default list defined by the product.

Search matching rules are a product contract. Prefix, phrase, and substring searches are not interchangeable. Nor should syntax errors be swallowed and presented as “zero results.”

Keep an External-Content Index in Sync with Triggers

An external-content table is a good fit for a project that already has an application table and does not want to store the body text twice. It is not a fit for an implementation that expects FTS5 to monitor changes to that table automatically. content only declares the relationship; inserts, deletes, and updates still need to be synchronized.

Run the following directly in the terminal. All data stays in memory, and any failed assertion causes the command to return a nonzero exit status:

sqlite3 -bail :memory: <<'SQL'
CREATE TABLE ticket(id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE VIRTUAL TABLE ticket_fts USING fts5(
  title,
  content='ticket',
  content_rowid='id',
  tokenize='unicode61 remove_diacritics 2'
);

CREATE TRIGGER ticket_ai AFTER INSERT ON ticket BEGIN
  INSERT INTO ticket_fts(rowid, title) VALUES(new.id, new.title);
END;

CREATE TRIGGER ticket_ad AFTER DELETE ON ticket BEGIN
  INSERT INTO ticket_fts(ticket_fts, rowid, title)
    VALUES('delete', old.id, old.title);
END;

CREATE TRIGGER ticket_au AFTER UPDATE ON ticket BEGIN
  INSERT INTO ticket_fts(ticket_fts, rowid, title)
    VALUES('delete', old.id, old.title);
  INSERT INTO ticket_fts(rowid, title) VALUES(new.id, new.title);
END;

CREATE TEMP TABLE assertion(ok INTEGER NOT NULL CHECK(ok = 1));

INSERT INTO ticket VALUES(1, 'Café delivery');
INSERT INTO assertion
  SELECT count(*) = 1 FROM ticket_fts
  WHERE ticket_fts MATCH '"cafe"';

UPDATE ticket SET title = 'Invoice approved' WHERE id = 1;
INSERT INTO assertion
  SELECT count(*) = 0 FROM ticket_fts
  WHERE ticket_fts MATCH '"cafe"';
INSERT INTO assertion
  SELECT count(*) = 1 FROM ticket_fts
  WHERE ticket_fts MATCH '"invoice"';

BEGIN;
DELETE FROM ticket WHERE id = 1;
INSERT INTO assertion
  SELECT count(*) = 0 FROM ticket_fts
  WHERE ticket_fts MATCH '"invoice"';
ROLLBACK;

INSERT INTO assertion
  SELECT count(*) = 1 FROM ticket_fts
  WHERE ticket_fts MATCH '"invoice"';

DELETE FROM ticket WHERE id = 1;
INSERT INTO assertion
  SELECT count(*) = 0 FROM ticket_fts
  WHERE ticket_fts MATCH '"invoice"';
SQL

Deleting an index entry requires the old text, so the update trigger removes the old entry before inserting the new one. Application data changes and trigger operations share the same transaction; after a rollback, both the application table and the index should be restored together. The sample passes if it finishes without error output. There is no need to inspect tables in the terminal manually.

Verify Existing Data and Sort Order Too

If a database's application table predates its search index, installing triggers will not automatically index the existing records. During migration, execute INSERT INTO ticket_fts(ticket_fts) VALUES('rebuild'); within a controlled write boundary, then verify matches using fixed records that existed before the migration. Rebuilding is a migration step, not something to run every time the search screen opens.

Do not use SELECT count(*) FROM ticket_fts to judge whether the index is complete: without MATCH, an external-content table may read directly from the content table. Use assertions against actual search queries to verify separately that old words no longer match and new words do.

When ranks tie, add the application's primary key as a stable secondary sort key, for example ORDER BY rank, rowid. Pagination samples should include several results with the same score. Otherwise, changes in order across repeated runs can easily be mistaken for missing data.

Turn Failures into Delivery Checks

For each delivery, record the SQLite version, table-creation parameters, sample version, and names of failing test cases. Do not store customers' actual search text. Add samples for empty strings, punctuation-only input, excessively long input, and double quotes. Explicitly reject input that exceeds the product's length limit rather than relying on a database exception as a fallback.

At acceptance, check each requirement: newly created and migrated databases return the same results; old words stop matching after a rename; deleted records leave no search hits; transaction rollback restores results; and Chinese search behaves as documented. Finally, run the same dataset in the iOS test target. When switching cloud Macs or handing over a customer project, this verifies search behavior itself—not an index that happens to have remained correct on one machine.

Frequently asked questions