Wednesday, December 7, 2022
No menu items!
HomeCloud ComputingGolang’s database/sql driver support for Cloud Spanner is now Generally Available

Golang’s database/sql driver support for Cloud Spanner is now Generally Available

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

You can also refer to thebatch 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:

Cloud Spanner product documentation

Getting started with Cloud Spanner in Go

Cloud Spanner Golang client library

Cloud Spanner Quotas & Limits

Benchmark run of driver against the go client

Cloud BlogRead More

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments