Browse and query Cloud Spanner databases from Visual Studio Code

Visual Studio Code is one of the most widely-used IDEs, due in part to the variety of extensions that are available to developers. For developers who are building applications that interact with Cloud Spanner, we're excited to announce the Google Cloud Spanner driver for the popular SQLTools extension for VS Code. 

The SQLTools extension works with a variety of SQL drivers and allows developers to manage database connections, execute and generate queries, and more from within VS Code. By using the Cloud Spanner driver with SQLTools, developers can browse tables and execute queries, DDL statements, and DML statements on Cloud Spanner databases without having to leave the IDE.

In this post, we'll walk through the process of installing the extension, connecting to a Cloud Spanner database, and using SQLTools with the database.

Prerequisites

Before you get started, you'll need to have a Google Cloud Platform project with a Cloud Spanner instance and a database. This codelab will walk you through the process if you haven't used Cloud Spanner before. Alternatively, you can use the emulator. You'll also need to have VS Code installed on your computer.

Installation

To install the Cloud Spanner driver for SQLTools, click on the Extensions icon in VS Code, search for "cloud spanner driver", and install the extension called Google Cloud Spanner Driver. Alternatively, you can install the Cloud Spanner driver for SQLTools from the Visual Studio Marketplace.

Once the extension is installed, you'll see a database icon for SQLTools, as highlighted by the red rectangle in the image below, show up in VS Code. Click this database icon to access the extension.

Connecting to a Cloud Spanner database

With the extension installed, click the Add New Connection icon in SQLTools to open the Connection Assistant and choose Google Cloud Spanner Driver. You can connect either to a Spanner instance on Google Cloud or to an emulator instance. 

Configuring a connection using the emulator

The Cloud SDK offers a local, in-memory emulator that you can use while developing and testing. To use the SQLTools extension with the emulator, you must first start the emulator. Then, in the Connection Settings step, enter values for Connection name, Google Cloud Project ID, Spanner Instance ID, and Spanner Database ID. Select the checkbox next to Connect to emulator. When you use this setting, the instance and database you specified will be automatically created for you in the emulator if they do not already exist.

Configuring a connection to a Cloud Spanner database on Google Cloud

If you are connecting to a Cloud Spanner database running on Google Cloud, you'll need to provide the Google Cloud Project ID, Spanner Instance ID, and Spanner Database ID. You can enter any value you like for the Connection name. You'll also need to specify your credentials in one of two ways: enter the absolute path to your credential key file in the Connection Assistant or set the GOOGLE_ACCOUNT_CREDENTIALS environment variable to the path to your credential key file. If you are using the GOOGLE_ACCOUNT_CREDENTIALS environment variable, note that if VS Code was already running before you set the environment variable, then you will need to restart VS Code. Your service account will need to be granted appropriate permissions for interacting with Cloud Spanner. For more information about credentials, see the documentation on creating service accounts and service account keys. You can find a list of Cloud Spanner roles in this table.

Testing and establishing connections

Once you've entered the connection settings information, you can click TEST CONNECTION to make sure the connection is successful, and then click SAVE CONNECTION.

On the final step of the Connection Assistant, click CONNECT NOW.

Browsing database tables

In the Connections section of SQLTools, you can view the tables in your database. In the screenshot below, you can see the columns in the comments table.

Right-clicking on a table name provides options such as showing table records or generating an insert query.

Executing queries and statements

The Cloud Spanner driver supports executing queries, DDL statements, and DML statements. If you execute multiple statements in a single script, each statement will be executed in a separate transaction. Note that the extension is intended for use during development and testing, not for administration of production database environments.

Queries use single-use read-only transactions, while DML statements use read-write transactions. Make sure that the service account you're using has the necessary permissions to execute the queries or statements. For more information on types of transactions, see the documentation. 

Next steps

Interacting with your Cloud Spanner databases from within your IDE can make your development process more efficient and reduce the need to switch between multiple tools and interfaces. Ready to try it yourself? Install the Cloud Spanner driver for SQLTools and start exploring and interacting with your Cloud Spanner databases from within VS Code. If you have suggestions or issues, you can raise them in the issue tracker or for questions or comments, feel free to reach out to me on Twitter. We would love to hear your feedback.