# How wacli Uses SQLite FTS5 for Full-Text Search: Implementation and Fallback Strategy

> Discover how wacli leverages SQLite FTS5 for efficient full-text search, with a fallback to LIKE queries and real-time indexing using triggers.

- Repository: [Peter Steinberger/wacli](https://github.com/steipete/wacli)
- Tags: internals
- Published: 2026-04-17

---

**wacli implements full-text search using SQLite FTS5 with automatic fallback to LIKE queries when the extension is unavailable, using a virtual table with triggers for real-time indexing.**

wacli is a command-line interface for WhatsApp that stores message history in a local SQLite database. To enable fast searching across message content, media captions, and sender information, the project leverages the **FTS5** (Full-Text Search 5) extension. When FTS5 is not available in the SQLite build, the system gracefully degrades to pattern matching using `LIKE` queries.

## FTS5 Virtual Table Architecture

The full-text search capability begins with database migration logic that attempts to create a virtual table using the FTS5 extension.

### Migration and Table Creation

In [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go), the function `migrateMessagesFTS` attempts to create the virtual table `messages_fts` during database initialization:

```sql
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
    text,
    media_caption,
    filename,
    chat_name,
    sender_name,
    display_text
);

```

If the SQLite build includes the `fts5` module, this migration succeeds and sets an internal flag `ftsEnabled = true`. If the build lacks FTS5 or any step fails, the migration catches the error, sets `ftsEnabled = false`, and allows the application to continue with degraded search functionality.

### Real-Time Indexing with Triggers

To keep the FTS index synchronized with the main `messages` table, [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go) creates SQLite triggers that execute automatically on data modifications:

```sql
CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN
    INSERT INTO messages_fts(rowid, text, media_caption, filename,
                           chat_name, sender_name, display_text)
    VALUES (new.rowid,
            COALESCE(new.text,''), COALESCE(new.media_caption,''), COALESCE(new.filename,''),
            COALESCE(new.chat_name,''), COALESCE(new.sender_name,''), COALESCE(new.display_text,''));
END;

```

Analogous `DELETE` and `UPDATE` triggers ensure that the FTS index remains consistent when messages are removed or modified, eliminating the need for manual reindexing.

## Search Implementation and Query Dispatch

The search logic resides in [`internal/store/search.go`](https://github.com/steipete/wacli/blob/main/internal/store/search.go), which provides a unified API that automatically selects the optimal search strategy based on FTS5 availability.

### The SearchMessages API

The public method `(*DB).SearchMessages` serves as the entry point for full-text queries. It accepts a `SearchMessagesParams` struct containing the query string, optional chat filter, and result limit. Internally, it checks the `ftsEnabled` flag (exposed via `HasFTS()`) and dispatches to either `searchFTS` or `searchLIKE`.

### FTS5 Query Sanitization

To prevent FTS syntax injection, the `searchFTS` function sanitizes user input through `sanitizeFTSQuery`:

```go
func sanitizeFTSQuery(query string) string {
    // Each token is quoted and embedded double-quotes are doubled
    // to prevent FTS-syntax injection
    tokens := strings.Fields(query)
    for i, token := range tokens {
        tokens[i] = `"` + strings.ReplaceAll(token, `"`, `""`) + `"` 将
    }
    return strings.Join(tokens, " ")
}

```

The sanitized query is then used with the `MATCH` operator against the `messages_fts` virtual table:

```sql
SELECT m.*, snippet(messages_fts, 0, '<<', '>>', '...', 10) as snippet
FROM messages m
JOIN messages_fts ON m.rowid = messages_fts.rowid
WHERE messages_fts MATCH ?
ORDER BY bm25(messages_fts)
LIMIT ?

```

Results are ordered by the **BM25** relevance score, and the `snippet` function provides highlighted excerpts showing where matches occur.

### LIKE Fallback Strategy

When FTS5 is unavailable, `searchLIKE` provides a safe fallback using pattern matching:

```go
func escapeLIKE(query string) string {
    // Escapes % and _ characters so they are treated literally
    query = strings.ReplaceAll(query, `\`, `\\`)
    query = strings.ReplaceAll(query, `%`, `\%`)
    query = strings.ReplaceAll(query, `_`, `\_`)
    return query
}

```

The escaped query is wrapped in wildcards and executed against multiple columns:

```sql
SELECT *, text as snippet FROM messages
WHERE LOWER(text) LIKE LOWER(?) ESCAPE '\'
   OR LOWER(media_caption) LIKE LOWER(?) ESCAPE '\'
   OR LOWER(sender_name) LIKE LOWER(?) ESCAPE '\'
ORDER BY timestamp DESC
LIMIT ?

```

This approach scans the regular `messages` table and is considerably slower than the FTS5 index, but ensures search functionality remains available across all SQLite builds.

## CLI Integration and User Experience

The command-line interface in [`cmd/wacli/messages.go`](https://github.com/steipete/wacli/blob/main/cmd/wacli/messages.go) integrates the search functionality and communicates the current mode to users.

### Detecting FTS5 Availability

In [`internal/store/types.go`](https://github.com/steipete/wacli/blob/main/internal/store/types.go), the `DB` struct exposes the `HasFTS()` method which returns the internal `ftsEnabled` boolean. This allows the CLI and other consumers to check capabilities before attempting searches.

### Command-Line Interface

The `wacli messages search` command invokes `a.DB().SearchMessages` and prints a warning when FTS5 is disabled:

```go
if !a.DB().HasFTS() {
    fmt.Fprintln(os.Stderr, "Note: FTS5 not enabled; search is using LIKE (slow).")
}
msgs, err := a.DB().SearchMessages(store.SearchMessagesParams{
    Query: c.Query,
    Limit: c.Limit,
})

```

Users can also verify FTS5 status through the `wacli doctor` command implemented in [`cmd/wacli/doctor.go`](https://github.com/steipete/wacli/blob/main/cmd/wacli/doctor.go), which reports whether the database has full-text search enabled.

## Summary

- **wacli** automatically provisions a SQLite FTS5 virtual table named `messages_fts` during database migration, with triggers that maintain index synchronization.
- The `SearchMessages` API in [`internal/store/search.go`](https://github.com/steipete/wacli/blob/main/internal/store/search.go) transparently dispatches to either BM25-ranked FTS5 queries or case-insensitive `LIKE` pattern matching based on runtime capability detection.
- User input is sanitized through `sanitizeFTSQuery` to prevent FTS syntax injection, while `escapeLIKE` ensures safe fallback queries.
- The CLI warns users when operating in slower `LIKE` mode and provides the `doctor` command for capability verification.

## Frequently Asked Questions

### How does wacli detect if FTS5 is available?

During database initialization in [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go), the code attempts to execute `CREATE VIRTUAL TABLE … USING fts5`. If this succeeds, the internal flag `ftsEnabled` is set to `true`; if any error occurs, it is set to `false`. The `HasFTS()` method in [`internal/store/types.go`](https://github.com/steipete/wacli/blob/main/internal/store/types.go) exposes this state to the rest of the application.

### What happens if I search without FTS5 enabled?

When FTS5 is unavailable, the `searchLIKE` function in [`internal/store/search.go`](https://github.com/steipete/wacli/blob/main/internal/store/search.go) performs a case-insensitive substring search using SQL `LIKE` clauses with escaped wildcards. The CLI prints a warning: "Note: FTS5 not enabled; search is using LIKE (slow)." Results are returned in chronological order rather than by relevance.

### How does wacli prevent FTS5 query injection?

The `sanitizeFTSQuery` function in [`internal/store/search.go`](https://github.com/steipete/wacli/blob/main/internal/store/search.go) tokenizes the input string using `strings.Fields`, then wraps each token in double quotes while doubling any existing double-quotes. This ensures that special FTS5 operators (like `AND`, `OR`, `NOT`, or `NEAR`) are treated as literal search terms rather than query syntax, preventing injection attacks.

### Does wacli support highlighted search results?

Yes, when using FTS5, the query in `searchFTS` utilizes the `snippet(messages_fts, ...)` function to generate highlighted excerpts that mark the matching terms with `<<` and `>>` delimiters. This provides context for where the search terms appear within the message content. The `LIKE` fallback returns the raw message text as the snippet without highlighting.