Repository files navigation

Google Cloud Spanner driver forGo'sdatabase/sql package.

This driver can be used with both Spanner GoogleSQL and Spanner PostgreSQL databases.

import _"github.com/googleapis/go-sql-spanner"db,err:=sql.Open("spanner","projects/PROJECT/instances/INSTANCE/databases/DATABASE")iferr!=nil {log.Fatal(err)}// Print singers with more than 500 likes.rows,err:=db.QueryContext(ctx,"SELECT id, name FROM singers WHERE likes > ?",500)iferr!=nil {log.Fatal(err)}deferrows.Close()var (idint64textstring)forrows.Next() {iferr:=rows.Scan(&id,&text);err!=nil {log.Fatal(err)    }fmt.Println(id,text)}

Statements support follows the officialGoogle Cloud Spanner Go clientstyle arguments as well as positional parameters. It is highly recommended to use either positional parameters incombination with positional arguments,or named parameters in combination with named arguments.

db.QueryContext(ctx,"SELECT id, text FROM tweets WHERE likes > ?",500)db.ExecContext(ctx,"INSERT INTO tweets (id, text, rts) VALUES (?, ?, ?)",id,text,10000)

db.ExecContext(ctx,"DELETE FROM tweets WHERE id = @id",sql.Named("id",14544498215374))db.ExecContext(ctx,"INSERT INTO tweets (id, text, rts) VALUES (@id, @text, @rts)",sql.Named("id",id),sql.Named("text",text),sql.Named("rts",10000))

When connected to a PostgreSQL-dialect Spanner database, you can also use PostgreSQL-stylequery parameters$1, $2, ..., $n. These query parameters are handled as positional parameters.

db.QueryContext(ctx,"SELECT id, text FROM tweets WHERE likes > $1",500)db.ExecContext(ctx,"INSERT INTO tweets (id, text, rts) VALUES ($1, $2, $3)",id,text,10000)

Named parameters can also be used in combination with positional arguments,but this isnot recommended, as the behavior can be hard to predict ifthe same named query parameter is used in multiple places in the statement.

// Possible, but not recommended.db.ExecContext(ctx,"DELETE FROM tweets WHERE id = @id",14544498215374)

Query options can be passed in as arguments to a query. Pass in a value oftypespannerdriver.ExecOptions to supply additional execution options fora statement. Thespanner.QueryOptions will be passed through to the Spannerclient as the query options to use for the query or DML statement.

tx.ExecContext(ctx,"INSERT INTO Singers (SingerId, Name) VALUES (@id, @name)",spannerdriver.ExecOptions{QueryOptions: spanner.QueryOptions{RequestTag:"insert_singer"}},123,"Bruce Allison")tx.QueryContext(ctx,"SELECT SingerId, Name FROM Singers WHERE SingerId = ?",    spannerdriver.ExecOptions{QueryOptions: spanner.QueryOptions{RequestTag:"select_singer"}},123)

Statement tags (request tags) can also be set using the custom SQL statementset statement_tag='my_tag':

tx.ExecContext(ctx,"set statement_tag = 'select_singer'")tx.QueryContext(ctx,"SELECT SingerId, Name FROM Singers WHERE SingerId = ?",123)

• Read-write transactions support isolation levelsSerializable,Snapshot andRepeatableRead.
• Read-only transactions do strong-reads by default. Read-only transactions must be ended by callingeither Commit or Rollback. Calling either of these methods will end the current read-onlytransaction.

tx,err:=db.BeginTx(ctx,&sql.TxOptions{})// Read-write transaction.// Read-write transaction with a transaction tag.conn,_:=db.Conn(ctx)_,_:=conn.ExecContext(ctx,"SET TRANSACTION_TAG='my_transaction_tag'")tx,err:=conn.BeginTx(ctx,&sql.TxOptions{})tx,err:=db.BeginTx(ctx,&sql.TxOptions{ReadOnly:true,// Read-only transaction using strong reads.})conn,_:=db.Conn(ctx)_,_:=conn.ExecContext(ctx,"SET READ_ONLY_STALENESS='EXACT_STALENESS 10s'")tx,err:=conn.BeginTx(ctx,&sql.TxOptions{ReadOnly:true,// Read-only transaction using a 10 second exact staleness.})

Spanner can abort a read/write transaction if concurrent modifications are detectedthat would violate the transaction consistency. When this happens, the driver willreturn theErrAbortedDueToConcurrentModification error. You can use theRunTransaction andRunTransactionWithOptions functions to let the driverautomatically retry transactions that are aborted by Spanner.

package sampleimport ("context""database/sql""fmt"  _"github.com/googleapis/go-sql-spanner"  spannerdriver"github.com/googleapis/go-sql-spanner")spannerdriver.RunTransactionWithOptions(ctx,db,&sql.TxOptions{},func(ctx context.Context,tx*sql.Tx)error {row:=tx.QueryRowContext(ctx,"select Name from Singers where SingerId=@id",123)varnamestringiferr:=row.Scan(&name);err!=nil {returnerr    }returnnil}, spanner.TransactionOptions{TransactionTag:"my_transaction_tag"})

See also thetransaction runner sample.

DDL statementsare not supported in transactions per Cloud Spanner restriction.Instead, run them on a connection without an active transaction:

db.ExecContext(ctx,"CREATE TABLE ...")

Multiple DDL statements can be sent in one batch to Cloud Spanner by defining a DDL batch:

conn,_:=db.Conn(ctx)_,_:=conn.ExecContext(ctx,"START BATCH DDL")_,_=conn.ExecContext(ctx,"CREATE TABLE Singers (SingerId INT64, Name STRING(MAX)) PRIMARY KEY (SingerId)")_,_=conn.ExecContext(ctx,"CREATE INDEX Idx_Singers_Name ON Singers (Name)")// Executing `RUN BATCH` will run the previous DDL statements as one batch._,_:=conn.ExecContext(ctx,"RUN BATCH")

See alsothe batch DDL example.

Theexamples directory contains standalone code samples that show how to use commonfeatures of Cloud Spanner and/or the database/sql package. Each standalone code sample can beexecuted without any prior setup, as long as Docker is installed on your local system.

Use theConn.Raw method to get access to a Cloud Spanner specific connection instance. Thisinstance can be used to access Cloud Spanner specific features and settings, such as mutations,read-only staleness settings and commit timestamps.

conn,_:=db.Conn(ctx)_=conn.Raw(func(driverConninterface{})error {spannerConn,ok:=driverConn.(spannerdriver.SpannerConn)if!ok {returnfmt.Errorf("unexpected driver connection %v, expected SpannerConn",driverConn)    }// Use the `SpannerConn` interface to set specific Cloud Spanner settings or access// specific Cloud Spanner features.// Example: Set and get the current read-only staleness of the connection._=spannerConn.SetReadOnlyStaleness(spanner.ExactStaleness(10*time.Second))_=spannerConn.ReadOnlyStaleness()returnnil})

See also theexamples directory for further code samples.

SeeGoogle Cloud Spanner Emulator to learn how to start the emulator.Once the emulator is started and the host environmental flag is set, the driver will automatically connect to theemulator.

$ gcloud beta emulators spanner start$ export SPANNER_EMULATOR_HOST=localhost:9010

You can also add theautoConfigEmulator=true option to the connection string. This will instruct the driverto connect to the Spanner emulator, and to automatically create the Spanner instance and database on theemulator. Seeexamples/emulator for a working example.

This driver works with both Spanner GoogleSQL and PostgreSQL dialects.The driver automatically detects the dialect of the database that it is connected to.

Note that the types of query parameters that are supported depends on the dialect of thedatabase that the driver is connected to:

1. Positional parameters (?): Supported for both dialects.
2. Named parameters (@id,@name, ...): Supported for both dialects.
3. PostgreSQL-style positional parameters ($1,$2, ...): Only supported for PostgreSQL databases.

You can also use Spanner PostgreSQL dialect databases with any off-the-shelf PostgreSQL driver thatimplements thedatabase/sql interface in combination withPGAdapter.

For example, thepgx driver can be used in combination withPGAdapter:https://github.com/GoogleCloudPlatform/pgadapter/blob/postgresql-dialect/docs/pgx.md

The driver will retry any Aborted error that is returned by Cloud Spannerduring a read/write transaction. If the driver detects that the data thatwas used by the transaction was changed by another transaction between theinitial attempt and the retry attempt, the Aborted error will be propagatedto the client application as anspannerdriver.ErrAbortedDueToConcurrentModificationerror.

The Spanner database/sql driver follows theGo release policyand supports the two latest major Go versions.

By default, each API will useGoogle Application Default Credentialsfor authorization credentials used in calling the API endpoints. This will allow yourapplication to run in many environments without requiring explicit configuration.

Contributions are welcome. Please, see theCONTRIBUTINGdocument for details.

Please note that this project is released with a Contributor Code of Conduct.By participating in this project you agree to abide by its terms.SeeContributor Code of Conductfor more information.