Db Search Bundle Laravel Package

Technical Evaluation

Architecture Fit

• Pros:
  • Lightweight alternative to Elasticsearch/FOSElasticaBundle, ideal for small-to-medium Symfony projects where dedicated search infrastructure is overkill.
  • Database-native search leverages existing full-text indexes (MySQL/PostgreSQL), reducing operational complexity.
  • Symfony-first design: Integrates seamlessly with Doctrine, Symfony’s DI container, and Console components.
  • Config-driven: Centralized configuration (db_search.yaml) simplifies maintenance for multiple entities/indexes.
• Cons:
  • Limited query capabilities: No fuzzy matching, ranking, or advanced syntax (e.g., AND/OR operators). Relies on basic LIKE/MATCH queries.
  • Indexing granularity: Concatenates fields into a single LONGTEXT column, which may reduce search relevance for multi-field queries.
  • No async indexing: Reindexing blocks the application during execution (critical for large datasets).

Integration Feasibility

• Symfony Compatibility:
  • Requires Symfony 6.4+ (due to symfony/console:^6.4 dependency). Verify compatibility with your LTS version (e.g., Symfony 6.4/7.0).
  • Doctrine ORM mandatory: Not compatible with other databases (e.g., Eloquent, Propel) without significant refactoring.
• Database Support:
  • MySQL/MariaDB: Fully supported (uses FULLTEXT index).
  • PostgreSQL: Partial support (requires manual tsvector/tsquery configuration; not tested).
  • SQLite/SQL Server: Unsupported (no full-text search or equivalent).
• Entity Requirements:
  • Entities must implement getId() (Doctrine standard). Custom ID types (e.g., UUID) may need type-casting in config.
  • Fields must return scalar values (no arrays, objects, or complex types).

Technical Risk

• Performance:
  • Indexing: Reindexing large datasets (e.g., 100K+ entities) may cause timeouts or lock contention. Test with db-search:index:regenerate in staging.
  • Search: Full-text search performance degrades with large indexed_text columns (MySQL’s FULLTEXT has a 85% word limit per row). Monitor query execution plans.
• Data Integrity:
  • No transactions: Index regeneration drops and recreates records atomically, but partial failures could corrupt the index.
  • Concurrency: Not thread-safe; avoid concurrent reindexing or searches.
• Upgrade Risk:
  • Minimal backward compatibility: Breaking changes likely between major versions (e.g., PHP 8.0+ required). Pin to ^1.1 for stability.

Key Questions

1. Database Choice:
   • Is your primary database MySQL/PostgreSQL? If PostgreSQL, confirm tsvector support is acceptable.
   • What’s the maximum entity size? Test with your largest table to validate indexing/search performance.
2. Search Requirements:
   • Do you need fuzzy matching, ranking, or multi-word queries? If yes, this bundle may not suffice.
   • Are wildcards (%) or regex required? The bundle uses basic LIKE fallbacks.
3. Scaling:
   • How often will data change? Frequent updates may require scheduled reindexing (e.g., via cron).
   • Is read-heavy vs. write-heavy? This bundle optimizes for search speed but penalizes write performance.
4. Alternatives:
   • Could native Doctrine queries (e.g., WHERE title LIKE '%term%') or PostgreSQL’s tsvector meet needs without a bundle?
   • Is Elasticsearch feasible for future growth? This bundle may become a migration sink.

Integration Approach

Stack Fit

• Symfony Ecosystem:
  • Best Fit: Symfony 6.4+ projects using Doctrine ORM and Console components.
  • Anti-Patterns:
    • Avoid in microservices (shared DB adds complexity).
    • Not suitable for headless APIs with high search volume (e.g., e-commerce).
• Database Layer:
  • MySQL/MariaDB: Native FULLTEXT integration works well.
  • PostgreSQL: Requires manual tsvector setup (not documented). Test with:

    # config/packages/db_search.yaml
    db_search:
      indexes:
        main:
          entities:
            App\Entity\Post:
              fields:
                - title
                - body
          # PostgreSQL-specific: Use a custom search service
          search_service: App\Service\PostgresSearchService
    

  • SQLite: Unsupported (no full-text search).

Migration Path

1. Assessment Phase:
   • Audit entities for compatibility (e.g., getId(), scalar fields).
   • Benchmark current search queries (e.g., Doctrine LIKE vs. bundle performance).
2. Pilot Index:
   • Start with one non-critical entity (e.g., App\Entity\BlogPost).
   • Configure in db_search.yaml and test:

     db_search:
       indexes:
         blog:
           entities:
             App\Entity\BlogPost:
               fields: [title, excerpt]
     

   • Run php bin/console db-search:index:regenerate --entity=App\Entity\BlogPost.
3. Gradual Rollout:
   • Add indexes incrementally (e.g., main, products, users).
   • Replace legacy search logic (e.g., custom LIKE queries) with the Searcher service.
4. Fallback Plan:
   • Implement a feature flag to toggle between old/new search.
   • Cache results (e.g., Redis) if search latency is unacceptable.

Compatibility

• Doctrine Events:
  • The bundle does not hook into Doctrine lifecycle events (e.g., postPersist). Indexing must be manual or triggered via cron.
  • Workaround: Use Symfony’s CronExpression to reindex nightly:

    # config/packages/messenger.yaml
    framework:
      messenger:
        transports:
          async: '%env(MESSENGER_TRANSPORT_DSN)%'
        routing:
          'Ayaou\DbSearchBundle\Message\ReindexMessage': async
    

• Customization:
  • Extend the Searcher service to add logic (e.g., filtering by entity type):

    // src/Service/CustomSearcher.php
    class CustomSearcher extends \Ayaou\DbSearchBundle\Searcher\Searcher
    {
        public function searchByType(string $query, string $entityClass): array
        {
            return $this->search($query, [], $entityClass);
        }
    }
    

  • Override templates (e.g., result formatting) via Twig or Symfony’s templating engine.

Sequencing

1. Pre-Installation:
   • Backup the database (index regeneration is destructive).
   • Verify PHP 8.0+ and Symfony 6.4+ compatibility.
2. Installation:

   composer require ayaou/db-search-bundle
   php bin/console make:migration
   php bin/console doctrine:migrations:migrate
   

3. Configuration:
   • Define indexes in config/packages/db_search.yaml.
   • Test with php bin/console db-search:index:regenerate --dry-run.
4. Integration:
   • Replace search endpoints with the Searcher service.
   • Add a search endpoint (e.g., /api/search):

     #[Route('/api/search', name: 'api_search')]
     public function search(Request $request, Searcher $searcher): JsonResponse
     {
         $results = $searcher->search($request->query->get('q'));
         return $this->json($results);
     }
     

5. Monitoring:
   • Log search queries and performance (e.g., XDEBUG_PROFILER_ENABLED=1).
   • Set up alerts for slow reindexing (e.g., php bin/console db-search:index:regenerate > 5min).

Operational Impact

Maintenance

• Index Management:
  • Manual Reindexing: Required after schema changes or data migrations. Schedule via cron:

    # cron.daily
    0 3 * * * php /path/to/bin/console db-search:index:regenerate
    

  • Incremental Updates: Not supported. Full reindexing is mandatory for changes.
• Configuration Drift:
  • Changes to db_search.yaml require reindexing. Document the process for devops.
  • Tooling: Use symfony/var-dumper to debug index contents:

    $index = $this->container->get('db_search.index_manager')->getIndex('main');
    dump($index->getEntities());
    

• Dependencies:
  • **Composer