My personal fork of spanner-cli, interactive command line tool for Cloud Spanner.
spanner-mycli is an interactive command line tool for Google Cloud Spanner.
You can control your Spanner databases with idiomatic SQL commands.
spanner-mycli was forked from spanner-cli v0.10.6 and restarted its version numbering from v0.1.0. There are differences between spanner-mycli and spanner-cli that include not only functionality but also philosophical differences.
- Advanced query plan features for constrained display environments and comprehensive analysis. See docs/query_plan.md for details.
- Configurable
EXPLAIN ANALYZEwith customizable execution stats columns usingCLI_ANALYZE_COLUMNSand inline stats usingCLI_INLINE_STATS - Configurable query plan appendix presets and sections with
EXPLAIN PRINT=<preset-or-sections>andCLI_EXPLAIN_PRINT_SECTIONS - Query plan investigation with
EXPLAIN [ANALYZE] LAST QUERYfor re-rendering without re-execution andSHOW PLAN NODEfor inspecting specific plan nodes - Compact format (
FORMAT=COMPACT) and wrapped plans (WIDTH=<width>) with hanging indent for limited display spaces like narrow terminals, code blocks, and technical documentation - Query plan linter (EARLY EXPERIMENTAL) using
CLI_LINT_PLANsystem variable for heuristic query plan analysis - Query profiles (EARLY EXPERIMENTAL) for rendering sampled query plans using
SHOW QUERY PROFILESandSHOW QUERY PROFILE
- Configurable
- Respects my minor use cases
- Protocol Buffers support as
SHOW LOCAL PROTO,SHOW REMOTE PROTO,SYNC PROTO BUNDLEstatement - Can use embedded runtime backends (
--embedded-emulator,--embedded-omni) - Support query parameters
- Test root-partitionable with
TRY PARTITIONED QUERY <sql>command - Experimental Partitioned Query and Data Boost support.
- GenAI support(
GEMINIstatement). - BigQuery support (
BIGQUERYstatement). - Interactive DDL batching
- Async DDL execution support (
--asyncflag andCLI_ASYNC_DDLsystem variable) - Experimental Cassandra interface support as
CQL <cql>statement. - Support split points.
- Run as MCP (Model Context Protocol) server (EXPERIMENTAL,
--mcp). See Model Context Protocol for more information.- Statement calls are serialized. Calls cancelled while waiting do not execute; cancellation after execution starts does not guarantee rollback.
- Protocol Buffers support as
- Respects training and verification use-cases.
- gRPC logging(
--log-grpc) - Support mutations
- gRPC logging(
- Respects batch use cases as well as interactive use cases
- Breaking change from spanner-cli: Default output format for batch mode is
TABLE(same as interactive mode), notTAB. Use--format=TABfor tab-separated output.
- Breaking change from spanner-cli: Default output format for batch mode is
- More
gcloud spanner databases execute-sqlcompatibilities- Support compatible flags (
--sql,--query-mode,--strong,--read-timestamp,--timeout)
- Support compatible flags (
- More
gcloud spanner databases ddl updatecompatibilities- Support
--proto-descriptor-fileflag
- Support
- More Google Cloud Spanner CLI (
gcloud alpha spanner cli) compatibilities- Support
--skip-column-namesflag to suppress column headers in output (useful for scripting) - Support
--hostand--portflags as first-class options - Support
--deployment-endpointas an alias for--endpoint - Support
--html,--xml, and--csvoutput format options with proper escaping (security-enhanced compared to reference implementation) - Support
--format=jsonlfor type-aware JSON Lines output: INT64/ENUM as numbers, BOOL as booleans, ARRAY as JSON arrays, STRUCT as JSON objects, NULL as null
- Support
- Generalized concepts to extend without a lot of original syntax
- Generalized system variables concept inspired by Spanner JDBC properties
SET <name> = <value>statementSET LOCAL <name> = <value>statement (transaction-scoped; the value reverts when the transaction ends)SHOW VARIABLESstatementSHOW VARIABLE <name>statement--set <name>=<value>flag
- Generalized system variables concept inspired by Spanner JDBC properties
- Improved interactive experience
- Use
hymkor/go-multiline-nyinstead ofchzyer/readline"- Native multi-line editing
- Improved prompt
- Use
%for prompt expansion, instead of\to avoid escaping - Allow newlines in prompt using
%n - System variables expansion
- Prompt2 with margin and waiting status
- Use
- Autowrap and auto adjust column width to fit within terminal width (overridable with
CLI_FIXED_WIDTH) whenCLI_AUTOWRAP = TRUE. Pluggable width strategies viaCLI_WIDTH_STRATEGY(GREEDY_FREQUENCY,PROPORTIONAL,MARGINAL_COST). - Pager support when
CLI_USE_PAGER = TRUE - Progress bar of DDL execution.
- Syntax highlight when
CLI_ENABLE_HIGHLIGHT = TRUE - Fuzzy finder (default
Ctrl+T, configurable viaCLI_FUZZY_FINDER_KEY) powered by fzf for databases, tables, system variables, database roles, operations, and statement names
- Use
- Utilize other libraries
- Dogfooding
cloudspannerecosystem/memefish- Spin out memefish logic as
apstndb/gsqlutils.
- Spin out memefish logic as
- Utilize
apstndb/spantypeandapstndb/spanvalue
- Dogfooding
Do not use this tool for production databases as the tool is experimental/alpha quality forever.
This software will not have a stable release. In other words, v1.0.0 will never be released. It will be operated as a kind of ZeroVer.
v0.X.Y will be operated as follows:
- The initial release version is v0.1.0, forked from spanner-cli v0.10.6.
- The patch version Y will be incremented for changes that include only bug fixes.
- The minor version X will always be incremented when there are new features or changes related to compatibility.
- As a general rule, unreleased updates to the main branch will be released within one week.
Download pre-built binaries from GitHub Releases.
Install Go and run the following command.
# Requires Go 1.26+
go install github.com/apstndb/spanner-mycli@latest
Release assets include spanner-mycli-slim, which leaves out the optional
GEMINI/LLM, BigQuery, and Cassandra-interface CQL statement families. Use it
when those capabilities are not needed. Omitted statements, --vertexai-*
flags, and related system variables or TOML keys are rejected rather than
ignored. Core MCP remains. Slim is not a sandbox or a smaller module graph;
see Slim binary.
Build the same variant from source with:
CGO_ENABLED=0 go build -o spanner-mycli-slim ./cmd/spanner-mycli-slimFor a reproducible full-versus-slim relative size comparison using identical stripping and target settings, see Slim binary.
Use the container image from GitHub Container Registry.
https://github.com/apstndb/spanner-mycli/pkgs/container/spanner-mycli
Usage: spanner-mycli [flags]
Flags:
-p, --project=STRING (required) GCP Project ID ($SPANNER_PROJECT_ID).
-i, --instance=STRING (required) Cloud Spanner Instance ID ($SPANNER_INSTANCE_ID)
-d, --database=STRING Cloud Spanner Database ID. Optional when --detached is used
($SPANNER_DATABASE_ID).
--detached Start in detached mode, ignoring database env var/flag
-e, --execute=STRING Execute SQL statement and quit. --sql is an alias.
-f, --file=STRING Execute SQL statement from file and quit. --source is an alias.
-t, --table Display output in table format for batch mode.
--html Display output in HTML format.
--xml Display output in XML format.
--csv Display output in CSV format.
--format=STRING Output format (table, tab, tsv, vertical, html, xml, csv, jsonl)
-v, --verbose Display verbose output.
--credential=STRING Use the specific credential file
--prompt=PROMPT Set the prompt to the specified format (default: "spanner%t> ")
--prompt2=PROMPT2 Set the prompt2 to the specified format (default: "%P%R> ")
--history=HISTORY Set the history file to the specified path (default:
~/.spanner_mycli_history)
--priority=STRING Set default request priority (HIGH|MEDIUM|LOW)
--role=STRING Use the specific database role. --database-role is an alias.
--endpoint=STRING Set the Spanner API endpoint (host:port)
--host=STRING Host on which Spanner server is located
--port=INT Port number for Spanner connection
--directed-read=STRING Directed read option (replica_location:replica_type). The replica_type is
optional and either READ_ONLY or READ_WRITE
--set=KEY=VALUE Set system variables e.g. --set=name1=value1 --set=name2=value2
--param=KEY=VALUE Set query parameters, it can be literal or type(EXPLAIN/DESCRIBE only)
e.g. --param="p1='string_value'" --param=p2=FLOAT64
--proto-descriptor-file=STRING Path of a file that contains a protobuf-serialized
google.protobuf.FileDescriptorSet message.
--insecure Skip TLS verification and permit plaintext gRPC. --skip-tls-verify is an
alias.
--embedded-emulator Use embedded Cloud Spanner Emulator. --project, --instance, --database,
--endpoint, --insecure will be automatically configured.
--embedded-omni Use embedded experimental Spanner Omni. --project, --instance,
--database, --endpoint, --insecure will be automatically configured.
--emulator-image=STRING container image for embedded runtime (--embedded-emulator or
--embedded-omni)
--emulator-platform=STRING Container platform (e.g. linux/amd64, linux/arm64) for embedded runtime
--sample-database=STRING Initialize embedded runtime with built-in sample (e.g. fingraph,
singers, banking) or path to a metadata file (.json, .yaml, .yml).
Requires --embedded-emulator or --embedded-omni. Cannot be combined with
--detached.
--list-samples List available sample databases and exit
--output-template=STRING Filepath of output template. (EXPERIMENTAL)
--log-level=STRING Set CLI log level (DEBUG, INFO, WARN, ERROR). INFO and DEBUG include
embedded runtime container lifecycle logs. SQL SET CLI_LOG_LEVEL does not
change those container logs.
--log-grpc Show gRPC logs
--query-mode=QUERY-MODE Mode in which the query must be processed. Allowed values: NORMAL, PLAN,
PROFILE, WITH_STATS, WITH_PLAN_AND_STATS.
--strong Perform a strong query.
--read-timestamp=STRING Perform a query at the given timestamp.
--database-dialect=DATABASE-DIALECT The SQL dialect of the Cloud Spanner Database. Allowed values:
POSTGRESQL, GOOGLE_STANDARD_SQL, DATABASE_DIALECT_UNSPECIFIED. Omit this
flag to leave it unset.
--impersonate-service-account=STRING Impersonate service account email
-h, --help Show this help message and exit.
--version Show version string.
--enable-partitioned-dml Partitioned DML as default (AUTOCOMMIT_DML_MODE=PARTITIONED_NON_ATOMIC)
--timeout=STRING Statement timeout (e.g., '10s', '5m', '1h'). Omit for 10m on ordinary
statements and 24h on partitioned DML.
--async Return immediately, without waiting for the operation in progress to
complete
--try-partition-query Test whether the query can be executed as partition query without
execution
--mcp Run as MCP server
--skip-system-command Do not allow system commands
--system-command=ON|OFF Enable or disable system commands (ON/OFF). Default: ON.
--tee=STRING Append a copy of output to the specified file (both screen and file)
-o, --output=STRING Redirect query/data output to file (overwrites existing file)
--skip-column-names Suppress column headers in output
--table-streaming="AUTO" Table streaming output mode: AUTO/FALSE buffer table output, TRUE streams
table output. Non-table formats always stream.
--color="AUTO" ANSI styling in output: AUTO (styled if TTY), TRUE (always styled),
FALSE (never styled)
-q, --quiet Suppress result lines like 'rows in set' for clean output
--vertexai-project=STRING Gemini Enterprise project override
--vertexai-model=VERTEXAI-MODEL Gemini model (default: gemini-3.7-flash)
--vertexai-location=VERTEXAI-LOCATION Gemini Enterprise location (default: global)
Unless you specify a credential file with --credential, this tool uses Application Default Credentials as credential source to connect to Spanner databases.
Please make sure to prepare your credential by gcloud auth application-default login.
If you're running spanner-mycli in docker container on your local machine, you have to pass local credentials to the container with the following command.
docker run -it \
-e GOOGLE_APPLICATION_CREDENTIALS=/tmp/credentials.json \
-v $HOME/.config/gcloud/application_default_credentials.json:/tmp/credentials.json:ro \
spanner-mycli --help
$ docker run -it \
-v $HOME/.config/gcloud/application_default_credentials.json:/home/nonroot/.config/gcloud/application_default_credentials.json:ro \
ghcr.io/apstndb/spanner-mycli --help
$ spanner-mycli -p myproject -i myinstance -d mydb
Connected.
spanner> CREATE TABLE users (
-> id INT64 NOT NULL,
-> name STRING(16) NOT NULL,
-> active BOOL NOT NULL
-> ) PRIMARY KEY (id);
Query OK, 0 rows affected (30.60 sec)
spanner> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| users |
+----------------+
1 rows in set (18.66 msecs)
spanner> INSERT INTO users (id, name, active) VALUES (1, "foo", true), (2, "bar", false);
Query OK, 2 rows affected (5.08 sec)
spanner> SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1 | foo | true |
| 2 | bar | false |
+----+------+--------+
2 rows in set (3.09 msecs)
spanner> BEGIN;
Query OK, 0 rows affected (0.02 sec)
spanner(rw txn)> DELETE FROM users WHERE active = false;
Query OK, 1 rows affected (0.61 sec)
spanner(rw txn)> COMMIT;
Query OK, 0 rows affected (0.20 sec)
spanner> SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1 | foo | true |
+----+------+--------+
1 rows in set (2.58 msecs)
spanner> DROP TABLE users;
Query OK, 0 rows affected (25.20 sec)
spanner> SHOW TABLES;
Empty set (2.02 msecs)
spanner> EXIT;
Bye
By passing SQL from standard input, spanner-mycli runs in batch mode.
$ echo 'SELECT * FROM users;' | spanner-mycli -p myproject -i myinstance -d mydb
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1 | foo | true |
| 2 | bar | false |
+----+------+--------+
You can also pass SQL with command line option -e.
$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;'
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1 | foo | true |
| 2 | bar | false |
+----+------+--------+
For tab-separated output (useful for scripting), use --format=TAB:
$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;' --format=TAB
id name active
1 foo true
2 bar false
TAB writes values as-is, so values containing tabs or newlines break the row/column structure.
Use --format=TSV for the same layout with lossless escaping: tab, newline, carriage return, and
backslash inside values are escaped as \t, \n, \r, and \\, guaranteeing one row per line
and one field per tab-separated column.
With --skip-column-names option, column headers are suppressed in output (useful for scripting).
$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;' --skip-column-names
1 foo true
2 bar false
# With table format
$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;' -t --skip-column-names
+---+-----+-------+
| 1 | foo | true |
| 2 | bar | false |
+---+-----+-------+
The --timeout flag allows you to set a timeout for SQL statement execution, compatible with gcloud spanner databases execute-sql behavior.
# Set 30 second timeout for queries
$ spanner-mycli --timeout 30s -p myproject -i myinstance -d mydb -e 'SELECT * FROM large_table;'
# Set 5 minute timeout for partitioned DML
$ spanner-mycli --timeout 5m --enable-partitioned-dml -p myproject -i myinstance -d mydb -e 'UPDATE large_table SET status = "active";'
# Use default timeout (10 minutes for queries, 24 hours for partitioned DML)
$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;'You can also configure timeout interactively using the STATEMENT_TIMEOUT system variable:
spanner> SET STATEMENT_TIMEOUT = '2m';
Query OK, 0 rows affected (0.00 sec)
spanner> SHOW VARIABLE STATEMENT_TIMEOUT;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| STATEMENT_TIMEOUT | 2m0s |
+-------------------+-------+
1 rows in set (0.00 sec)spanner-mycli provides two ways to capture output to files:
- Tee functionality: Append output to a file while still displaying it on the console (like the Unix
teecommand) - Output redirect: Send query and result output to a file while prompts, progress, and errors stay on their normal screen streams
Both features are available through command-line options and interactive meta-commands.
# Log all query results to a file while displaying on screen
$ spanner-mycli --tee output.log -p myproject -i myinstance -d mydb
# In batch mode with --tee
$ spanner-mycli --tee queries.log -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;'spanner> \T session.log -- Start tee to session.log (both screen and file)
spanner> SELECT * FROM users; -- This query and result will be logged and displayed
spanner> \t -- Stop tee
spanner> SELECT * FROM keys; -- This won't be logged (screen only)
spanner> \T another.log -- Start tee to a different file# Redirect query/data output to file (overwrites existing file)
$ spanner-mycli --output backup.sql -p myproject -i myinstance -d mydb -e 'DUMP DATABASE;'
# Useful for clean SQL exports while leaving progress and errors on screen
$ spanner-mycli --output export.sql -p myproject -i myinstance -d mydbspanner> \o backup.sql -- Redirect query/data output to file (overwrite if it exists)
spanner> DUMP DATABASE; -- SQL goes to file, progress shows on screen
spanner> \o -- Disable redirect (return to screen output)
spanner> SELECT * FROM users; -- This shows on screen only
-- Alternative using \O (symmetric with \T/\t pattern)
spanner> \o export.sql -- Redirect query/data output to file (overwrite if it exists)
spanner> SELECT * FROM keys; -- Output goes to file only
spanner> \O -- Disable redirect using \ODUMP SCHEMA and DUMP DATABASE move inline foreign keys that reference
tables created later into ALTER TABLE ... ADD statements after the other DDL,
before any data. This allows an empty cyclic foreign-key schema to be restored
even when Spanner returns its constraints inside CREATE TABLE. If an affected
table definition cannot be parsed or safely rewritten, the dump fails before
emitting SQL. DUMP TABLES does not export or modify DDL. Populated cycles
involving enforced foreign keys or interleave relationships are rejected by
default. The opt-in CLI_DUMP_CYCLIC_MODE = 'MUTATE' mode emits one mutation
transaction per populated cyclic table group; it does not make the whole restore
atomic or predict service commit limits. See cyclic DUMP restoration
for usage, local buffering limits, and target prerequisites.
The tee file will contain:
- Query results and output
- SQL statements when
CLI_ECHO_INPUTis enabled - Error messages and warnings
- Result metadata (row counts, execution times)
The tee file will NOT contain:
- Interactive prompts (e.g.,
spanner>) - Progress indicators (e.g., DDL progress bars)
- Confirmation dialogs (e.g., DROP DATABASE confirmations)
- Readline input display
- Dynamic control with meta-commands: Start and stop logging during the session
- File switching: Starting a new tee (with
\T) automatically closes the previous file - Quoted filenames: Supports filenames with spaces:
\T "my output.log" - Combine with --tee: Start with
--tee, use\tto pause, and\Tto resume
- Tee files use append mode;
--outputand\ooverwrite existing content - Files are created if they don't exist
- Only regular files are supported (not directories, FIFOs, or device files)
- Tee file write failures warn once and leave console output running
- File-only output preserves write errors instead of treating the failed file as optional. A failed export can leave a partial file; do not replay it as a complete dump.
- Result-display failures, including summaries and query-plan appendices, report an error. This does not undo a statement that already completed successfully.
# Example: Logging a session with CLI_ECHO_INPUT
$ spanner-mycli --tee session.log -p myproject -i myinstance -d mydb
Connected.
spanner> SET CLI_ECHO_INPUT = TRUE;
Query OK, 0 rows affected (0.00 sec)
spanner> SELECT 1 AS test;
# In session.log:
# SELECT 1 AS test;
# +------+
# | test |
# +------+
# | 1 |
# +------+
# 1 rows in set (2.41 msecs)Warning
The Cloud Spanner Emulator does not return query plans in PLAN mode (the field is absent in the API response). While the API itself succeeds and returns other metadata like row types, EXPLAIN will error in spanner-mycli as it requires query plan data to produce meaningful output. See emulator limitations for details.
You can see query plan without query execution using the EXPLAIN client side statement.
For advanced query plan features and configuration options, see docs/query_plan.md.
spanner> EXPLAIN
SELECT SingerId, FirstName FROM Singers WHERE FirstName LIKE "A%";
+----+-------------------------------------------------------------------------------------------+
| ID | Query_Execution_Plan |
+----+-------------------------------------------------------------------------------------------+
| *0 | Distributed Union <Row> (distribution_table: indexOnSingers, split_ranges_aligned: false) |
| 1 | +- Local Distributed Union <Row> |
| 2 | +- Serialize Result <Row> |
| 3 | +- Filter Scan <Row> (seekable_key_size: 1) |
| *4 | +- Index Scan <Row> (Index: indexOnSingers, scan_method: Row) |
+----+-------------------------------------------------------------------------------------------+
Predicates(identified by ID):
0: Split Range: STARTS_WITH($FirstName, 'A')
4: Seek Condition: STARTS_WITH($FirstName, 'A')
5 rows in set (0.86 sec)
Note: <Row> or <Batch> after the operator name mean execution method of the operator node.
Warning
The Cloud Spanner Emulator does not return query plans in PROFILE mode (the field is absent in the API response). While the API itself succeeds and returns other metadata like row types, EXPLAIN ANALYZE will error in spanner-mycli as it requires query plan data to produce meaningful output. See emulator limitations for details.
You can see query plan and execution profile using the EXPLAIN ANALYZE client side statement.
You should know that it requires executing the query.
For advanced query plan features and configuration options, see docs/query_plan.md.
spanner> EXPLAIN ANALYZE
SELECT SingerId, FirstName FROM Singers WHERE FirstName LIKE "A%";
+----+-------------------------------------------------------------------------------------------+---------------+------------+---------------+
| ID | Query_Execution_Plan | Rows_Returned | Executions | Total_Latency |
+----+-------------------------------------------------------------------------------------------+---------------+------------+---------------+
| *0 | Distributed Union <Row> (distribution_table: indexOnSingers, split_ranges_aligned: false) | 235 | 1 | 1.17 msecs |
| 1 | +- Local Distributed Union <Row> | 235 | 1 | 1.12 msecs |
| 2 | +- Serialize Result <Row> | 235 | 1 | 1.1 msecs |
| 3 | +- Filter Scan <Row> (seekable_key_size: 1) | 235 | 1 | 1.05 msecs |
| *4 | +- Index Scan <Row> (Index: indexOnSingers, scan_method: Row) | 235 | 1 | 1.02 msecs |
+----+-------------------------------------------------------------------------------------------+---------------+------------+---------------+
Predicates(identified by ID):
0: Split Range: STARTS_WITH($FirstName, 'A')
4: Seek Condition: STARTS_WITH($FirstName, 'A')
5 rows in set (4.49 msecs)
timestamp: 2025-04-16T01:07:59.137819+09:00
cpu time: 3.73 msecs
rows scanned: 235 rows
deleted rows scanned: 0 rows
optimizer version: 7
optimizer statistics: auto_20250413_15_34_23UTC
spanner-mycli now supports directed reads, a feature that allows you to read data from a specific replica of a Spanner database.
To use directed reads with spanner-mycli, you need to specify the --directed-read flag.
The --directed-read flag takes a single argument, which is the name of the replica that you want to read from.
The replica name can be specified in one of the following formats:
<replica_location><replica_location>:<replica_type>
The <replica_location> specifies the region where the replica is located such as us-central1, asia-northeast2.
The <replica_type> specifies the type of the replica either READ_WRITE or READ_ONLY.
$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read us-central1
$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read us-central1:READ_ONLY
$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read asia-northeast2:READ_WRITE
Directed reads are only effective for single queries or queries within a read-only transaction. Please note that directed read options do not apply to queries within a read-write transaction.
Note
If you specify an incorrect region or type for directed reads, directed reads will not be enabled and your requsts won't be routed as expected. For example, in a multi-region configuration nam3, if you mistype us-east1 as us-east-1, the connection will succeed, but directed reads will not be enabled.
To perform directed reads to asia-northeast2 in a multi-region configuration asia1, you need to specify asia-northeast2 or asia-northeast2:READ_WRITE.
Since the replicas placed in asia-northeast2 are READ_WRITE replicas, directed reads will not be enabled if you specify asia-northeast2:READ_ONLY.
Please refer to the Spanner documentation to verify the valid configurations.
spanner-mycli supports all Spanner GoogleSQL and Spanner Graph statements, as well as several client-side statements.
Note: If any valid Spanner statement can't be executed, it is a bug.
In the following syntax, we use <> for a placeholder, [] for an optional keyword,
and {A|B|...} for a mutually exclusive keyword.
- The syntax is case-insensitive.
| Usage | Syntax | Note | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Switch database | USE <database> [ROLE <role>]; |
The role you set is used for accessing with fine-grained access control. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Detach from database | DETACH; |
Switch to detached mode, disconnecting from the current database. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Drop database | DROP DATABASE <database>; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| List databases | SHOW DATABASES; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Show DDL of the schema object | SHOW CREATE <type> <fqn>; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| List tables | SHOW TABLES [<schema>]; |
If schema is not provided, the default schema is used | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Show columns | SHOW COLUMNS FROM <table_fqn>; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Show indexes | SHOW INDEX FROM <table_fqn>; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| SHOW DDLs | SHOW DDLS; |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export database DDL and data as SQL statements | DUMP DATABASE; |
Exports DDL plus BASE TABLE data from the default schema and named schemas. Views and synonyms are omitted from data. Catalog, column, and row reads share one read-only transaction. Requires spanner.databases.getDdl; that admin RPC is a fresh GetDatabaseDdl call and is not timestamp-bound to the dump transaction. When the admin response includes proto descriptors, prepends SET PROTO_DESCRIPTORS before rewritten DDL. CLI_DUMP_CYCLIC_MODE defaults to REJECT for populated cyclic FK/interleave groups, including all-NULL or row-acyclic data. Opt-in MUTATE pre-encodes all cyclic groups before output, then emits one unsplit transaction per populated group. No service-quota prediction or globally atomic restore; earlier work may remain committed. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export database DDL only as SQL statements | DUMP SCHEMA; |
Exports a fresh GetDatabaseDdl response as SQL. Requires spanner.databases.getDdl. When the admin response includes proto descriptors, prepends SET PROTO_DESCRIPTORS so CREATE PROTO BUNDLE replay is self-contained. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Export specific tables as SQL statements | DUMP TABLES <table1> [, <table2>, ...]; |
Table names are [.]. Data only; no constraint changes or implicit inclusion of other tables. Invalid names are rejected before GetDatabaseDdl. Requires spanner.databases.getDdl only when a selected interleaved child has another selected BASE TABLE whose name matches the catalog parent basename. CLI_DUMP_CYCLIC_MODE defaults to REJECT; opt-in MUTATE pre-encodes selected cyclic groups and emits one unsplit transaction per populated group. Omitted parents and prerequisite target rows remain caller responsibilities. No service-quota prediction; earlier restore work may remain committed.
Meta commands are special commands that start with a backslash ( Note: Meta commands are only supported in interactive mode. They cannot be used in batch mode (with
For detailed documentation on each meta command, see docs/meta_commands.md. You can customize the prompt by Escape sequences:
Example: The default prompt is
The default prompt2 is Continuation prompts must be nonempty, whether supplied through If you set only This tool supports a TOML configuration file called Example: project = "myproject"
instance = "myinstance"
prompt = "[%p:%i:%d]%t> "
You can set request priority for command level or transaction level.
By default To set a priority for command line level, you can use To set a priority for transaction level, you can use Here are some examples for transaction-level priority. Note that transaction-level priority takes precedence over command-level priority. You can set transaction tag using This tool supports the Cloud Spanner Emulator via the $ 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 --insecureWarning 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:
spanner-mycli supports connecting to regional endpoints for improved performance and reliability. You can specify a regional endpoint using either the # 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
Note: 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. You can start spanner-mycli in detached mode using the $ 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)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*>The database connection follows this priority order:
When in detached mode, you can only execute:
Database-specific operations will fail with an error message indicating that no database is connected. This section describes some notable features of spanner-mycli, they are not appeared in original spanner-cli. 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.
After a query or
Table width calculation and wrapping account for 7-bit ANSI escape sequences already present in values or headers, independently of CLI-added type styling. SGR color/style sequences are preserved across wrapped lines. Disabling CLI styling does not sanitize escape sequences in the input; CSV, JSONL and SQL export behavior is unchanged. You can issue a batch DDL statements.
Note: You can use batch DML.
spanner-mycli can launch Cloud Spanner Emulator with empty database, powered by testcontainers. You can initialize the embedded runtime with Google's official sample databases using the # 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.yamlThe 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. spanner-mycli can also launch experimental Spanner Omni via $ spanner-mycli --embedded-omni
default:default:emulator-database
> SELECT 1
You can use You can also use This replaces
(EXPERIMENTAL) It also supports non-compiled Comma-separated inputs are merged into one descriptor graph before validation,
both for This feature is powered by bufbuild/protocompile. (EXPERIMENTAL) Note:
If
If
Illustrative input and composed SQL (not a live session transcript): When Names are set-wise per operation: repeated names in UPSERT or in DELETE keep the first occurrence. The same full name in both UPSERT and DELETE is rejected before execution. Official GoogleSQL documents Loaded proto descriptors are used for formatting You can enable multiline format. You can list schema update operations and their progress. Note: Warning Split points operations ( spanner-mycli can manage split points for pre-splitting. You can add split points using
The entry structure follows
You can show defined split points using You can drop split points using The detail lines are customizable using Go text/template.
Note: You need to know spanner-mycli utilizes memefish as:
Statement type detector behavior can be controlled by
spanner-mycli supports mutations. Mutations are buffered in read-write transaction, or immediately commit outside explicit transaction. Note: In this context, parenthesized expression and some simple literals are treated as a single field struct literal. Delete keys also accept typed Example schema Insert a single row with key( Insert or update four rows with keys( You can set commit timestamps using Delete all rows in Delete rows with PK ( Delete a single row with PK ( Delete two rows with PK ( Delete rows between 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 spanner-mycli solves this problem by supporting query parameters. You can define query parameters using command line option Note: They are supported on the best effort basis, and type conversions are not supported. You can see defined query parameters using You can use value query parameters in any statement. You can use type query parameters only in You can define type query parameters using You can define type query parameters using spanner-mycli have some partition queries functionality. You can test whether the query is root-partitionable using You can execute partitioned query using Or you can use Note: Any stats are not available in partitioned query. You can set session-level default isolation level and transaction-level isolation level. You can enable Data Boost using In default, the number of worker goroutines for partitioned query is the value of You can change it using Note: Partitioned queries do not support streaming output in the current implementation. You can show partition tokens using Note: spanner-mycli does not clean up batch read-only transactions, which may prevent resources from being freed until they time out. The vertexai_project = "example-project"To use the Gemini API instead, set SET CLI_GENAI_BACKEND = "GEMINI_API";The canonical backend values are
The Built-in Spanner reference docs are always available. Dynamic documentation
lookup via the Developer Knowledge API uses The generated query is automatically filled in the prompt. spanner-mycli can execute BigQuery SQL with the spanner-mycli can emit input and output in Markdown. TODO: More description spanner-mycli can execute CQL statements of Cassandra interface with Run unit tests. Note: It requires Docker because integration tests using testcontainers. Or run test except integration tests. In principle, spanner-mycli accepts the same input as spanner-cli, but some compatibility is intentionally not maintained.
spanner-mycli expands tab characters to whitespaces.
Tab width can be configured using |