Getting Started

Minimal Setup

Installation:
```
composer require singlestoredb/singlestoredb-laravel
```
Ensure pdo_mysql is enabled (php -i | grep pdo_mysql).

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

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

Set default to 'singlestoredb' or 'singlestore'.

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

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

Implementation Patterns

1. Schema Migrations

Universal Storage (Default): No extra configuration needed; all tables default to columnstore.

Rowstore Tables:

Schema::create('high_freq_transactions', function (Blueprint $table) {
    $table->rowstore(); // Optimized for OLTP
    $table->increments('id');
    $table->string('data')->sparse(); // Sparse column
});

Shard/Sort Keys:

Schema::create('analytics', function (Blueprint $table) {
    $table->shardKey('user_id'); // Distribute data
    $table->sortKey(['event_date', 'desc']); // Optimize queries
});

2. Eloquent Integration

JSON Columns: Use json type for nested data:

Schema::create('profiles', function (Blueprint $table) {
    $table->json('metadata'); // Native JSON support
});

Access via Eloquent:

$profile->metadata['key'] = 'value';

Global Temporary Tables:

Schema::create('temp_session', function (Blueprint $table) {
    $table->temporary()->global(); // Visible across sessions
});

3. Query Workflows

Bulk Operations: Leverage SingleStore’s parallel processing for INSERT/UPDATE:

DB::table('users')->insert([
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
]);

Full-Text Search:

Schema::create('articles', function (Blueprint $table) {
    $table->text('content');
    $table->fullText('content'); // FULLTEXT index
});
DB::table('articles')->where('content', 'like', '%laravel%')->get();

4. Connection Management

Persistent Connections: Enable in config/database.php for high-throughput apps:
```
'options' => [
    PDO::ATTR_PERSISTENT => true,
],
```

SSL for Managed Service:

'options' => [
    PDO::MYSQL_ATTR_SSL_CA => base_path('singlestore_bundle.pem'),
],

5. Testing

Use singlestoredb in phpunit.xml:

<env name="DB_CONNECTION" value="singlestoredb"/>

Mock SingleStore-specific features in tests:

$this->artisan('migrate:fresh')
     ->expectsQuestion('Proceed with migration?', 'yes')
     ->assertExitCode(0);

Gotchas and Tips

Pitfalls

ORDER BY in DELETE/UPDATE: SingleStore rejects ORDER BY in these queries. Workaround:
```
// config/database.php
'ignore_order_by_in_deletes' => true,
'ignore_order_by_in_updates' => true,
```
Note: Results may be non-deterministic with LIMIT/OFFSET.
PHP < 8.1 and PDO::ATTR_EMULATE_PREPARES: Returns numeric values as strings. Solutions:
- Upgrade to PHP 8.1+.
- Use Eloquent casting:
```
protected $casts = [
    'user_id' => 'integer',
];
```
Shard Key Misconfiguration:
- Avoid high-cardinality shard keys (e.g., uuid).
- Test data distribution with SHOW TABLE STATUS LIKE 'your_table'.
Global Temporary Tables:
- Visible to all sessions but not transaction-scoped. Clean up manually:
```
DB::statement('DROP TEMPORARY TABLE IF EXISTS temp_session');
```

Debugging Tips

Query Logging: Enable in config/database.php:
```
'logging' => true,
'log' => storage_path('logs/singlestore.log'),
```
Check logs for malformed queries (e.g., unsupported syntax).

Connection Issues:

Verify SSL certs for Managed Service:

openssl s_client -connect your-cluster.singlestore.com:3306 -showcerts

Test connectivity:
```
mysql -h your-host -u user -p
```

Performance Bottlenecks:
- Monitor SHOW PROCESSLIST for long-running queries.
- Use EXPLAIN for complex joins:
```
DB::select('EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id');
```

Extension Points

Custom Table Options: Extend Blueprint for SingleStore-specific options:

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

class SingleStoreBlueprint extends Blueprint {
    public function columnstoreSegment($rows) {
        $this->columnstoreSegmentRows = $rows;
    }
}

Usage:

Schema::create('analytics', function (Blueprint $table) {
    $table->sortKey('timestamp')->with(['columnstore_segment_rows' => 100000]);
});

Query Builder Macros: Add SingleStore-specific methods:

DB::macro('shardedQuery', function ($query) {
    return $query->toSql() . " /*+ SHARD_KEY(user_id) */";
});

Event Listeners: Hook into migrations for post-creation tasks:

Schema::create('users', function (Blueprint $table) {
    $table->shardKey('email');
})->after(function () {
    DB::statement('ALTER TABLE users ADD INDEX idx_email (email)');
});

Pro Tips

Use reference Tables Sparingly: Replication overhead grows with data size. Reserve for small lookup tables (e.g., countries).
Leverage sparse Columns: Reduce storage for rarely used fields (e.g., profile_picture in a users table).

Batch Migrations: For large tables, use DB::transaction() with chunking:

DB::transaction(function () {
    User::chunk(1000, function ($users) {
        foreach ($users as $user) {
            $user->update(['status' => 'active']);
        }
    });
});

Monitor Idle Connections: SingleStore handles ~100K idle connections by default. Adjust max_connections in my.cnf if needed:
```
[mysqld]
max_connections = 100000
```

Singlestoredb Laravel Laravel Package