spanner-cli

spanner-cli

spanner-cli, the Cloud Spanner command line interface.

Description

spanner-cli is a command line client for Cloud Spanner.
You can control your Spanner databases with idiomatic SQL commands like mysql(1).

This tool is still ALPHA quality.
Do not use this tool for operating production databases.

Install

go get -u github.com/yfuruyama/spanner-cli

Usage

Usage:
  spanner-cli [OPTIONS]

Application Options:
  -p, --project=  (required) GCP Project ID.
  -i, --instance= (required) Cloud Spanner Instance ID
  -d, --database= (required) Cloud Spanner Database ID.
  -e, --execute=  Execute SQL statement and quit.
  -t, --table     Display output in table format for batch mode.
      --prompt=   Set the prompt to the specified format

Help Options:
  -h, --help      Show this help message

This tool uses Application Default Credentials as credential source to connect to Spanner databases.
Please be sure to prepare your credential by gcloud auth application-default login.

Example

Interactive mode

$ spanner-cli -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

Batch mode

By passing SQL from standard input, spanner-cli runs in batch mode

$ echo 'SELECT * FROM users;' | spanner-cli -p myproject -i myinstance -d mydb
id      name    active
1       foo     true
2       bar     false

You can also pass SQL from command line option -e

$ spanner-cli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;'
id      name    active
1       foo     true
2       bar     false

With -t option, results are displayed in table format

$ spanner-cli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;' -t
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
| 2  | bar  | false  |
+----+------+--------+

Syntax

The syntax is case-insensitive.
\G delimiter is also supported for dispalying results vertically.

Customize prompt

You can customize the prompt by --prompt option.
There are some defined variables for being used in prompt.

Variables:

• \p : GCP Project ID
• \i : Cloud Spanner Instance ID
• \d : Cloud Spanner Database ID
• \t : In transaction

Example:

$ spanner-cli -p myproject -i myinstance -d mydb --prompt='[\p:\i:\d]\t> '
Connected.
[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> .

Config file

This tool supports a configuration file called spanner_cli.cnf like my.cnf.
The config file path must be ~/.spanner_cli.cnf.
In the config file, you can set default option values for command line options.

Example:

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

How to develop

Run unit tests

$ make test

Run unit tests and integration tests, which connects to real Cloud Spanner database.

$ PROJECT=${PROJECT_ID} INSTANCE=${INSTANCE_ID} DATABASE=${DATABASE_ID} CREDENTIAL=${CREDENTIAL} make test

TODO

• STRUCT data type
• show secondary index by "SHOW CREATE TABLE"