Getting started with Spanner in Go database/sql

Objectives

This tutorial walks you through the following steps using the Spannerdatabase/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 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 database/sql environment

  1. Download and installGo on your developmentmachine if it isn't already installed.

  2. Clone the sample repository to your local machine:

    gitclonehttps://github.com/googleapis/go-sql-spanner.git

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

    cdgo-sql-spanner/snippets

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 database/sql.

Take a look through thegetting_started_guide.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

gcloudspannerdatabasescreateexample-db--instance=test-instance

PostgreSQL

gcloudspannerdatabasescreateexample-db--instance=test-instance \--database-dialect=POSTGRESQL

You should see:

Creatingdatabase...done.

Create tables

The following code creates 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""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcCreateTables(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//CreatetwotablesinonebatchonSpanner.conn,err:=db.Conn(ctx)deferconn.Close()//StartaDDLbatchontheconnection.//ThisinstructstheconnectiontobufferallDDLstatementsuntilthe//command`runbatch`isexecuted.if_,err:=conn.ExecContext(ctx,"start batch ddl");err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),SingerInfoBYTES(MAX))PRIMARYKEY(SingerId)`);err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`CREATETABLEAlbums(SingerIdINT64NOTNULL,AlbumIdINT64NOTNULL,AlbumTitleSTRING(MAX))PRIMARYKEY(SingerId,AlbumId),INTERLEAVEINPARENTSingersONDELETECASCADE`);err!=nil{returnerr}//`runbatch`sendstheDDLstatementstoSpannerandblocksuntil//allstatementshavefinishedexecuting.if_,err:=conn.ExecContext(ctx,"run batch");err!=nil{returnerr}fmt.Fprintf(w,"Created Singers & Albums tables in database: [%s]\n",databaseName)returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcCreateTablesPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//CreatetwotablesinonebatchonSpannerPostgreSQL.conn,err:=db.Conn(ctx)iferr!=nil{returnerr}deferfunc(){_=conn.Close()}()//StartaDDLbatchontheconnection.//ThisinstructstheconnectiontobufferallDDLstatementsuntilthe//command`runbatch`isexecuted.if_,err:=conn.ExecContext(ctx,"start batch ddl");err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`createtablesingers(singer_idbigintnotnullprimarykey,first_namevarchar(1024),last_namevarchar(1024),singer_infobytea)`);err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`createtablealbums(singer_idbigintnotnull,album_idbigintnotnull,album_titlevarchar,primarykey(singer_id,album_id))interleaveinparentsingersondeletecascade`);err!=nil{returnerr}//`runbatch`sendstheDDLstatementstoSpannerandblocksuntil//allstatementshavefinishedexecuting.if_,err:=conn.ExecContext(ctx,"run batch");err!=nil{returnerr}_,_=fmt.Fprintf(w,"Created singers & albums tables in database: [%s]\n",databaseName)returnnil}

Run the sample with the following command:

GoogleSQL

gorungetting_started_guide.gocreatetablesprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.gocreatetablespgprojects/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 poolthat can be used to interact with Spanner. The database name andother connection properties are specified in the database/sql data source name.

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcCreateConnection(ctxcontext.Context,wio.Writer,databaseNamestring)error{//ThedataSourceNameshouldstartwithafullyqualifiedSpannerdatabasename//intheformat`projects/my-project/instances/my-instance/databases/my-database`.//Additionalpropertiescanbeaddedafterthedatabasenameby//addingoneormore`;name=value`pairs.dsn:=fmt.Sprintf("%s;numChannels=8",databaseName)db,err:=sql.Open("spanner",dsn)iferr!=nil{returnerr}deferdb.Close()row:=db.QueryRowContext(ctx,"select 'Hello world!' as hello")varmsgstringiferr:=row.Scan(&msg);err!=nil{returnerr}fmt.Fprintf(w,"Greeting from Spanner:%s\n",msg)returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcCreateConnectionPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{//ThedataSourceNameshouldstartwithafullyqualifiedSpannerdatabasename//intheformat`projects/my-project/instances/my-instance/databases/my-database`.//Additionalpropertiescanbeaddedafterthedatabasenameby//addingoneormore`;name=value`pairs.dsn:=fmt.Sprintf("%s;numChannels=8",databaseName)db,err:=sql.Open("spanner",dsn)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//TheSpannerdatabase/sqldriversupportsbothPostgreSQL-stylequery//parameters($1,$2,...)andpositionalqueryparameters(?,?,...).row:=db.QueryRowContext(ctx,"select $1 as hello","Hello world!")varmsgstringiferr:=row.Scan(&msg);err!=nil{returnerr}_,_=fmt.Fprintf(w,"Greeting from Spanner PostgreSQL:%s\n",msg)returnnil}

Write data with DML

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

You use theExecContext function to execute a DML statement.

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcWriteDataWithDml(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//Add4rowsinonestatement.//Thedatabase/sqldriversupportspositionalqueryparameters.res,err:=db.ExecContext(ctx,"INSERT INTO Singers (SingerId, FirstName, LastName) "+"VALUES (?, ?, ?), (?, ?, ?), "+"       (?, ?, ?), (?, ?, ?)",12,"Melissa","Garcia",13,"Russel","Morales",14,"Jacqueline","Long",15,"Dylan","Shaw")iferr!=nil{returnerr}c,err:=res.RowsAffected()iferr!=nil{returnerr}fmt.Fprintf(w,"%v records inserted\n",c)returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcWriteDataWithDmlPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//Add4rowsinonestatement.//Thedatabase/sqldriversupportspositionalqueryparameters.res,err:=db.ExecContext(ctx,"insert into singers (singer_id, first_name, last_name) "+"values (?, ?, ?), (?, ?, ?), "+"       (?, ?, ?), (?, ?, ?)",12,"Melissa","Garcia",13,"Russel","Morales",14,"Jacqueline","Long",15,"Dylan","Shaw")iferr!=nil{returnerr}c,err:=res.RowsAffected()iferr!=nil{returnerr}_,_=fmt.Fprintf(w,"%v records inserted\n",c)returnnil}

Run the sample with the following command:

GoogleSQL

gorungetting_started_guide.godmlwriteprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.godmlwritepgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

4recordsinserted.
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.

Use theconn.Raw function to get a reference to the underlyingSpanner connection. TheSpannerConn.Apply function appliesmutations atomically to the database.

The following code shows how to write the data using mutations:

GoogleSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcWriteDataWithMutations(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//GetaconnectionsothatwecangetaccesstotheSpannerspecific//connectioninterfaceSpannerConn.conn,err:=db.Conn(ctx)iferr!=nil{returnerr}deferconn.Close()singerColumns:=[]string{"SingerId","FirstName","LastName"}albumColumns:=[]string{"SingerId","AlbumId","AlbumTitle"}mutations:=[]*spanner.Mutation{spanner.Insert("Singers",singerColumns,[]interface{}{int64(1),"Marc","Richards"}),spanner.Insert("Singers",singerColumns,[]interface{}{int64(2),"Catalina","Smith"}),spanner.Insert("Singers",singerColumns,[]interface{}{int64(3),"Alice","Trentor"}),spanner.Insert("Singers",singerColumns,[]interface{}{int64(4),"Lea","Martin"}),spanner.Insert("Singers",singerColumns,[]interface{}{int64(5),"David","Lomond"}),spanner.Insert("Albums",albumColumns,[]interface{}{int64(1),int64(1),"Total Junk"}),spanner.Insert("Albums",albumColumns,[]interface{}{int64(1),int64(2),"Go, Go, Go"}),spanner.Insert("Albums",albumColumns,[]interface{}{int64(2),int64(1),"Green"}),spanner.Insert("Albums",albumColumns,[]interface{}{int64(2),int64(2),"Forever Hold Your Peace"}),spanner.Insert("Albums",albumColumns,[]interface{}{int64(2),int64(3),"Terrified"}),}//MutationscanbewrittenoutsideanexplicittransactionusingSpannerConn#Apply.iferr:=conn.Raw(func(driverConninterface{})error{spannerConn,ok:=driverConn.(spannerdriver.SpannerConn)if!ok{returnfmt.Errorf("unexpected driver connection %v, expected SpannerConn",driverConn)}_,err=spannerConn.Apply(ctx,mutations)returnerr});err!=nil{returnerr}fmt.Fprintf(w,"Inserted %v rows\n",len(mutations))returnnil}

PostgreSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcWriteDataWithMutationsPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//GetaconnectionsothatwecangetaccesstotheSpannerspecific//connectioninterfaceSpannerConn.conn,err:=db.Conn(ctx)iferr!=nil{returnerr}deferfunc(){_=conn.Close()}()singerColumns:=[]string{"singer_id","first_name","last_name"}albumColumns:=[]string{"singer_id","album_id","album_title"}mutations:=[]*spanner.Mutation{spanner.Insert("singers",singerColumns,[]interface{}{int64(1),"Marc","Richards"}),spanner.Insert("singers",singerColumns,[]interface{}{int64(2),"Catalina","Smith"}),spanner.Insert("singers",singerColumns,[]interface{}{int64(3),"Alice","Trentor"}),spanner.Insert("singers",singerColumns,[]interface{}{int64(4),"Lea","Martin"}),spanner.Insert("singers",singerColumns,[]interface{}{int64(5),"David","Lomond"}),spanner.Insert("albums",albumColumns,[]interface{}{int64(1),int64(1),"Total Junk"}),spanner.Insert("albums",albumColumns,[]interface{}{int64(1),int64(2),"Go, Go, Go"}),spanner.Insert("albums",albumColumns,[]interface{}{int64(2),int64(1),"Green"}),spanner.Insert("albums",albumColumns,[]interface{}{int64(2),int64(2),"Forever Hold Your Peace"}),spanner.Insert("albums",albumColumns,[]interface{}{int64(2),int64(3),"Terrified"}),}//MutationscanbewrittenoutsideanexplicittransactionusingSpannerConn#Apply.iferr:=conn.Raw(func(driverConninterface{})error{spannerConn,ok:=driverConn.(spannerdriver.SpannerConn)if!ok{returnfmt.Errorf("unexpected driver connection %v, expected SpannerConn",driverConn)}_,err=spannerConn.Apply(ctx,mutations)returnerr});err!=nil{returnerr}_,_=fmt.Fprintf(w,"Inserted %v rows\n",len(mutations))returnnil}

Run the following example using thewrite argument:

GoogleSQL

gorungetting_started_guide.gowriteprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.gowritepgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db
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 database/sql driver.

On the command line

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

GoogleSQL

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance \--sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

PostgreSQL

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance \--sql='SELECT singer_id, album_id, album_title FROM albums'
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 database/sql driver

In addition to executing a SQL statement on the command line, you can issue thesame SQL statement programmatically using the Spanner database/sql driver.

The following functions and structs are used to execute a SQL query:

  • TheQueryContextfunction in theDB struct: use this to execute a SQL statement that returnsrows, such as a query or a DML statement with aTHEN RETURN clause.
  • TheRows struct: use this to access thedata returned by a SQL statement.

The following example uses theQueryContext function:

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryData(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()rows,err:=db.QueryContext(ctx,`SELECTSingerId,AlbumId,AlbumTitleFROMAlbumsORDERBYSingerId,AlbumId`)deferrows.Close()iferr!=nil{returnerr}forrows.Next(){varsingerId,albumIdint64vartitlestringerr=rows.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryDataPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()rows,err:=db.QueryContext(ctx,`selectsinger_id,album_id,album_titlefromalbumsorderbysinger_id,album_id`)deferfunc(){_=rows.Close()}()iferr!=nil{returnerr}forrows.Next(){varsingerId,albumIdint64vartitlestringerr=rows.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}_,_=fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goqueryprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goquerypgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified

Query using a SQL parameter

If your application has a frequently executed query, you can improve its performanceby parameterizing it. The resulting parametric query can be cached and reused, whichreduces 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 toquery records containing a specific value forLastName.

The Spanner database/sql driver supports both positional and namedquery parameters. A? in a SQL statement indicates a positional queryparameter. Pass the query parameter values as additional arguments to theQueryContext function. For example:

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryDataWithParameter(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()rows,err:=db.QueryContext(ctx,`SELECTSingerId,FirstName,LastNameFROMSingersWHERELastName=?`,"Garcia")deferrows.Close()iferr!=nil{returnerr}forrows.Next(){varsingerIdint64varfirstName,lastNamestringerr=rows.Scan(&singerId, &firstName, &lastName)iferr!=nil{returnerr}fmt.Fprintf(w,"%v %v %v\n",singerId,firstName,lastName)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryDataWithParameterPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()rows,err:=db.QueryContext(ctx,`selectsinger_id,first_name,last_namefromsingerswherelast_name=?`,"Garcia")deferfunc(){_=rows.Close()}()iferr!=nil{returnerr}forrows.Next(){varsingerIdint64varfirstName,lastNamestringerr=rows.Scan(&singerId, &firstName, &lastName)iferr!=nil{returnerr}_,_=fmt.Fprintf(w,"%v %v %v\n",singerId,firstName,lastName)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goquerywithparameterprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goquerywithparameterpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

12MelissaGarcia

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 database/sql driver.

On the command line

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

GoogleSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance \--ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

PostgreSQL

gcloudspannerdatabasesddlupdateexample-db--instance=test-instance \--ddl='alter table albums add column marketing_budget bigint'

You should see:

Schemaupdating...done.

Use the Spanner database/sql driver

Use theExecContext function tomodify the schema:

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcAddColumn(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()_,err=db.ExecContext(ctx,`ALTERTABLEAlbumsADDCOLUMNMarketingBudgetINT64`)iferr!=nil{returnerr}fmt.Fprint(w,"Added MarketingBudget column\n")returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcAddColumnPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()_,err=db.ExecContext(ctx,`altertablealbumsaddcolumnmarketing_budgetbigint`)iferr!=nil{returnerr}_,_=fmt.Fprint(w,"Added marketing_budget column\n")returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goaddcolumnprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goaddcolumnpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

AddedMarketingBudgetcolumn.

Execute a DDL batch

We recommend that you execute multiple schema modifications in one batch. UsetheSTART BATCH DDL andRUN BATCH commands to execute a DDL batch. Thefollowing example creates two tables in one batch:

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcDdlBatch(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//ExecutingmultipleDDLstatementsasonebatchis//moreefficientthanexecutingeachstatement//individually.conn,err:=db.Conn(ctx)deferconn.Close()if_,err:=conn.ExecContext(ctx,"start batch ddl");err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`CREATETABLEVenues(VenueIdINT64NOTNULL,NameSTRING(1024),DescriptionJSON,)PRIMARYKEY(VenueId)`);err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`CREATETABLEConcerts(ConcertIdINT64NOTNULL,VenueIdINT64NOTNULL,SingerIdINT64NOTNULL,StartTimeTIMESTAMP,EndTimeTIMESTAMP,CONSTRAINTFk_Concerts_VenuesFOREIGNKEY(VenueId)REFERENCESVenues(VenueId),CONSTRAINTFk_Concerts_SingersFOREIGNKEY(SingerId)REFERENCESSingers(SingerId),)PRIMARYKEY(ConcertId)`);err!=nil{returnerr}//`runbatch`sendstheDDLstatementstoSpannerandblocksuntil//allstatementshavefinishedexecuting.if_,err:=conn.ExecContext(ctx,"run batch");err!=nil{returnerr}fmt.Fprint(w,"Added Venues and Concerts tables\n")returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcDdlBatchPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//ExecutingmultipleDDLstatementsasonebatchis//moreefficientthanexecutingeachstatement//individually.conn,err:=db.Conn(ctx)deferfunc(){_=conn.Close()}()if_,err:=conn.ExecContext(ctx,"start batch ddl");err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`createtablevenues(venue_idbigintnotnullprimarykey,namevarchar(1024),descriptionjsonb)`);err!=nil{returnerr}if_,err:=conn.ExecContext(ctx,`createtableconcerts(concert_idbigintnotnullprimarykey,venue_idbigintnotnull,singer_idbigintnotnull,start_timetimestamptz,end_timetimestamptz,constraintfk_concerts_venuesforeignkey(venue_id)referencesvenues(venue_id),constraintfk_concerts_singersforeignkey(singer_id)referencessingers(singer_id))`);err!=nil{returnerr}//`runbatch`sendstheDDLstatementstoSpannerandblocksuntil//allstatementshavefinishedexecuting.if_,err:=conn.ExecContext(ctx,"run batch");err!=nil{returnerr}_,_=fmt.Fprint(w,"Added venues and concerts tables\n")returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goddlbatchprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goddlbatchpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

AddedVenuesandConcertstables.

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

GoogleSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcUpdateDataWithMutations(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//GetaconnectionsothatwecangetaccesstotheSpannerspecific//connectioninterfaceSpannerConn.conn,err:=db.Conn(ctx)iferr!=nil{returnerr}deferconn.Close()cols:=[]string{"SingerId","AlbumId","MarketingBudget"}mutations:=[]*spanner.Mutation{spanner.Update("Albums",cols,[]interface{}{1,1,100000}),spanner.Update("Albums",cols,[]interface{}{2,2,500000}),}iferr:=conn.Raw(func(driverConninterface{})error{spannerConn,ok:=driverConn.(spannerdriver.SpannerConn)if!ok{returnfmt.Errorf("unexpected driver connection %v, "+"expected SpannerConn",driverConn)}_,err=spannerConn.Apply(ctx,mutations)returnerr});err!=nil{returnerr}fmt.Fprintf(w,"Updated %v albums\n",len(mutations))returnnil}

PostgreSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcUpdateDataWithMutationsPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//GetaconnectionsothatwecangetaccesstotheSpannerspecific//connectioninterfaceSpannerConn.conn,err:=db.Conn(ctx)iferr!=nil{returnerr}deferfunc(){_=conn.Close()}()cols:=[]string{"singer_id","album_id","marketing_budget"}mutations:=[]*spanner.Mutation{spanner.Update("albums",cols,[]interface{}{1,1,100000}),spanner.Update("albums",cols,[]interface{}{2,2,500000}),}iferr:=conn.Raw(func(driverConninterface{})error{spannerConn,ok:=driverConn.(spannerdriver.SpannerConn)if!ok{returnfmt.Errorf("unexpected driver connection %v, "+"expected SpannerConn",driverConn)}_,err=spannerConn.Apply(ctx,mutations)returnerr});err!=nil{returnerr}_,_=fmt.Fprintf(w,"Updated %v albums\n",len(mutations))returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goupdateprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goupdatepgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

Updated2albums

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

The following example uses theQueryContext function to execute a query:

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryNewColumn(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()rows,err:=db.QueryContext(ctx,`SELECTSingerId,AlbumId,MarketingBudgetFROMAlbumsORDERBYSingerId,AlbumId`)deferrows.Close()iferr!=nil{returnerr}forrows.Next(){varsingerId,albumIdint64varmarketingBudgetsql.NullInt64err=rows.Scan(&singerId, &albumId, &marketingBudget)iferr!=nil{returnerr}budget:="NULL"ifmarketingBudget.Valid{budget=fmt.Sprintf("%v",marketingBudget.Int64)}fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,budget)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcQueryNewColumnPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()rows,err:=db.QueryContext(ctx,`selectsinger_id,album_id,marketing_budgetfromalbumsorderbysinger_id,album_id`)deferfunc(){_=rows.Close()}()iferr!=nil{returnerr}forrows.Next(){varsingerId,albumIdint64varmarketingBudgetsql.NullInt64err=rows.Scan(&singerId, &albumId, &marketingBudget)iferr!=nil{returnerr}budget:="null"ifmarketingBudget.Valid{budget=fmt.Sprintf("%v",marketingBudget.Int64)}_,_=fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,budget)}ifrows.Err()!=nil{returnrows.Err()}returnrows.Close()}

To execute this query, run the following command:

GoogleSQL

gorungetting_started_guide.goquerymarketingbudgetprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goquerymarketingbudgetpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

You should see:

1110000012null21null2250000023null

Update data

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

CallDB.BeginTx to execute read-writetransactions in database/sql.

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcWriteWithTransactionUsingDml(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//Transfermarketingbudgetfromonealbumtoanother.Wedoitina//transactiontoensurethatthetransferisatomic.tx,err:=db.BeginTx(ctx, &sql.TxOptions{})iferr!=nil{returnerr}//TheSpannerdatabase/sqldriversupportsbothpositionalandnamed//queryparameters.Thisqueryusesnamedqueryparameters.constselectSql="SELECT MarketingBudget "+"FROM Albums "+"WHERE SingerId = @singerId and AlbumId = @albumId"//Getthemarketing_budgetofsinger2/album2.row:=tx.QueryRowContext(ctx,selectSql,sql.Named("singerId",2),sql.Named("albumId",2))varbudget2int64iferr:=row.Scan(&budget2);err!=nil{tx.Rollback()returnerr}consttransfer=20000//Thetransactionwillonlybecommittedifthisconditionstillholds//atthetimeofcommit.Otherwise,thetransactionwillbeaborted.ifbudget2 >=transfer{//Getthemarketing_budgetofsinger1/album1.row:=tx.QueryRowContext(ctx,selectSql,sql.Named("singerId",1),sql.Named("albumId",1))varbudget1int64iferr:=row.Scan(&budget1);err!=nil{tx.Rollback()returnerr}//TransferpartofthemarketingbudgetofAlbum2toAlbum1.budget1+=transferbudget2-=transferconstupdateSql="UPDATE Albums "+"SET MarketingBudget = @budget "+"WHERE SingerId = @singerId and AlbumId = @albumId"//StartaDMLbatchandexecuteitaspartofthecurrenttransaction.if_,err:=tx.ExecContext(ctx,"start batch dml");err!=nil{tx.Rollback()returnerr}if_,err:=tx.ExecContext(ctx,updateSql,sql.Named("singerId",1),sql.Named("albumId",1),sql.Named("budget",budget1));err!=nil{_,_=tx.ExecContext(ctx,"abort batch")tx.Rollback()returnerr}if_,err:=tx.ExecContext(ctx,updateSql,sql.Named("singerId",2),sql.Named("albumId",2),sql.Named("budget",budget2));err!=nil{_,_=tx.ExecContext(ctx,"abort batch")tx.Rollback()returnerr}//`runbatch`sendstheDMLstatementstoSpanner.//Theresultcontainsthetotalaffectedrowsacrosstheentirebatch.result,err:=tx.ExecContext(ctx,"run batch")iferr!=nil{tx.Rollback()returnerr}ifaffected,err:=result.RowsAffected();err!=nil{tx.Rollback()returnerr}elseifaffected!=2{//Thebatchshouldupdate2rows.tx.Rollback()returnfmt.Errorf("unexpected number of rows affected: %v",affected)}}//Committhecurrenttransaction.iferr:=tx.Commit();err!=nil{returnerr}fmt.Fprintln(w,"Transferred marketing budget from Album 2 to Album 1")returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcWriteWithTransactionUsingDmlPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//Transfermarketingbudgetfromonealbumtoanother.Wedoitina//transactiontoensurethatthetransferisatomic.tx,err:=db.BeginTx(ctx, &sql.TxOptions{})iferr!=nil{returnerr}constselectSql="select marketing_budget "+"from albums "+"where singer_id = $1 and album_id = $2"//Getthemarketing_budgetofsinger2/album2.row:=tx.QueryRowContext(ctx,selectSql,2,2)varbudget2int64iferr:=row.Scan(&budget2);err!=nil{_=tx.Rollback()returnerr}consttransfer=20000//Thetransactionwillonlybecommittedifthisconditionstillholds//atthetimeofcommit.Otherwise,thetransactionwillbeaborted.ifbudget2 >=transfer{//Getthemarketing_budgetofsinger1/album1.row:=tx.QueryRowContext(ctx,selectSql,1,1)varbudget1int64iferr:=row.Scan(&budget1);err!=nil{_=tx.Rollback()returnerr}//TransferpartofthemarketingbudgetofAlbum2toAlbum1.budget1+=transferbudget2-=transferconstupdateSql="update albums "+"set marketing_budget = $1 "+"where singer_id = $2 and album_id = $3"//StartaDMLbatchandexecuteitaspartofthecurrenttransaction.if_,err:=tx.ExecContext(ctx,"start batch dml");err!=nil{_=tx.Rollback()returnerr}if_,err:=tx.ExecContext(ctx,updateSql,budget1,1,1);err!=nil{_,_=tx.ExecContext(ctx,"abort batch")_=tx.Rollback()returnerr}if_,err:=tx.ExecContext(ctx,updateSql,budget2,2,2);err!=nil{_,_=tx.ExecContext(ctx,"abort batch")_=tx.Rollback()returnerr}//`runbatch`sendstheDMLstatementstoSpanner.//Theresultcontainsthetotalaffectedrowsacrosstheentirebatch.result,err:=tx.ExecContext(ctx,"run batch")iferr!=nil{_=tx.Rollback()returnerr}ifaffected,err:=result.RowsAffected();err!=nil{_=tx.Rollback()returnerr}elseifaffected!=2{//Thebatchshouldupdate2rows._=tx.Rollback()returnfmt.Errorf("unexpected number of rows affected: %v",affected)}}//Committhecurrenttransaction.iferr:=tx.Commit();err!=nil{returnerr}_,_=fmt.Fprintln(w,"Transferred marketing budget from Album 2 to Album 1")returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.gowritewithtransactionusingdmlprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.gowritewithtransactionusingdmlpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

Transaction tags and request tags

Usetransaction tags and request tagsto troubleshoot transactions and queries in Spanner. You can passadditional transaction options to thespannerdriver.BeginReadWriteTransactionfunction.

Usespannerdriver.ExecOptions to pass additional query options for a SQLstatement. For example:

GoogleSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcTags(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//Usethespannerdriver.BeginReadWriteTransactionfunction//tospecifyspecificSpanneroptions,suchastransactiontags.tx,err:=spannerdriver.BeginReadWriteTransaction(ctx,db,spannerdriver.ReadWriteTransactionOptions{TransactionOptions:spanner.TransactionOptions{TransactionTag:"example-tx-tag",},})iferr!=nil{returnerr}//Passinanargumentoftypespannerdriver.ExecOptionstosupply//additionaloptionsforastatement.row:=tx.QueryRowContext(ctx,"SELECT MarketingBudget "+"FROM Albums "+"WHERE SingerId=? and AlbumId=?",spannerdriver.ExecOptions{QueryOptions:spanner.QueryOptions{RequestTag:"query-marketing-budget"},},1,1)varbudgetint64iferr:=row.Scan(&budget);err!=nil{tx.Rollback()returnerr}//Reducethemarketingbudgetby10%ifitismorethan1,000.ifbudget >1000{budget=int64(float64(budget)-float64(budget)*0.1)if_,err:=tx.ExecContext(ctx,`UPDATEAlbumsSETMarketingBudget=@budgetWHERESingerId=@singerIdANDAlbumId=@albumId`,spannerdriver.ExecOptions{QueryOptions:spanner.QueryOptions{RequestTag:"reduce-marketing-budget"},},sql.Named("budget",budget),sql.Named("singerId",1),sql.Named("albumId",1));err!=nil{tx.Rollback()returnerr}}//Committhecurrenttransaction.iferr:=tx.Commit();err!=nil{returnerr}fmt.Fprintln(w,"Reduced marketing budget")returnnil}

PostgreSQL

import("context""database/sql""fmt""io""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcTagsPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//Usethespannerdriver.BeginReadWriteTransactionfunction//tospecifyspecificSpanneroptions,suchastransactiontags.tx,err:=spannerdriver.BeginReadWriteTransaction(ctx,db,spannerdriver.ReadWriteTransactionOptions{TransactionOptions:spanner.TransactionOptions{TransactionTag:"example-tx-tag",},})iferr!=nil{returnerr}//Passinanargumentoftypespannerdriver.ExecOptionstosupply//additionaloptionsforastatement.row:=tx.QueryRowContext(ctx,"select marketing_budget "+"from albums "+"where singer_id=? and album_id=?",spannerdriver.ExecOptions{QueryOptions:spanner.QueryOptions{RequestTag:"query-marketing-budget"},},1,1)varbudgetint64iferr:=row.Scan(&budget);err!=nil{_=tx.Rollback()returnerr}//Reducethemarketingbudgetby10%ifitismorethan1,000.ifbudget >1000{budget=int64(float64(budget)-float64(budget)*0.1)if_,err:=tx.ExecContext(ctx,`updatealbumssetmarketing_budget=$1wheresinger_id=$2andalbum_id=$3`,spannerdriver.ExecOptions{QueryOptions:spanner.QueryOptions{RequestTag:"reduce-marketing-budget"},},budget,1,1);err!=nil{_=tx.Rollback()returnerr}}//Committhecurrenttransaction.iferr:=tx.Commit();err!=nil{returnerr}_,_=fmt.Fprintln(w,"Reduced marketing budget")returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.gotagsprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.gotagspgprojects/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-onlytransactions observe a consistentprefix of the transaction commit history, so your application always getsconsistent data.Set theTxOptions.ReadOnly field totrue to execute a read-only transaction.

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

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcReadOnlyTransaction(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//Startaread-onlytransactionbysupplyingadditionaltransactionoptions.tx,err:=db.BeginTx(ctx, &sql.TxOptions{ReadOnly:true})albumsOrderedById,err:=tx.QueryContext(ctx,`SELECTSingerId,AlbumId,AlbumTitleFROMAlbumsORDERBYSingerId,AlbumId`)deferalbumsOrderedById.Close()iferr!=nil{returnerr}foralbumsOrderedById.Next(){varsingerId,albumIdint64vartitlestringerr=albumsOrderedById.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}albumsOrderedTitle,err:=tx.QueryContext(ctx,`SELECTSingerId,AlbumId,AlbumTitleFROMAlbumsORDERBYAlbumTitle`)deferalbumsOrderedTitle.Close()iferr!=nil{returnerr}foralbumsOrderedTitle.Next(){varsingerId,albumIdint64vartitlestringerr=albumsOrderedTitle.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}//Endtheread-onlytransactionbycallingCommit.returntx.Commit()}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcReadOnlyTransactionPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//Startaread-onlytransactionbysupplyingadditionaltransactionoptions.tx,err:=db.BeginTx(ctx, &sql.TxOptions{ReadOnly:true})iferr!=nil{returnerr}albumsOrderedById,err:=tx.QueryContext(ctx,`selectsinger_id,album_id,album_titlefromalbumsorderbysinger_id,album_id`)deferfunc(){_=albumsOrderedById.Close()}()iferr!=nil{returnerr}foralbumsOrderedById.Next(){varsingerId,albumIdint64vartitlestringerr=albumsOrderedById.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}_,_=fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}albumsOrderedTitle,err:=tx.QueryContext(ctx,`selectsinger_id,album_id,album_titlefromalbumsorderbyalbum_title`)deferfunc(){_=albumsOrderedTitle.Close()}()iferr!=nil{returnerr}foralbumsOrderedTitle.Next(){varsingerId,albumIdint64vartitlestringerr=albumsOrderedTitle.Scan(&singerId, &albumId, &title)iferr!=nil{returnerr}_,_=fmt.Fprintf(w,"%v %v %v\n",singerId,albumId,title)}//Endtheread-onlytransactionbycallingCommit.returntx.Commit()}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.goreadonlytransactionprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.goreadonlytransactionpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

The result shows:

11TotalJunk12Go,Go,Go21Green22ForeverHoldYourPeace23Terrified22ForeverHoldYourPeace12Go,Go,Go21Green23Terrified11TotalJunk

Partitioned queries and Data Boost

ThepartitionQueryAPI divides a query into smaller pieces, or partitions, and uses multiplemachines to fetch the partitions in parallel. Each partition is identified by apartition token. The partitionQuery API has higher latency than the standardquery API,because it's only intended for bulk operations such as exporting or scanning thewhole database.

Data Boostlets you execute analytics queries and data exports with near-zeroimpact to existing workloads on the provisioned Spanner instance.Data Boost only supportspartitioned queries.

The following example shows how to execute a partitioned query with Data Boostwith the database/sql driver:

GoogleSQL

import("context""database/sql""fmt""io""slices""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcDataBoost(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()//RunapartitionedquerythatusesDataBoost.rows,err:=db.QueryContext(ctx,"SELECT SingerId, FirstName, LastName from Singers",spannerdriver.ExecOptions{PartitionedQueryOptions:spannerdriver.PartitionedQueryOptions{//AutoPartitionQueryinstructstheSpannerdatabase/sqldriverto//automaticallypartitionthequeryandexecuteeachpartitioninparallel.//Therowsarereturnedasoneresultsetinundefinedorder.AutoPartitionQuery:true,},QueryOptions:spanner.QueryOptions{//SetDataBoostEnabledtotruetoenableDataBoost.//Seehttps://cloud.google.com/spanner/docs/databoost/databoost-overview//formoreinformation.DataBoostEnabled:true,},})deferrows.Close()iferr!=nil{returnerr}typeSingerstruct{SingerIdint64FirstNamestringLastNamestring}varsingers[]Singerforrows.Next(){varsingerSingererr=rows.Scan(&singer.SingerId, &singer.FirstName, &singer.LastName)iferr!=nil{returnerr}singers=append(singers,singer)}//QueriesthatusetheAutoPartitionoptionreturnrowsinundefinedorder,//soweneedtosorttheminmemorytoguaranteetheoutputorder.slices.SortFunc(singers,func(a,bSinger)int{returnint(a.SingerId-b.SingerId)})for_,s:=rangesingers{fmt.Fprintf(w,"%v %v %v\n",s.SingerId,s.FirstName,s.LastName)}returnnil}

PostgreSQL

import("context""database/sql""fmt""io""slices""cloud.google.com/go/spanner"spannerdriver"github.com/googleapis/go-sql-spanner")funcDataBoostPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()//RunapartitionedquerythatusesDataBoost.rows,err:=db.QueryContext(ctx,"select singer_id, first_name, last_name from singers",spannerdriver.ExecOptions{PartitionedQueryOptions:spannerdriver.PartitionedQueryOptions{//AutoPartitionQueryinstructstheSpannerdatabase/sqldriverto//automaticallypartitionthequeryandexecuteeachpartitioninparallel.//Therowsarereturnedasoneresultsetinundefinedorder.AutoPartitionQuery:true,},QueryOptions:spanner.QueryOptions{//SetDataBoostEnabledtotruetoenableDataBoost.//Seehttps://cloud.google.com/spanner/docs/databoost/databoost-overview//formoreinformation.DataBoostEnabled:true,},})deferfunc(){_=rows.Close()}()iferr!=nil{returnerr}typeSingerstruct{SingerIdint64FirstNamestringLastNamestring}varsingers[]Singerforrows.Next(){varsingerSingererr=rows.Scan(&singer.SingerId, &singer.FirstName, &singer.LastName)iferr!=nil{returnerr}singers=append(singers,singer)}//QueriesthatusetheAutoPartitionoptionreturnrowsinundefinedorder,//soweneedtosorttheminmemorytoguaranteetheoutputorder.slices.SortFunc(singers,func(a,bSinger)int{returnint(a.SingerId-b.SingerId)})for_,s:=rangesingers{_,_=fmt.Fprintf(w,"%v %v %v\n",s.SingerId,s.FirstName,s.LastName)}returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.godataboostprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.godataboostpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

Partitioned DML

Partitioned Data Manipulation Language (DML) isdesigned for the following types of bulk updates and deletes:

  • Periodic cleanup and garbage collection.
  • Backfilling new columns with default values.

GoogleSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcPartitionedDml(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferdb.Close()conn,err:=db.Conn(ctx)iferr!=nil{returnerr}//EnablePartitionedDMLonthisconnection.if_,err:=conn.ExecContext(ctx,"SET AUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC'");err!=nil{returnfmt.Errorf("failed to change DML mode to Partitioned_Non_Atomic: %v",err)}//Back-filladefaultvaluefortheMarketingBudgetcolumn.res,err:=conn.ExecContext(ctx,"UPDATE Albums SET MarketingBudget=0 WHERE MarketingBudget IS NULL")iferr!=nil{returnerr}affected,err:=res.RowsAffected()iferr!=nil{returnfmt.Errorf("failed to get affected rows: %v",err)}//PartitionedDMLreturnstheminimumnumberofrecordsthatwereaffected.fmt.Fprintf(w,"Updated at least %v albums\n",affected)//Closingtheconnectionwillreturnittotheconnectionpool.TheDMLmodewillautomaticallyberesettothe//defaultTRANSACTIONALmodewhentheconnectionisreturnedtothepool,sowedonotneedtochangeitback//manually._=conn.Close()returnnil}

PostgreSQL

import("context""database/sql""fmt""io"_"github.com/googleapis/go-sql-spanner")funcPartitionedDmlPostgreSQL(ctxcontext.Context,wio.Writer,databaseNamestring)error{db,err:=sql.Open("spanner",databaseName)iferr!=nil{returnerr}deferfunc(){_=db.Close()}()conn,err:=db.Conn(ctx)iferr!=nil{returnerr}//EnablePartitionedDMLonthisconnection.if_,err:=conn.ExecContext(ctx,"set autocommit_dml_mode='partitioned_non_atomic'");err!=nil{returnfmt.Errorf("failed to change DML mode to Partitioned_Non_Atomic: %v",err)}//Back-filladefaultvalueforthemarketing_budgetcolumn.res,err:=conn.ExecContext(ctx,"update albums set marketing_budget=0 where marketing_budget is null")iferr!=nil{returnerr}affected,err:=res.RowsAffected()iferr!=nil{returnfmt.Errorf("failed to get affected rows: %v",err)}//PartitionedDMLreturnstheminimumnumberofrecordsthatwereaffected._,_=fmt.Fprintf(w,"Updated at least %v albums\n",affected)//Closingtheconnectionwillreturnittotheconnectionpool.TheDMLmodewillautomaticallyberesettothe//defaultTRANSACTIONALmodewhentheconnectionisreturnedtothepool,sowedonotneedtochangeitback//manually._=conn.Close()returnnil}

Run the example with the following command:

GoogleSQL

gorungetting_started_guide.gopdmlprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

PostgreSQL

gorungetting_started_guide.gopdmlpgprojects/GCLOUD_PROJECT/instances/test-instance/databases/example-db

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 2025-12-17 UTC.