Prepare your local PHP environment

1. Follow the steps in Service accounts to set up a service account as your Application Default Credentials. Following those steps, you should obtain both a service account key file (in JSON) and a GOOGLE_APPLICATION_CREDENTIALS environment variable that lets you authenticate to the Spanner API.

2. Install the following on your development machine if they are not already installed:

   • PHP
   • composer
   • gRPC

3. Clone the sample app repository to your local machine:

   git clone https://github.com/GoogleCloudPlatform/php-docs-samples
   

   Alternatively, you can download the sample as a zip file and extract it.

4. Change to the directory that contains the Spanner sample code:

   cd php-docs-samples/spanner
   

5. Install dependencies:

   composer install
   

   This installs the Spanner client library for PHP, which you can add to any project by running composer require google/cloud-spanner.

Create an instance

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases. When you create an instance, you choose an instance configuration, which determines where your data is stored, and also the number of nodes to use, which determines the amount of serving and storage resources in your instance.

See Create an instance to learn how to create a Spanner instance using any of the following methods. You can name your instance test-instance to use it with other topics in this document that reference an instance named test-instance.

• The Google Cloud CLI
• The Google Cloud console
• A client library (C++, C#, Go, Java, Node.js, PHP, Python, or Ruby)

Look through sample files

The samples repository contains a sample that shows how to use Spanner with PHP.

Take a look at the functions in src/create_database.php and src/add_column.php, which show how to create a database and modify a database schema. The data uses the example schema shown in the Schema and data model page.

Create a database

php src/create_database.php test-instance example-db

php src/pg_create_database.php test-instance example-db

You should see:

Created database example-db on instance test-instance

The following code creates a database and two tables in the database.

The next step is to write data to your database.

Create a database client

To do reads and writes, you need to obtain an instance of Google\Cloud\Spanner\Database.

You can think of a Database as a database connection: all of your interactions with Spanner must go through a Database. Typically you create a Database when your application starts up, then you re-use that Database to read, write, and execute transactions. Each client uses resources in Spanner.

If you create multiple clients in the same app, you should call Database::close to clean up the client's resources, including network connections, as soon as it is no longer needed.

Read more in the Database reference.

Write data with DML

You can insert data using Data Manipulation Language (DML) in a read-write transaction.

You use the executeUpdate() method to execute a DML statement.

Run the sample file src/write_data_with_dml.php.

php src/write_data_with_dml.php test-instance example-db

You should see:

Inserted 4 row(s).

Write data with mutations

You can also insert data using mutations.

You write data using the Database::insertBatch method. insertBatch adds new rows to a table. All inserts in a single batch are applied atomically.

This code shows how to write the data using mutations:

Run the sample file src/insert_data.php.

php src/insert_data.php test-instance example-db

You should see:

Inserted data.

Query data using SQL

Spanner supports a SQL interface for reading data, which you can access on the command line using the Google Cloud CLI or programmatically using the Spanner client library for PHP.

On the command line

Execute the following SQL statement to read the values of all columns from the Albums table:

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

The result shows:

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Use the Spanner client library for PHP

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner client library for PHP.

Use Database::execute() to run the SQL query.

Here's how to issue the query and access the data:

Run the sample file src/query_data.php.

php src/query_data.php test-instance example-db

You should see the following result:

SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold Your Peace
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified
SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk

Your results won't necessarily be in this order. If you need to ensure the ordering of the result, use an ORDER BY clause, as documented in SQL best practices.

Query using a SQL parameter

If your application has a frequently executed query, you can improve its performance by parameterizing it. The resulting parametric query can be cached and reused, which reduces compilation costs. For more information, see Use query parameters to speed up frequently executed queries.

Here is an example of using a parameter in the WHERE clause to query records containing a specific value for LastName.

Run the sample file src/query_data_with_parameter.php.

php src/query_data_with_parameter.php test-instance example-db

You should see the following result:

SingerId: 12, FirstName: Melissa, LastName: Garcia

Read data using the read API

In addition to Spanner's SQL interface, Spanner also supports a read interface.

Use Database::read() to read rows from the database. Use a KeySet object to define a collection of keys and key ranges to read.

Here's how to read the data:

Run the sample in read_data.php file.

php read_data.php test-instance example-db

You should see output similar to:

SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold your Peace
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table. Adding a new column to an existing table requires an update to your database schema. Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates don't require taking the database offline and they don't lock entire tables or columns; you can continue writing data to the database during the schema update. Read more about supported schema updates and schema change performance in Make schema updates.

Add a column

You can add a column on the command line using the Google Cloud CLI or programmatically using the Spanner client library for PHP.

On the command line

Use the following ALTER TABLE command to add the new column to the table:

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget BIGINT'

You should see:

Schema updating...done.

Use the Spanner client library for PHP

Use Database::updateDdl to modify the schema:

Run the sample file src/add_column.php.

php src/add_column.php test-instance example-db

You should see:

Added the MarketingBudget column.

Write data to the new column

The following code writes data to the new column. It sets MarketingBudget to 100000 for the row keyed by Albums(1, 1) and to 500000 for the row keyed by Albums(2, 2).

Run the sample file src/update_data.php.

php src/update_data.php test-instance example-db

You should see:

Updated data.

You can also execute a SQL query or a read call to fetch the values that you just wrote.

Here's the code to execute the query:

To execute this query, run the sample file src/query-data-with-new-column.php.

php src/query_data_with_new_column.php test-instance example-db

You should see:

SingerId: 1, AlbumId: 1, MarketingBudget: 100000
SingerId: 1, AlbumId: 2, MarketingBudget: 0
SingerId: 2, AlbumId: 1, MarketingBudget: 0
SingerId: 2, AlbumId: 2, MarketingBudget: 500000
SingerId: 2, AlbumId: 3, MarketingBudget: 0

Update data

You can update data using DML in a read-write transaction.

You use the executeUpdate() method to execute a DML statement.

Run the sample file src/write_data_with_dml_transaction.php.

php src/write_data_with_dml_transaction.php test-instance example-db

You should see:

Transaction complete.

Use a secondary index

Suppose you wanted to fetch all rows of Albums that have AlbumTitle values in a certain range. You could read all values from the AlbumTitle column using a SQL statement or a read call, and then discard the rows that don't meet the criteria, but doing this full table scan is expensive, especially for tables with a lot of rows. Instead you can speed up the retrieval of rows when searching by non-primary key columns by creating a secondary index on the table.

Adding a secondary index to an existing table requires a schema update. Like other schema updates, Spanner supports adding an index while the database continues to serve traffic. Spanner automatically backfills the index with your existing data. Backfills might take a few minutes to complete, but you don't need to take the database offline or avoid writing to the indexed table during this process. For more details, see Add a secondary index.

After you add a secondary index, Spanner automatically uses it for SQL queries that are likely to run faster with the index. If you use the read interface, you must specify the index that you want to use.

Add a secondary index

You can add an index on the command line using the gcloud CLI or programmatically using the Spanner client library for PHP.

On the command line

Use the following CREATE INDEX command to add an index to the database:

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

Schema updating...done.

Using the Spanner client library for PHP

Use Database::updateDdl to add an index:

Run the sample file src/create_index.php.

php src/create_index.php test-instance example-db

Adding an index can take a few minutes. After the index is added, you should see:

Added the AlbumsByAlbumTitle index.

Read using the index

For SQL queries, Spanner automatically uses an appropriate index. In the read interface, you must specify the index in your request.

To use the index in the read interface, use the Database::read method.

Run the sample file src/read_data_with_index.php.

php src/read_data_with_index.php test-instance example-db

You should see:

AlbumId: 2, AlbumTitle: Forever Hold your Peace
AlbumId: 2, AlbumTitle: Go, Go, Go
AlbumId: 1, AlbumTitle: Green
AlbumId: 3, AlbumTitle: Terrified
AlbumId: 1, AlbumTitle: Total Junk

Add an index for index-only reads

You might have noticed that the previous read example doesn't include reading the MarketingBudget column. This is because Spanner's read interface doesn't support the ability to join an index with a data table to look up values that are not stored in the index.

Create an alternate definition of AlbumsByAlbumTitle that stores a copy of MarketingBudget in the index.

On the command line

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) INCLUDE (MarketingBudget)

Adding an index can take a few minutes. After the index is added, you should see:

Schema updating...done.

Using the Spanner client library for PHP

Use Database::updateDdl to add an index with a STORING clause:

Run the sample file src/create_storing_index.php.

php src/create_storing_index.php test-instance example-db

You should see:

Added the AlbumsByAlbumTitle2 index.

Now you can execute a read that fetches all AlbumId, AlbumTitle, and MarketingBudget columns from the AlbumsByAlbumTitle2 index:

Run the sample file src/read_data_with_storing_index.php.

php src/read_data_with_storing_index.php test-instance example-db

You should see output similar to:

AlbumId: 2, AlbumTitle: Forever Hold your Peace, MarketingBudget: 300000
AlbumId: 2, AlbumTitle: Go, Go, Go, MarketingBudget: 0
AlbumId: 1, AlbumTitle: Green, MarketingBudget: 0
AlbumId: 3, AlbumTitle: Terrified, MarketingBudget: 0
AlbumId: 1, AlbumTitle: Total Junk, MarketingBudget: 300000

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp. Read-only transactions observe a consistent prefix of the transaction commit history, so your application always gets consistent data. Use a Snapshot object for executing read-only transactions. Use the Database::snapshot method to get a Snapshot object.

The following shows how to run a query and perform a read in the same read-only transaction:

Run the sample file src/read_only_transaction.php.

php src/read_only_transaction.php test-instance example-db

You should see output similar to:

Results from first read:
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold Your Peace
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified
SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk
Results from second read:
SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold Your Peace
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified

Cleanup

To avoid incurring additional charges to your Cloud Billing account for the resources used in this tutorial, drop the database and delete the instance that you created.

Delete the database

If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).

On the command line

gcloud spanner databases delete example-db --instance=test-instance

Using the Google Cloud console

1. Go to the Spanner Instances page in the Google Cloud console.

   Go to the Instances page

2. Click the instance.

3. Click the database that you want to delete.

4. In the Database details page, click Delete.

5. Confirm that you want to delete the database and click Delete.

Delete the instance

Deleting an instance automatically drops all databases created in that instance.

On the command line

gcloud spanner instances delete test-instance

Using the Google Cloud console

1. Go to the Spanner Instances page in the Google Cloud console.

   Go to the Instances page

2. Click your instance.

3. Click Delete.

4. Confirm that you want to delete the instance and click Delete.