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 Connection String as dataSourceName:
From our partners:
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 Singers with ID greater than 500.
rows, err := db.QueryContext(ctx, "SELECT SingerId, FirstName FROM Singers WHERE SingerId > @id", 500)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var (
singerID int64
firstName string
)
for rows.Next() {
if err := rows.Scan(&singerID, &firstName); err != nil {
log.Fatal(err)
}
fmt.Println(singerID, firstName)
}
$ go get -u github.com/googleapis/go-sql-spanner
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:
projects/my-project/instances/my-instance/databases/my-database?credentials=/path/to/credentials.json
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:
127.0.0.1:55217/projects/p/instances/i/databases/d?useplaintext=true;
The full connection string regular expression is:
((?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>.*))?
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 (?
).
db.QueryContext(ctx, "SELECT SingerId, FirstName FROM Singers WHERE SingerId = @id", 14544498215374)
db.ExecContext(ctx, "INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName)", id, firstName, lastName)
db.ExecContext(ctx, "DELETE FROM Singers WHERE SingerId = ?", 14544498215374)
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:
db, _ := sql.Open("spanner", "projects/PROJECT/instances/INSTANCE/databases/DATABASE")
db.ExecContext(ctx, "CREATE TABLE ...")
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:
conn, _ := db.Conn(ctx)
// Executing `START BATCH DDL` will initialize a DDL batch.
// Subsequent statements must all be DDL until `RUN BATCH` or `ABORT BATCH` is executed.
_, _ = conn.ExecContext(ctx, "START BATCH DDL")
// This will be cached locally until a `RUN BATCH` statement is executed.
_, _ = conn.ExecContext(ctx, `CREATE TABLE Singers (
SingerId INT64,
FirstName STRING(MAX),
LastName 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")
You can also refer to the batch DDL example.
Limitations
Cloud Spanner features that are not supported in the driver are listed here.
Getting Involved
We’d love to hear from you, especially if you’re a Golang developer considering Cloud Spanner or an existing Cloud Spanner customer who is considering using database/sql for new projects. The project is open-source, and you can comment, report bugs, and open pull requests on Github.
See Also
Before you get started, you need to have a golang project. For a complete set of examples, you can find them in the examples directory for the driver. You can additionally refer:
By: Rahul Yadav (Software Engineer, Google Cloud)
Source: Google Cloud Blog
For enquiries, product placements, sponsorships, and collaborations, connect with us at [email protected]. We'd love to hear from you!
Our humans need coffee too! Your support is highly appreciated, thank you!