colopl / laravel-spanner
Laravel database driver for Google Cloud Spanner
Installs: 43 607
Dependents: 1
Suggesters: 0
Security: 0
Stars: 78
Watchers: 12
Forks: 10
Open Issues: 0
Requires
- php: >=8
- ext-grpc: *
- ext-json: *
- google/cloud-spanner: ^1.47
- grpc/grpc: ^1.42
- laravel/framework: ~9.42
- symfony/cache: ~6
- symfony/lock: ~6
Requires (Dev)
- orchestra/testbench: ~7
- phpstan/phpstan: ^1
- phpunit/phpunit: ~9.0
Suggests
- ext-sysvmsg: Can use SemaphoreLock for session handling. Will use FileLock if not present.
- ext-sysvsem: Can use SemaphoreLock for session handling. Will use FileLock if not present.
- ext-sysvshm: Can use SemaphoreLock for session handling. Will use FileLock if not present.
This package is auto-updated.
Last update: 2023-03-27 02:10:33 UTC
README
Laravel database driver for Google Cloud Spanner
Requirements
- PHP >= 8.0
- Laravel >= 9
- gRPC extension
- protobuf extension (not required, but strongly recommended)
Installation
Put JSON credential file path to env variable: GOOGLE_APPLICATION_CREDENTIALS
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json
Install via composer
composer require colopl/laravel-spanner
Add connection config to config/database.php
[ 'connections' => [ 'spanner' => [ 'driver' => 'spanner', 'instance' => '<Cloud Spanner instanceId here>', 'database' => '<Cloud Spanner database name here>', ] ] ];
That's all. You can use database connection as usual.
$conn = DB::connection('spanner'); $conn->...
Additional Configurations
You can pass SpannerClient
config and CacheSessionPool
options as below.
For more information, please see Google Client Library docs
[ 'connections' => [ 'spanner' => [ 'driver' => 'spanner', 'instance' => '<Cloud Spanner instanceId here>', 'database' => '<Cloud Spanner database name here>', // Spanner Client configurations 'client' => [ 'projectId' => 'xxx', ... ], // CacheSessionPool options 'session_pool' => [ 'minSessions' => 10, 'maxSessions' => 500, ], ] ] ];
Unsupported features
- STRUCT data types
- Explicit Read-only transaction (snapshot)
Limitations
Migrations
Most functions of SchemaBuilder
(eg, Schema
facade, and Blueprint
) can be used.
However, artisan migrate
command does not work since AUTO_INCREMENT does not exist in Google Cloud Spanner.
Eloquent
If you use interleaved keys, you MUST define them in the interleaveKeys
property or you won't be able to save. For more detailed instructions, see Colopl\Spanner\Tests\Eloquent\ModelTest
.
Additional Information
Transactions
Google Cloud Spanner sometimes requests transaction retries (e.g. UNAVAILABLE
, and ABORTED
), even if the logic is correct. For that reason, please do not manage transactions manually.
You should always use the transaction
method which handles retry requests internally.
// BAD: Do not use transactions manually!! try { DB::beginTransaction(); ... DB::commit(); } catch (\Throwable $ex) { DB::rollBack(); } // GOOD: You should always use transaction method DB::transaction(function() { ... });
Google Cloud Spanner creates transactions for all data operations even if you do not explicitly create transactions.
In particular, in the SELECT statement, the type of transaction varies depending on whether it is explicit or implicit.
// implicit transaction (Read-only transaction) $conn->select('SELECT ...'); // explicit transaction (Read-write transaction) $conn->transaction(function() { $conn->select('SELECT ...'); }); // implicit transaction (Read-write transaction) $conn->insert('INSERT ...'); // explicit transaction (Read-write transaction) $conn->transaction(function() { $conn->insert('INSERT ...'); });
Transaction type | SELECT statement | INSERT/UPDATE/DELETE statement |
---|---|---|
implicit transaction | Read-only transaction with singleUse option | Read-write transaction with singleUse option |
explicit transaction | Read-write transaction | Read-write transaction |
For more information, see Cloud Spanner Documentation about transactions
Stale reads
You can use Stale reads (timestamp bounds) as below.
// There are four types of timestamp bounds: ExactStaleness, MaxStaleness, MinReadTimestamp and ReadTimestamp. $timestampBound = new ExactStaleness(10); // by Connection $connection->selectWithTimestampBound('SELECT ...', $bindings, $timestampBound); // by Query Builder $queryBuilder ->withStaleness($timestampBound) ->get();
Stale reads always runs as read-only transaction with singleUse
option. So you can not run as read-write transaction.
Data Types
Some data types of Google Cloud Spanner does not have corresponding built-in type of PHP. You can use following classes by Google Cloud PHP Client
- DATE:
Google\Cloud\Spanner\Date
- BYTES:
Google\Cloud\Spanner\Bytes
- TIMESTAMP:
Google\Cloud\Spanner\Timestamp
Google\Cloud\Spanner\Timestamp
is a DateTime representation with UTC timezone and nanoseconds.
In laravel-spanner QueryBuilder converts Timestamp
in the fetched rows to Carbon with the default timezone in PHP.
Note that if you execute a query without QueryBuilder, it will not have a conversion to Carbon.
Partitioned DML
You can run partitioned DML as below.
// by Connection $connection->runPartitionedDml('UPDATE ...'); // by Query Builder $queryBuilder->partitionedUpdate($values); $queryBuilder->partitionedDelete();
However, Partitioned DML has some limitations. See Cloud Spanner Documentation about Partitioned DML for more information.
Interleave
You can define interleaved tables as below.
$schemaBuilder->create('user_items', function (Blueprint $table) { $table->uuid('user_id'); $table->uuid('id'); $table->uuid('item_id'); $table->integer('count'); $table->timestamps(); $table->primary(['user_id', 'id']); // interleaved table $table->interleave('users')->cascadeOnDelete(); // interleaved index $table->index(['userId', 'created_at'])->interleave('users'); });
Row Deletion Policy
You can define row deletion policy as below.
$schemaBuilder->create('user', function (Blueprint $table) { $table->uuid('user_id'); $table->timestamps(); // create a policy $table->deleteRowsOlderThan(['updated_at'], 365); }); $schemaBuilder->table('user', function (Blueprint $table) { // replace policy $table->replaceRowDeletionPolicy('udpated_at', 100); // drop policy $table->dropRowDeletionPolicy(); });
Secondary Index Options
You can define Spanner specific index options like null filtering and storing as below.
$schemaBuilder->table('user_items', function (Blueprint $table) { $table->index('userId') // Interleave in parent table ->interleave('user') // Add null filtering ->nullFiltered() // Add storing ->storing(['itemId', 'count']); });
Mutations
You can insert, update, and delete data using mutations to modify data instead of using DML to improve performance.
$queryBuilder->insertUsingMutation($values);
$queryBuilder->updateUsingMutation($values);
$queryBuilder->deleteUsingMutation($values);
Please note that mutation api does not work the same way as DML. All mutations calls within a transaction are queued and sent as batch at the time you commit. This means that if you make any modifications through the above functions and then try to SELECT the same records before committing, the returned results will not include any of the modifications you've made inside the transaction.
SessionPool and AuthCache
In order to improve the performance of the first connection per request, we use AuthCache and CacheSessionPool.
By default, laravel-spanner uses Filesystem Cache Adapter as the caching pool. If you want to use your own caching pool, you can extend ServiceProvider and inject it into the constructor of Colopl\Spanner\Connection
.
'Session not found' exception handling
There are a few cases when a 'Session not found' error can happen:
- Scripts that idle too long - for example, a Laravel queue worker or anything that doesn't call Spanner frequently enough (more than once an hour).
- The session is more than 28 days old.
- Some random flukes on Google's side.
The errors can be handled by one of the supported modes:
- MAINTAIN_SESSION_POOL - When the 'session not found' error is encountered, the library tries to disconnect, maintain a session pool (to remove outdated sessions), reconnect, and then try querying again. The mode is enabled by default, but you can enable it explicitly via congifuration:
'spanner' => [ 'driver' => 'spanner', ... 'sessionNotFoundErrorMode' => 'MAINTAIN_SESSION_POOL', ]
- CLEAR_SESSION_POOL (default) - The MAINTAIN_SESSION_POOL mode is tried first. If the error still happens, then the clearing of the session pool is enforced and the query is tried once again. As a consequence of session pool clearing, all processes that share the current session pool will be forced to use the new session on the next call.
'spanner' => [ 'driver' => 'spanner', ... 'sessionNotFoundErrorMode' => 'CLEAR_SESSION_POOL' ]
- THROW_EXCEPTION - The QueryException is raised and the client code is free to handle it by itself.:
'spanner' => [ 'driver' => 'spanner', ... 'sessionNotFoundErrorMode' => 'THROW_EXCEPTION', ]
Please note, that getDatabaseContext()->execute(...)->rows()
returns a /Generator
object, which only accesses Spanner when iterated. That affects cursor()
and cursorWithTimestampBound()
functions and many low-level calls. So you might still
get Google\Cloud\Core\Exception\NotFoundException
when trying to resolve cursor.
To avoid that, please run cursor* functions inside
explicit transactions so statements will repeat on error.
$conn->transaction(function () use ($conn) { $cursor = $conn->cursor('SELECT ...'); foearch ($cursor as $value) { ... });
Queue Worker
After every job is processed, the connection will be disconnected so the session can be released into the session pool.
This allows the session to be renewed (through maintainSessionPool()
) or expire.
Laravel Tinker
You can use Laravel Tinker with commands such as php artisan tinker
.
But your session may hang when accessing Cloud Spanner. This is known gRPC issue that occurs when PHP forks a process.
The workaround is to add following line to php.ini
.
grpc.enable_fork_support=1
Development
Testing
You can run tests on docker by the following command. Note that some environment variables must be set.
In order to set the variables, rename .env.sample to .env
and edit the values of the
defined variables.
Name | Value |
---|---|
GOOGLE_APPLICATION_CREDENTIALS |
The path of the service account key file with access privilege to Google Cloud Spanner instance |
DB_SPANNER_INSTANCE_ID |
Instance ID of your Google Cloud Spanner |
DB_SPANNER_DATABASE_ID |
Name of the database with in the Google Cloud Spanner instance |
DB_SPANNER_PROJECT_ID |
Not required if your credential includes the project ID |
make test
License
Apache 2.0 - See LICENSE for more information.