Cloud Spanner Laravel Package

## Getting Started

### Minimal Setup
1. **Installation**:
   ```bash
   composer require google/cloud-spanner

Ensure the grpc PHP extension is installed and enabled (required for gRPC communication).

2. Authentication: Configure credentials via environment variables (recommended) or service account key file:

   putenv('GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json');
   

   Follow the Authentication Guide.

3. First Query:

   use Google\Cloud\Spanner\SpannerClient;
   
   $spanner = new SpannerClient();
   $db = $spanner->instance('your-instance')->database('your-database');
   
   $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/writes with parameterized queries.
• Transactions: Wrap operations in a transaction for consistency:

  $transaction = $db->runTransaction(function ($transaction) {
      $transaction->execute('UPDATE Accounts SET balance = balance - 100 WHERE id = @id', [
          'parameters' => ['id' => 1]
      ]);
      return $transaction->commit();
  });
  

• Schema Management: Create tables/indexes via DDL:

  $db->executeDdl('CREATE TABLE Users (id INT64, name STRING(100))');
  

Implementation Patterns

Core Workflows

1. Connection Management:

   • Reuse SpannerClient instances (stateless) and cache database connections:

     $spanner = new SpannerClient();
     $db = $spanner->instance('instance')->database('db'); // Reusable
     

   • Leverage multiplexed sessions (default) for high concurrency without session cleanup.

2. Query Patterns:

   • Parameterized Queries (always use to prevent SQL injection):

     $result = $db->execute('SELECT * FROM Orders WHERE status = @status', [
         'parameters' => ['status' => 'shipped']
     ]);
     

   • Batch Operations:

     $db->executeUpdate('INSERT INTO Logs (message) VALUES (@message)', [
         'parameters' => ['message' => 'Event occurred']
     ], ['batch' => true]);
     

3. Transaction Handling:

   • Read-Write Transactions (default):

     $db->runTransaction(function ($transaction) {
         $transaction->execute('UPDATE Inventory SET quantity = quantity - 1 WHERE id = @id', [
             'parameters' => ['id' => 100]
         ]);
         return $transaction->commit();
     });
     

   • Read-Only Transactions (for snapshots):

     $snapshot = $db->snapshot();
     $result = $snapshot->execute('SELECT * FROM Products');
     

4. Async Operations:

   • Use executeAsync() for non-blocking queries (returns a Future):

     $future = $db->executeAsync('SELECT * FROM LargeTable');
     $result = $future->wait(); // Blocks until ready
     

Integration Tips

• Laravel Service Provider: Bind the client to the container for dependency injection:

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

  Inject into controllers:

  public function __construct(private SpannerClient $spanner) {}
  

• Query Builder Abstraction: Create a wrapper for Spanner-specific queries:

  class SpannerQueryBuilder {
      public function __construct(private SpannerDatabase $db) {}
  
      public function findUser(int $id) {
          return $this->db->execute('SELECT * FROM Users WHERE id = @id', [
              'parameters' => ['id' => $id]
          ])->rows()->current();
      }
  }
  

• Error Handling: Catch Google\Cloud\Core\Exception\GoogleException for Spanner-specific errors:

  try {
      $db->execute('INVALID SQL');
  } catch (GoogleException $e) {
      Log::error('Spanner error: ' . $e->getMessage());
  }
  

• Configuration: Override defaults (e.g., cache, locking) via constructor options:

  $spanner = new SpannerClient([
      'cacheItemPool' => new FileSystemCacheItemPool('/custom/cache/path'),
      'grpcOptions' => [
          'keepalive_time_ms' => 60000, // 60s keepalive
      ]
  ]);
  

Gotchas and Tips

Common Pitfalls

1. Session Expiry:

   • Sessions expire after 7 days of inactivity. Refresh manually or use the session cache (default) to avoid latency:

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

   • Schedule a cron job to refresh sessions every 24 hours (recommended).

2. gRPC Requirements:

   • Missing grpc extension: Install via PECL or system package manager (e.g., sudo apt-get install php-grpc).
   • Keepalive Issues: Default keepalive_time_ms is 120s. Adjust if connections drop:

     $spanner = new SpannerClient(['grpcOptions' => ['keepalive_time_ms' => 300000]]);
     

3. Parameter Binding:

   • Null Values: Explicitly pass null for nullable columns:

     $db->execute('UPDATE Users SET email = @email WHERE id = @id', [
         'parameters' => ['email' => null, 'id' => 1] // email is NULL
     ]);
     

   • Type Mismatches: Spanner enforces strict typing (e.g., INT64 vs. STRING). Cast values explicitly:

     'parameters' => ['id' => (string) $userId] // Cast to STRING
     

4. Transactions and Locks:

   • Deadlocks: Avoid nested transactions or long-running transactions. Use READ_ONLY for analytics:

     $db->runTransaction(function ($transaction) {
         $transaction->setOptions(['isolation_level' => 'READ_ONLY']);
         // ...
     });
     

   • Read Lock Modes: Specify for strong consistency:

     $db->execute('SELECT * FROM Inventory', [
         'read_lock_mode' => 'READ_WRITE' // or 'STRONG', 'WEAK'
     ]);
     

5. Multiplexed Sessions:

   • Cache Configuration: Default uses sysvshm (Linux) or FileSystemCache. Customize if needed:

     $spanner = new SpannerClient([
         'cacheItemPool' => new RedisCacheItemPool($redisClient)
     ]);
     

   • Locking: Default uses sysvmsg/sysvsem locks. Fallback to flock on unsupported systems.

6. Large Result Sets:

   • Memory Limits: Stream results for large datasets:

     $result = $db->execute('SELECT * FROM LargeTable');
     foreach ($result->rows() as $row) {
         // Process row-by-row
     }
     

   • Cursor Pagination: Use OFFSET/LIMIT or application-side cursors.

Debugging Tips

1. Enable gRPC Logging: Add to php.ini or runtime:

   grpc.verbose_logging = 1
   

   Logs appear in stderr or PHP error logs.

2. Query Plan Analysis: Use Spanner’s built-in EXPLAIN:

   $plan = $db->execute('EXPLAIN SELECT * FROM Users');
   print_r($plan->rows()->current());
   

3. Session Inspection: Check active sessions:

   $sessions = $spanner->instance('instance')->sessions();
   foreach ($sessions as $session) {
       print_r($session->info());
   }
   

4. Retry Logic: Implement exponential backoff for transient errors (e.g., ABORTED):

   use Google\Cloud\Core\Exception\GoogleException;
   
   try {
       $db->execute('SELECT * FROM Users');
   } catch (GoogleException $e) {
       if ($e->getCode() === 11) { // ABORTED
           sleep(1); // Retry after delay
           retry();
       }
   }
   

Extension Points

1. Custom Middleware: Add headers or logging to requests: