Ai Maria Db Store Laravel Package

Getting Started

Minimal Setup

1. Install Dependencies

   composer require symfony/ai-maria-db-store
   

   Ensure your composer.json includes:

   "require": {
       "php": "^8.2",
       "mariadb/server": "^11.7",
       "symfony/ai": "^0.8"
   }
   

2. Configure MariaDB

   • Create a table with a VECTOR column and index:

     CREATE TABLE `embeddings` (
         `id` INT AUTO_INCREMENT PRIMARY KEY,
         `content` TEXT,
         `metadata` JSON,
         `embedding` VECTOR(1536),  -- Adjust dimension as needed
         INDEX `idx_embedding` ((embedding))
     ) ENGINE=InnoDB;
     

3. Basic Laravel Integration Register the store in AppServiceProvider:

   use Symfony\AI\MariaDbStore\MariaDbStore;
   use Symfony\Component\AI\Store\AiStoreInterface;
   
   public function register()
   {
       $this->app->singleton(AiStoreInterface::class, function ($app) {
           return new MariaDbStore(
               $app['db']->connection()->getPdo(),
               [
                   'table' => 'embeddings',
                   'vector_column' => 'embedding',
                   'distance' => 'cosine', // or 'euclidean'
               ]
           );
       });
   }
   

4. First Use Case: Storing/Retrieving Vectors

   // Inject AiStoreInterface via constructor
   public function __construct(private AiStoreInterface $store) {}
   
   // Store a vector
   $this->store->save([
       'id' => 'doc1',
       'content' => 'Sample content',
       'metadata' => ['category' => 'tech'],
       'embedding' => [0.1, 0.2, ..., 0.0], // Your 1536-dim vector
   ]);
   
   // Find nearest neighbors
   $results = $this->store->findNearest(
       [0.5, 0.3, ..., 0.1], // Query vector
       5,                   // Limit
       ['metadata.category' => 'tech'] // Optional filter
   );
   

Implementation Patterns

Core Workflows

1. Vector CRUD Operations

• Batch Inserts: Use save() with an array of embeddings:

  $this->store->save([
      ['id' => 'doc1', 'embedding' => $vec1, 'metadata' => [...]],
      ['id' => 'doc2', 'embedding' => $vec2, 'metadata' => [...]],
  ]);
  

• Updates: Replace entire records or use update() for partial updates (if supported by your MariaDB version).
• Deletes: Leverage remove() with IDs or filters:

  $this->store->remove(['id' => ['doc1', 'doc2']]);
  // Or with metadata filter
  $this->store->remove(['metadata.category' => 'deprecated']);
  

2. Hybrid Search (Vector + SQL Filters)

Combine vector similarity with metadata queries:

$results = $this->store->findNearest(
    $queryVector,
    10,
    [
        'metadata.published' => true,
        'metadata.language' => 'en',
    ]
);

Under the hood, this translates to:

SELECT * FROM embeddings
WHERE metadata->>'$.published' = 'true'
  AND metadata->>'$.language' = 'en'
ORDER BY embedding <=> ?  -- Cosine distance
LIMIT 10;

3. Distance Metrics

Configure the distance function in the store options:

$store = new MariaDbStore(..., ['distance' => 'euclidean']);

Supported values: 'cosine' (default), 'euclidean', or 'l2'.

4. Pagination

Use findNearest() with offset and limit:

$page1 = $this->store->findNearest($query, 10, [], 0, 10);
$page2 = $this->store->findNearest($query, 10, [], 10, 10);

Integration Tips

Laravel-Specific Patterns

1. Query Builder Integration For complex queries, use raw PDO via Laravel’s DB facade:

   $results = DB::select(
       'SELECT * FROM embeddings
        ORDER BY embedding <=> ?
        LIMIT ?',
       [$queryVector, 5]
   );
   

2. Model Binding Attach vectors to Eloquent models:

   use Symfony\AI\MariaDbStore\MariaDbStore;
   
   class Document extends Model
   {
       public function getEmbedding(): array
       {
           return $this->embedding; // Assuming `embedding` is a serialized vector
       }
   
       public function saveToStore(MariaDbStore $store)
       {
           $store->save([
               'id' => $this->id,
               'embedding' => $this->getEmbedding(),
               'metadata' => $this->metadata,
           ]);
       }
   }
   

3. Artisan Commands Create a command to manage vectors:

   use Illuminate\Console\Command;
   use Symfony\Component\AI\Store\AiStoreInterface;
   
   class VectorSyncCommand extends Command
   {
       protected $signature = 'vectors:sync';
       protected $description = 'Sync documents to MariaDB vector store';
   
       public function handle(AiStoreInterface $store)
       {
           foreach (Document::all() as $doc) {
               $store->save($doc->toArrayForStore());
           }
       }
   }
   

Performance Optimization

1. Indexing Ensure your VECTOR column has an index:

   CREATE INDEX idx_embedding ON embeddings ((embedding));
   

   For large datasets, consider IVF (Inverted File Index):

   ALTER TABLE embeddings ADD INDEX idx_embedding_ivf ((embedding) USING IVF);
   

2. Batch Operations Use transactions for bulk inserts:

   DB::transaction(function () use ($store, $vectors) {
       $store->save($vectors);
   });
   

3. Caching Cache frequent queries with Laravel’s cache:

   $cacheKey = 'vector_query_' . md5(serialize($queryVector));
   $results = Cache::remember($cacheKey, now()->addMinutes(5), function () use ($store, $queryVector) {
       return $store->findNearest($queryVector, 10);
   });
   

Gotchas and Tips

Pitfalls

1. MariaDB Version Requirements

   • Error: Unknown column type 'VECTOR' or Function 'vector' doesn't exist.
   • Fix: Upgrade to MariaDB 11.7+ and enable the vector engine:

     INSTALL PLUGIN vector SONAME 'vector';
     

2. Vector Dimension Mismatch

   • Error: Vector dimension mismatch when inserting/retrieving.
   • Fix: Ensure all vectors in a column have the same dimension (e.g., 1536 for all-MiniLM-L6-v2 embeddings). Validate with:

     $store->getVectorDimension(); // Check expected dimension
     

3. Distance Function Confusion

   • Error: Unknown function 'distance' or incorrect similarity scores.
   • Fix: Use MariaDB’s syntax:
     • Cosine: embedding <=> ? (default in the package).
     • Euclidean/L2: SQRT(SUM(POWER(embedding - ?, 2))).
   • Tip: Test with known vectors:

     SELECT embedding <=> [1,0,0] FROM embeddings WHERE id = 'doc1';
     

4. Filter Syntax Quirks

   • Error: JSON path not found or Invalid filter.
   • Fix: Use JSON path syntax correctly:

     // Correct
     ['metadata->$.category' => 'tech']
     // Or for nested JSON
     ['metadata->$.user->$.role' => 'admin']
     

   • Tip: Escape special characters in metadata values.

5. Connection Pooling

   • Error: Slow queries due to connection overhead.
   • Fix: Reuse the PDO connection:

     $pdo = DB::connection()->getPdo();
     $store = new MariaDbStore($pdo, $config);
     

Debugging Tips

1. Enable MariaDB Logging Add to my.cnf:

   [mysqld]
   general_log = 1
   general_log_file = /var