vuthaihoc/scout-crdb-driver

A Laravel Scout engine for CockroachDB with support for full-text, trigram, fuzzy, prefix and exact search strategies.

v1.0.0 2026-05-27 05:07 UTC

Requires

• php: ^8.3
• laravel/framework: ^11.0|^12.0|^13.0
• laravel/scout: ^10.0|^11.0

Requires (Dev)

• laravel/pint: ^1.0
• orchestra/testbench: ^9.0|^10.0
• pestphp/pest: ^3.0
• pestphp/pest-plugin-laravel: ^3.0

MIT

• search
• laravel
• fuzzy
• full-text
• scout
• cockroachdb
• trigram
• crdb

README

A Laravel Scout engine for CockroachDB with fine-grained, per-column index strategy control via PHP Attributes.

Features

• ✅ Full-text search — uses @@ plainto_tsquery() with CockroachDB's Inverted Full-text Index
• ✅ Trigram substring — uses ILIKE '%keyword%' with GIN Trigram Index
• ✅ Fuzzy matching — uses word_similarity() >= threshold for typo-tolerant search
• ✅ Prefix search — uses ILIKE 'keyword%' with B-Tree Index
• ✅ Exact match — uses col = ? with B-Tree / GIN Index
• ✅ Relevance ordering — combines ts_rank() and word_similarity() automatically
• ✅ Engine-agnostic searchInFields() macro — scope search fields without touching Controller code when switching drivers

Requirements

• PHP 8.3+
• Laravel 11 / 12 / 13
• Laravel Scout ≥ 10
• CockroachDB with pg_trgm extension (for Trigram & Fuzzy features)

Installation

composer require vuthaihoc/scout-crdb-driver

Publish the config (optional):

php artisan vendor:publish --tag=scout-crdb-config

Set the Scout driver in .env:

SCOUT_DRIVER=crdb

Database Setup

Enable the trigram extension and create appropriate indexes in your migrations:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

return new class extends Migration {
    public function up(): void
    {
        // Enable trigram extension (run once per database)
        DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');

        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('sku')->index();           // B-Tree for prefix/exact
            $table->string('title');
            $table->text('content');
            $table->string('status')->index();         // B-Tree for exact
            $table->timestamps();
        });

        // GIN Trigram index for ILIKE / word_similarity
        DB::statement('CREATE INDEX products_title_trgm ON products USING GIN (title gin_trgm_ops)');

        // Full-text Inverted index (simple dictionary = no stemming, good for Vietnamese)
        DB::statement("CREATE INDEX products_content_fts ON products USING GIN (to_tsvector('simple', content))");
    }
};

Model Configuration

Use PHP Attributes on toSearchableArray() to declare the search strategy per column:

use Illuminate\Database\Eloquent\Model;
use Laravel\Scout\Searchable;
use Laravel\Scout\Attributes\SearchUsingFullText;
use Laravel\Scout\Attributes\SearchUsingPrefix;
use Hocvt\CrdbScout\Attributes\SearchUsingFuzzy;
use Hocvt\CrdbScout\Attributes\SearchUsingTrigram;
use Hocvt\CrdbScout\Attributes\SearchUsingExact;

class Product extends Model
{
    use Searchable;

    // Full-text search with 'simple' dictionary (no stemming, good for Vietnamese)
    #[SearchUsingFullText(['content'], ['language' => 'simple'])]

    // Fuzzy search: allows typos, threshold 0.4 (0.0 = very loose, 1.0 = exact)
    #[SearchUsingFuzzy(['title'], 0.4)]

    // Trigram substring: ILIKE '%keyword%'
    #[SearchUsingTrigram(['tags'])]

    // Prefix: ILIKE 'keyword%'  (uses B-Tree index)
    #[SearchUsingPrefix(['sku'])]

    // Exact match: col = ?
    #[SearchUsingExact(['status'])]
    public function toSearchableArray(): array
    {
        return [
            'content' => $this->content,
            'title'   => $this->title,
            'tags'    => $this->tags,
            'sku'     => $this->sku,
            'status'  => $this->status,
        ];
    }
}

Search Strategy Summary

Attribute	SQL Generated	Index Required
SearchUsingFullText	@@ plainto_tsquery(?)	GIN Full-text Inverted Index
SearchUsingFuzzy	word_similarity(?, col) >= N	GIN Trigram (gin_trgm_ops)
SearchUsingTrigram	col ILIKE '%keyword%'	GIN Trigram (gin_trgm_ops)
SearchUsingPrefix (Scout)	col ILIKE 'keyword%'	B-Tree
SearchUsingExact	col = ?	B-Tree / GIN
(default)	col = ?	B-Tree

Usage

Basic search

// Searches across all configured columns
$products = Product::search('iphone')->get();

Field-scoped search (engine-agnostic)

// Only search in 'title' and 'tags' columns
$products = Product::search('iphone')
    ->searchInFields(['title', 'tags'])
    ->get();

This macro works with any engine. If you switch from CRDB to Meilisearch, your Controller code stays unchanged — the engine handles the translation.

Combining with other Scout features

Product::search('wireless')
    ->searchInFields(['title', 'content'])
    ->where('status', 'active')
    ->paginate(15);

Configuration

// config/scout-crdb.php
return [
    // Default threshold for #[SearchUsingFuzzy] when not explicitly set
    'default_fuzzy_threshold' => env('SCOUT_CRDB_FUZZY_THRESHOLD', 0.3),

    // Override the DB connection used for search queries (null = app default)
    'connection' => env('SCOUT_CRDB_CONNECTION', null),
];

Full-text Options

The SearchUsingFullText attribute (from Laravel Scout) supports an options array:

// Use 'simple' dictionary (no stemming — recommended for Vietnamese or multi-language)
#[SearchUsingFullText(['content'], ['language' => 'simple'])]

// Use phrase matching
#[SearchUsingFullText(['content'], ['language' => 'english', 'mode' => 'phrase'])]

// Use websearch syntax (AND, OR, -)
#[SearchUsingFullText(['content'], ['language' => 'english', 'mode' => 'websearch'])]

License

MIT