README
¶
go-sql-spanner
Google Cloud Spanner driver for Go's database/sql package.
import _ "github.com/googleapis/go-sql-spanner"
db, err := sql.Open("spanner", "projects/PROJECT/instances/INSTANCE/databases/DATABASE")
if err != nil {
log.Fatal(err)
}
// Print tweets with more than 500 likes.
rows, err := db.QueryContext(ctx, "SELECT id, text FROM tweets WHERE likes > @likes", sql.Named("likes", 500))
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var (
id int64
text string
)
for rows.Next() {
if err := rows.Scan(&id, &text); err != nil {
log.Fatal(err)
}
fmt.Println(id, text)
}
Statements
Statements support follows the official Google Cloud Spanner Go client style arguments as well as positional parameters. It is highly recommended to use either positional parameters in combination with positional arguments, or named parameters in combination with named arguments.
Using positional parameters with positional 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)
Using named parameters with named arguments
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))
Using named parameters with positional arguments (not recommended)
Named parameters can also be used in combination with positional arguments, but this is not recommended, as the behavior can be hard to predict if the 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)
Transactions
- Read-write transactions always uses the strongest isolation level and ignore the user-specified level.
- Read-only transactions do strong-reads by default. Read-only transactions must be ended by calling either Commit or Rollback. Calling either of these methods will end the current read-only transaction and return the session that is used to the session pool.
tx, err := db.BeginTx(ctx, &sql.TxOptions{}) // Read-write transaction.
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.
})
Transaction Runner (Retry Transactions)
Spanner can abort a read/write transaction if concurrent modifications are detected
that would violate the transaction consistency. When this happens, the driver will
return the ErrAbortedDueToConcurrentModification error. You can use the
RunTransaction function to let the driver automatically retry transactions that
are aborted by Spanner.
package sample
import (
"context"
"database/sql"
"fmt"
_ "github.com/googleapis/go-sql-spanner"
spannerdriver "github.com/googleapis/go-sql-spanner"
)
spannerdriver.RunTransaction(ctx, db, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error {
row := tx.QueryRowContext(ctx, "select Name from Singers where SingerId=@id", 123)
var name string
if err := row.Scan(&name); err != nil {
return err
}
return nil
})
See also the transaction runner sample.
DDL Statements
DDL statements are 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 also the batch DDL example.
Examples
The examples directory contains standalone code samples that show how to use common
features of Cloud Spanner and/or the database/sql package. Each standalone code sample can be
executed without any prior setup, as long as Docker is installed on your local system.
Raw Connection / Specific Cloud Spanner Features
Use the Conn.Raw method to get access to a Cloud Spanner specific connection instance. This
instance 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(driverConn interface{}) error {
spannerConn, ok := driverConn.(spannerdriver.SpannerConn)
if !ok {
return fmt.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()
return nil
})
See also the examples directory for further code samples.
Emulator
See Google 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 the emulator.
$ gcloud beta emulators spanner start
$ export SPANNER_EMULATOR_HOST=localhost:9010
Spanner PostgreSQL Interface
This driver only works with the Spanner GoogleSQL dialect. For the Spanner PostgreSQL dialect, any PostgreSQL driver that implements the database/sql interface can be used in combination with PGAdapter.
For example, the pgx driver can be used in combination with PGAdapter: https://github.com/GoogleCloudPlatform/pgadapter/blob/postgresql-dialect/docs/pgx.md
Troubleshooting
The driver will retry any Aborted error that is returned by Cloud Spanner
during a read/write transaction. If the driver detects that the data that
was used by the transaction was changed by another transaction between the
initial attempt and the retry attempt, the Aborted error will be propagated
to the client application as an spannerdriver.ErrAbortedDueToConcurrentModification
error.
Go Versions Supported
Our libraries are compatible with at least the three most recent, major Go releases. They are currently compatible with:
- Go 1.23
- Go 1.22
- Go 1.21
Authorization
By default, each API will use Google Application Default Credentials for authorization credentials used in calling the API endpoints. This will allow your application to run in many environments without requiring explicit configuration.
Contributing
Contributions are welcome. Please, see the CONTRIBUTING document 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. See Contributor Code of Conduct for more information.
Documentation
¶
Index ¶
Constants ¶
This section is empty.
Variables ¶
var ErrAbortedDueToConcurrentModification = status.Error(codes.Aborted, "Transaction was aborted due to a concurrent modification")
ErrAbortedDueToConcurrentModification is returned by a read/write transaction that was aborted by Cloud Spanner, and where the internal retry attempt failed because it detected that the results during the retry were different from the initial attempt.
Use the RunTransaction function to execute a read/write transaction in a retry loop. This function will never return ErrAbortedDueToConcurrentModification.
Functions ¶
func RunTransaction ¶ added in v1.8.0
func RunTransaction(ctx context.Context, db *sql.DB, opts *sql.TxOptions, f func(ctx context.Context, tx *sql.Tx) error) error
RunTransaction runs the given function in a transaction on the given database. If the connection is a connection to a Spanner database, the transaction will automatically be retried if the transaction is aborted by Spanner. Any other errors will be propagated to the caller and the transaction will be rolled back. The transaction will be committed if the supplied function did not return an error.
If the connection is to a non-Spanner database, no retries will be attempted, and any error that occurs during the transaction will be propagated to the caller.
The application should *NOT* call tx.Commit() or tx.Rollback(). This is done automatically by this function, depending on whether the transaction function returned an error or not.
This function will never return ErrAbortedDueToConcurrentModification.
func WithDisableRetryAborts ¶ added in v1.9.0
func WithDisableRetryAborts(level sql.IsolationLevel) sql.IsolationLevel
WithDisableRetryAborts returns a specific Spanner isolation level that contains both the given standard isolation level and a custom Spanner isolation level that disables internal retries for aborted transactions for a single transaction.
Types ¶
type AutocommitDMLMode ¶
type AutocommitDMLMode int
AutocommitDMLMode indicates whether a single DML statement should be executed in a normal atomic transaction or as a Partitioned DML statement. See https://cloud.google.com/spanner/docs/dml-partitioned for more information.
const ( Transactional AutocommitDMLMode = iota PartitionedNonAtomic )
func (AutocommitDMLMode) String ¶
func (mode AutocommitDMLMode) String() string
type Driver ¶
type Driver struct {
// contains filtered or unexported fields
}
Driver represents a Google Cloud Spanner database/sql driver.
type SpannerConn ¶
type SpannerConn interface {
// StartBatchDDL starts a DDL batch on the connection. After calling this
// method all subsequent DDL statements will be cached locally. Calling
// RunBatch will send all cached DDL statements to Spanner as one batch.
// Use DDL batching to speed up the execution of multiple DDL statements.
// Note that a DDL batch is not atomic. It is possible that some DDL
// statements are executed successfully and some not.
// See https://cloud.google.com/spanner/docs/schema-updates#order_of_execution_of_statements_in_batches
// for more information on how Cloud Spanner handles DDL batches.
StartBatchDDL() error
// StartBatchDML starts a DML batch on the connection. After calling this
// method all subsequent DML statements will be cached locally. Calling
// RunBatch will send all cached DML statements to Spanner as one batch.
// Use DML batching to speed up the execution of multiple DML statements.
// DML batches can be executed both outside of a transaction and during
// a read/write transaction. If a DML batch is executed outside an active
// transaction, the batch will be applied atomically to the database if
// successful and rolled back if one or more of the statements fail.
// If a DML batch is executed as part of a transaction, the error will
// be returned to the application, and the application can decide whether
// to commit or rollback the transaction.
StartBatchDML() error
// RunBatch sends all batched DDL or DML statements to Spanner. This is a
// no-op if no statements have been batched or if there is no active batch.
RunBatch(ctx context.Context) error
// AbortBatch aborts the current DDL or DML batch and discards all batched
// statements.
AbortBatch() error
// InDDLBatch returns true if the connection is currently in a DDL batch.
InDDLBatch() bool
// InDMLBatch returns true if the connection is currently in a DML batch.
InDMLBatch() bool
// GetBatchedStatements returns a copy of the statements that are currently
// buffered to be executed as a DML or DDL batch. It returns an empty slice
// if no batch is active, or if there are no statements buffered.
GetBatchedStatements() []spanner.Statement
// RetryAbortsInternally returns true if the connection automatically
// retries all aborted transactions.
RetryAbortsInternally() bool
// SetRetryAbortsInternally enables/disables the automatic retry of aborted
// transactions. If disabled, any aborted error from a transaction will be
// propagated to the application.
SetRetryAbortsInternally(retry bool) error
// AutocommitDMLMode returns the current mode that is used for DML
// statements outside a transaction. The default is Transactional.
AutocommitDMLMode() AutocommitDMLMode
// SetAutocommitDMLMode sets the mode to use for DML statements that are
// executed outside transactions. The default is Transactional. Change to
// PartitionedNonAtomic to use Partitioned DML instead of Transactional DML.
// See https://cloud.google.com/spanner/docs/dml-partitioned for more
// information on Partitioned DML.
SetAutocommitDMLMode(mode AutocommitDMLMode) error
// ReadOnlyStaleness returns the current staleness that is used for
// queries in autocommit mode, and for read-only transactions.
ReadOnlyStaleness() spanner.TimestampBound
// SetReadOnlyStaleness sets the staleness to use for queries in autocommit
// mode and for read-only transaction.
SetReadOnlyStaleness(staleness spanner.TimestampBound) error
// ExcludeTxnFromChangeStreams returns true if the next transaction should be excluded from change streams with the
// DDL option `allow_txn_exclusion=true`.
ExcludeTxnFromChangeStreams() bool
// SetExcludeTxnFromChangeStreams sets whether the next transaction should be excluded from change streams with the
// DDL option `allow_txn_exclusion=true`.
SetExcludeTxnFromChangeStreams(excludeTxnFromChangeStreams bool) error
// Apply writes an array of mutations to the database. This method may only be called while the connection
// is outside a transaction. Use BufferWrite to write mutations in a transaction.
// See also spanner.Client#Apply
Apply(ctx context.Context, ms []*spanner.Mutation, opts ...spanner.ApplyOption) (commitTimestamp time.Time, err error)
// BufferWrite writes an array of mutations to the current transaction. This method may only be called while the
// connection is in a read/write transaction. Use Apply to write mutations outside a transaction.
// See also spanner.ReadWriteTransaction#BufferWrite
BufferWrite(ms []*spanner.Mutation) error
// CommitTimestamp returns the commit timestamp of the last implicit or explicit read/write transaction that
// was executed on the connection, or an error if the connection has not executed a read/write transaction
// that committed successfully. The timestamp is in the local timezone.
CommitTimestamp() (commitTimestamp time.Time, err error)
// UnderlyingClient returns the underlying Spanner client for the database.
// The client cannot be used to access the current transaction or batch on
// this connection. Executing a transaction or batch using the client that is
// returned, does not affect this connection.
// Note that multiple connections share the same Spanner client. Calling
// this function on different connections to the same database, can
// return the same Spanner client.
UnderlyingClient() (client *spanner.Client, err error)
// contains filtered or unexported methods
}
SpannerConn is the public interface for the raw Spanner connection for the sql driver. This interface can be used with the db.Conn().Raw() method.
Source Files
Directories