Singlestoredb Laravel Laravel Package

## Getting Started

### Minimal Setup
1. **Installation**:
   ```bash
   composer require singlestoredb/singlestoredb-laravel

Ensure pdo_mysql is enabled (php -i | grep pdo_mysql).

2. Configure Database: Update config/database.php to use the singlestore driver:

   'singlestore' => [
       'driver' => 'singlestore',
       'host' => env('DB_HOST'),
       'database' => env('DB_DATABASE'),
       'username' => env('DB_USERNAME'),
       'password' => env('DB_PASSWORD'),
       'charset' => 'utf8mb4',
       'collation' => 'utf8mb4_unicode_ci',
       'options' => [
           PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
           PDO::ATTR_EMULATE_PREPARES => true,
           PDO::ATTR_PERSISTENT => true, // Recommended for performance
       ],
   ],
   

   Set default to 'singlestore' in the same file.

3. First Use Case: Run a migration with SingleStore-specific features (e.g., shardKey or sortKey):

   Schema::create('users', function (Blueprint $table) {
       $table->id();
       $table->string('email')->unique();
       $table->shardKey('id'); // SingleStore-specific
       $table->sortKey('created_at'); // SingleStore-specific
       $table->timestamps();
   });
   

   Run migrations:

   php artisan migrate
   

Implementation Patterns

1. Schema & Migrations

• Universal Storage (Default): Use Schema::create() for columnstore tables (optimized for mixed workloads).

  Schema::create('analytics', function (Blueprint $table) {
      $table->id();
      $table->json('metadata'); // SingleStore JSON support
      $table->timestamps();
  });
  

• Rowstore Tables: Explicitly mark for low-latency transactional workloads:

  Schema::create('transactions', function (Blueprint $table) {
      $table->rowstore(); // Force rowstore
      $table->string('user_id')->shardKey();
      $table->decimal('amount');
      $table->timestamps();
  });
  

• Reference Tables: Use for small, frequently joined data:

  Schema::create('countries', function (Blueprint $table) {
      $table->reference(); // Fully replicated
      $table->string('code')->primary();
      $table->string('name');
  });
  

• Global Temporary Tables: For session-scoped data:

  Schema::create('temp_sessions', function (Blueprint $table) {
      $table->rowstore()->temporary()->global();
      $table->string('user_id');
      $table->json('data');
  });
  

• Sparse Columns/Tables: Optimize for sparse data (e.g., user preferences):

  Schema::create('user_prefs', function (Blueprint $table) {
      $table->rowstore()->sparse(); // Entire table is sparse
      $table->string('user_id')->shardKey();
      $table->string('pref_key')->sparse(); // Column-level sparsity
      $table->text('pref_value');
  });
  

2. Querying & Eloquent

• JSON Columns: Use native JSON functions:

  $user = User::where('metadata->$.preferences', 'like', '%theme%')->first();
  

  Or update JSON directly:

  User::where('id', 1)->update(['metadata' => DB::raw('JSON_SET(metadata, "$.preferences.theme", "dark")')]);
  

• Shard/Sort Keys: Leverage in queries for performance:

  // Shard key ensures data locality
  $users = User::where('shard_key', 'region_eu')->get();
  
  // Sort key optimizes range queries
  $recentOrders = Order::where('created_at', '>', now()->subDays(7))
                      ->orderBy('created_at') // Uses sort key
                      ->get();
  

• Full-Text Search: Create a FULLTEXT index in migrations:

  Schema::table('articles', function (Blueprint $table) {
      $table->fullText('content', 'title');
  });
  

  Query with:

  $articles = Article::whereRaw('MATCH(content, title) AGAINST(? IN NATURAL LANGUAGE MODE)', ['laravel'])
                     ->get();
  

3. Performance Optimizations

• Persistent Connections: Enable in config/database.php for high-throughput apps:

  'options' => [
      PDO::ATTR_PERSISTENT => true,
      PDO::MYSQL_ATTR_SSL_CA => env('SSL_CA_CERT'),
  ],
  

  Note: Clean up transactions explicitly to avoid leaks.

• Batch Operations: Use chunk() for large datasets:

  User::chunk(1000, function ($users) {
      foreach ($users as $user) {
          // Process batch
      }
  });
  

4. Queue Integration

• Store failed jobs in SingleStore:

  // config/queue.php
  'failed' => [
      'driver' => 'database-uuids',
      'database' => 'singlestore',
      'table' => 'failed_jobs',
  ],
  

Gotchas and Tips

Pitfalls

1. ORDER BY in DELETE/UPDATE: SingleStore rejects ORDER BY in these queries. Configure the driver to ignore it:

   'singlestore' => [
       'driver' => 'singlestore',
       'ignore_order_by_in_deletes' => true,
       'ignore_order_by_in_updates' => true,
   ],
   

   Warning: Ignoring ORDER BY + LIMIT may delete/update random rows.

2. PHP < 8.1: PDO::ATTR_EMULATE_PREPARES returns numeric values as strings. Workarounds:

   • Upgrade to PHP 8.1+.
   • Use Eloquent’s attribute casting.

3. SSL Certificates: SingleStore Managed Service requires a custom CA cert. Download and configure:

   'options' => [
       PDO::MYSQL_ATTR_SSL_CA => __DIR__.'/singlestoredb-bundle.pem',
   ],
   

4. Persistent Connections:

   • Pros: Faster for transactional workloads.
   • Cons:
     • Transactions must be cleaned up explicitly (e.g., DB::disconnect()).
     • Session variables (e.g., SET @var = value) may leak across requests.
   • Tip: Use DB::reconnect() to reset state if needed.

5. Shard Key Misconfiguration:

   • Shard keys must be immutable (e.g., id, user_id). Avoid sharding on frequently updated columns.
   • Debug: Check query plans with EXPLAIN to verify shard key usage.

6. Sort Key Direction:

   • Defaults to ASC. Use desc for descending:

     $table->sortKey(['created_at' => 'desc']);
     

Debugging Tips

1. Query Logging: Enable in config/logging.php:

   'default' => env('LOG_CHANNEL', 'stack'),
   'channels' => [
       'singlestore' => [
           'driver' => 'single',
           'path' => storage_path('logs/singlestore.log'),
           'level' => 'debug',
       ],
   ],
   

   Then set the logger in AppServiceProvider:

   public function boot()
   {
       DB::connection()->enableQueryLog();
       DB::connection()->setLogger(new \Monolog\Logger('singlestore'));
   }
   

2. Slow Queries: Use SingleStore’s EXPLAIN:

   $query = DB::table('users')->where('email', 'like', '%@example.com%');
   $explain = DB::select(DB::raw("EXPLAIN $query->toSql()"));
   

3. Connection Issues:

   • Verify pdo_mysql is loaded (php -m | grep pdo_mysql).
   • Check SingleStore’s connection troubleshooting.

Extension Points

1. Custom Blueprint Methods: Extend the Blueprint class to add SingleStore-specific methods:

   // app/Extensions/SingleStoreBlueprint.php
   namespace App\Extensions;
   use Illuminate\Database\Schema\Blueprint;
   
   class