Getting Started

Minimal Setup

Installation:
```
composer require google/cloud-spanner
```
Ensure the grpc PHP extension is installed (see gRPC guide). Pin to ^2.11.3 or later so you get the buffered-mutation/commit-retry fix (see Gotchas).
Authentication: Configure credentials via environment variables (GOOGLE_APPLICATION_CREDENTIALS) or a service account JSON file. Follow the Authentication guide.

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. Buffered Mutations with Precommit Tokens

When using insertBatch/updateBatch/deleteBatch with a precommit token (for idempotent commit retries), the client now clears its buffered mutation queue on retry (fixed in v2.11.3). Prefer this pattern over manual commit() retry loops:

$db->insertBatch('Users', [
    ['id' => 10, 'name' => 'Carol'],
    ['id' => 11, 'name' => 'Dave'],
]);

// The SDK handles commit retries using the precommit token and reuses
// the same mutation buffer without duplicating it (>= v2.11.3).

5. Prepared Statements

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

6. Schema Management

$db->execute('CREATE TABLE Users (id INT64, name STRING(100))');
$db->execute('ALTER TABLE Users ADD COLUMN email STRING(255)');
$db->execute('DROP TABLE Users');

Integration Tips

Laravel Eloquent

Use vladimir-yuldashev/laravel-spanner 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 behaviour:

use Google\Cloud\Spanner\SpannerClient;

class SpannerConnection extends \Illuminate\Database\Connection { /* ... */ }

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. Duplicated Buffered Mutations on Commit Retry (fixed in v2.11.3)

Behavior prior to v2.11.3: When a commit using a precommit token was retried, the buffered mutation queue was not cleared, so the mutation batch could be replayed and rows inserted/updated twice.
Fix: Upgrade to google/cloud-spanner >= 2.11.3. If you cannot upgrade, avoid mixing insertBatch/updateBatch/deleteBatch with manual commit retry loop; use runInTransaction/transaction() instead, or wrap retries with an idempotency key check.

5. 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]);

6. 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);

5. Verifying the v2.11.3 Fix

If you suspect duplicate writes after an ABORTED/UNAVAILABLE commit retry, check composer.lock for google/cloud-spanner version. Anything < 2.11.3 is affected. Enable verbose gRPC logging to confirm the commit is being re-issued with a precommit token.

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;
    }
}

$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 = parent::execute($sql, $options);
        // Wrap or post-process the result set.
        return new CustomResult($result);
    }
}

Cloud Spanner Laravel Package