Singlestoredb Laravel Laravel Package

Getting Started

Minimal Setup

1. Installation:

   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 to test the connection:

   php artisan migrate
   

   Example migration:

   Schema::create('users', function (Blueprint $table) {
       $table->id();
       $table->string('name');
       $table->timestamps();
   });
   

Implementation Patterns

Core Workflows

1. Migrations with SingleStore-Specific Features: Leverage SingleStore’s unique table types and optimizations:

   Schema::create('analytics', function (Blueprint $table) {
       $table->columnstore(); // Default, but explicit for clarity
       $table->sortKey('created_at'); // Optimize for time-series queries
       $table->json('metadata'); // Native JSON support
   });
   

2. Eloquent Model Integration: Use Eloquent as usual, but with SingleStore’s optimizations:

   class User extends Model {
       protected $connection = 'singlestore';
       protected $casts = [
           'options' => 'json', // Native JSON casting
       ];
   }
   

3. Query Optimization:

   • Persistent Connections: Enable PDO::ATTR_PERSISTENT in config for transaction-heavy apps.
   • Shard Keys: Distribute data efficiently:

     Schema::create('orders', function (Blueprint $table) {
         $table->shardKey('user_id'); // Shard by user_id
     });
     

4. Temporary Tables: Use global temporary tables for session-specific data:

   Schema::create('temp_data', function (Blueprint $table) {
       $table->rowstore()->temporary()->global();
   });
   

5. JSON Columns: Store and query JSON natively:

   $user = User::find(1);
   $user->metadata->push(['key' => 'value']); // Mutate JSON
   $user->save();
   

6. Full-Text Search: Enable FULLTEXT indexes for search:

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

Integration Tips

• Queue Failed Jobs: Configure config/queue.php to use SingleStore:

  'failed' => [
      'driver' => 'database-uuids',
      'database' => 'singlestore',
      'table' => 'failed_jobs',
  ],
  

• SSL for Managed Service: Download SingleStore’s CA certificate and set:

  PDO::MYSQL_ATTR_SSL_CA => '/path/to/singlestore_bundle.pem',
  

• Avoid ORDER BY in Updates/Deletes: Configure the driver to ignore it:

  'ignore_order_by_in_updates' => true,
  'ignore_order_by_in_deletes' => true,
  

Gotchas and Tips

Pitfalls

1. Persistent Connections:

   • Issue: Transactions may leak if not cleaned up properly (e.g., unhandled exceptions).
   • Fix: Use DB::transaction() and ensure rollbacks in finally blocks.
   • Tip: Monitor idle connections (default limit: ~100k per aggregator).

2. ORDER BY in Updates/Deletes:

   • Issue: SingleStore rejects ORDER BY in UPDATE/DELETE. Ignoring it may delete/update random rows if LIMIT/OFFSET is used.
   • Fix: Explicitly sort data in application logic before querying.

3. PHP < 8.1:

   • Issue: PDO::ATTR_EMULATE_PREPARES returns numeric values as strings.
   • Fix: Upgrade to PHP 8.1+ or use Eloquent’s $casts to convert types.

4. JSON Column Quirks:

   • Issue: JSON functions (e.g., ->>) may not work as expected in raw queries.
   • Fix: Use Eloquent’s $casts or SingleStore’s JSON functions in raw queries:

     DB::select("SELECT JSON_EXTRACT(column, '$.key') FROM table");
     

5. Shard Key Misconfiguration:

   • Issue: Poor shard key choice leads to data skew or performance degradation.
   • Fix: Test with ANALYZE TABLE and adjust based on query patterns.

6. Temporary Tables:

   • Issue: Global temporary tables persist until the session ends (not per connection).
   • Fix: Explicitly drop them when done:

     DB::statement("DROP TEMPORARY TABLE IF EXISTS temp_data");
     

Debugging

• Connection Issues:

  • Check SSL certificates (PDO::MYSQL_ATTR_SSL_CA).
  • Verify pdo_mysql is enabled (php -m | grep pdo_mysql).
  • Use DB::enableQueryLog() to inspect generated SQL:

    DB::connection('singlestore')->enableQueryLog();
    User::all(); // Trigger query
    dd(DB::getQueryLog());
    

• Query Performance:

  • Use SingleStore’s EXPLAIN:

    DB::select("EXPLAIN SELECT * FROM users WHERE id = ?", [1]);
    

  • Monitor with SHOW STATUS LIKE 'Com_%' for connection metrics.

Extension Points

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

   use Illuminate\Database\Schema\Blueprint;
   use Illuminate\Support\Facades\Schema;
   
   Schema::extend(function (Blueprint $blueprint) {
       $blueprint->macro('customMethod', function () {
           // Add SingleStore-specific logic
       });
   });
   

2. Query Builder Modifications: Override the query builder to handle SingleStore syntax:

   DB::connection('singlestore')->setQueryGrammar(
       new \SinglestoreDB\Laravel\Grammars\SinglestoreGrammar
   );
   

3. Event Listeners: Hook into SingleStore events (e.g., after table creation) to automate optimizations:

   Schema::after(function (Blueprint $blueprint, $table) {
       if ($table === 'users') {
           DB::statement("ALTER TABLE users ADD INDEX idx_name (name)");
       }
   });
   

4. Service Provider: Bind SingleStore-specific interfaces in a service provider:

   $this->app->bind(
       \SinglestoreDB\Laravel\Contracts\Optimizer::class,
       \SinglestoreDB\Laravel\Optimizers\ColumnstoreOptimizer::class
   );
   

Pro Tips

• Use columnstore_segment_rows: Tune sort keys for analytical workloads:

  $table->sortKey('date')->with(['columnstore_segment_rows' => 50000]);
  

• Leverage sparse Columns: Reduce storage for low-cardinality columns:

  $table->string('status')->sparse(); // Only stores non-null values
  

• Batch Operations: Use SingleStore’s bulk APIs for large inserts/updates:

  DB::table('users')->insert([
      ['name' => 'Alice'], ['name' => 'Bob'],
  ]);
  

• Monitor with SHOW ENGINE: Check table status:

  DB::select("SHOW ENGINE table_name STATUS");