search_api_sqlite

The Search API SQLite FTS5 module provides a full-text search backend for Drupal using SQLite FTS5. It offers improved search relevance and performance compared to the Database Search backend without requiring external services like Solr or Elasticsearch.

This module uses the BM25 ranking algorithm and is suitable for small to medium-sized Drupal sites with up to 500,000 searchable items.

Features

• BM25 Ranking — Industry-standard relevance scoring.
• Phrase & Prefix Search — Exact phrase matching and wildcard prefix searches.
• Multiple Tokenizers — Unicode61 (default), Porter stemming, ASCII, or Trigram for substring search.
• Faceted Search — Full support for the Facets module.
• Autocomplete — Support for the Search API Autocomplete module.
• Highlighting — Native FTS5 highlighting via processor plugin.
• Spell Check — "Did you mean?" suggestions via processor plugin (requires Search API Spellcheck).
• Performance — Isolated index files, WAL mode, memory-mapped I/O, auto-optimization.

Why Choose SQLite FTS5?

SQLite FTS5 provides full-text search capabilities built directly into SQLite with no external dependencies.

• No Infrastructure — No external services to install or maintain.
• Proven Technology — SQLite is used by countless applications worldwide.
• Reliable — ACID-compliant with crash recovery.

Choose this backend when:

• Your site has less than 500,000 searchable items.
• You need better relevance ranking (BM25) than Database Search provides.
• You want to avoid the complexity of managing external services like Solr or Elasticsearch.
• Server resources are limited or you're on shared hosting.

Comparison with Database Search

Feature Database Search SQLite FTS5 Relevance Ranking Basic frequency BM25 (Industry Standard) Phrase Search Bigrams (high storage) Native Support Prefix Search Full table scan Optimized Index Lookup Substring Search Not supported Trigram tokenizer Highlighting Not built-in Native FTS5 Support Index Isolation Shared database Separate file per index Concurrent Reads Limited by connections Unlimited (WAL mode)

Requirements

• PHP 8.3+ — This module requires PHP 8.3 or later.
• SQLite support — PHP must be compiled with SQLite support (enabled by default in most distributions).
• Private file system — A writable private file path must be configured in settings.php.

Configuration

Server Configuration

1. Navigate to Administration → Configuration → Search and metadata → Search API.
2. Add a new server or edit an existing one.
3. Select SQLite FTS5 as the backend.
4. Configure the server options:
   • Database path — Location for SQLite database files (defaults to private://search_api_sqlite/).
   • Query logging — Log all search queries for analysis.

Index Configuration

Index-specific settings are configured per-index, allowing different search behaviors for each index on the same server:

1. Navigate to your Search API index edit page.
2. Configure the SQLite FTS5 index options:
   • Tokenizer — Choose between Unicode61 (recommended for most sites), Porter (English stemming), ASCII (simple English), or Trigram (substring search).
   • Case-sensitive matching — Enable case-sensitive search for Trigram tokenizer only (useful when product codes differ by case).
   • Minimum word length — Search terms shorter than this value are ignored in queries (filters search queries, not indexing). Not applicable to Trigram tokenizer.
   • Default matching mode — Choose between:
     • Match all words (AND) — All terms must appear in results.
     • Prefix matching — Matches word beginnings, best for autocomplete.
     • Partial matching — Substring search with Trigram tokenizer, falls back to prefix matching with other tokenizers.
     • Phrase matching — Terms must appear in exact order.
   • Auto-optimization — Enable automatic index optimization after a specified number of changes to improve query performance.
   • Optimization threshold — Number of indexed/updated/deleted items before triggering automatic optimization.

Note: By default, index files are stored in your private file system at private://search_api_sqlite/. Each index is stored as a separate SQLite file, enabling per-index configuration. Ensure your private file path is correctly configured in your settings.php file.

Highlighting

The SQLite FTS5 Highlighting processor uses native FTS5 snippet() function for fast, accurate highlighting of search terms in results.

Enable the "SQLite FTS5 Highlighting" processor on your index and configure:

• Highlight prefix/suffix — HTML tags to wrap matched terms (default: <mark>).
• Excerpt length — Number of tokens to include in the excerpt.
• Exclude fields — Fields to exclude from highlighting.

Please note that Search API's built-in Highlight processor should not be used together with the native FTS5 highlighting processor.

Spell Check

The SQLite Spell Check processor provides "Did you mean?" suggestions when searches return few results. It uses external spell checkers (aspell, hunspell, or pspell) and validates suggestions against your search index.

Requirements: composer require tigitz/php-spellchecker and a spell check backend with language dictionaries installed.

Enable the "SQLite Spell Check" processor on your index and configure the backend, language, and result threshold. Use with the Search API Spellcheck module for Views integration.

For more details, see the blog post about SQLite FTS5 for Search API.