otaqq / search-in-box
Full-text search inside your own database, no search server: a small index table, search-box query languages (websearch, any, all), and events and batches to keep it up to date.
Requires
- php: ^8.3
- ext-mbstring: *
- symfony/polyfill-intl-normalizer: ^1.31
Requires (Dev)
- illuminate/console: ^12.0|^13.0
- illuminate/database: ^12.0|^13.0
- illuminate/queue: ^12.0|^13.0
- illuminate/support: ^12.0|^13.0
- orchestra/testbench: ^10.0|^11.0
- phpunit/phpunit: ^11.5|^12.5
Suggests
- ext-pdo_mysql: For MySQL and MariaDB
- ext-pdo_pgsql: For PostgreSQL
- ext-pdo_sqlite: For SQLite
- illuminate/database: For the Laravel integration (^12.0|^13.0): config, an Eloquent scope, events and a reindex command
Provides
None
Conflicts
None
Replaces
None
README
Full-text search inside your own database — no search server. A small index table next to your data, search-box query languages (websearch like Google, or simple any / all), and events and batches that keep the index up to date.
Elasticsearch, OpenSearch or Meilisearch are great when you need them. Most business apps do not: they need a search box over a few thousand to a few hundred thousand rows, in the database they already run. search-in-box keeps everything in that box.
- No extra server. The index is a table in your MySQL, MariaDB, PostgreSQL or SQLite database, searched with plain SQL (
LIKE, MySQL ngram FULLTEXT, or PostgreSQLpg_trgm). - Search-box languages.
websearch(東京 "駅 徒歩" -夜勤 大阪 or 京都) by default;any(any word) andall(every word) for simple boxes. Pick per index or per query. Parsing never fails. - Text from your model and its relations. List the fields as paths (
title,industry.name,locations.*.prefecture.name); fields you do not list (an internal memo) are never searchable. - Normalized both ways. The index and the query go through the same normalizer, so full-width and half-width, upper and lower case, and (with
ja) hiragana and katakana match — the same on every database. - Kept up to date. Events rebuild a row when it or a related row changes; a batch rebuilds what is stale and catches writes that bypass events.
- One spec, many languages. The rules live in spec/ and are checked by shared data in conformance/. PHP (with Laravel) comes first; Node.js, Ruby, Python, Go and Rust can follow.
Status: v0.1: PHP and Laravel, tested on SQLite, MySQL and PostgreSQL, in use in one application. Other languages are planned; see docs/roadmap.md. See docs/roadmap.md.
Layout
| Path | What | Package |
|---|---|---|
| spec | What every implementation does | — |
| conformance | Shared inputs and expected results | — |
lang/php/core |
Framework-independent PHP: parsers, normalizers, SQL for each backend | Composer otaqq/search-in-box |
lang/php/laravel |
Laravel integration: config, Eloquent scope, events, queue job, artisan command | Composer otaqq/search-in-box |
lang/node, lang/ruby, lang/python, lang/go, lang/rust |
Other languages | later |
Laravel
composer require otaqq/search-in-box php artisan vendor:publish --tag=search-in-box-config
List each searchable model in config/search-in-box.php:
'indexes' => [ App\Models\JobPosting::class => [ 'table' => 'job_posting_search_indices', 'fields' => ['code', 'title', 'content', 'industry.name', 'occupations.*.name', 'locations.*.prefecture.name'], 'normalizer' => 'ja', 'language' => 'any', // websearch (default), any or all 'backend' => 'like', // like (default), mysql-fulltext or pgsql-trigram ], ],
Create the index table in a migration, add the trait, and search:
use Otaqq\SearchInBox\Laravel\IndexTable; IndexTable::create(JobPosting::class); // key and cascade follow the model; binary collation on MySQL; // the backend's FULLTEXT (ngram) or pg_trgm index
use Otaqq\SearchInBox\Laravel\Searchable; class JobPosting extends Model { use Searchable; } JobPosting::query()->search($request->input('q'))->paginate(); JobPosting::query()->search('倉庫 カフェ', 'any'); // another language for this query
Keeping the texts up to date:
| Change | What happens |
|---|---|
| The model is saved (or restored) | Its text is rebuilt; inside a transaction, once after commit |
A child (hasOne / hasMany on the model) is saved or deleted |
The parent's text is rebuilt (found by the child's foreign key, so moves and deletes work) |
| Another related row changes a column in a path (a category is renamed) | Every row that uses it is rebuilt, in a queued job (defaults.queue; right away when null) |
| The model is deleted for good | Its text is deleted; soft-deleted rows keep their text and are left out of the search |
No model event (belongsToMany sync, bulk updates, raw SQL) |
Save the model afterwards, or let the batch catch it |
php artisan search-in-box:reindex # stale texts only: missing, or older than the row or a related row php artisan search-in-box:reindex --force # every text
Schedule the batch (Schedule::command('search-in-box:reindex')->daily()) to catch what events miss.
For mysql-fulltext, turn off InnoDB FULLTEXT stopwords (innodb_ft_enable_stopword=OFF) and keep ngram_token_size at 2; for pgsql-trigram, the pg_trgm extension must be available (spec/matching.md).
License
MIT. See LICENSE.