What Happens When FTS5 Is Not Available and wacli Falls Back to LIKE Queries

When the FTS5 extension is unavailable, wacli automatically disables full-text search and falls back to case-insensitive LIKE queries with proper SQL escaping, printing a performance warning to stderr while remaining fully functional across all SQLite builds.

wacli is an open-source WhatsApp chat exporter that stores messages in SQLite. When the database is built without the sqlite_fts5 extension, the application gracefully degrades from FTS5's tokenized MATCH queries to standard LIKE pattern matching. This fallback ensures the messages search command works on any SQLite platform, though with reduced performance.

How wacli Detects FTS5 Availability

The detection logic centers on a boolean flag set during database initialization. In internal/store/migrations.go, wacli attempts to create the virtual table messages_fts and its supporting triggers. If this fails—either because the binary was compiled without sqlite_fts5 or the extension cannot be loaded—the code sets ftsEnabled to false.

The DB struct exposes this state through the HasFTS() method defined in internal/store/types.go:108:

// HasFTS returns true if the FTS5 extension is available.
func (db *DB) HasFTS() bool {
    return db.ftsEnabled
}

The Search Dispatch Logic

When you run a search, the SearchMessages function in internal/store/search.go:27-30 acts as a router. It checks the capability flag and delegates to the appropriate implementation:

func (db *DB) SearchMessages(ctx context.Context, query string, opts SearchOptions) ([]Message, error) {
    if db.ftsEnabled {
        return db.searchFTS(ctx, query, opts)
    }
    return db.searchLIKE(ctx, query, opts)
}

This branching ensures that code paths remain separate: searchFTS uses the MATCH operator against the virtual table, while searchLIKE constructs a pattern-matching query against the standard messages table.

Inside the LIKE Query Fallback

The searchLIKE function in internal/store/search.go:42-50 implements the fallback with security and correctness in mind. It builds a case-insensitive LIKE query that wraps the user input in % wildcards to find substring matches anywhere in the message text.

Critical to this implementation is the escapeLIKE helper, which sanitizes user input to prevent SQL injection and accidental wildcard expansion:

func escapeLIKE(s string) string {
    s = strings.ReplaceAll(s, `\`, `\\`)
    s = strings.ReplaceAll(s, `%`, `\%`)
    s = strings.ReplaceAll(s, `_`, `\_`)
    return s
}

The final query uses LOWER() for case-insensitive comparison and the ESCAPE '\' clause to treat the backslash as a literal escape character:

SELECT ... FROM messages 
WHERE LOWER(text) LIKE LOWER(?) ESCAPE '\'

User Experience When FTS5 Is Disabled

When the CLI executes a search without FTS5, it surfaces the performance degradation to the user. In cmd/wacli/messages.go:205-207, the code checks db.HasFTS() and prints a warning to stderr:

if !db.HasFTS() {
    fmt.Fprintln(os.Stderr, "Note: FTS5 not enabled; search is using LIKE (slow).")
}

This ensures users understand why searches take longer and confirms that the fallback is active rather than failing silently.

Performance Comparison: FTS5 vs LIKE

Aspect	With FTS5	Without FTS5 (LIKE fallback)
Query language	messages_fts MATCH ? (tokenized, ranked)	WHERE LOWER(col) LIKE LOWER(?) ESCAPE '\' (pattern matching)
Performance	Index-driven, O(log N) lookups	Full table scans, O(N) complexity
Result enrichment	Returns snippet generated by FTS5	snippet field empty; falls back to raw Text
User experience	Silent, fast operation	Warning printed to stderr about slow performance
Robustness	Requires SQLite with fts5 extension	Works on any SQLite build

Summary

• Automatic detection: wacli sets ftsEnabled to false during migration if the messages_fts virtual table cannot be created.
• Transparent routing: SearchMessages in internal/store/search.go switches between searchFTS and searchLIKE based on the HasFTS() flag.
• Secure fallback: searchLIKE escapes special characters and uses ESCAPE '\' to prevent SQL injection while performing case-insensitive substring matching.
• User notification: The CLI prints a warning to stderr when falling back to LIKE, alerting users to the performance impact.

Frequently Asked Questions

How can I check if my wacli installation supports FTS5?

Run any search query and check stderr for the warning message. If you see "Note: FTS5 not enabled; search is using LIKE (slow)," your binary was compiled without the sqlite_fts5 extension. You can also verify programmatically by checking the HasFTS() method on the database handle.

Can I enable FTS5 on an existing database that was created without it?

No, FTS5 support must be determined during the initial migration when wacli creates the messages_fts virtual table and its triggers. If you have an existing database built without FTS5, you would need to export your data, delete the database file, ensure your wacli binary supports FTS5, and re-import the chats to rebuild the schema with full-text indexing.

Is the LIKE fallback vulnerable to SQL injection attacks?

No, the implementation in internal/store/search.go uses a dedicated escapeLIKE function that sanitizes user input by escaping backslashes, percent signs, and underscores. The query also uses the ESCAPE '\' clause to ensure these characters are treated as literals rather than wildcards, making the fallback safe against injection.

Why are my searches significantly slower than before?

If your search performance degraded, your wacli binary is likely using the LIKE fallback instead of FTS5. The LIKE implementation performs full table scans with O(N) complexity, while FTS5 uses an inverted index for O(log N) lookups. Check stderr for the FTS5 warning, and consider recompiling wacli with the sqlite_fts5 build tag to restore fast full-text search.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}