Spanner CLI quickstart

Preview — Spanner CLI

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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

This page introduces Spanner CLI and explains how to use it.

The Spanner CLI is a command-line interface (CLI) that lets you connect and interact with your Spanner database. It's incorporated into the Google Cloud CLI (gcloud CLI) to interface with Spanner. You can use the Spanner CLI to directly execute GoogleSQL statements in your Spanner database. Your statements can consist of data definition language (DDL), data manipulation language (DML), or data query language (DQL) statements. You can use the Spanner CLI to run SQL command scripts and automate tasks.

For more information, see a list of all supported gcloud alpha spanner cli commands.

Key benefits

The key benefits of the Spanner CLI include:

• Runs DDL, DML, and DQL SQL commands.
• You can write and execute SQL statements across multiple lines.
• Supports meta-commands to help you with system tasks such as executing a system shell command and executing SQL from a file.
• You can automate SQL executions by writing a series of SQL statements into a script file, and then instructing Spanner CLI to execute the script. In addition, you can redirect the output to an output file.
• You can start an interactive Spanner CLI session, which lets you directly type SQL statements and meta-commands and see results in the CLI.

Before you begin

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

Use the Spanner CLI

1. Set up a Google Cloud project.

2. Set up authentication using the gcloud CLI.

3. Create an instance.

4. Create a database.

5. Run the following command to start the Spanner CLI and interact with your Spanner database:

   gcloud alpha spanner cli DATABASE_ID --instance=INSTANCE_ID
   

   Replace the following:

   • DATABASE_ID: the ID of the Spanner database. This is the name you used in the previous Create a database step. You can use the gcloud spanner databases list command to list the Spanner databases contained within the given instance.
   • INSTANCE_ID: the ID of the Spanner instance. This is the name you used in the previous Create an instance step. You can use the gcloud spanner instances list command to list the Spanner instances contained within the given project.

Execute SQL

You can execute SQL statements in the Spanner CLI by using the execute option or using a file-based input and output method. Your SQL statements can consist of DDL, DML, or DQL.

Use the execute flag

To use the execute flag to execute SQL, run the following gcloud alpha spanner cli command:

gcloud alpha spanner cli DATABASE_ID --instance INSTANCE_ID \
    --execute "SQL"

Replace the following:

• DATABASE_ID: the ID of the Spanner database that you want to connect to.
• INSTANCE_ID: the ID of the Spanner instance that you want to connect to.
• SQL: the SQL that you want to execute.

For example, to execute a DDL statement:

gcloud alpha spanner cli test-database --instance test-instance \
    --execute "CREATE TABLE Singers ( \
        SingerId   INT64 NOT NULL, \
        FirstName  STRING(1024), \
        LastName   STRING(1024), \
        SingerInfo STRING(1024), \
        BirthDate  DATE \
      ) PRIMARY KEY(SingerId);"

To execute a DML statement:

gcloud alpha spanner cli test-database --instance test-instance \
    --execute "INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo) \
        VALUES(1, 'Marc', 'Richards', 'nationality: USA'), \
              (2, 'Catalina', 'Smith', 'nationality: Brazil'), \
              (3, 'Andrew', 'Duneskipper', NULL);"

Use a file-based input and output

If you use the file-based input and output method, Spanner reads its input from a file and writes its output to another file. To use the file-based input and output method to execute SQL, run the following command:

gcloud alpha spanner cli DATABASE_ID --instance INSTANCE_ID \
    --source INPUT_FILE_PATH --tee OUTPUT_FILE_PATH

You can also use the file-based redirection input and output method:

gcloud alpha spanner cli DATABASE_ID --instance INSTANCE_ID \
    < INPUT_FILE_PATH > OUTPUT_FILE_PATH

Replace the following:

• DATABASE_ID: the ID of the Spanner database that you want to connect to.
• INSTANCE_ID: the ID of the Spanner instance that you want to connect to.
• SOURCE_FILE_PATH: the file that contains the SQL that you want to execute.
• OUTPUT_FILE_PATH: the named file to append a copy of the SQL output.

Start an interactive session

You can start an interactive Spanner CLI session, which lets you directly type SQL statements and meta-commands and see results in the CLI. To do so, run the following command:

gcloud alpha spanner cli DATABASE_ID --instance=INSTANCE_ID

Upon successful connection between the CLI and your database, you will see a prompt (for example, spanner-cli>) where you can do the following:

• Directly type GoogleSQL statements:
  • GoogleSQL DDL reference
  • GoogleSQL DML reference
  • GoogleSQL query syntax
• Execute transactions
• Use supported meta-commands

After pressing the ENTER key, the statement or command is sent to the appropriate Spanner database. Spanner then executes the statement or command.

In the following example, you start an interactive session in test-database and then execute SELECT 1;:

gcloud alpha spanner cli test-database --instance test-instance

Welcome to Spanner-Cli Client.
Type 'help;' or '\h' for help.
Type 'exit;' or 'quit;' or '\q' to exit.

spanner-cli> SELECT 1;
+---+
|   |
+---+
| 1 |
+---+

1 rows in set (1.11 msecs)

Execute DDL statement

To execute a DDL statement, you can run the following:

spanner-cli> CREATE TABLE Singers (
          ->         SingerId   INT64 NOT NULL,
          ->         FirstName  STRING(1024),
          ->         LastName   STRING(1024),
          ->         SingerInfo STRING(1024),
          ->         BirthDate  DATE
          -> ) PRIMARY KEY(SingerId);

Query OK, 0 rows affected (17.08 sec)

Execute DML statement

To execute a DML statement, you can run the following:

spanner-cli> INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo)
          -> VALUES(1, 'Marc', 'Richards', 'nationality: USA'),
          -> (2, 'Catalina', 'Smith', 'nationality: Brazil'),
          -> (3, 'Andrew', 'Duneskipper', NULL);

Query OK, 3 rows affected (0.32 sec)

Supported meta-commands

The Spanner CLI supports utility meta-commands, which are commands that operate on the client, in this case the Spanner CLI. The following meta-commands are supported in the Spanner CLI:

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

What's next

• See a list of all supported gcloud alpha spanner cli commands.