Cloud Spanner Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require google/cloud-spanner
   

   Ensure the grpc PHP extension is installed and enabled.

2. Authentication: Configure credentials via environment variables (GOOGLE_APPLICATION_CREDENTIALS) or service account JSON file. Example:

   putenv('GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json');
   

3. First Query:

   use Google\Cloud\Spanner\SpannerClient;
   
   $spanner = new SpannerClient();
   $db = $spanner->instance('your-instance-id')->database('your-database-id');
   
   $result = $db->execute('SELECT * FROM Users WHERE id = @id', [
       'parameters' => ['id' => 123]
   ]);
   
   foreach ($result->rows() as $row) {
       print_r($row);
   }
   

Key First Use Cases

• CRUD Operations: Use execute() for reads and runUpdate() for writes.
• Transactions: Wrap operations in beginTransaction() for atomicity.
• Schema Management: Use executeDdl() for schema changes.

Implementation Patterns

Core Workflows

1. Query Execution

// Parameterized query (recommended)
$result = $db->execute('SELECT * FROM Users WHERE email = @email', [
    'parameters' => ['email' => 'user@example.com']
]);

// Batch queries
$batch = $db->createBatchDmlStatement();
$batch->insert('Users', ['id' => 1, 'name' => 'John']);
$batch->update('Users', ['name' => 'Jane'], ['id' => 1]);
$db->executeBatchDml($batch);

2. Transactions

$transaction = $db->beginTransaction();
try {
    $transaction->runUpdate('UPDATE Accounts SET balance = balance - @amount WHERE id = @id', [
        'parameters' => ['amount' => 100, 'id' => 1]
    ]);
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollback();
    throw $e;
}

3. Schema Management

$db->executeDdl('
    CREATE TABLE IF NOT EXISTS Users (
        id INT64 NOT NULL,
        name STRING(100),
        email STRING(255)
    ) PRIMARY KEY (id)
');

4. Batch Operations

$batch = $db->createBatchDmlStatement();
$batch->insert('Orders', ['id' => 1, 'user_id' => 1, 'amount' => 100]);
$batch->insert('Orders', ['id' => 2, 'user_id' => 2, 'amount' => 200]);
$db->executeBatchDml($batch);

Integration Tips

• Laravel Service Provider: Bind the client in AppServiceProvider:

  $this->app->singleton(SpannerClient::class, function ($app) {
      return new SpannerClient();
  });
  

• Eloquent Integration: Use a custom query builder or repository pattern to abstract Spanner operations.
• Async Operations: Leverage executeAsync() for non-blocking queries (requires grpc async support).

Gotchas and Tips

Pitfalls

1. gRPC Extension:

   • Error: Class 'Google\Cloud\Spanner\SpannerClient' not found or Failed to load gRPC extension.
   • Fix: Install the grpc extension and restart PHP-FPM/Apache.

     pecl install grpc
     sudo systemctl restart php-fpm
     

2. Session Expiry:

   • Error: Session expired after 7 days of inactivity.
   • Fix: Refresh sessions asynchronously (recommended every 24 hours):

     $db->session()->refresh();
     

3. Parameter Binding:

   • Error: Invalid parameter type for complex objects.
   • Fix: Use scalar values or JSON strings for nested objects.

4. Multiplexed Sessions:

   • Error: High memory usage with many concurrent sessions.
   • Fix: Configure a custom cacheItemPool (e.g., Redis) for shared caching:

     $spanner = new SpannerClient([
         'cacheItemPool' => new RedisCacheItemPool(Redis::connection())
     ]);
     

Debugging Tips

• Enable Logging:

  $spanner = new SpannerClient([
      'logging' => [
          'level' => \Google\Cloud\Core\Logging\LogLevel::DEBUG,
          'handler' => new \Monolog\Handler\StreamHandler('spanner.log', \Monolog\Logger::DEBUG)
      ]
  ]);
  

• Query Plan: Use EXPLAIN to analyze query performance:

  $explain = $db->execute('EXPLAIN SELECT * FROM Users');
  

Extension Points

1. Custom Value Mappers: Override default type mapping (e.g., for custom JSON fields):

   $db->setValueMapper(new class extends \Google\Cloud\Spanner\ValueMapper {
       public function mapValue($value, $type) {
           if ($type === 'JSON') {
               return json_decode($value, true);
           }
           return parent::mapValue($value, $type);
       }
   });
   

2. Middleware: Add request/response middleware for logging or metrics:

   $spanner = new SpannerClient([
       'middleware' => [
           new class implements \Google\Cloud\Core\Middleware\MiddlewareInterface {
               public function handle($request, callable $next) {
                   // Pre-process request
                   $response = $next($request);
                   // Post-process response
                   return $response;
               }
           }
       ]
   ]);
   

3. Locking Strategies: Customize session locking for high-concurrency scenarios:

   $lock = new \Google\Cloud\Core\Lock\RedisLock(Redis::connection());
   $db = $spanner->instance('instance')->database('db', ['lock' => $lock]);
   

Performance Quirks

• Batch Size: Limit batch DML to <100 operations to avoid timeouts.
• Read/Write Splits: Use separate sessions for read-heavy vs. write-heavy workloads.
• Indexing: Ensure primary keys and frequently queried columns are indexed.