apstndb

Posted on Jun 25 • Originally published at zenn.dev

A spanner-cli Contributor's Perspective on the Official Spanner CLI

#googlecloud #spanner #database #abotwrotethis

Note: This article is an AI-assisted English translation of my original Japanese article. As the author, I wanted to share these insights about the official Spanner CLI release with the international developer community. The translation was performed using Claude to ensure technical accuracy while making the content accessible to a broader audience.

The official Spanner CLI has been released in preview.
https://cloud.google.com/spanner/docs/release-notes

You can directly connect and interact with your Spanner database using the Spanner CLI, an interactive shell for Spanner that is built into the Google Cloud CLI. You can use the Spanner CLI to start an interactive session and automate SQL executions from the shell or an input file. This feature is available in Preview. For more information, see Spanner CLI quickstart.

I'll share my observations about what this is, from the perspective of apstndb, an unemployed technology enthusiast who is a contributor to the OSS spanner-cli and the author of its fork spanner-mycli. For more about spanner-mycli, please read Why I Forked spanner-cli: Introducing spanner-mycli (Japanese article).

Note: This article describes what can be observed about gcloud alpha spanner cli, which is still in Preview, as of June 25, 2025. Please note that specifications may change at any time.

Key Points of This Article

Official Spanner CLI is derived from OSS spanner-cli v0.10.6
Technical evidence discovered through binary analysis
Unique evolution including introduction of meta-commands
Expectations for a healthy ecosystem through coexistence of official and OSS versions

The Importance of Interactive Tools

Interactive clients have been and continue to be essential for SQL databases.

Interactive clients for SQL databases have been recognized as essential tools since the era of SEQUEL: A structured English query language, SQL's predecessor, which made multiple references to interactive systems.
All major RDBMSs without exception provide first-party interactive clients:

MySQL's mysql
PostgreSQL's psql
Oracle's dbcli
SQL Server's mssql-cli, sqlcmd
- SQL Server seems to have a greater emphasis on the GUI tool SQL Server Management Studio compared to others.

How about Spanner? The officially provided interfaces that users could directly use were:

gcloud spanner subcommand within gcloud CLI
Spanner Studio within Cloud Console, Google Cloud's official Web UI

The gcloud spanner subcommand is not an interactive tool. It's a command that can execute SQL one-off from Bash or PowerShell, or be incorporated into scripts:

gcloud spanner databases execute-sql example-db \
    --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

Also, Spanner Studio is operated from a browser and doesn't run standalone locally.
The execution plan display is not suitable for sharing and cannot be used for automation.

Thus, Spanner lacked what other RDBMSs naturally provided.
Therefore, after spanner-cli developed by current Googler Yuki Furuyama was donated to the Cloud Spanner Ecosystem organization, it became the de facto standard tool for Spanner.

However, since spanner-cli was not part of Google's product, there was a disconnect between Spanner Studio used in official documentation and spanner-cli used primarily by the user community, without official documentation references.

Official Spanner CLI

After years of this situation, the official Spanner CLI was suddenly released. This happened on the morning of June 25, 2025 (JST) as I write this article.

Installation

As of June 25, 2025, the official documentation states that running gcloud alpha spanner cli will automatically install it:

The Spanner CLI is available in the gcloud CLI. When you run the gcloud alpha spanner cli command for the first time, gcloud CLI automatically installs the Spanner CLI component.

However, you currently cannot use the official Spanner CLI as described.

$ gcloud alpha spanner cli --project ${SPANNER_PROJECT_ID} --instance ${SPANNER_INSTANCE_ID} ${SPANNER_DATABASE_ID}  
Pausing command execution:

This command requires the `spannercli` component to be installed. Would you like to install the `spannercli` component to continue command execution? (Y/n)?  y

ERROR: (gcloud.alpha.spanner.cli) The following components are unknown [spannercli].

This is because the component it's trying to install called spannercli doesn't exist. What actually exists is spanner-cli.

$ gcloud components list | grep spanner

Your current Google Cloud CLI version is: 528.0.0
The latest available version is: 528.0.0

To install or remove components at your current SDK version [528.0.0], run:
  $ gcloud components install COMPONENT_ID
  $ gcloud components remove COMPONENT_ID

To update your SDK installation to the latest version [528.0.0], run:
  $ gcloud components update

│ Not Installed │ Spanner Cli                                          │ spanner-cli                  │  12.1 MiB │

So you need to install the spanner-cli component as follows:

$ gcloud components install --quiet spanner-cli

Your current Google Cloud CLI version is: 528.0.0
Installing components from version: 528.0.0

┌──────────────────────────────────────────────────────┐
│         These components will be installed.          │
├─────────────────────────────────┬─────────┬──────────┤
│               Name              │ Version │   Size   │
├─────────────────────────────────┼─────────┼──────────┤
│ Spanner Cli (Platform Specific) │   1.0.0 │ 12.1 MiB │
└─────────────────────────────────┴─────────┴──────────┘

For the latest full release notes, please visit:
  https://cloud.google.com/sdk/release_notes

Performing in place update...

╔════════════════════════════════════════════════════════════╗
╠═ Downloading: Spanner Cli                                 ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Downloading: Spanner Cli (Platform Specific)             ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Spanner Cli                                  ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Spanner Cli (Platform Specific)              ═╣
╚════════════════════════════════════════════════════════════╝

Performing post processing steps...done.                                                                                                                                                                                                                                                                            

Google Cloud CLI works best with Python 3.12 and certain modules.

Setting up virtual environment
Updating modules...
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 5.5/5.5 MB 31.2 MB/s eta 0:00:00
Modules updated.
Virtual env enabled.

Update done!

Once installed, you can use gcloud alpha spanner cli:

$ gcloud alpha spanner cli --project ${SPANNER_PROJECT_ID} --instance ${SPANNER_INSTANCE_ID} ${SPANNER_DATABASE_ID}
Welcome to Spanner-Cli Client.

Type 'help;' or '\h' for help.
Type 'exit;' or 'quit;' or '\q' to exit.

spanner-cli> DESCRIBE SELECT 1;
+-------------+-------------+
| Column_Name | Column_Type |
+-------------+-------------+
|             | INT64       |
+-------------+-------------+
1 rows in set (0.17 sec)

The official Spanner CLI can also be used directly without going through the gcloud command, as the google-cloud-sdk/bin/spannercli binary is available:

$ ~/google-cloud-sdk/bin/spannercli sql --help
Starts a SQL shell for a Spanner instance.

Usage:
  spanner sql --database=<database> [flags]

Examples:

# Start an interactive SQL shell for the 'my-database' database:
$ spanner sql --database my-database

# Execute a batch of SQL statements from the 'my-commands.sql' file:
$ spanner sql --database my-database --file my-commands.sql

# Execute a batch of SQL statements directly on the command line:
$ spanner sql --database my-database --execute "SELECT * FROM my-table; INSERT INTO my-table VALUES (1, 'apple');"

# SQL statements can be piped into the command:
$ spanner sql --database my-database < my-commands.sql
$ cat my-commands.sql | spanner sql --database my-database



Flags:
      --database string                The name of the database to start the sqlshell for. This flag is required.
      --delimiter string               The delimiter to use in the SQL shell.
      --deployment_endpoint string     The endpoint to use for the Spanner instance.
      --execute string                 The SQL commands to execute. This flag is mutually exclusive with the --source flag.
  -h, --help                           help for sql
      --history string                 The file to use for the SQL shell history.
      --html                           Show output in HTML format.
      --idle_transaction_timeout int   Idle transaction timeout. (default 60)
      --init-command string            The SQL command to execute before the SQL commands in the source file.
      --init-command-add string        The SQL command to execute after the SQL commands in the source file.
      --instance string                The instance ID of the Spanner instance.
      --project string                 The project ID of the Spanner instance.
      --prompt string                  The prompt to use in the SQL shell.
      --role string                    Role to use to connect to the Spanner instance.
      --skip-column-names              Show column names in the SQL shell.
      --skip-system-command            Allow system command in the SQL shell.
      --source string                  The file containing the SQL commands to execute. This flag is mutually exclusive with the --execute flag.
      --table                          Show output in Table format. (default true)
      --tee string                     The file to tee the SQL commands to.
      --xml                            Show output in XML format.

Evidence that Official Spanner CLI is a Derivative of OSS spanner-cli

The relationship between OSS spanner-cli and official Spanner CLI is not mentioned at all in the official Spanner CLI documentation.
However, I can say with confidence: The official Spanner CLI is a derivative of OSS spanner-cli.

Let me show you the evidence. Let's extract strings contained in the official Spanner CLI binary using the strings command and filter for a specific string (apstndb):

$ strings google-cloud-sdk/bin/spannercli | grep apstndb
Bgoogle3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*InputStatement).StripComments
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.SeparateInputString
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.SeparateInput
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.newSeparator
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.SeparateInputPreserveComments
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeRawString
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeBytesString
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeRawBytesString
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeString
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeStringContent
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.hasStringPrefix
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.hasPrefix
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).consumeStringDelimiter
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).skipComments
google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.(*separator).separate
type:.eq.google3/third_party/golang/github_com/apstndb/gsqlsep/v/v0/gsqlsep.InputStatement
third_party/golang/github_com/apstndb/gsqlsep/v/v0/separator.go

We found github.com/apstndb/gsqlsep, a third-party library vendored within Google's monorepo google3.
To tell the truth, this apstndb/gsqlsep is something I created by improving and turning into a library the statement splitting implementation that spanner-cli had, in order to fix comment handling bugs in spanner-cli with fix comment handling #150.
No software other than spanner-cli uses this library. This is highly reliable evidence that it's a derivative of spanner-cli.

https://pkg.go.dev/github.com/apstndb/gsqlsep?tab=importedby

Note

Currently, it's explicitly stated that the official Spanner CLI doesn't support Spanner PostgreSQL interface.

PostgreSQL interface note: For PostgreSQL-dialect databases, you can use the psql command-line tool. The examples in this document are intended for GoogleSQL-dialect databases.

Since apstndb/gsqlsep is an implementation based on GoogleSQL lexical structure, it cannot support Spanner PostgreSQL interface.
As Spanner-specific features that cannot be supported through PGAdapter are expected to continue increasing, I predict that at some point they will abandon the dependency on apstndb/gsqlsep and natively support both GoogleSQL/PostgreSQL dialects.

Note end

As for when exactly it was forked, since DESCRIBE that I implemented by October 2024 is available, we can narrow it down to being derived from v0.10.6 or later.

spanner-cli> DESCRIBE SELECT * FROM Singers;
+-------------+-------------+
| Column_Name | Column_Type |
+-------------+-------------+
| SingerId    | INT64       |
| FirstName   | STRING      |
| LastName    | STRING      |
| SingerInfo  | BYTES       |
| BirthDate   | DATE        |
+-------------+-------------+
5 rows in set (0.17 sec)

Also, since BEGIN RW ISOLATION LEVEL doesn't work, it's highly likely to be before v0.11.0.

spanner-cli> BEGIN RW ISOLATION LEVEL SERIALIZABLE;
ERROR: invalid statement

Note: While many spanner-cli features including DESCRIBE can still be used in the official Spanner CLI, they may be removed in future releases as they are not documented in the official documentation.

Organizing the Fork Relationships

Based on the investigation so far, the relationships between OSS spanner-cli, official Spanner CLI, and spanner-mycli can be organized as follows (as of June 25, 2025):

Thus, both official Spanner CLI and spanner-mycli were forked from spanner-cli v0.10.6, each evolving independently. As of June 25, 2025, OSS spanner-cli has progressed to v0.11.0, and spanner-mycli to v0.19.0.

New Features Added in Official Spanner CLI

The official Spanner CLI has significant changes from OSS spanner-cli even in this initial release.

Meta-commands

The biggest change is the introduction of meta-commands.

Command	Syntax	Description
?	`\?`	Displays help information. Same as `\h`.
Delimiter	`\d`	Sets the statement delimiter. The default delimiter is a semi-colon.
Exit	`\q`	Exits the Spanner CLI. Same as quit.
Go	`\g`	Sends and runs SQL statement in Spanner.
Help	`\h`	Displays help information. Same as `\?`.
Notee	`\t`	Turns off writing to the output file set by the `\T`.
Prompt	`\R`	Changes your prompt to a user prompt string.
Quit	`\q`	Quits Spanner CLI. Same as exit.
Source	`\.`	Executes SQL from an input file. Takes `[filename]` as an argument.
System	`\!`	Executes a system shell command.
Tee	`\T`	Appends command output to a specified `[filename]` along with the standard output.
Use	`\u`	Connects to another database. Takes the new database name as an argument.

This seems to be strongly influenced by PostgreSQL psql command's meta-commands.
Client-side features of Spanner CLI will likely be extended as meta-commands in the future.

Note: Features for interacting with Spanner that are not limited to the client side are still largely undocumented. My intuition is that since it's undesirable for Google's official tools to have inconsistent command systems, it will converge to the Spanner JDBC command system.

Changes in Command-line Options

Command-line options have also changed significantly. Comparing the initial release v1.0.0 of official Spanner CLI with the latest release v0.11.1 of OSS spanner-cli, the following differences appear:

gcloud spanner integration
- Connection specification is now common with gcloud spanner, supporting gcloud config set etc.
- Authentication: --credential is removed and integrated into gcloud credential
- However, I confirmed it doesn't work after gcloud auth application-default revoke, so it seems gcloud auth login alone isn't sufficient yet > Error: failed to create sql shell: credentials: could not find default credentials. See https://cloud.google.com/docs/authentication/external/set-up-adc for more information
- GCLOUD_WIDE_FLAG support - mentions support for other gcloud level flags like --impersonate-service-account
Single-letter short forms are removed except for -h corresponding to --help
--verbose(-v), which was almost default in spanner-cli, is removed
Changed from --file to --source
Connection API endpoint changes: from --endpoint to --deployment_endpoint, --host, --post
Output format: Added --html, --xml in addition to --table
Other options removed from OSS spanner-cli: --priority, --directed-read, --proto-descriptor-file, --skip-tls-verify
Other options added in official Spanner CLI: --init-command string, --init-command-add string --tee string, --idle-transaction-timeout int, --skip-column-names string, --skip-system-command string, --system-command string, --delimiter string

While functionality as a CLI tool is enhanced, some Spanner API-related parts seem to have been removed.

Note

Regarding command-line options, there seem to be differences in official Spanner CLI between launching as gcloud alpha spanner cli and as spannercli sql.
This is probably because gcloud alpha spanner cli as a wrapper adjusts for consistency with the gcloud spanner command. There's a possibility that spannercli sql may become unavailable in the future.

	`gcloud alpha spanner cli`	`spannercli sql`
Database ID	Positional parameter	`--database`
Role specification	`--database-role`	`--role`

Thoughts as a Spanner Interactive Tool Developer

The birth of official Spanner CLI is very welcome.
This is because, as official documentation is written using it going forward, both officials and the user community will use the same tool and be able to discuss operations and results in a shareable text format.
While EXPLAIN and EXPLAIN ANALYZE are not currently mentioned, I hope we'll be able to discuss execution plans using this tool as well.

My concern is that unlike OSS-developed spanner-cli, the official Spanner CLI's source is not public.
Being provided only as a compiled binary in the compiled language Go means it's impossible to investigate problems independently and apply patches, which may lead to continued inconvenience for users.

I think it would be preferable to become like gsutil, which is distributed by Google as one of the gcloud components but developed on GitHub.

With this, the official tool's future evolution is promised, but I intend to continue developing my spanner-mycli as the only actively developed OSS Spanner interactive client.

Acknowledgments

When I confirmed the relationship between this official Spanner CLI and OSS spanner-cli, I learned that they are considering putting spanner-cli into maintenance mode and archiving it after the official Spanner CLI goes GA.

https://github.com/cloudspannerecosystem/spanner-cli/issues/214

Today Google officially released the official Spanner CLI and it's basically equivalent (or superset) of OSS spanner-cli in terms of functionality.
To prevent confusion from having two separate CLIs, I'm considering to make OSS spanner-cli maintenance mode and archive this repository once the official Spanner CLI goes to GA.

I deeply thank Yuki Furuyama, the author and owner of spanner-cli, who has supported the Spanner community as an interactive client not provided by first-party Google itself for over six and a half years since v0.1.0 in October 2018.

DEV Community

A spanner-cli Contributor's Perspective on the Official Spanner CLI

Key Points of This Article

The Importance of Interactive Tools

Official Spanner CLI

Installation

Evidence that Official Spanner CLI is a Derivative of OSS spanner-cli

Note

Note end

Organizing the Fork Relationships

New Features Added in Official Spanner CLI

Meta-commands

Changes in Command-line Options

Note

Thoughts as a Spanner Interactive Tool Developer

Acknowledgments

Top comments (0)