Objectives
This tutorial walks you through the following steps using the Spanner database/sql driver:
- Create a Spanner instance and database.
- Write, read, and execute SQL queries on data in the database.
- Update the database schema.
- Update data using a read-write transaction.
- Add a secondary index to the database.
- Use the index to read and execute SQL queries on data.
- Retrieve data using a read-only transaction.
Costs
This tutorial uses Spanner, which is a billable component of the Google Cloud. For information on the cost of using Spanner, see Pricing.
Before you begin
Complete the steps described in Set up, which cover creating and setting a default Google Cloud project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.
In particular, make sure that you run gcloud auth
application-default login
to set up your local development environment with authentication
credentials.
Prepare your local database/sql environment
Download and install Go on your development machine if it isn't already installed.
Clone the sample repository to your local machine:
git clone https://github.com/googleapis/go-sql-spanner.git
Change to the directory that contains the Spanner sample code:
cd go-sql-spanner/snippets
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.
Execute the following command to create a Spanner instance in the region
us-central1
with 1 node:
gcloud spanner instances create test-instance --config=regional-us-central1 \
--description="Test Instance" --nodes=1
Note that this creates an instance with the following characteristics:
- Instance ID
test-instance
- Display name
Test Instance
- Instance configuration
regional-us-central1
(Regional configurations store data in one region, while multi-region configurations distribute data across multiple regions. For more information, see About instances.) - Node count of 1 (
node_count
corresponds to the amount of serving and storage resources available to databases in the instance. Learn more in Nodes and processing units.)
You should see:
Creating instance...done.
Look through sample files
The samples repository contains a sample that shows how to use Spanner with database/sql.
Take a look through thegetting_started_guide.go
file, which shows how to use
Spanner. The code shows how to create and use a new database. The data
uses the example schema shown in the
Schema and data model page.
Create a database
gcloud spanner databases create example-db --instance=test-instance
You should see:
Creating database...done.
Create tables
The following code creates two tables in the database.
Run the sample with the following command:
go run getting_started_guide.go createtables projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The next step is to write data to your database.
Create a connection
Before you can do reads or writes, you must create asql.DB
. sql.DB
contains a connection pool
that can be used to interact with Spanner. The database name and
other connection properties are specified in the database/sql data source name.
Write data with DML
You can insert data using Data Manipulation Language (DML) in a read-write transaction.
You use the ExecContext
function to execute a DML statement.
Run the sample with the following command:
go run getting_started_guide.go dmlwrite projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
4 records inserted.
Write data with mutations
You can also insert data using mutations.
A Mutation
is
a container for mutation operations. A Mutation
represents a sequence of
inserts, updates, and deletes that Spanner applies atomically to
different rows and tables in a Spanner database.
Use Mutation.InsertOrUpdate()
to construct an INSERT_OR_UPDATE
mutation, which adds a new row or updates
column values if the row already exists. Alternatively, use the
Mutation.Insert()
method to construct an INSERT
mutation, which adds a new row.
conn.Raw
function to get a reference to the underlying
Spanner connection. The SpannerConn.Apply
function applies
mutations atomically to the database.
The following code shows how to write the data using mutations:
Run the following example using the write
argument:
go run getting_started_guide.go write projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
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 database/sql driver.
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 should be:
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 database/sql driver
In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Spanner database/sql driver.
The following functions and structs are used to execute a SQL query:- The
QueryContext
function in theDB
struct: use this to execute a SQL statement that returns rows, such as a query or a DML statement with aTHEN RETURN
clause. - The
Rows
struct: use this to access the data returned by a SQL statement.
The following example uses the QueryContext
function:
Run the example with the following command:
go run getting_started_guide.go query projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified
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
.
The Spanner database/sql driver supports both positional and named
query parameters. A ?
in a SQL statement indicates a positional query
parameter. Pass the query parameter values as additional arguments to the
QueryContext
function. For example:
Run the example with the following command:
go run getting_started_guide.go querywithparameter projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
12 Melissa Garcia
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 database/sql driver.
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'
You should see:
Schema updating...done.
Use the Spanner database/sql driver
Use theExecContext
function to
modify the schema:
Run the example with the following command:
go run getting_started_guide.go addcolumn projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
Added MarketingBudget column.
Execute a DDL batch
We recommend that you execute multiple schema modifications in one batch. Use
the START BATCH DDL
and RUN BATCH
commands to execute a DDL batch. The
following example creates two tables in one batch:
Run the example with the following command:
go run getting_started_guide.go ddlbatch projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
Added Venues and Concerts tables.
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 example with the following command:
go run getting_started_guide.go update projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
Updated 2 albums
You can also execute a SQL query to fetch the values that you just wrote.
The following example uses the QueryContext
function to execute a query:
To execute this query, run the following command:
go run getting_started_guide.go querymarketingbudget projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
You should see:
1 1 100000
1 2 null
2 1 null
2 2 500000
2 3 null
Update data
You can update data using DML in a read-write transaction.
Call DB.BeginTx
to execute read-write
transactions in database/sql.
Run the example with the following command:
go run getting_started_guide.go writewithtransactionusingdml projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
Transaction tags and request tags
Use transaction tags and request tags
to troubleshoot transactions and queries in Spanner. You can pass
additional transaction options to the spannerdriver.BeginReadWriteTransaction
function.
Use spannerdriver.ExecOptions
to pass additional query options for a SQL
statement. For example:
Run the example with the following command:
go run getting_started_guide.go tags projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
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.
Set the TxOptions.ReadOnly
field to
true
to execute a read-only transaction.
The following shows how to run a query and perform a read in the same read-only transaction:
Run the example with the following command:
go run getting_started_guide.go readonlytransaction projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
The result shows:
1 1 Total Junk
1 2 Go, Go, Go
2 1 Green
2 2 Forever Hold Your Peace
2 3 Terrified
2 2 Forever Hold Your Peace
1 2 Go, Go, Go
2 1 Green
2 3 Terrified
1 1 Total Junk
Partitioned queries and Data Boost
The partitionQuery
API divides a query into smaller pieces, or partitions, and uses multiple
machines to fetch the partitions in parallel. Each partition is identified by a
partition token. The partitionQuery API has higher latency than the standard
query API,
because it's only intended for bulk operations such as exporting or scanning the
whole database.
Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned Spanner instance. Data Boost only supports partitioned queries.
The following example shows how to execute a partitioned query with Data Boost with the database/sql driver:
Run the example with the following command:
go run getting_started_guide.go databoost projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
Partitioned DML
Partitioned Data Manipulation Language (DML) is designed for the following types of bulk updates and deletes:
- Periodic cleanup and garbage collection.
- Backfilling new columns with default values.
Run the example with the following command:
go run getting_started_guide.go pdml projects/$GCLOUD_PROJECT/instances/test-instance/databases/example-db
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
Go to the Spanner Instances page in the Google Cloud console.
Click the instance.
Click the database that you want to delete.
In the Database details page, click Delete.
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
Go to the Spanner Instances page in the Google Cloud console.
Click your instance.
Click Delete.
Confirm that you want to delete the instance and click Delete.
What's next
Learn how to access Spanner with a virtual machine instance.
Learn about authorization and authentication credentials in Authenticate to Cloud services using client libraries.
Learn more about Spanner Schema design best practices.