Getting started with Spanner in Go

Objectives

This tutorial walks you through the following steps using the Spannerclient library for Go:

  • 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 theGoogle Cloud. For information on the cost of using Spanner, seePricing.

Before you begin

Complete the steps described inSet up, which cover creating andsetting a default Google Cloud project, enabling billing, enabling theCloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to usethe Cloud Spanner API.

In particular, make sure that you rungcloud authapplication-default loginto set up your local development environment with authenticationcredentials.

Note: If you don't plan to keep the resources that you create in this tutorial,consider creating a new Google Cloud project instead of selecting an existingproject. After you finish the tutorial, you can delete the project, removing allresources associated with the project.

Prepare your local Go environment

  1. Install Go (download) on your developmentmachine if it is not already installed.

  2. Configure theGOPATH environment variable if it is not already configured,as described inTest your installation.

  3. Download the samples to your machine.

    gitclonehttps://github.com/GoogleCloudPlatform/golang-samples $GOPATH/src/github.com/GoogleCloudPlatform/golang-samples

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

    cd$GOPATH/src/github.com/GoogleCloudPlatform/golang-samples/spanner/spanner_snippets

  5. Set thePROJECT_ID environment variable to your Google Cloud projectID:

    exportPROJECT_ID=[MY_PROJECT_ID]

Create an instance

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

SeeCreate an instanceto learn how to create a Spanner instance using any of thefollowing methods. You can name your instancetest-instance to use it withother topics in this document that reference an instance namedtest-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 Spannerwith Go.

Take a look through thesnippet.go file, which shows how to useSpanner. The code shows how to create and use a new database. The datauses the example schema shown in theSchema and data model page.

Create a database

GoogleSQL

gorunsnippet.gocreatedatabaseprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgcreatedatabaseprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

Createddatabase[example-db]
The following code creates a database and two tables in the database.Note: The subsequent code samples use these two tables. If you don't executethis code, then create the tables by using the Google Cloud console or thegcloud CLI. For more information, see theexample schema.

GoogleSQL

import("context""fmt""io""regexp"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funccreateDatabase(ctxcontext.Context,wio.Writer,dbstring)error{matches:=regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)ifmatches==nil||len(matches)!=3{returnfmt.Errorf("Invalid database id %s",db)}adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()op,err:=adminClient.CreateDatabase(ctx,&adminpb.CreateDatabaseRequest{Parent:matches[1],CreateStatement:"CREATE DATABASE `"+matches[2]+"`",ExtraStatements:[]string{`CREATE TABLE Singers (SingerId   INT64 NOT NULL,FirstName  STRING(1024),LastName   STRING(1024),SingerInfo BYTES(MAX),FullName   STRING(2048) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED) PRIMARY KEY (SingerId)`,`CREATE TABLE Albums (SingerId     INT64 NOT NULL,AlbumId      INT64 NOT NULL,AlbumTitle   STRING(MAX)) PRIMARY KEY (SingerId, AlbumId),INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,},})iferr!=nil{returnerr}if_,err:=op.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Created database [%s]\n",db)returnnil}

PostgreSQL

import("context""fmt""io""regexp"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")// pgCreateDatabase shows how to create a Spanner database that uses the// PostgreSQL dialect.funcpgCreateDatabase(ctxcontext.Context,wio.Writer,dbstring)error{// db := "projects/my-project/instances/my-instance/databases/my-database"matches:=regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)ifmatches==nil||len(matches)!=3{returnfmt.Errorf("invalid database id %v",db)}adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()// Databases with PostgreSQL dialect do not support extra DDL statements in the `CreateDatabase` call.req:=&adminpb.CreateDatabaseRequest{Parent:matches[1],DatabaseDialect:adminpb.DatabaseDialect_POSTGRESQL,// Note that PostgreSQL uses double quotes for quoting identifiers. This also// includes database names in the CREATE DATABASE statement.CreateStatement:`CREATE DATABASE "`+matches[2]+`"`,}opCreate,err:=adminClient.CreateDatabase(ctx,req)iferr!=nil{returnerr}if_,err:=opCreate.Wait(ctx);err!=nil{returnerr}updateReq:=&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{`CREATE TABLE Singers (SingerId   bigint NOT NULL PRIMARY KEY,FirstName  varchar(1024),LastName   varchar(1024),SingerInfo bytea)`,`CREATE TABLE Albums (AlbumId      bigint NOT NULL,SingerId     bigint NOT NULL REFERENCES Singers (SingerId),AlbumTitle   text,                PRIMARY KEY(SingerId, AlbumId))`,`CREATE TABLE Venues (VenueId  bigint NOT NULL PRIMARY KEY,Name     varchar(1024) NOT NULL)`,},}opUpdate,err:=adminClient.UpdateDatabaseDdl(ctx,updateReq)iferr!=nil{returnerr}iferr:=opUpdate.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Created Spanner PostgreSQL database [%v]\n",db)returnnil}

The next step is to write data to your database.

Create a database client

Before you can do reads or writes, you must create aClient:

import("context""io""cloud.google.com/go/spanner"database"cloud.google.com/go/spanner/admin/database/apiv1")funccreateClients(wio.Writer,dbstring)error{ctx:=context.Background()adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()dataClient,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferdataClient.Close()_=adminClient_=dataClientreturnnil}

You can think of aClient as a database connection: all of your interactionswith Spanner must go through aClient. Typically you create aClientwhen your application starts up, then you re-use thatClient to read, write,and execute transactions. Each client uses resources inSpanner.

If you create multiple clients in the same app, you should callClient.Close() to clean upthe client's resources, including network connections, as soon as it is nolonger needed.

Read more in theClientreference.

The code in the previous example also shows how to create aDatabaseAdminClient,which is used to create a database.

Write data with DML

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

You use theUpdate() method to execute a DML statement.

GoogleSQL

import("context""fmt""io""cloud.google.com/go/spanner")funcwriteUsingDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`INSERT Singers (SingerId, FirstName, LastName) VALUES(12, 'Melissa', 'Garcia'),(13, 'Russell', 'Morales'),(14, 'Jacqueline', 'Long'),(15, 'Dylan', 'Shaw')`,}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)returnerr})returnerr}

PostgreSQL

import("context""fmt""io""cloud.google.com/go/spanner")funcpgWriteUsingDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`INSERT INTO Singers (SingerId, FirstName, LastName) VALUES(12, 'Melissa', 'Garcia'),(13, 'Russell', 'Morales'),(14, 'Jacqueline', 'Long'),(15, 'Dylan', 'Shaw')`,}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)returnerr})returnerr}

Run the sample using thedmlwrite argument for Google SQL and thepgdmlwrite argument for PostgreSQL:

GoogleSQL

gorunsnippet.godmlwriteprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgdmlwriteprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

4record(s)inserted.
Note: There are limits to commit size. SeeCRUD limitfor more information.

Write data with mutations

You can also insert data usingmutations.

AMutation isa container for mutation operations. AMutation represents a sequence ofinserts, updates, and deletes that Spanner applies atomically todifferent rows and tables in a Spanner database.

UseMutation.InsertOrUpdate()to construct anINSERT_OR_UPDATE mutation, which adds a new row or updatescolumn values if the row already exists. Alternatively, use theMutation.Insert()method to construct anINSERT mutation, which adds a new row.

Client.Apply() appliesmutations atomically to a database.

This code shows how to write the data using mutations:

import("context""io""cloud.google.com/go/spanner")funcwrite(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()singerColumns:=[]string{"SingerId","FirstName","LastName"}albumColumns:=[]string{"SingerId","AlbumId","AlbumTitle"}m:=[]*spanner.Mutation{spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{1,"Marc","Richards"}),spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{2,"Catalina","Smith"}),spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{3,"Alice","Trentor"}),spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{4,"Lea","Martin"}),spanner.InsertOrUpdate("Singers",singerColumns,[]interface{}{5,"David","Lomond"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{1,1,"Total Junk"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{1,2,"Go, Go, Go"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{2,1,"Green"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{2,2,"Forever Hold Your Peace"}),spanner.InsertOrUpdate("Albums",albumColumns,[]interface{}{2,3,"Terrified"}),}_,err=client.Apply(ctx,m)returnerr}

Run the sample using thewrite argument:

gorunsnippet.gowriteprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see the command run successfully.Note: There are limits to commit size. SeeCRUD limitfor more information.

Query data using SQL

Spanner supports a SQL interface for reading data, which you canaccess on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner client library for Go.

On the command line

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

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance\--sql='SELECTSingerId,AlbumId,AlbumTitleFROMAlbums'
Note: For the GoogleSQL reference, seeQuery syntax in GoogleSQLand for PostgreSQL reference, seePostgreSQL lexical structure and syntax.

The result shows:

SingerIdAlbumIdAlbumTitle11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Use the Spanner client library for Go

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

The following methods and types are used to run the SQL query:

  • Client.Single(): usethis to read the value of one or more columns from one or more rows in aSpanner table.Client.Single returns aReadOnlyTransaction,which is used for running a read or SQL statement.
  • ReadOnlyTransaction.Query():use this method to execute a query against a database.
  • TheStatement type: usethis to construct a SQL string.
  • TheRow type: use this toaccess the data returned by a SQL statement or read call.

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

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcquery(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, AlbumTitle FROM Albums`}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerID,albumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}}

Run the sample using thequery argument.

gorunsnippet.goqueryprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see the following result:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve itsperformance by parameterizing it. The resulting parametric query can be cachedand reused, which reduces compilation costs. For more information, seeUse query parameters to speed up frequently executed queries.

Here is an example of using a parameter in theWHERE clause to query recordscontaining a specific value forLastName.

GoogleSQL

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcqueryWithParameter(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, FirstName, LastName FROM SingersWHERE LastName = @lastName`,Params:map[string]interface{}{"lastName":"Garcia",},}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerIDint64varfirstName,lastNamestringiferr:=row.Columns(&singerID,&firstName,&lastName);err!=nil{returnerr}fmt.Fprintf(w,"%d %s %s\n",singerID,firstName,lastName)}}

PostgreSQL

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")// pgQueryParameter shows how to execute a query with parameters on a Spanner// PostgreSQL database. The PostgreSQL dialect uses positional parameters, as// opposed to the named parameters of Cloud Spanner.funcpgQueryParameter(wio.Writer,dbstring)error{// db := "projects/my-project/instances/my-instance/databases/my-database"ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, FirstName, LastName FROM SingersWHERE LastName = $1`,Params:map[string]interface{}{"p1":"Garcia",},}typeSingersstruct{SingerIDint64FirstName,LastNamestring}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varvalSingersiferr:=row.ToStruct(&val);err!=nil{returnerr}fmt.Fprintf(w,"%d %s %s\n",val.SingerID,val.FirstName,val.LastName)}}

Run the sample using thequerywithparameter argument for Google SQL and thepgqueryparameter argument for PostgreSQL.

GoogleSQL

gorunsnippet.goquerywithparameterprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgqueryparameterprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see output similar to:

12MelissaGarcia

Read data using the read API

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

UseReadOnlyTransaction.Read()to read rows from the database. UseKeySetto define a collection of keys and key ranges to read.

Here's how to read the data:

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcread(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()iter:=client.Single().Read(ctx,"Albums",spanner.AllKeys(),[]string{"SingerId","AlbumId","AlbumTitle"})deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerID,albumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}}

Run the sample using theread argument.

gorunsnippet.goreadprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see output similar to:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Update the database schema

Assume you need to add a new column calledMarketingBudget to theAlbumstable. Adding a new column to an existing table requires an update to yourdatabase schema. Spanner supports schema updates to a database while thedatabase continues to serve traffic. Schema updates don't require taking thedatabase offline and they don't lock entire tables or columns; you can continuewriting data to the database during the schema update. Read more about supportedschema updates and schema change performance inMake schema updates.

Add a column

You can add a column on the command line using the Google Cloud CLI orprogrammatically usingthe Spanner client library for Go.

On the command line

Use the followingALTER TABLE command toadd the new column to the table:

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetINT64'

PostgreSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='ALTERTABLEAlbumsADDCOLUMNMarketingBudgetBIGINT'

You should see:

Schemaupdating...done.

Use the Spanner client library for Go

UseDatabaseAdminClient.UpdateDatabaseDdl()to modify the schema:

GoogleSQL

import("context""fmt""io"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funcaddNewColumn(ctxcontext.Context,wio.Writer,dbstring)error{adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()op,err:=adminClient.UpdateDatabaseDdl(ctx,&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64",},})iferr!=nil{returnerr}iferr:=op.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Added MarketingBudget column\n")returnnil}

PostgreSQL

import("context""fmt""io"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funcpgAddNewColumn(ctxcontext.Context,wio.Writer,dbstring)error{adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()op,err:=adminClient.UpdateDatabaseDdl(ctx,&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{"ALTER TABLE Albums ADD COLUMN MarketingBudget bigint",},})iferr!=nil{returnerr}iferr:=op.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Added MarketingBudget column\n")returnnil}

Run the sample using theaddnewcolumn argument for Google SQL and thepgaddnewcolumn argument for PostgreSQL.

GoogleSQL

gorunsnippet.goaddnewcolumnprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgaddnewcolumnprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

AddedMarketingBudgetcolumn.

Write data to the new column

The following code writes data to the new column. It setsMarketingBudget to100000 for the row keyed byAlbums(1, 1) and to500000 for the row keyedbyAlbums(2, 2).

import("context""io""cloud.google.com/go/spanner")funcupdate(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()cols:=[]string{"SingerId","AlbumId","MarketingBudget"}_,err=client.Apply(ctx,[]*spanner.Mutation{spanner.Update("Albums",cols,[]interface{}{1,1,100000}),spanner.Update("Albums",cols,[]interface{}{2,2,500000}),})returnerr}

Run the sample using theupdate argument.

gorunsnippet.goupdateprojects/PROJECT_ID/instances/test-instance/databases/example-db

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

Here's the code to execute the query:

GoogleSQL

import("context""fmt""io""strconv""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcqueryNewColumn(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, MarketingBudget FROM Albums`}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerID,albumIDint64varmarketingBudgetspanner.NullInt64iferr:=row.ColumnByName("SingerId",&singerID);err!=nil{returnerr}iferr:=row.ColumnByName("AlbumId",&albumID);err!=nil{returnerr}iferr:=row.ColumnByName("MarketingBudget",&marketingBudget);err!=nil{returnerr}budget:="NULL"ifmarketingBudget.Valid{budget=strconv.FormatInt(marketingBudget.Int64,10)}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,budget)}}

PostgreSQL

import("context""fmt""io""strconv""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcpgQueryNewColumn(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, MarketingBudget FROM Albums`}iter:=client.Single().Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerID,albumIDint64varmarketingBudgetspanner.NullInt64iferr:=row.ColumnByName("singerid",&singerID);err!=nil{returnerr}iferr:=row.ColumnByName("albumid",&albumID);err!=nil{returnerr}iferr:=row.ColumnByName("marketingbudget",&marketingBudget);err!=nil{returnerr}budget:="NULL"ifmarketingBudget.Valid{budget=strconv.FormatInt(marketingBudget.Int64,10)}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,budget)}}

To execute this query, run the sample using thequerynewcolumn argument for Google SQL and thepgquerynewcolumn argument for PostgreSQL.

GoogleSQL

gorunsnippet.goquerynewcolumnprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgquerynewcolumnprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

1110000012NULL21NULL2250000023NULL

Update data

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

You use theUpdate() method to execute a DML statement.

GoogleSQL

import("context""fmt""io""cloud.google.com/go/spanner")funcwriteWithTransactionUsingDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{// getBudget returns the budget for a record with a given albumId and singerId.getBudget:=func(albumID,singerIDint64)(int64,error){key:=spanner.Key{albumID,singerID}row,err:=txn.ReadRow(ctx,"Albums",key,[]string{"MarketingBudget"})iferr!=nil{return0,err}varbudgetint64iferr:=row.Column(0,&budget);err!=nil{return0,err}returnbudget,nil}// updateBudget updates the budget for a record with a given albumId and singerId.updateBudget:=func(singerID,albumID,albumBudgetint64)error{stmt:=spanner.Statement{SQL:`UPDATE AlbumsSET MarketingBudget = @AlbumBudgetWHERE SingerId = @SingerId and AlbumId = @AlbumId`,Params:map[string]interface{}{"SingerId":singerID,"AlbumId":albumID,"AlbumBudget":albumBudget,},}_,err:=txn.Update(ctx,stmt)returnerr}// Transfer the marketing budget from one album to another. By keeping the actions// in a single transaction, it ensures the movement is atomic.consttransferAmt=200000album2Budget,err:=getBudget(2,2)iferr!=nil{returnerr}// The transaction will only be committed if this condition still holds at the time// of commit. Otherwise it will be aborted and the callable will be rerun by the// client library.ifalbum2Budget>=transferAmt{album1Budget,err:=getBudget(1,1)iferr!=nil{returnerr}iferr=updateBudget(1,1,album1Budget+transferAmt);err!=nil{returnerr}iferr=updateBudget(2,2,album2Budget-transferAmt);err!=nil{returnerr}fmt.Fprintf(w,"Moved %d from Album2's MarketingBudget to Album1's.",transferAmt)}returnnil})returnerr}

PostgreSQL

import("context""fmt""io""cloud.google.com/go/spanner")funcpgWriteWithTransactionUsingDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{// getBudget returns the budget for a record with a given albumId and singerId.getBudget:=func(albumID,singerIDint64)(int64,error){key:=spanner.Key{albumID,singerID}row,err:=txn.ReadRow(ctx,"Albums",key,[]string{"MarketingBudget"})iferr!=nil{return0,fmt.Errorf("error reading marketing budget for album_id=%v,singer_id=%v: %w",albumID,singerID,err)}varbudgetint64iferr:=row.Column(0,&budget);err!=nil{return0,fmt.Errorf("error decoding marketing budget for album_id=%v,singer_id=%v: %w",albumID,singerID,err)}returnbudget,nil}// updateBudget updates the budget for a record with a given albumId and singerId.updateBudget:=func(singerID,albumID,albumBudgetint64)error{stmt:=spanner.Statement{SQL:`UPDATE AlbumsSET MarketingBudget = $1WHERE SingerId = $2 and AlbumId = $3`,Params:map[string]interface{}{"p1":albumBudget,"p2":singerID,"p3":albumID,},}_,err:=txn.Update(ctx,stmt)returnerr}// Transfer the marketing budget from one album to another. By keeping the actions// in a single transaction, it ensures the movement is atomic.consttransferAmt=200000album2Budget,err:=getBudget(2,2)iferr!=nil{returnerr}// The transaction will only be committed if this condition still holds at the time// of commit. Otherwise it will be aborted and the callable will be rerun by the// client library.ifalbum2Budget>=transferAmt{album1Budget,err:=getBudget(1,1)iferr!=nil{returnerr}iferr=updateBudget(1,1,album1Budget+transferAmt);err!=nil{returnerr}iferr=updateBudget(2,2,album2Budget-transferAmt);err!=nil{returnerr}fmt.Fprintf(w,"Moved %d from Album2's MarketingBudget to Album1's.",transferAmt)}returnnil})returnerr}

Run the sample using thedmlwritetxn argument.

gorunsnippet.godmlwritetxnprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

Moved200000fromAlbum2'sMarketingBudgettoAlbum1's.
Note: You can alsoupdate data using mutations.

Use a secondary index

Suppose you wanted to fetch all rows ofAlbums that haveAlbumTitle valuesin a certain range. You could read all values from theAlbumTitle column usinga SQL statement or a read call, and then discard the rows that don't meet thecriteria, but doing this full table scan is expensive, especially for tableswith a lot of rows. Instead you can speed up the retrieval of rows whensearching by non-primary key columns by creating asecondary index on the table.

Adding a secondary index to an existing table requires a schema update. Likeother schema updates, Spanner supports adding an index while thedatabase continues to serve traffic. Spanner automatically backfills theindex 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 indexedtable during this process. For more details, seeAdd a secondary index.

After you add a secondary index, Spanner automatically uses it forSQL queries that are likely to run faster with the index. If you use the readinterface, 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 orprogrammatically using the Spanner client library for Go.

On the command line

Use the followingCREATE INDEXcommand to add an index to the database:

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

Schemaupdating...done.

Using the Spanner client library for Go

UseUpdateDatabaseDdl()to add an index:

import("context""fmt""io"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funcaddIndex(ctxcontext.Context,wio.Writer,dbstring)error{adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()op,err:=adminClient.UpdateDatabaseDdl(ctx,&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)",},})iferr!=nil{returnerr}iferr:=op.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Added index\n")returnnil}

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

Addedindex

Read using the index

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

To use the index in the read interface, useReadOnlyTransaction.ReadUsingIndex(), which reads zero ormore rows from a database using an index.

The following code fetches allAlbumId, andAlbumTitle columns from theAlbumsByAlbumTitle index.

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcreadUsingIndex(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()iter:=client.Single().ReadUsingIndex(ctx,"Albums","AlbumsByAlbumTitle",spanner.AllKeys(),[]string{"AlbumId","AlbumTitle"})deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varalbumIDint64varalbumTitlestringiferr:=row.Columns(&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %s\n",albumID,albumTitle)}}

Run the sample using thereadindex argument.

gorunsnippet.goreadindexprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see:

2ForeverHoldYourPeace2Go,Go,Go1Green3Terrified1TotalJunk

Add an index for index-only reads

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

Create an alternate definition ofAlbumsByAlbumTitle that stores a copy ofMarketingBudget in the index.

On the command line

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)STORING(MarketingBudget)

PostgreSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance\--ddl='CREATEINDEXAlbumsByAlbumTitle2ONAlbums(AlbumTitle)INCLUDE(MarketingBudget)

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

Schemaupdating...done.

Using the Spanner client library for Go

UseUpdateDatabaseDdl()to add an index with aSTORING clause for GoogleSQL andINCLUDE clause for PostgreSQL:

GoogleSQL

import("context""fmt""io"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funcaddStoringIndex(ctxcontext.Context,wio.Writer,dbstring)error{adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()op,err:=adminClient.UpdateDatabaseDdl(ctx,&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)",},})iferr!=nil{returnerr}iferr:=op.Wait(ctx);err!=nil{returnerr}fmt.Fprintf(w,"Added storing index\n")returnnil}

PostgreSQL

import("context""fmt""io"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")// pgAddStoringIndex shows how to create 'STORING' indexes on a Spanner// PostgreSQL database. The PostgreSQL dialect uses INCLUDE keyword, as// opposed to the STORING keyword of Cloud Spanner.funcpgAddStoringIndex(ctxcontext.Context,wio.Writer,dbstring)error{// db := "projects/my-project/instances/my-instance/databases/my-database"adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnfmt.Errorf("failed to initialize spanner database admin client: %w",err)}deferadminClient.Close()op,err:=adminClient.UpdateDatabaseDdl(ctx,&adminpb.UpdateDatabaseDdlRequest{Database:db,Statements:[]string{"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) INCLUDE (MarketingBudget)",},})iferr!=nil{returnfmt.Errorf("failed to execute spanner database DDL request: %w",err)}iferr:=op.Wait(ctx);err!=nil{returnfmt.Errorf("failed to complete spanner database DDL request: %w",err)}fmt.Fprintf(w,"Added storing index\n")returnnil}

Run the sample using theaddstoringindex argument.

GoogleSQL

gorunsnippet.goaddstoringindexprojects/PROJECT_ID/instances/test-instance/databases/example-db

PostgreSQL

gorunsnippet.gopgaddstoringindexprojects/PROJECT_ID/instances/test-instance/databases/example-db

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

Addedstoringindex

Now you can execute a read that fetches allAlbumId,AlbumTitle, andMarketingBudget columns from theAlbumsByAlbumTitle2 index:

import("context""fmt""io""strconv""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcreadStoringIndex(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()iter:=client.Single().ReadUsingIndex(ctx,"Albums","AlbumsByAlbumTitle2",spanner.AllKeys(),[]string{"AlbumId","AlbumTitle","MarketingBudget"})deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varalbumIDint64varmarketingBudgetspanner.NullInt64varalbumTitlestringiferr:=row.Columns(&albumID,&albumTitle,&marketingBudget);err!=nil{returnerr}budget:="NULL"ifmarketingBudget.Valid{budget=strconv.FormatInt(marketingBudget.Int64,10)}fmt.Fprintf(w,"%d %s %s\n",albumID,albumTitle,budget)}}

Run the sample using thereadstoringindex argument.

gorunsnippet.goreadstoringindexprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see output similar to:

2ForeverHoldYourPeace3000002Go,Go,GoNULL1GreenNULL3TerrifiedNULL1TotalJunk300000

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp.Read-onlytransactions observe a consistentprefix of the transaction commit history, so your application always getsconsistent data.Use theReadOnlyTransactiontype for executing read-only transactions. UseClient.ReadOnlyTransaction()to get aReadOnlyTransaction.

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

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcreadOnlyTransaction(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()ro:=client.ReadOnlyTransaction()deferro.Close()stmt:=spanner.Statement{SQL:`SELECT SingerId, AlbumId, AlbumTitle FROM Albums`}iter:=ro.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varsingerIDint64varalbumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}iter=ro.Read(ctx,"Albums",spanner.AllKeys(),[]string{"SingerId","AlbumId","AlbumTitle"})deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{returnnil}iferr!=nil{returnerr}varsingerIDint64varalbumIDint64varalbumTitlestringiferr:=row.Columns(&singerID,&albumID,&albumTitle);err!=nil{returnerr}fmt.Fprintf(w,"%d %d %s\n",singerID,albumID,albumTitle)}}

Run the sample using thereadonlytransaction argument.

gorunsnippet.goreadonlytransactionprojects/PROJECT_ID/instances/test-instance/databases/example-db

You should see output similar to:

22ForeverHoldYourPeace12Go,Go,Go21Green23Terrified11TotalJunk11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Cleanup

To avoid incurring additional charges to your Cloud Billing account for theresources used in this tutorial, drop the database and delete the instance thatyou 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 wouldstill incur charges for the instance).

On the command line

gcloudspannerdatabasesdeleteexample-db--instance=test-instance

Using the Google Cloud console

  1. Go to theSpanner 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 theDatabase details page, clickDelete.

  5. Confirm that you want to delete the database and clickDelete.

Delete the instance

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

On the command line

gcloudspannerinstancesdeletetest-instance

Using the Google Cloud console

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

    Go to the Instances page

  2. Click your instance.

  3. ClickDelete.

  4. Confirm that you want to delete the instance and clickDelete.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.