Full-text search in Laravel on both SQLite and MySQL: FTS5, FULLTEXT and one interface

TL;DR: you can add real search inside files to a Laravel app without Meilisearch or Elasticsearch. Keep the extracted text in one table, give each database its own small driver (FTS5 on SQLite, a FULLTEXT index on MySQL and MariaDB), and join the results to your normal tables so permissions stay in one place.

I'm building Ferrite, a self-hosted file server. It runs on SQLite by default and on MySQL or MariaDB if you prefer. People wanted to find a file by a word inside it, not only by its name, and I did not want the install to grow another service. This is how I did it, including the places where the two databases differ enough to bite.

(If you can run a search service, Laravel Scout with Meilisearch or Typesense is a perfectly good answer. This is for when you can't, or won't.)

One table, keyed on the content

Extracted text goes into a node_contents table, one row per distinct content (the file's SHA-256), not per file. Two identical files are read once. A queued job on its own search queue reads the file after it is stored: text files directly (the first 2 MB, valid UTF-8 only) and PDFs through pdftotext. A row has a status (indexed, skipped with a reason, or failed), so a binary file or a broken PDF is not retried forever.

The index is built on top of that table, and that is the part that differs per database.

One interface, two drivers

The search code does not know which database it is on. It asks for a driver:

interface ContentSearchDriver
{
    // Narrow $nodes to the files whose text matches, best first, with a `snippet` column.
    public function match(Builder $nodes, string $term): ?Builder;

    // The snippet with the matched words marked.
    public function present(string $snippet, string $term): string;
}

return match (DB::connection()->getDriverName()) {
    'sqlite' => new SqliteContentSearch,
    'mysql', 'mariadb' => new MysqlContentSearch,
    default => null,   // other databases keep the table but have no content search
};

match() receives a query that is already limited to what the user may see, and adds the join, the full-text condition, the ranking and the snippet. More on that in a moment.

SQLite: an FTS5 table kept in step by triggers

FTS5 is built into SQLite. I use an external content table, so the text is stored once (in node_contents) and the FTS table only holds the index. Three triggers keep it in step:

DB::unprepared("create virtual table node_contents_fts using fts5(
    text, content='node_contents', content_rowid='id',
    tokenize='unicode61 remove_diacritics 2')");

DB::unprepared("create trigger node_contents_ai after insert on node_contents begin
    insert into node_contents_fts(rowid, text) values (new.id, coalesce(new.text, ''));
end");
// plus an 'after delete' and an 'after update' trigger that remove the old entry first

remove_diacritics 2 makes reuniao find reunião, which matters if your users are not English-speaking. The query itself is short, with bm25 for ranking and snippet() for the excerpt:

return $nodes
    ->join('node_contents as nc', 'nc.sha256', '=', 'nodes.sha256')
    ->join('node_contents_fts', 'node_contents_fts.rowid', '=', 'nc.id')
    ->whereRaw('node_contents_fts match ?', [$query])
    ->select('nodes.*')
    ->selectRaw("snippet(node_contents_fts, 0, char(1), char(2), '…', 18) as snippet")
    ->orderByRaw('bm25(node_contents_fts)');

I mark the matched words with two control characters (char(1) and char(2)), so the view can escape the whole snippet as HTML and only then replace the markers with <mark>. That keeps the snippet safe even when the file's text contains <script>.

MySQL and MariaDB: a FULLTEXT index

On MySQL the migration is one line: $table->fullText('text'), on a LONGTEXT column. The query uses boolean mode:

return $nodes
    ->join('node_contents as nc', 'nc.sha256', '=', 'nodes.sha256')
    ->whereRaw('match(nc.text) against (? in boolean mode)', [$query])
    ->select('nodes.*')
    ->selectRaw('substring(nc.text, greatest(locate(?, nc.text) - 60, 1), 240) as snippet', [$words[0]])
    ->selectRaw('match(nc.text) against (? in boolean mode) as content_rank', [$query])
    ->orderByDesc('content_rank');

Three differences from SQLite:

• No snippet function. I cut a 240-character window around the first word in SQL and mark the words in PHP afterwards, with the same control characters, so the view code is identical.
• Short words and stopwords. InnoDB ignores words shorter than three characters and a built-in list of stopwords. If you make such a word required (+the*) the search can come back empty. I don't rely on the behaviour: I drop those words from the query, and if nothing is left I return no content matches.
• Ranking differs. bm25 on SQLite, InnoDB's relevance score on MySQL. The order of results is not the same, and I documented that instead of pretending.

Never let the user's text reach the search syntax

Both engines have a query language (quotes, OR, NEAR(, -, *, : in FTS5). If you pass the raw input through, plain OR ( becomes a syntax error, or worse, a way to run operators you did not intend. So the input is reduced first to letters and digits:

preg_match_all('/[\p{L}\p{N}]+/u', mb_strtolower($term), $found);

Each word then becomes "word"* on SQLite or +word* on MySQL: every word required, each matched as a prefix. The tests throw odd inputs at it ("plain, col:plain, NEAR(plain words), *, a lone quote) and assert that nothing throws.

Permissions stay where they were

The index never decides who sees what. Search starts from the same visibility query as the file list (your own files, plus everything below folders shared with you, trashed items excluded) and then match() narrows it. A user only ever gets snippets of nodes they could already open, even though identical content is stored once across users.

$query = $driver->match(
    $this->visibleTo(Node::query(), $user)
        ->where('nodes.type', NodeType::File)
        ->whereNull('nodes.trashed_at'),
    $term,
);

Testing on both databases

The search tests are one Pest file. By default it runs against in-memory SQLite. To run the same file on MySQL or MariaDB you point the suite at an empty test database, and CI does that with a MySQL service container.

One trap: an InnoDB FULLTEXT index only sees committed rows, and Laravel's RefreshDatabase wraps each test in a transaction. On MySQL every search found nothing. The fix, in the test file only: commit at the start of each test and empty the tables afterwards.

// An InnoDB FULLTEXT index only sees committed rows, so on MySQL these tests commit
// the transaction the suite wraps them in and empty the tables afterwards instead.
$this->commits = in_array(DB::connection()->getDriverName(), ['mysql', 'mariadb'], true);

if ($this->commits) {
    DB::commit();
}

I ran the file on SQLite, MySQL 8.4 and 9, and MariaDB 11.

Gotchas to know about

• Accents on MySQL depend on the collation. With utf8mb4_unicode_ci (Laravel's default) accents and case are ignored. With a _bin or _as_cs collation they are not.
• The queue worker has to listen to the new queue. Mine is queue:work --queue=default,search. Without it the extraction jobs are queued and never run. With the sync queue driver, PDFs are better left to a scheduled command so a slow PDF can't hit a time limit inside a request.
• PDFs need pdftotext (poppler-utils). Scanned documents have no text layer, so there is nothing to index. I did not add OCR.
• The index holds the file's text unencrypted in your database. If that matters, make indexing switchable. Mine is one environment variable.

What I would do differently

• If you already run Meilisearch or Typesense, use them through Scout. Typo tolerance and facets are something this setup does not give you.
• A Postgres driver would be a third class behind the same interface (tsvector), and the structure makes it easy to add. I did not need it.
• I would test the very large case (millions of rows) before promising anything. This is built for a person or a small team.

The code is open source (AGPL-3.0). If you want to read it rather than my summary:

• The drivers: SqliteContentSearch and MysqlContentSearch
• The design notes: docs/content-search.md
• The project: github.com/zhoorta/ferrite, with a live demo (throwaway account, wiped after an hour) where you can upload a text file or PDF and search for a word in it.

Questions or corrections are welcome in the comments.

Disclosure: I built Ferrite and I use an AI coding assistant (Claude) while developing it; I review and test what it writes. An AI assistant also helped me draft this article, and I edited it and checked the claims against the code. I chose the "AI-assisted" option when publishing.