Cloud Spanner Laravel Package

## Getting Started

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

Ensure grpc PHP extension is installed (see gRPC guide).

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

3. First Query:

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

Key Entry Points

• Instance/Database Access: $spanner->instance('id')->database('name')
• Query Execution: $db->execute('SQL', ['parameters' => [...]])
• Transactions: $db->runInTransaction(fn($transaction) => $transaction->execute('SQL'))

Implementation Patterns

Core Workflows

1. CRUD Operations

// Create
$db->execute('INSERT INTO Users (id, name) VALUES (@id, @name)', [
    'parameters' => ['id' => 1, 'name' => 'John']
]);

// Read
$rows = $db->execute('SELECT * FROM Users WHERE id = @id', [
    'parameters' => ['id' => 1]
]);

// Update
$db->execute('UPDATE Users SET name = @name WHERE id = @id', [
    'parameters' => ['id' => 1, 'name' => 'Updated']
]);

// Delete
$db->execute('DELETE FROM Users WHERE id = @id', ['parameters' => ['id' => 1]]);

2. Transactions

$db->runInTransaction(function ($transaction) {
    $transaction->execute('INSERT INTO Orders (user_id, amount) VALUES (@user_id, @amount)', [
        'parameters' => ['user_id' => 1, 'amount' => 100.00]
    ]);
    $transaction->execute('UPDATE Users SET balance = balance - @amount WHERE id = @user_id', [
        'parameters' => ['user_id' => 1, 'amount' => 100.00]
    ]);
});

3. Batch DML

$db->execute([
    'sql' => [
        'INSERT INTO Users (id, name) VALUES (@id1, @name1)',
        'INSERT INTO Users (id, name) VALUES (@id2, @name2)'
    ],
    'parameters' => [
        ['id1' => 2, 'name1' => 'Alice'],
        ['id2' => 3, 'name2' => 'Bob']
    ]
]);

4. Prepared Statements

$stmt = $db->prepare('SELECT * FROM Users WHERE id = @id');
$rows = $stmt->execute(['parameters' => ['id' => 1]]);

5. Schema Management

// Create table
$db->execute('CREATE TABLE Users (id INT64, name STRING(100))');

// Alter table
$db->execute('ALTER TABLE Users ADD COLUMN email STRING(255)');

// Drop table
$db->execute('DROP TABLE Users');

Integration Tips

Laravel Eloquent

Use the vladimir-yuldashev/laravel-spanner package for seamless integration:

// config/database.php
'spanner' => [
    'driver' => 'spanner',
    'instance' => env('SPANNER_INSTANCE'),
    'database' => env('SPANNER_DATABASE'),
    'project_id' => env('GOOGLE_PROJECT_ID'),
],

Query Builder

Extend Laravel's query builder for Spanner-specific features:

use Google\Cloud\Spanner\SpannerClient;

class SpannerQueryBuilder extends \Illuminate\Database\Query\Builder {
    protected function connect($dsn = null) {
        $spanner = new SpannerClient();
        $db = $spanner->instance(config('database.spanner.instance'))
                      ->database(config('database.spanner.database'));
        return new SpannerConnection($db);
    }
}

Caching Sessions

For high-concurrency apps, customize the session cache:

use Symfony\Component\Cache\Adapter\RedisAdapter;

$cache = new RedisAdapter();
$spanner = new SpannerClient(['cacheItemPool' => $cache]);

Gotchas and Tips

Common Pitfalls

1. gRPC Extension Missing

• Error: Class 'GRPC\Channel' not found
• Fix: Install the grpc extension and enable it in php.ini:

  extension=grpc.so
  

2. Authentication Issues

• Error: Could not authenticate request
• Fix:
  • Ensure GOOGLE_APPLICATION_CREDENTIALS points to a valid service account JSON file.
  • For production, use workload identity federation or short-lived credentials.

3. Session Expiry

• Behavior: Sessions expire after 7 days of inactivity.
• Mitigation:
  • Refresh sessions asynchronously (recommended every 24 hours):

    $spanner->instance('id')->database('name')->session()->refresh();
    

  • Use multiplexed sessions (enabled by default) to reduce overhead.

4. Parameter Binding Quirks

• Gotcha: Named parameters (@param) are required; positional binding is not supported.
• Fix: Always use associative arrays for parameters:

  // Correct
  $db->execute('SELECT * FROM Users WHERE id = @id', ['parameters' => ['id' => 1]]);
  
  // Incorrect (will fail)
  $db->execute('SELECT * FROM Users WHERE id = ?', [1]);
  

5. Large Result Sets

• Gotcha: Fetching large datasets may exhaust memory.
• Fix: Stream results using iterators:

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

Debugging Tips

1. Enable gRPC Logging

Add to php.ini:

grpc.verbose_logging = 1

Logs will appear in stderr.

2. Query Plan Analysis

Use EXPLAIN to analyze query performance:

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

3. Transaction Debugging

Enable transaction debug mode:

$db->runInTransaction(function ($transaction) {
    $transaction->setDebugMode(true);
    // Your transaction logic
});

4. Retry Logic

Implement exponential backoff for transient errors:

use Google\Cloud\Core\Retry\RetryPolicy;

$retryPolicy = new RetryPolicy();
$retryPolicy->setMaxAttempts(3);
$retryPolicy->setInitialBackoff(100); // ms

$db->executeWithRetry('SELECT * FROM Users', ['parameters' => []], $retryPolicy);

Extension Points

1. Custom Middleware

Add request/response middleware for logging or metrics:

use Google\Cloud\Core\Grpc\Middleware\MiddlewareInterface;

class LoggingMiddleware implements MiddlewareInterface {
    public function handle($request, callable $next) {
        \Log::debug('Spanner request:', $request->getMetadata());
        $response = $next($request);
        \Log::debug('Spanner response:', $response->getMetadata());
        return $response;
    }
}

// Register middleware
$spanner = new SpannerClient([
    'middleware' => [new LoggingMiddleware()]
]);

2. Custom Lock Providers

For distributed environments, replace the default lock with a custom implementation:

use Google\Cloud\Core\Lock\LockInterface;

class RedisLock implements LockInterface {
    public function acquire(array $options = []) { /* ... */ }
    public function release() { /* ... */ }
    public function synchronize(callable $func, array $options = []) { /* ... */ }
}

$spanner->instance('id')->database('name', ['lock' => new RedisLock()]);

3. Result Set Transformers

Extend result set processing:

use Google\Cloud\Spanner\Database;

class CustomDatabase extends Database {
    public function execute($sql, array $options = []) {
        $result