Cloud Spanner in Visual Studio Code

Google Cloud Spanner is a fully managed, scalable, relational database service for regional and global application data. It is the first scalable, enterprise-grade, globally-distributed, and strongly consistent database service built for the cloud specifically to combine the benefits of relational database structure with non-relational horizontal scale.

One of the advantages of working with a relational database is the large set of standard tooling available. Cloud Spanner now also offers integration with the popular Visual Studio Code extension SQL Tools. This integration enables you to execute queries, DML statements and DDL statements directly from Visual Studio Code.

Installing

Before you can install the Cloud Spanner driver, you need to add SQL Tools from the extensions plugin in Visual Studio Code. Then follow these steps:

1. Open SQL Tools and click on Add new connection.
2. Depending on whether you already have drivers installed or not, SQL Tools will show the current drivers with the option 'Get more drivers', or an empty screen with the option 'Search VSCode Marketplace' for drivers. Click on one of these.
3. The VS Code Marketplace will be opened with all known SQL Tools drivers. Append 'Cloud Spanner' to the search string and select the Google Cloud Spanner driver. Then click on Install.

Install Google Cloud Spanner Driver

Connecting to Cloud Spanner

Click on Add a new connection to connect to an existing Cloud Spanner database. Select the Google Cloud Spanner driver and enter the following information:

• Connection name: Any name that you want to give this connection.
• Google Cloud Project ID: The ID of your Google Cloud project where the Spanner instance has been created.
• Spanner Instance ID: The ID of the Cloud Spanner instance where the database is located.
• Spanner Database ID: The name of the Cloud Spanner database.
• Credentials Key File: A credentials file that should be used to connect to the database. This is only required if you do not have any default Google Cloud credentials set up on your environment already, or if you want to use other credentials.

Then click Save Connection.

Executing Statements

The Cloud Spanner SQL Tools driver supports both queries, DML-, and DDL statements. Queries that return more than 100,000 rows are not supported, because SQL Tools loads all query results into memory. Add LIMIT clause to your query to reduce the total number of rows.

DML statements will return the number of rows inserted/updated/deleted. Each DML statement is executed as a separate transaction. The current version of the driver (0.3.0) does not support multiple DML statements within one transaction, and also does not support Partitioned DML statements.

DDL statements do not return any results, other than a short 'Success' message once the DDL statement has finished executing. Note that DDL statements on Cloud Spanner can take a long time. This especially applies to operations that require an index to be filled.

Connecting to the Spanner Emulator

The SQL Tools driver for Cloud Spanner also supports connecting to the Spanner Emulator. Follow these steps to connect to the emulator:

1. Start the Spanner Emulator.
2. Click on Add New Connection in SQL Tools and select the Google Cloud Spanner driver.
3. Give the connection a name and enter 'test-project', 'test-instance' and 'test-database' into the project, instance and database fields for the connection. Any other IDs can also be used, including any existing instance and/or database on the emulator. If the project, instance or database does not already exist on the emulator, the driver will automatically create it.
4. Check the 'Connect to emulator' checkbox.
5. You do not need to set the SPANNER_EMULATOR_HOST environment variable to connect to it from Visual Studio Code.
6. You do not need to set the host and port number of the emulator in the connection settings in Visual Studio Code, unless you have started the emulator on a different host/port than the default.
7. Save the connection.

You can now connect to the emulator and create a couple of test tables, and test different queries / inserts / updates / deletes etc.

Remember that you always have to start the emulator before you can connect to it, but you do not need to create an instance and/or database on it. Also, you do not need to set the SPANNER_EMULATOR_HOST environment variable when connecting through Visual Studio Code.

Issues / Feature Requests

Please report any issues and/or feature requests you might have for Cloud Spanner in Visual Studio Code in this repository.