Overview
Go SQL Spanner Driver is an implementation of Go’s database/sql/driver interface. Add the driver to your application to enable the use of the database/sql API with Cloud Spanner. Use spanner as driverName and a valid ConnectionString as dataSourceName:
code_block[StructValue([(u’code’, u’import _ “github.com/googleapis/go-sql-spanner”rnrndb, err := sql.Open(“spanner”, “projects/PROJECT/instances/INSTANCE/databases/DATABASE”)rnif err != nil {rn log.Fatal(err)rn}rnrn// Print Singers with ID greater than 500.rnrows, err := db.QueryContext(ctx, “SELECT SingerId, FirstName FROM Singers WHERE SingerId > @id”, 500)rnif err != nil {rn log.Fatal(err)rn}rndefer rows.Close()rnrnvar (rn singerID int64rn firstName stringrn)rnfor rows.Next() {rn if err := rows.Scan(&singerID, &firstName); err != nil {rn log.Fatal(err)rn }rn fmt.Println(singerID, firstName)rn}’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bbed76990>)])]
Installation
Simple install the package to your $GOPATH with the go tool from shell:
code_block[StructValue([(u’code’, u’$ go get -u github.com/googleapis/go-sql-spanner’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bbed76a90>)])]
Make sure Git is installed on your machine and in your system’s PATH.
Architecture
The Spanner database/sql driver uses the Go client library for Google Cloud Spanner for session management, encoding and decoding data, and transaction management.
Connection String
A connection string is the string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider to initiate the connection.
A typical connection string for Cloud Spanner consists of the fully qualified database name followed by an optional list of parameters:
code_block[StructValue([(u’code’, u’projects/my-project/instances/my-instance/databases/my-database?credentials=/path/to/credentials.json’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bbffb3550>)])]
A connection string can optionally also include a host and port number. This is used to connect to a custom endpoint (for example an in-mem mock server) and looks like this:
code_block[StructValue([(u’code’, u’127.0.0.1:55217/projects/p/instances/i/databases/d?useplaintext=true;’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bc8824a10>)])]
The full connection string regular expression is:
code_block[StructValue([(u’code’, u”((?P<HOSTGROUP>[\w.-]+(?:\.[\w\.-]+)*[\w\-\._~:/?#\[\]@!\$&’\(\)\*\+,;=.]+)/)?projects/(?P<PROJECTGROUP>(([a-z]|[-.:]|[0-9])+|(DEFAULT_PROJECT_ID)))(/instances/(?P<INSTANCEGROUP>([a-z]|[-]|[0-9])+)(/databases/(?P<DATABASEGROUP>([a-z]|[-]|[_]|[0-9])+))?)?(([\?|;])(?P<PARAMSGROUP>.*))?”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bc88244d0>)])]
Note that as the driver uses the Go client to connect to Spanner, it is not necessary to specify a custom endpoint to connect to the Spanner emulator. The driver will automatically connect to the emulator if the SPANNER_EMULATOR_HOST environment variable has been set.
Usage
Statements
The driver supports both named and positional parameters. Named parameters start with an ampersand (@). Positional parameters are declared using a question mark (?).
code_block[StructValue([(u’code’, u’db.QueryContext(ctx, “SELECT SingerId, FirstName FROM Singers WHERE SingerId = @id”, 14544498215374)rnrndb.ExecContext(ctx, “INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName)”, id, firstName, lastName)rnrndb.ExecContext(ctx, “DELETE FROM Singers WHERE SingerId = ?”, 14544498215374)’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bc8824f50>)])]
DDL Statements
DDL statements are not supported in transactions as per Cloud Spanner restriction. Instead, you can run them on a connection without an active transaction:
code_block[StructValue([(u’code’, u’db, _ := sql.Open(“spanner”, “projects/PROJECT/instances/INSTANCE/databases/DATABASE”)rndb.ExecContext(ctx, “CREATE TABLE …”)’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bc8824210>)])]
It is recommended to batch multiple DDL statements together for optimal execution speed. Multiple DDL statements can be sent in one batch to Cloud Spanner by defining a DDL batch:
code_block[StructValue([(u’code’, u’conn, _ := db.Conn(ctx)rn// Executing `START BATCH DDL` will initialize a DDL batch.rn// Subsequent statements must all be DDL until `RUN BATCH` or `ABORT BATCH` is executed.rn_, _ = conn.ExecContext(ctx, “START BATCH DDL”)rnrn// This will be cached locally until a `RUN BATCH` statement is executed.rn_, _ = conn.ExecContext(ctx, `CREATE TABLE Singers (rn SingerId INT64,rn FirstName STRING(MAX),rn LastName STRING(MAX),rn) PRIMARY KEY (SingerId)`)rnrnrn_, _ = conn.ExecContext(ctx, “CREATE INDEX Idx_Singers_Name ON Singers (Name)”)rnrn// Executing `RUN BATCH` will run the previous DDL statements as one batch.rn_, _ := conn.ExecContext(ctx, “RUN BATCH”)’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e9bbe3e2590>)])]
Cloud Blog
