spanner-mycli

Meta Commands

Meta commands are special commands that start with a backslash (\) and are processed by the CLI itself rather than being sent to Spanner. They are terminated by a newline rather than a semicolon, following the official spanner-cli style.

Note: Meta commands are only supported in interactive mode. They cannot be used in batch mode (with --execute or --file flags).

Supported Meta Commands

For detailed documentation on each meta command, see docs/meta_commands.md.

Customize prompt

You can customize the prompt by --prompt option or CLI_PROMPT system variable.
There are some escape sequences for being used in prompt.

Escape sequences:

• %p : GCP Project ID
• %i : Cloud Spanner Instance ID
• %d : Cloud Spanner Database ID (shows *detached* when in detached mode)
• %t : In transaction mode (ro txn) or (rw txn)
• %n : Newline
• %% : Character %
• %{VAR_NAME} : System variable expansion

Example:

$ spanner-mycli -p myproject -i myinstance -d mydb --prompt='[%p:%i:%d]%n\t%% '
Connected.
[myproject:myinstance:mydb]
%
[myproject:myinstance:mydb]
% SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
| 2  | bar  | false  |
+----+------+--------+
2 rows in set (3.09 msecs)

[myproject:myinstance:mydb]
% begin;
Query OK, 0 rows affected (0.08 sec)

[myproject:myinstance:mydb]
(rw txn)% ...

The default prompt is spanner%t> .

Prompt2

• %P is substituted with padding to align the primary prompt.
• %R is substituted with the current waiting status.

spanner> SELECT """
    """> test
    """> """ AS s,
      -> '''
    '''> test
    '''> ''' AS s2,
      -> /*
     /*> 
     /*> */
      -> ;

The default prompt2 is %P%R> .

If you set only %P to prompt2, continuation prompt is only indentations. It is convenient because of copy-and-paste friendly.

spanner> SET CLI_PROMPT2 = "%P";
Empty set (0.00 sec)

spanner> SELECT """
         test
         """ AS s;

Config file

This tool supports a TOML configuration file called .spanner_mycli.toml. The config files are loaded from ~/.spanner_mycli.toml and ./.spanner_mycli.toml. In the config file, you can set default option values for command line options. TOML keys normally follow hyphen-separated flag names, for example vertexai-project for --vertexai-project. For convenience, matching snake_case aliases such as vertexai_project are accepted too.

Example:

project = "myproject"
instance = "myinstance"
prompt = "[%p:%i:%d]%t> "

Configuration Precedence

1. Command line flags(highest)
2. Environment variables
3. .spanner_mycli.toml in current directory
4. .spanner_mycli.toml in home directory(lowest)

Request Priority

You can set request priority for command level or transaction level. By default MEDIUM priority is used for every request.

To set a priority for command line level, you can use --priority={HIGH|MEDIUM|LOW} command line option or RPC_PRIORITY system variable.

To set a priority for transaction level, you can use PRIORITY {HIGH|MEDIUM|LOW} keyword.

Here are some examples for transaction-level priority.

# Read-write transaction with low priority
BEGIN PRIORITY LOW;

# Read-only transaction with low priority
BEGIN RO PRIORITY LOW;

# Read-only transaction with 60s stale read and medium priority
BEGIN RO 60 PRIORITY MEDIUM;

# Read-only transaction with exact timestamp and medium priority
BEGIN RO 2021-04-01T23:47:44+00:00 PRIORITY MEDIUM;

Note that transaction-level priority takes precedence over command-level priority.

Transaction Tags and Request Tags

You can set transaction tag using SET TRANSACTION_TAG = "<tag>", and request tag using SET STATEMENT_TAG = "<tag>". TRANSACTION_TAG is the tag for the next physical read-write transaction. After that owner starts, SHOW VARIABLE TRANSACTION_TAG reports the applied tag. When the owner ends, the consumed slot is empty unless a SET LOCAL baseline remains: SET TRANSACTION_TAG = 'A' then pending SET LOCAL TRANSACTION_TAG = 'B' consumes B and restores A after COMMIT, ROLLBACK, or Close. An ordinary SET after SET LOCAL supersedes that baseline, so A then LOCAL B then SET C consumes C and does not restore A or B. SET and SET LOCAL of TRANSACTION_TAG are rejected while a physical read-write transaction is active. A pending BEGIN does not consume the tag, so BEGIN; then SET TRANSACTION_TAG still applies. Read-only transactions do not consume it; ordinary SET during RO stages the next RW slot, and SET LOCAL during RO follows the usual undo restoration. Partitioned DML does not currently send a transaction tag. STATEMENT_TAG is effective only for the next statement.

+------------------------------+
| BEGIN;                       |
| SET TRANSACTION_TAG = "tx1"; |
|                              |
| SET STATEMENT_TAG = "req1";  |
| SELECT val                   |
| FROM tab1      +--------------transaction_tag = tx1, request_tag = req1
| WHERE id = 1;                |
|                              |
| SELECT *                     |
| FROM tab1;     +--------------transaction_tag = tx1
|                              |
| SET STATEMENT_TAG = "req2";  |
|                              |
| UPDATE tab1                  |
| SET val = 10   +--------------transaction_tag = tx1, request_tag = req2
| WHERE id = 1;                |
|                              |
| COMMIT;        +--------------transaction_tag = tx1
|                              |
| BEGIN;                       |
| SELECT 1;                    |
| COMMIT;        +--------------no transaction_tag
+------------------------------+

Using with the Cloud Spanner Emulator

This tool supports the Cloud Spanner Emulator via the SPANNER_EMULATOR_HOST environment variable.

$ export SPANNER_EMULATOR_HOST=localhost:9010
# Or with gcloud env-init:
$ $(gcloud emulators spanner env-init)

$ spanner-mycli -p myproject -i myinstance -d mydb

# Or use --endpoint with --insecure
$ unset SPANNER_EMULATOR_HOST
$ spanner-mycli -p myproject -i myinstance -d mydb --endpoint=localhost:9010 --insecure

[!WARNING] The Cloud Spanner Emulator has several limitations that affect spanner-mycli functionality. See the official emulator limitations documentation for a complete list. Notable limitations include:

• Split points operations (ADD SPLIT POINTS, SHOW SPLIT POINTS, DROP SPLIT POINTS) are not supported
• EXPLAIN and EXPLAIN ANALYZE will error (emulator doesn't return query plans)

Using Regional Endpoints

spanner-mycli supports connecting to regional endpoints for improved performance and reliability. You can specify a regional endpoint using either the --endpoint flag or the --host flag:

# Using --endpoint (requires both host and port)
$ spanner-mycli -p myproject -i myinstance -d mydb --endpoint=spanner.us-central1.rep.googleapis.com:443

# Using --host (port 443 is used by default)
$ spanner-mycli -p myproject -i myinstance -d mydb --host=spanner.us-central1.rep.googleapis.com

# Verify the endpoint configuration
spanner> SHOW VARIABLE CLI_ENDPOINT;
+--------------------------------------------+
| CLI_ENDPOINT                               |
+--------------------------------------------+
| spanner.us-central1.rep.googleapis.com:443 |
+--------------------------------------------+

The --host and --port flags provide more flexibility:

• Use --port alone to connect to a local emulator: --port=9010 (implies localhost:9010)
• Use --host alone to connect to a regional endpoint: --host=spanner.us-central1.rep.googleapis.com (implies port 443)
• Use both for custom configurations: --host=custom-host --port=9999

Note: --endpoint and the combination of --host/--port are mutually exclusive.

Detached Mode

spanner-mycli supports a detached mode (admin operation only mode) that allows you to connect to a Cloud Spanner instance without initially connecting to a specific database. This is useful for performing instance-level administrative operations like creating or dropping databases.

Starting in Detached Mode

You can start spanner-mycli in detached mode using the --detached flag:

$ spanner-mycli -p myproject -i myinstance --detached
Connected in detached mode.
spanner:*detached*> SHOW DATABASES;
+----------------+
| Database       |
+----------------+
| db1            |
| db2            |
+----------------+
2 rows in set (18.66 msecs)

spanner:*detached*> CREATE DATABASE mydb;
Query OK, 0 rows affected (45.20 sec)

Switching Between Database and Detached Mode

You can switch between databases and detached mode during an interactive session:

# Connect to a database from detached mode
spanner:*detached*> USE mydb;
Database changed
spanner:mydb> 

# Detach from database and return to detached mode  
spanner:mydb> DETACH;
Detached from database
spanner:*detached*>

Database Parameter Priority

The database connection follows this priority order:

1. --detached flag (highest priority) - forces detached mode
2. --database command line option
3. SPANNER_DATABASE_ID environment variable
4. Default behavior (detached mode if no database specified)

Limitations in Detached Mode

When in detached mode, you can only execute:

• Instance-level operations (SHOW DATABASES, CREATE DATABASE, DROP DATABASE)
• Client-side statements that don't require database access
• USE statements to connect to a database

Database-specific operations will fail with an error message indicating that no database is connected.

Notable features of spanner-mycli

This section describes some notable features of spanner-mycli, they are not appeared in original spanner-cli.

System Variables

Spanner JDBC inspired variables

They have almost same semantics with Spanner JDBC properties

For how these and other connection properties map to the official Spanner drivers (Spanner JDBC and go-sql-spanner), including tracked and intentionally skipped deltas, see docs/spanner-driver-compatibility.md.

spanner-mycli original variables

Note: CLI_BIGQUERY_PROJECT defaults to CLI_PROJECT when empty. CLI_BIGQUERY_LOCATION and CLI_BIGQUERY_MAX_BYTES_BILLED are optional BigQuery job settings.

Note: CLI_FORMAT accepts the following values:

• TABLE - ASCII table with borders (default for both interactive and batch modes)
• TABLE_COMMENT - Table wrapped in /* */ comments
• TABLE_DETAIL_COMMENT - Table and execution details wrapped in /* */ comments (useful for embedding results in SQL code blocks)
• VERTICAL - Vertical format (column: value pairs)
• TAB - Tab-separated values (raw; values containing tabs or newlines break the row/column structure)
• TSV - Tab-separated values with escaping (tab, newline, carriage return, and backslash in values are escaped as \t, \n, \r, \\)
• HTML - HTML table format (compatible with Google Cloud Spanner CLI)
• XML - XML format (compatible with Google Cloud Spanner CLI)
• CSV - Comma-separated values (RFC 4180 compliant with automatic escaping)
• JSONL - JSON Lines (one JSON object per row with type-aware values)
• SQL_INSERT - SQL INSERT statements
• SQL_INSERT_OR_IGNORE - SQL INSERT OR IGNORE statements
• SQL_INSERT_OR_UPDATE - SQL INSERT OR UPDATE statements

You can change the output format at runtime using SET CLI_FORMAT = 'CSV'; or use command-line flags --table, --html, --xml, --csv, or --format.

Note: CLI_TABLE_STREAMING controls table streaming output mode:

• AUTO (default) - Buffers Table formats for accurate column width calculation; non-table formats stream
• TRUE - Streams Table formats too (reduces memory usage, faster time-to-first-byte)
• FALSE - Buffers Table formats; non-table formats still stream

For Table formats with streaming enabled, CLI_TABLE_PREVIEW_ROWS (default: 50) controls how many rows are used to calculate column widths before streaming the rest.

Note: CLI_FUZZY_FINDER_OPTIONS passes additional fzf options to the fuzzy finder. Options are appended after built-in defaults, so user options take precedence (last wins). Built-in defaults: --reverse, --no-sort, --height=<computed>, --select-1, --exit-0, --highlight-line, --cycle, --border=rounded, --info=inline-right, and --header-border=inline when a header is shown. --tmux and --popup are not supported because the fuzzy finder runs fzf in-process via the Go library.

Note: CLI_TYPE_STYLES configures ANSI styling for query result values based on their Spanner type. Format: colon-separated TYPE=STYLE pairs.

• Named colors/attributes: red, green, bold, dim, italic, underline, etc.
• Raw SGR numbers: 38;5;214 (256-color), 38;2;R;G;B (truecolor)
• Combined: bold;green
• Supported types: BOOL, INT64, FLOAT32, FLOAT64, NUMERIC, STRING, BYTES, JSON, DATE, TIMESTAMP, ARRAY, STRUCT, PROTO, ENUM, INTERVAL, UUID, NULL
• Example: SET CLI_TYPE_STYLES = 'STRING=green:INT64=cyan:NULL=dim';
• Set to empty string to disable all type styling.
• See docs/system_variables.md for full reference.

Batch statements

DDL batching

You can issue a batch DDL statements.

• START BATCH DDL starts DDL batching. Consecutive DDL statements are batched.
• RUN BATCH runs the batch.
• or ABORT BATCH aborts the batch.

Note: SET CLI_ECHO_EXECUTED_DDL = TRUE enables echo back of actual executed DDLs. It is useful to observe multiple schema versions in batch DDL.

spanner> START BATCH DDL;
Empty set (0.00 sec)

spanner> DROP INDEX IF EXISTS BatchExampleByCol;
Query OK, 0 rows affected (0.00 sec) (1 DDL in batch)

spanner> DROP TABLE IF EXISTS BatchExample;
Query OK, 0 rows affected (0.00 sec) (2 DDLs in batch)

spanner> CREATE TABLE BatchExample (PK INT64 PRIMARY KEY, Col INT64);
Query OK, 0 rows affected (0.00 sec) (3 DDLs in batch)

spanner> CREATE INDEX ExistingTableByCol ON ExistingTable(Col);
Query OK, 0 rows affected (0.00 sec) (4 DDLs in batch)

spanner> CREATE INDEX BatchExampleByCol ON BatchExample(Col);
Query OK, 0 rows affected (0.00 sec) (5 DDLs in batch)

spanner> RUN BATCH;
+--------------------------------------------------------------+-----------------------------+
| Executed                                                     | Commit Timestamp            |
+--------------------------------------------------------------+-----------------------------+
| DROP INDEX IF EXISTS BatchExampleByCol;                      | 2024-12-29T09:09:00.404097Z |
| DROP TABLE IF EXISTS BatchExample;                           | 2024-12-29T09:09:00.404097Z |
| CREATE TABLE BatchExample (PK INT64 PRIMARY KEY, Col INT64); | 2024-12-29T09:09:00.404097Z |
| CREATE INDEX ExistingTableByCol ON ExistingTable(Col);       | 2024-12-29T09:09:18.278025Z |
| CREATE INDEX BatchExampleByCol ON BatchExample(Col);         | 2024-12-29T09:09:41.061034Z |
+--------------------------------------------------------------+-----------------------------+
Query OK, 0 rows affected (51.00 sec)
timestamp:      2024-12-29T09:09:41.061034Z


# or abort using ABORT BATCH.

DML

You can use batch DML.

• START BATCH DML starts DML batching. Consecutive DML statements are batched.
• RUN BATCH runs the batch.
• or ABORT BATCH aborts the batch.

spanner> START BATCH DML;
Empty set (0.00 sec)

spanner> DELETE FROM BatchExample WHERE TRUE;
Query OK, 0 rows affected (0.00 sec) (1 DML in batch)

spanner> INSERT INTO BatchExample (PK, Col) VALUES(1, 2);
Query OK, 0 rows affected (0.00 sec) (2 DMLs in batch)

spanner> UPDATE BatchExample SET Col = Col + 1 WHERE TRUE;
Query OK, 0 rows affected (0.00 sec) (3 DMLs in batch)

spanner> RUN BATCH;
+--------------------------------------------------+------+
| DML                                              | Rows |
+--------------------------------------------------+------+
| DELETE FROM BatchExample WHERE TRUE              | 1    |
| INSERT INTO BatchExample (PK, Col) VALUES(1, 2)  | 1    |
| UPDATE BatchExample SET Col = Col + 1 WHERE TRUE | 1    |
+--------------------------------------------------+------+
Query OK, at most 3 rows affected (0.59 sec)
timestamp:      2024-12-29T17:25:48.616395+09:00
mutation_count: 9

# or abort using ABORT BATCH.

Embedded Cloud Spanner Emulator

spanner-mycli can launch Cloud Spanner Emulator with empty database, powered by testcontainers.

$ spanner-mycli --embedded-emulator [--emulator-image=gcr.io/cloud-spanner-emulator/emulator:${VERSION}] [--emulator-platform=linux/amd64]
> SET CLI_PROMPT="%p:%i:%d%n> ";
Empty set (0.00 sec)

emulator-project:emulator-instance:emulator-database
> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA", "SPANNER_SYS");
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARENT_TABLE_NAME | ON_DELETE_ACTION | TABLE_TYPE | SPANNER_STATE | INTERLEAVE_TYPE | ROW_DELETION_POLICY_EXPRESSION |
| STRING        | STRING       | STRING     | STRING            | STRING           | STRING     | STRING        | STRING          | STRING                         |
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
Empty set (8.763167ms)

Sample Databases

You can initialize the embedded runtime with Google's official sample databases using the --sample-database flag:

# List available sample databases
$ spanner-mycli --list-samples
Available sample databases:

  banking        GoogleSQL    Banking application with accounts and transactions
  finance        GoogleSQL    Finance application schema (GoogleSQL)
  finance-graph  GoogleSQL    Finance application with graph features
  finance-pg     PostgreSQL   Finance application (PostgreSQL dialect)
  fingraph       GoogleSQL    Financial graph example demonstrating Spanner Graph features
  gaming         GoogleSQL    Gaming application with players and scores
  singers        GoogleSQL    Music database used throughout Spanner documentation

Usage: spanner-mycli --embedded-emulator --sample-database=<name>
       spanner-mycli --embedded-omni --sample-database=<name>
       spanner-mycli --embedded-emulator --sample-database=/path/to/metadata.yaml
       spanner-mycli --embedded-omni --sample-database=/path/to/metadata.yaml
       Sample metadata files may use .json, .yaml, or .yml.

# Start with the banking sample database
$ spanner-mycli --embedded-emulator --sample-database=banking
emulator-project:emulator-instance:emulator-database
> SELECT COUNT(*) AS count FROM Accounts;
+-------+
| count |
| INT64 |
+-------+
| 10    |
+-------+
1 rows in set (2.76 ms)

# Use embedded fingraph sample
$ spanner-mycli --embedded-emulator --sample-database=fingraph

# Load the same sample on embedded Omni
$ spanner-mycli --embedded-omni --sample-database=fingraph

# Use custom sample with metadata file
$ spanner-mycli --embedded-emulator --sample-database=/path/to/mysample.yaml
$ spanner-mycli --embedded-omni --sample-database=/path/to/mysample.yaml

The sample databases include both embedded samples (fingraph, singers) and samples downloaded from Google Cloud Storage. You can also create custom samples using metadata files in JSON or YAML format.

[!NOTE] The embedded emulator has the same limitations as the standalone emulator. See the warning in the Using with the Cloud Spanner Emulator section above for details.

Embedded Spanner Omni

spanner-mycli can also launch experimental Spanner Omni via spanemuboost.

$ spanner-mycli --embedded-omni
default:default:emulator-database
> SELECT 1

--embedded-omni automatically configures the fixed single-server Omni project and instance defaults, reuses the backend-provided client options needed for the experimental host, and can load the same sample databases as the embedded emulator path. In Spanner Omni terminology, this embedded single-server setup corresponds to a deployment, which is the Omni equivalent of a Google Cloud Spanner instance; see Spanner Omni key terms. Before relying on embedded Omni locally, also check the official Spanner Omni system requirements.

Protocol Buffers support

You can use --proto-descriptor-file option to specify proto descriptor file.

$ spanner-mycli --proto-descriptor-file=testdata/protos/order_descriptors.pb 
Connected.
spanner> SHOW LOCAL PROTO;
+---------------------------------+-------+-------------------+--------------------+
| full_name                       | kind  | package           | file               |
+---------------------------------+-------+-------------------+--------------------+
| examples.shipping.Order         | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.Order.Address | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.Order.Item    | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.OrderHistory  | PROTO | examples.shipping | order_protos.proto |
+---------------------------------+-------+-------------------+--------------------+
4 rows in set (0.00 sec)

spanner> SET CLI_PROTO_DESCRIPTOR_FILE += "testdata/protos/query_plan_descriptors.pb";
Empty set (0.00 sec)

spanner> SHOW LOCAL PROTO;
+----------------------------------------------------------------+-------+-------------------+-----------------------------------------------+
| full_name                                                      | kind  | package           | file                                          |
+----------------------------------------------------------------+-------+-------------------+-----------------------------------------------+
| examples.shipping.Order                                        | PROTO | examples.shipping | order_protos.proto                            |
| examples.shipping.Order.Address                                | PROTO | examples.shipping | order_protos.proto                            |
| examples.shipping.Order.Item                                   | PROTO | examples.shipping | order_protos.proto                            |
| examples.shipping.OrderHistory                                 | PROTO | examples.shipping | order_protos.proto                            |
| google.protobuf.Struct                                         | PROTO | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.Struct.FieldsEntry                             | PROTO | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.Value                                          | PROTO | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.ListValue                                      | PROTO | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.NullValue                                      | ENUM  | google.protobuf   | google/protobuf/struct.proto                  |
| google.spanner.v1.PlanNode                                     | PROTO | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ChildLink                           | PROTO | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ShortRepresentation                 | PROTO | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ShortRepresentation.SubqueriesEntry | PROTO | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.Kind                                | ENUM  | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.QueryPlan                                    | PROTO | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
+----------------------------------------------------------------+-------+-------------------+-----------------------------------------------+
15 rows in set (0.00 sec)

spanner> CREATE PROTO BUNDLE (`examples.shipping.Order`);
Query OK, 0 rows affected (6.34 sec)

spanner> SHOW REMOTE PROTO;
+-------------------------+-------+-------------------+
| full_name               | kind  | package           |
+-------------------------+-------+-------------------+
| examples.shipping.Order | PROTO | examples.shipping |
+-------------------------+-------+-------------------+
1 rows in set (0.94 sec)

spanner> ALTER PROTO BUNDLE INSERT (`examples.shipping.Order.Item`);
Query OK, 0 rows affected (9.25 sec)

spanner> SHOW REMOTE PROTO;
+------------------------------+-------+-------------------+
| full_name                    | kind  | package           |
+------------------------------+-------+-------------------+
| examples.shipping.Order      | PROTO | examples.shipping |
| examples.shipping.Order.Item | PROTO | examples.shipping |
+------------------------------+-------+-------------------+
2 rows in set (0.82 sec)

spanner> ALTER PROTO BUNDLE UPDATE (`examples.shipping.Order`);
Query OK, 0 rows affected (8.68 sec)

spanner> ALTER PROTO BUNDLE DELETE (`examples.shipping.Order.Item`);
Query OK, 0 rows affected (9.55 sec)

spanner> SHOW REMOTE PROTO;
+-------------------------+-------+-------------------+
| full_name               | kind  | package           |
+-------------------------+-------+-------------------+
| examples.shipping.Order | PROTO | examples.shipping |
+-------------------------+-------+-------------------+
1 rows in set (0.79 sec)

You can also use CLI_PROTO_DESCRIPTOR_FILE system variable to update or read the current proto descriptor file setting.

spanner> SET CLI_PROTO_DESCRIPTOR_FILE = "./other_descriptors.pb";
Empty set (0.00 sec)

spanner> SHOW VARIABLE CLI_PROTO_DESCRIPTOR_FILE;
+---------------------------+
| CLI_PROTO_DESCRIPTOR_FILE |
+---------------------------+
| ./other_descriptors.pb    |
+---------------------------+
Empty set (0.00 sec)

PROTO_DESCRIPTORS holds the same effective graph as base64. SET PROTO_DESCRIPTORS replaces the graph and clears file provenance. DUMP SCHEMA and DUMP DATABASE emit that SET before DDL when the admin response includes descriptors, so a single SQL script can replay CREATE PROTO BUNDLE without a sidecar file. SET LOCAL is not supported, and SET is rejected while a manual batch is active.

(EXPERIMENTAL) It also supports non-compiled .proto files, including their transitive imports and standard protobuf imports.

Comma-separated inputs are merged into one descriptor graph before validation, both for --proto-descriptor-file and SET CLI_PROTO_DESCRIPTOR_FILE. Later inputs replace earlier descriptors with the same protobuf file name; file names must therefore identify the intended version, even if its package changes. The resulting graph must resolve all imports and symbols. Failed SET or ADD leaves both the previous descriptor graph and file list unchanged; split binary descriptor fragments must be supplied together in one SET, not as incomplete intermediate ADD operations.

spanner> SET CLI_PROTO_DESCRIPTOR_FILE = "testdata/protos/order_protos.proto";
Empty set (0.00 sec)

spanner> SHOW LOCAL PROTO;
+---------------------------------+-------+-------------------+------------------------------------+
| full_name                       | kind  | package           | file                               |
+---------------------------------+-------+-------------------+------------------------------------+
| examples.shipping.Order         | PROTO | examples.shipping | testdata/protos/order_protos.proto |
| examples.shipping.Order.Address | PROTO | examples.shipping | testdata/protos/order_protos.proto |
| examples.shipping.Order.Item    | PROTO | examples.shipping | testdata/protos/order_protos.proto |
| examples.shipping.OrderHistory  | PROTO | examples.shipping | testdata/protos/order_protos.proto |
+---------------------------------+-------+-------------------+------------------------------------+
4 rows in set (0.00 sec)

This feature is powered by bufbuild/protocompile.

(EXPERIMENTAL) .pb and .proto files can be loaded from URL.

spanner> SET CLI_PROTO_DESCRIPTOR_FILE = "https://github.com/apstndb/spanner-mycli/raw/refs/heads/main/testdata/protos/order_descriptors.pb";
Empty set (0.68 sec)

[gcpug-public-spanner:merpay-sponsored-instance:apstndb-sampledb3]
spanner> SHOW LOCAL PROTO;
+---------------------------------+-------+-------------------+--------------------+
| full_name                       | kind  | package           | file               |
+---------------------------------+-------+-------------------+--------------------+
| examples.shipping.Order         | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.Order.Address | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.Order.Item    | PROTO | examples.shipping | order_protos.proto |
| examples.shipping.OrderHistory  | PROTO | examples.shipping | order_protos.proto |
+---------------------------------+-------+-------------------+--------------------+
4 rows in set (0.00 sec)

SYNC PROTO BUNDLE statement

Note: SET CLI_ECHO_EXECUTED_DDL = TRUE enables echo back of actual executed DDLs.

spanner> SET CLI_ECHO_EXECUTED_DDL = TRUE;
Empty set (0.00 sec)

SYNC PROTO BUNDLE UPSERT (FullName, ...) simplifies use of CREATE PROTO BUNDLE and ALTER PROTO BUNDLE {INSERT|UPDATE}.

SYNC PROTO BUNDLE executes ALTER PROTO BUNDLE INSERT or ALTER PROTO BUNDLE UPDATE in UPSERT semantics.

spanner> SHOW REMOTE PROTO;
+-------------------------+-------+-------------------+
| full_name               | kind  | package           |
+-------------------------+-------+-------------------+
| examples.shipping.Order | PROTO | examples.shipping |
+-------------------------+-------+-------------------+
1 rows in set (0.87 sec)

spanner> SYNC PROTO BUNDLE UPSERT (`examples.shipping.Order`, examples.shipping.OrderHistory);
+------------------------------------------------------------------------------------------------+
| executed                                                                                       |
+------------------------------------------------------------------------------------------------+
| ALTER PROTO BUNDLE INSERT (examples.shipping.OrderHistory) UPDATE (examples.shipping.`Order`); |
+------------------------------------------------------------------------------------------------+
Query OK, 0 rows affected (8.57 sec)

If SYNC PROTO BUNDLE UPSERT is executed on empty proto bundle, CREATE PROTO BUNDLE will be executed instead of ALTER PROTO BUNDLE.

spanner> SHOW REMOTE PROTO;
Empty set (0.89 sec)

spanner> SYNC PROTO BUNDLE UPSERT (`examples.shipping.Order`);
+--------------------------------------------------+
| executed                                         |
+--------------------------------------------------+
| CREATE PROTO BUNDLE (examples.shipping.`Order`); |
+--------------------------------------------------+
Query OK, 0 rows affected (8.27 sec)

SYNC PROTO BUNDLE DELETE (<full_name>, ...) executes PROTO BUNDLE DELETE only existing types(DELETE IF EXISTS semantics).

spanner> SHOW REMOTE PROTO;
+--------------------------------+-------+-------------------+
| full_name                      | kind  | package           |
+--------------------------------+-------+-------------------+
| examples.shipping.Order        | PROTO | examples.shipping |
| examples.shipping.OrderHistory | PROTO | examples.shipping |
+--------------------------------+-------+-------------------+
2 rows in set (0.87 sec)

spanner> SYNC PROTO BUNDLE DELETE (`examples.shipping.Order`, examples.UnknownType);
+--------------------------------------------------------+
| executed                                               |
+--------------------------------------------------------+
| ALTER PROTO BUNDLE DELETE (examples.shipping.`Order`); |
+--------------------------------------------------------+
Query OK, 0 rows affected (8.86 sec)

spanner> SYNC PROTO BUNDLE DELETE (examples.UnknownType);
Query OK, 0 rows affected (0.79 sec)

If SYNC PROTO BUNDLE DELETE will delete the last Protocol Buffers type in the proto bundle, DROP PROTO BUNDLE will be executed instead of ALTER PROTO BUNDLE.

spanner> SHOW REMOTE PROTO;

+-------------------------+-------+-------------------+
| full_name               | kind  | package           |
+-------------------------+-------+-------------------+
| examples.shipping.Order | PROTO | examples.shipping |
+-------------------------+-------+-------------------+
1 rows in set (0.87 sec)

spanner> SYNC PROTO BUNDLE DELETE (`examples.shipping.OrderHistory`);
+--------------------+
| executed           |
+--------------------+
| DROP PROTO BUNDLE; |
+--------------------+
Query OK, 0 rows affected (8.24 sec)

PROTO/ENUM value formatting

Loaded proto descriptors are used for formatting PROTO and ENUM column values.

spanner> SELECT p, p.*
         FROM (
           SELECT AS VALUE NEW examples.spanner.music.SingerInfo{singer_id: 1, birth_date: "1970-01-01", nationality: "Japan", genre: "POP"}
         ) AS p;
+----------------------------------+-----------+------------+-------------+-------------+
| p                                | singer_id | birth_date | nationality | genre       |
| PROTO<SingerInfo>                | INT64     | STRING     | STRING      | ENUM<Genre> |
+----------------------------------+-----------+------------+-------------+-------------+
| CAESCjE5NzAtMDEtMDEaBUphcGFuIAA= | 1         | 1970-01-01 | Japan       | 0           |
+----------------------------------+-----------+------------+-------------+-------------+
1 rows in set (0.33 msecs)

spanner> SET CLI_PROTO_DESCRIPTOR_FILE += "testdata/protos/singer.proto";
Empty set (0.00 sec)

spanner> SHOW LOCAL PROTO;
+-----------------------------------------+-------+------------------------+------------------------------+
| full_name                               | kind  | package                | file                         |
+-----------------------------------------+-------+------------------------+------------------------------+
| examples.spanner.music.SingerInfo       | PROTO | examples.spanner.music | testdata/protos/singer.proto |
| examples.spanner.music.CustomSingerInfo | PROTO | examples.spanner.music | testdata/protos/singer.proto |
| examples.spanner.music.Genre            | ENUM  | examples.spanner.music | testdata/protos/singer.proto |
| examples.spanner.music.CustomGenre      | ENUM  | examples.spanner.music | testdata/protos/singer.proto |
+-----------------------------------------+-------+------------------------+------------------------------+
4 rows in set (0.00 sec)

spanner> SELECT p, p.*
         FROM (
           SELECT AS VALUE NEW examples.spanner.music.SingerInfo{singer_id: 1, birth_date: "1970-01-01", nationality: "Japan", genre: "POP"}
         ) AS p;
+-------------------------------------------------------------------+-----------+------------+-------------+-------------+
| p                                                                 | singer_id | birth_date | nationality | genre       |
| PROTO<SingerInfo>                                                 | INT64     | STRING     | STRING      | ENUM<Genre> |
+-------------------------------------------------------------------+-----------+------------+-------------+-------------+
| singer_id:1 birth_date:"1970-01-01" nationality:"Japan" genre:POP | 1         | 1970-01-01 | Japan       | POP         |
+-------------------------------------------------------------------+-----------+------------+-------------+-------------+
1 rows in set (0.87 msecs)

You can enable multiline format.

spanner> SET CLI_PROTOTEXT_MULTILINE=TRUE;
Empty set (0.00 sec)

spanner> SELECT p, p.*
         FROM (
           SELECT AS VALUE NEW examples.spanner.music.SingerInfo{singer_id: 1, birth_date: "1970-01-01", nationality: "Japan", genre: "POP"}
         ) AS p;
+--------------------------+-----------+------------+-------------+-------------+
| p                        | singer_id | birth_date | nationality | genre       |
| PROTO<SingerInfo>        | INT64     | STRING     | STRING      | ENUM<Genre> |
+--------------------------+-----------+------------+-------------+-------------+
| singer_id: 1             | 1         | 1970-01-01 | Japan       | POP         |
| birth_date: "1970-01-01" |           |            |             |             |
| nationality: "Japan"     |           |            |             |             |
| genre: POP               |           |            |             |             |
+--------------------------+-----------+------------+-------------+-------------+
1 rows in set (3.37 msecs)

Show database operations

You can list schema update operations and their progress.

Note: PROGESS column is not populated for done operations due to avoid N+1 API calls.

spanner> SHOW SCHEMA UPDATE OPERATIONS;
+---------------------------+---------------------------------------------------+-------+----------+-----------------------------+-------+
| OPERATION_ID              | STATEMENTS                                        | DONE  | PROGRESS | COMMIT_TIMESTAMP            | ERROR |
+---------------------------+---------------------------------------------------+-------+----------+-----------------------------+-------+
| _auto_op_0175680d57629380 | CREATE INDEX SongsBySongName3 ON Songs(SongName); | false | 100      | 2025-05-02T14:43:14.658597Z |       |
|                           | CREATE INDEX SongsBySongName4 ON Songs(SongName); |       | 17       |                             |       |
+---------------------------+---------------------------------------------------+-------+----------+-----------------------------+-------+
1 rows in set (1.88 sec)

Split Points support

[!WARNING] Split points operations (ADD SPLIT POINTS, SHOW SPLIT POINTS, DROP SPLIT POINTS) are not supported by the Cloud Spanner Emulator and will result in an error. See emulator limitations for details.

spanner-mycli can manage split points for pre-splitting.

ADD SPLIT POINTS

You can add split points using ADD SPLIT POINTS statement.

• You can add multiple split points in a statement.
• Table and index split points can use composite keys.
• Index split points can include the complete table primary key with TableKey (...).
• You can specify the expiration of split points.

-- split point for table key with default expiration
spanner> ADD SPLIT POINTS
         TABLE Singers (42);

-- split point for table key in named schema
spanner> ADD SPLIT POINTS EXPIRED AT "2025-05-05T00:00:00"
         TABLE sch1.Singers (21);

spanner> ADD SPLIT POINTS EXPIRED AT "2025-05-05T00:00:00Z"
         -- split point for index key
         INDEX SingersByFirstLastName ("John", "Doe")
         -- split point for index key with table primary key
         INDEX SingersByFirstLastName ("Mary", "Sue") TableKey (12);

The entry structure follows gcloud spanner databases splits add --splits-file, including composite keys and the optional index TableKey (...) suffix. Literal syntax differs as follows:

• It is implemented using memefish so it follows GoogleSQL lexical structure.
• INT64 and NUMERIC Spanner data types can be integer literal. For example, 123 or 99.99
• Floating number values need to be written as float literal. For example, 1.0 or 1.287
• Even if the split value needs to have a comma, you must not escape the comma.

SHOW SPLIT POINTS

You can show defined split points using SHOW SPLIT POINTS statement.

spanner> SHOW SPLIT POINTS;
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
| TABLE_NAME   | INDEX_NAME             | INITIATOR           | SPLIT_KEY                                                                                     | EXPIRE_TIME                 |
| STRING       | STRING                 | STRING              | STRING                                                                                        | TIMESTAMP                   |
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
| Singers      |                        | cloud_spanner_mycli | Singers(42)                                                                                   | 2025-05-09T11:29:43.928097Z |
| Singers      | SingersByFirstLastName | cloud_spanner_mycli | Index: SingersByFirstLastName on Singers, Index Key: (John,Doe), Primary Table Key: (<begin>) | 2025-05-05T00:00:00Z        |
| Singers      | SingersByFirstLastName | cloud_spanner_mycli | Index: SingersByFirstLastName on Singers, Index Key: (Mary,Sue), Primary Table Key: (12)      | 2025-05-05T00:00:00Z        |
| sch1.Singers |                        | cloud_spanner_mycli | sch1.Singers(21)                                                                              | 2025-05-05T00:00:00Z        |
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
4 rows in set (0.26 sec)

DROP SPLIT POINTS

You can drop split points using DROP SPLIT POINTS statement.

spanner> DROP SPLIT POINTS
         INDEX SingersByFirstLastName ("Mary", "Sue") TableKey (12);
-
Query OK, 0 rows affected (0.26 sec)

spanner> SHOW SPLIT POINTS;
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
| TABLE_NAME   | INDEX_NAME             | INITIATOR           | SPLIT_KEY                                                                                     | EXPIRE_TIME                 |
| STRING       | STRING                 | STRING              | STRING                                                                                        | TIMESTAMP                   |
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
| Singers      |                        | cloud_spanner_mycli | Singers(42)                                                                                   | 2025-05-09T11:29:43.928097Z |
| Singers      | SingersByFirstLastName | cloud_spanner_mycli | Index: SingersByFirstLastName on Singers, Index Key: (John,Doe), Primary Table Key: (<begin>) | 2025-05-05T00:00:00Z        |
| sch1.Singers |                        | cloud_spanner_mycli | sch1.Singers(21)                                                                              | 2025-05-05T00:00:00Z        |
+--------------+------------------------+---------------------+-----------------------------------------------------------------------------------------------+-----------------------------+
3 rows in set (0.02 sec)

Configurable query stats

The detail lines are customizable using Go text/template. Note: You need to know OutputContext in spanner-mycli and spanstats.QueryStats. Prefer OutputContext.ReadTimestamp and OutputContext.CommitTimestamp in custom templates. OutputContext.Timestamp remains a compatibility alias for whichever of those fields is present. The bundled output_full.tmpl uses the explicit fields.

$ cat output_timestamp.tmpl
{{- /*gotype: github.com/apstndb/spanner-mycli.OutputContext */ -}}
{{if .Verbose -}}
{{with .ReadTimestamp -}}                   read_timestamp:       {{.}}{{"\n"}}{{end -}}
{{with .CommitTimestamp -}}                 commit_timestamp:     {{.}}{{"\n"}}{{end -}}
{{with .CommitStats.GetMutationCount -}}    mutation_count:       {{.}}{{"\n"}}{{end -}}
{{with .Stats.ElapsedTime -}}               elapsed time:         {{.}}{{"\n"}}{{end -}}
{{with .Stats.CPUTime -}}                   cpu time:             {{.}}{{"\n"}}{{end -}}
{{with .Stats.RowsReturned -}}              rows returned:        {{.}}{{"\n"}}{{end -}}
{{with .Stats.RowsScanned -}}               rows scanned:         {{.}}{{"\n"}}{{end -}}
{{with .Stats.DeletedRowsScanned -}}        deleted rows scanned: {{.}}{{"\n"}}{{end -}}
{{with .Stats.OptimizerVersion -}}          optimizer version:    {{.}}{{"\n"}}{{end -}}
{{with .Stats.OptimizerStatisticsPackage -}}optimizer statistics: {{.}}{{"\n"}}{{end -}}
{{with .Stats.RemoteServerCalls -}}         remote server calls:  {{.}}{{"\n"}}{{end -}}
{{with .Stats.MemoryPeakUsageBytes -}}      peak memory usage:    {{.}} bytes{{"\n"}}{{end -}}
{{with .Stats.TotalMemoryPeakUsageByte -}}  total peak memory:    {{.}} bytes{{"\n"}}{{end -}}
{{with .Stats.BytesReturned -}}             bytes returned:       {{.}} bytes{{"\n"}}{{end -}}
{{with .Stats.RuntimeCreationTime -}}       runtime creation:     {{.}}{{"\n"}}{{end -}}
{{with .Stats.StatisticsLoadTime -}}        statistics load:      {{.}}{{"\n"}}{{end -}}
{{with .Stats.MemoryUsagePercentage -}}     memory usage:         {{.}} %{{"\n"}}{{end -}}
{{with .Stats.FilesystemDelaySeconds -}}    filesystem delay:     {{.}}{{"\n"}}{{end -}}
{{with .Stats.LockingDelay -}}              locking delay:        {{.}}{{"\n"}}{{end -}}
{{with .Stats.QueryPlanCreationTime -}}     plan creation time:   {{.}}{{"\n"}}{{end -}}
{{with .Stats.ServerQueueDelay -}}          server queue delay:   {{.}}{{"\n"}}{{end -}}
{{with .Stats.DataBytesRead -}}             data bytes read:      {{.}} bytes{{"\n"}}{{end -}}
{{with .Stats.IsGraphQuery -}}              is graph query:       {{.}}{{"\n"}}{{end -}}
{{with .Stats.RuntimeCached -}}             runtime cached:       {{.}}{{"\n"}}{{end -}}
{{with .Stats.QueryPlanCached -}}           query plan cached:    {{.}}{{"\n"}}{{end -}}
{{with .Stats.Unknown -}}                   unknown:              {{.}}{{"\n"}}{{end -}}
{{end -}}

$ spanner-mycli -t -v --output-template output_timestamp.tmpl -e 'SELECT 1'
+-------+
| n     |
| INT64 |
+-------+
| 1     |
+-------+
1 rows in set (2.21 msecs)
timestamp:            2025-05-04T02:46:40.457385+09:00
elapsed time:         2.21 msecs
cpu time:             1.53 msecs
rows returned:        1
rows scanned:         0
deleted rows scanned: 0
optimizer version:    7
optimizer statistics: auto_20250430_23_51_48UTC
remote server calls:  0/0
peak memory usage:    4 bytes
total peak memory:    4 bytes
bytes returned:       8 bytes
runtime creation:     0.04 msecs
statistics load:      0
memory usage:         0.000 %
filesystem delay:     0 msecs
locking delay:        0 msecs
plan creation time:   1.05 msecs
server queue delay:   0.02 msecs
is graph query:       false

memefish integration

spanner-mycli utilizes memefish as:

• statement type detector
• comment stripper
• statement separator

Statement type detector behavior can be controlled by CLI_PARSE_MODE system variable.

spanner> SET CLI_PARSE_MODE = "MEMEFISH_ONLY";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
ERROR: invalid statement: syntax error: :1:10: expected token: <eof>, but: <ident>

  1:  SELECT * FRM 1
               ^~~

spanner> SET CLI_PARSE_MODE = "FALLBACK";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
2024/11/02 00:22:57 ignore memefish parse error, err: syntax error: :1:10: expected token: <eof>, but: <ident>

  1:  SELECT * FRM 1
               ^~~

ERROR: spanner: code = "InvalidArgument", desc = "Syntax error: Expected end of input but got identifier \\\"FRM\\\" [at 1:10]\\nSELECT * FRM 1\\n         ^"
spanner> SET CLI_PARSE_MODE = "NO_MEMEFISH";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
ERROR: spanner: code = "InvalidArgument", desc = "Syntax error: Expected end of input but got identifier \\\"FRM\\\" [at 1:10]\\nSELECT * FRM 1\\n         ^"

Mutations support

spanner-mycli supports mutations.

Mutations are buffered in read-write transaction, or immediately commit outside explicit transaction.

Write mutations

MUTATE <table_fqn> {INSERT|UPDATE|REPLACE|INSERT_OR_UPDATE} {<struct_literal> | <array_of_struct_literal>};

Delete mutations

MUTATE <table_fqn> DELETE ALL;
MUTATE <table_fqn> DELETE {<tuple_struct_literal> | <array_of_tuple_struct_literal>};
MUTATE <table_fqn> DELETE KEY_RANGE({start_closed | start_open} => <tuple_struct_literal>,
                                    {end_closed | end_open} => <tuple_struct_literal>);

Note: In this context, parenthesized expression and some simple literals are treated as a single field struct literal.

Examples of mutations

Example schema

CREATE TABLE MutationTest (PK INT64, Col INT64) PRIMARY KEY(PK);
CREATE TABLE MutationTest2 (PK1 INT64, PK2 STRING(MAX), Col INT64) PRIMARY KEY(PK1, PK2);

Insert a single row with key(1).

MUTATE MutationTest INSERT STRUCT(1 AS PK);

Insert or update four rows with keys(1, "foo", 1, "n", 1, "m", 50, "foobar").

MUTATE MutationTest2 INSERT_OR_UPDATE [STRUCT(1 AS PK1, "foo" AS PK2, 0 AS Col), (1, "n", 1), (1, "m", 3), (50, "foobar", 4)];

You can set commit timestamps using PENDING_COMMIT_TIMESTAMP().

spanner> MUTATE Performances INSERT_OR_UPDATE STRUCT(
                1 AS SingerId, 1 AS VenueId,
                DATE "2024-12-25" AS EventDate,
                PENDING_COMMIT_TIMESTAMP() AS LastUpdateTime);
Query OK, 0 rows affected (1.18 sec)
timestamp:      2024-12-13T02:40:46.253698+09:00
mutation_count: 4

spanner> SELECT * FROM Performances;
+----------+---------+------------+---------+-----------------------------+
| SingerId | VenueId | EventDate  | Revenue | LastUpdateTime              |
| INT64    | INT64   | DATE       | INT64   | TIMESTAMP                   |
+----------+---------+------------+---------+-----------------------------+
| 1        | 1       | 2024-12-25 | NULL    | 2024-12-12T17:40:46.253698Z |
+----------+---------+------------+---------+-----------------------------+
1 rows in set (14.89 msecs)
timestamp:            2024-12-13T02:40:57.15133+09:00
cpu time:             13.35 msecs
rows scanned:         1 rows
deleted rows scanned: 0 rows
optimizer version:    7
optimizer statistics: auto_20241212_12_01_00UTC

Delete all rows in MutationTest table.

MUTATE MutationTest DELETE ALL;

Delete rows with PK (1) in MutationTest table.

MUTATE MutationTest DELETE (1);

Delete a single row with PK (1, "foo") in MutationTest2 table.

MUTATE MutationTest2 DELETE (1, "foo");

Delete two rows with PK (1, "foo", 2, "bar") in MutationTest2 table.

MUTATE MutationTest2 DELETE [(1, "foo"), (2, "bar")];

Delete rows between (1, "a") <= PK < (1, "n") in MutationTest2 table

MUTATE MutationTest2 DELETE KEY_RANGE(start_closed => (1, "a"), end_open => (1, "n"));

Query parameter support

Many Cloud Spanner clients don't support query parameters.

If you do not modify the query, you will not be able to execute queries that contain query parameters, and you will not be able to view the query plan for queries with parameter types STRUCT or ARRAY.

spanner-mycli solves this problem by supporting query parameters.

You can define query parameters using command line option --param or SET commands. It supports type notation or literal value notation in GoogleSQL.

Note: They are supported on the best effort basis, and type conversions are not supported.

Query parameters definition using --param option

$ spanner-mycli \
                --param='array_type=ARRAY<STRUCT<FirstName STRING, LastName STRING>>' \
                --param='array_value=[STRUCT("Marc" AS FirstName, "Richards" AS LastName), ("Catalina", "Smith")]'

You can see defined query parameters using SHOW PARAMS; command.

> SHOW PARAMS;
+-------------+------------+-------------------------------------------------------------------+
| Param_Name  | Param_Kind | Param_Value                                                       |
+-------------+------------+-------------------------------------------------------------------+
| array_value | VALUE      | [STRUCT("Marc" AS FirstName, "Richards" AS LastName), ("Catalina", "Smith")] |
| array_type  | TYPE       | ARRAY<STRUCT<FirstName STRING, LastName STRING>>                  |
+-------------+------------+-------------------------------------------------------------------+
Empty set (0.00 sec)

You can use value query parameters in any statement.

> SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_value);
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
+----------+-----------+----------+------------+------------+
2 rows in set (7.8 msecs)

You can use type query parameters only in EXPLAIN or DESCRIBE without value.

> EXPLAIN SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_type);
+-----+----------------------------------------------------------------------------------+
| ID  | Query_Execution_Plan                                                             |
+-----+----------------------------------------------------------------------------------+
|   0 | Distributed Cross Apply                                                          |
|   1 | +- [Input] Create Batch                                                          |
|   2 | |  +- Compute Struct                                                             |
|   3 | |     +- Hash Aggregate                                                          |
|   4 | |        +- Compute                                                              |
|   5 | |           +- Array Unnest                                                      |
|  10 | |              +- [Scalar] Array Subquery                                        |
|  11 | |                 +- Array Unnest                                                |
|  35 | +- [Map] Serialize Result                                                        |
|  36 |    +- Cross Apply                                                                |
|  37 |       +- [Input] Batch Scan (Batch: $v14, scan_method: Scalar)                   |
|  40 |       +- [Map] Local Distributed Union                                           |
| *41 |          +- Filter Scan (seekable_key_size: 0)                                   |
|  42 |             +- Table Scan (Full scan: true, Table: Singers, scan_method: Scalar) |
+-----+----------------------------------------------------------------------------------+
Predicates(identified by ID):
 41: Residual Condition: (($FirstName = $batched_v8) AND ($LastName = $batched_v9))

14 rows in set (0.18 sec)

> DESCRIBE SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_type);
+-------------+-------------+
| Column_Name | Column_Type |
+-------------+-------------+
| SingerId    | INT64       |
| FirstName   | STRING      |
| LastName    | STRING      |
| SingerInfo  | BYTES       |
| BirthDate   | DATE        |
+-------------+-------------+
5 rows in set (0.17 sec)

Interactive definition of query parameters using SET commands

You can define type query parameters using SET PARAM param_name type; command.

> SET PARAM string_type STRING;
Empty set (0.00 sec)

> SHOW PARAMS;
+-------------+------------+-------------+
| Param_Name  | Param_Kind | Param_Value |
+-------------+------------+-------------+
| string_type | TYPE       | STRING      |
+-------------+------------+-------------+
Empty set (0.00 sec)

You can define type query parameters using SET PARAM param_name = value; command.

> SET PARAM bytes_value = b"foo";
Empty set (0.00 sec)

> SHOW PARAMS;
+-------------+------------+-------------+
| Param_Name  | Param_Kind | Param_Value |
+-------------+------------+-------------+
| bytes_value | VALUE      | B"foo"      |
+-------------+------------+-------------+
Empty set (0.00 sec)

Partition Queries

spanner-mycli have some partition queries functionality.

Test root-partitionable

You can test whether the query is root-partitionable using TRY PARTITIONED QUERY command.

spanner> TRY PARTITIONED QUERY SELECT * FROM Singers;
+--------------------+
| Root_Partitionable |
+--------------------+
| TRUE               |
+--------------------+
1 rows in set (0.78 sec)

spanner> TRY PARTITIONED QUERY SELECT * FROM Singers ORDER BY SingerId;
ERROR: query can't be a partition query: rpc error: code = InvalidArgument desc = Query is not root partitionable since it does not have a DistributedUnion at the root. Please check the conditions for a query to be root-partitionable.
error details: name = Help desc = Conditions for a query to be root-partitionable. url = https://cloud.google.com/spanner/docs/reads#read_data_in_parallel

Run partitioned query (EXPERIMENTAL)

You can execute partitioned query using RUN PARTITIONED QUERY command.

spanner> RUN PARTITIONED QUERY SELECT * FROM Singers;
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 3        | Alice     | Trentor  | NULL       | 1991-10-02 |
| 4        | Lea       | Martin   | NULL       | 1991-11-09 |
| 5        | David     | Lomond   | NULL       | 1977-01-29 |
+----------+-----------+----------+------------+------------+
5 rows in set from 3 partitions (1.40 sec)

Or you can use SET AUTO_PARTITION_MODE.

spanner> SET AUTO_PARTITION_MODE = TRUE;

Empty set (0.00 sec)

spanner> SELECT * FROM Singers;
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 3        | Alice     | Trentor  | NULL       | 1991-10-02 |
| 4        | Lea       | Martin   | NULL       | 1991-11-09 |
| 5        | David     | Lomond   | NULL       | 1977-01-29 |
+----------+-----------+----------+------------+------------+
5 rows in set from 3 partitions (1.40 sec)

Note: Any stats are not available in partitioned query.

You can set session-level default isolation level and transaction-level isolation level.

spanner> SET DEFAULT_ISOLATION_LEVEL = "REPEATABLE_READ";
spanner> BEGIN ISOLATION LEVEL REPEATABLE READ;

You can enable Data Boost using DATA_BOOST_ENABLED.

spanner> SET DATA_BOOST_ENABLED = TRUE;

In default, the number of worker goroutines for partitioned query is the value of GOMAXPROCS.

You can change it using MAX_PARTITIONED_PARALLELISM.

spanner> SET MAX_PARTITIONED_PARALLELISM = 1;

Note: Partitioned queries do not support streaming output in the current implementation.

Show partition tokens.

You can show partition tokens using PARTITION command.

Note: spanner-mycli does not clean up batch read-only transactions, which may prevent resources from being freed until they time out.

spanner> PARTITION SELECT * FROM Singers;
+-------------------------------------------------------------------------------------------------+
| Partition_Token                                                                                 |
+-------------------------------------------------------------------------------------------------+
| QUw0MGxyRjJDZENocXc0TkZnR3NxVHN1QnFCMy1yWkxWZmlIdFhhc2U4T2lWOGJRVFhJRkgydU1URmZUb2dBLXVvZE1O... |
| QUw0MGxyRzhPdmZUR04xclFQVTJKQXlVMEJjZFBUWTV3NUFsT2x0VGxTNHZWSExsejQweXNUWUFtUXd5ZjBzWEhmQ0Fa... |
| QUw0MGxyR3paSk96WUNqdjFsQW9tc2UwOFFoNlA4SzhHUzNWQVltNzVlRHZxdjZpUmFVSFN2UmtBanozc0hEaE9Iem9x... |
+-------------------------------------------------------------------------------------------------+
3 rows in set (0.65 sec)

GenAI support

The GEMINI statement supports both Gemini Enterprise Agent Platform (the Vertex AI backend) and the Gemini API. Gemini Enterprise is the default backend, uses Application Default Credentials, and uses the connected Spanner project (CLI_PROJECT) unless CLI_VERTEXAI_PROJECT or vertexai_project overrides it.

vertexai_project = "example-project"

To use the Gemini API instead, set GEMINI_API_KEY or GOOGLE_API_KEY in the environment and select the backend in spanner-mycli. When both variables are set, the Google Gen AI SDK uses GOOGLE_API_KEY:

SET CLI_GENAI_BACKEND = "GEMINI_API";

The canonical backend values are GEMINI_ENTERPRISE and GEMINI_API. VERTEX_AI remains accepted as an alias for GEMINI_ENTERPRISE.

CLI_VERTEXAI_MODEL selects the model for either backend and defaults to gemini-3.7-flash. CLI_GENAI_THINKING_LEVEL accepts UNSPECIFIED, MINIMAL, LOW, MEDIUM, or HIGH. Its default is UNSPECIFIED, which omits the thinking configuration and lets the selected model use its own default.

The GEMINI statement sends your prompt, the connected database DDL, and its proto descriptors to the selected Google Gen AI service. Review your organization's data-handling policy before using it with sensitive schemas.

Built-in Spanner reference docs are always available. Dynamic documentation lookup via the Developer Knowledge API uses DEVELOPERKNOWLEDGE_API_KEY, or falls back to GOOGLE_API_KEY. GEMINI_API_KEY is not used for Developer Knowledge lookup.

The generated query is automatically filled in the prompt.

spanner> GEMINI "Generate query to show all table with table_schema concat by dot if table_schema is not empty string.";
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Column              | Value                                                                                                                                                                        |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| text                | SELECT IF(table_schema != '', CONCAT(table_schema, '.', table_name), table_name) AS table_name_with_schema FROM INFORMATION_SCHEMA.TABLES;                                   |
| semanticDescription | This query retrieves all tables and their schemas, concatenating the schema name with the table name if a schema exists, effectively listing all tables with their schema na |
|                     | mes when applicable.                                                                                                                                                         |
| syntaxDescription   | The query selects from the INFORMATION_SCHEMA.TABLES view. It uses IF() to check if table_schema is not empty, and if so, concatenates table_schema and table_name with a do |
|                     | t ('.'). Otherwise, it just returns the table_name. The result is aliased as table_name_with_schema.                                                                         |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Empty set (5.72 sec)

spanner> SELECT IF(table_schema != '', CONCAT(table_schema, '.', table_name), table_name) AS table_name_with_schema FROM INFORMATION_SCHEMA.TABLES;

BigQuery support

spanner-mycli can execute BigQuery SQL with the BIGQUERY client-side statement. Configure the target project with CLI_BIGQUERY_PROJECT (defaults to CLI_PROJECT when empty). Optional job settings are available via CLI_BIGQUERY_LOCATION and CLI_BIGQUERY_MAX_BYTES_BILLED. READONLY sessions allow BIGQUERY scripts only when every statement is a query (SELECT, WITH, or FROM-pipe / parenthesized query); mixed or unrecognized payloads are rejected locally before a BigQuery job is created.

spanner> SET CLI_BIGQUERY_PROJECT = 'my-gcp-project';
Empty set (0.00 sec)

spanner> BIGQUERY SELECT 1 AS n;
+-----+
| n   |
| INT |
+-----+
| 1   |
+-----+
1 rows in set (0.42 sec)

Markdown output

spanner-mycli can emit input and output in Markdown.

TODO: More description

$ spanner-mycli -v --set 'CLI_FORMAT=TABLE_DETAIL_COMMENT' --set 'CLI_MARKDOWN_CODEBLOCK=TRUE' --set 'CLI_ECHO_INPUT=TRUE'

spanner> GRAPH FinGraph
      -> MATCH (n:Account)
      -> RETURN LABELS(n) AS labels, PROPERTY_NAMES(n) AS props, n.id;
```sql
GRAPH FinGraph
MATCH (n:Account)
RETURN LABELS(n) AS labels, PROPERTY_NAMES(n) AS props, n.id;
/*---------------+------------------------------------------+-------+
 | labels        | props                                    | id    |
 | ARRAY<STRING> | ARRAY<STRING>                            | INT64 |
 +---------------+------------------------------------------+-------+
 | [Account]     | [create_time, id, is_blocked, nick_name] | 7     |
 | [Account]     | [create_time, id, is_blocked, nick_name] | 16    |
 | [Account]     | [create_time, id, is_blocked, nick_name] | 20    |
 +---------------+------------------------------------------+-------+
3 rows in set (6.26 msecs)
timestamp:            2025-02-09T23:28:26.088524+09:00
cpu time:             4.45 msecs
rows scanned:         3 rows
deleted rows scanned: 0 rows
optimizer version:    7
optimizer statistics: auto_20250207_09_19_31UTC
*/
```

Cassandra interface support

spanner-mycli can execute CQL statements of Cassandra interface with CQL client side statement.

# Spanner Cassandra interface doesn't support CQL DDL, so you need to write GoogleSQL DDL
spanner> CREATE TABLE users (
           id INT64 OPTIONS (cassandra_type = 'int'),
           active BOOL OPTIONS (cassandra_type = 'boolean'),
           username STRING(MAX) OPTIONS (cassandra_type = 'text'),
         ) PRIMARY KEY (id);
Query OK, 0 rows affected (7.88 sec)

spanner> CQL INSERT INTO users (id, active, username) VALUES (1, TRUE, 'John Doe');
Empty set (0.54 sec)

spanner> CQL SELECT * FROM users WHERE id = 1;
+-----+---------+----------+
| id  | active  | username |
| int | boolean | varchar  |
+-----+---------+----------+
| 1   | true    | John Doe |
+-----+---------+----------+
1 rows in set (0.55 sec)

How to develop

Run unit tests.

$ make test

Note: It requires Docker because integration tests using testcontainers.

Or run test except integration tests.

$ make test-quick

Incompatibilities from spanner-cli

In principle, spanner-mycli accepts the same input as spanner-cli, but some compatibility is intentionally not maintained.

• BEGIN RW TAG <tag> and BEGIN RO TAG <tag> are no longer supported.
  • Use SET TRANSACTION_TAG = "<tag>" and SET STATEMENT_TAG = "<tag>".
  • Rationale: spanner-cli are broken. https://github.com/cloudspannerecosystem/spanner-cli/issues/132
• <rfc3339_timestamp> in BEGIN RO <rfc3339_timestamp> must be quoted like BEGIN RO "2025-01-01T00:00:00Z".
  • Rationale: Raw RFC3339 timestamp is not compatible with GoogleSQL lexical structure and memefish.
• \G is no longer supported.
  • Use SET CLI_FORMAT = "VERTICAL".
  • Rationale: \G is not compatible with GoogleSQL lexical structure and memefish.
• \ is no longer used for prompt expansions.
  • Use % instead.
  • Rationale: % works consistently in prompts and TOML configuration files.
• The default format of EXPLAIN and EXPLAIN ANALYZE has been changed.

Tab character handling

spanner-mycli expands tab characters to whitespaces. Tab width can be configured using CLI_TAB_WIDTH system variable. (default: 4)

spanner> SET CLI_TAB_WIDTH = 2;

Empty set (0.00 sec)

spanner> SELECT "a\tb\tc\td\te\tf\t\n"||
      ->        "あ\tい\tう\tえ\tお\tか\t\n"||
      ->        "abc\tdef\tghi\tjkl\tmno\tpqr\t\n"||
      ->        "あい\tうえ\tおか\tきく\tけこ\tさし\t" AS s;
+--------------------------------------+
| s                                    |
+--------------------------------------+
| a b c d e f                          |
| あ  い  う  え  お  か               |
| abc def ghi jkl mno pqr              |
| あい  うえ  おか  きく  けこ  さし   |
+--------------------------------------+
1 rows in set (2.81 msecs)

spanner> SET CLI_TAB_WIDTH = 4;

Empty set (0.00 sec)

spanner> SELECT "a\tb\tc\td\te\tf\t\n"||
      ->        "あ\tい\tう\tえ\tお\tか\t\n"||
      ->        "abc\tdef\tghi\tjkl\tmno\tpqr\t\n"||
      ->        "あい\tうえ\tおか\tきく\tけこ\tさし\t" AS s;
+--------------------------------------------------+
| s                                                |
+--------------------------------------------------+
| a   b   c   d   e   f                            |
| あ  い  う  え  お  か                           |
| abc def ghi jkl mno pqr                          |
| あい    うえ    おか    きく    けこ    さし     |
+--------------------------------------------------+
1 rows in set (2.31 msecs)

spanner> SET CLI_TAB_WIDTH = 8;

Empty set (0.00 sec)

spanner> SELECT "a\tb\tc\td\te\tf\t\n"||
      ->        "あ\tい\tう\tえ\tお\tか\t\n"||
      ->        "abc\tdef\tghi\tjkl\tmno\tpqr\t\n"||
      ->        "あい\tうえ\tおか\tきく\tけこ\tさし\t" AS s;
+--------------------------------------------------+
| s                                                |
+--------------------------------------------------+
| a       b       c       d       e       f        |
| あ      い      う      え      お      か       |
| abc     def     ghi     jkl     mno     pqr      |
| あい    うえ    おか    きく    けこ    さし     |
+--------------------------------------------------+
1 rows in set (2.76 msecs)