Getting Started with Cloud Spanner in Node.js

Getting Started with Cloud Spanner in Node.js

Objectives

This tutorial walks you through the following steps using the Cloud Spanner client library for Node.js:

  • Create a Cloud Spanner instance and database.
  • Write, read, and execute SQL queries on data in the database.
  • Update the database schema.
  • Update data using a read-write transaction.
  • Add a secondary index to the database.
  • Use the index to read and execute SQL queries on data.
  • Retrieve data using a read-only transaction.

Costs

This tutorial uses Cloud Spanner, which is a billable component of the Google Cloud Platform. For information on the cost of using Cloud Spanner, see Pricing.

Before you begin

  1. Complete the steps described in Set Up, which covers creating and setting a default Google Cloud Platform project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.

    In particular, ensure that you run gcloud auth application-default login to set up your local development environment with authentication credentials.

  2. Install Node Version Manager (NVM) if it is not already installed.

  3. Use nvm to install Node.js if it is not already installed.

    nvm install stable
    
  4. Clone the sample app repository to your local machine:

    git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples.git
    

    Alternatively, you can download the sample as a zip file and extract it.

  5. Change to the directory that contains the Cloud Spanner sample code:

    cd nodejs-docs-samples/spanner/
    
  6. Install dependencies using npm:

    npm install
    
  7. Set the GCLOUD_PROJECT environment variable to your Google Cloud Platform project ID:

    export GCLOUD_PROJECT=[MY_PROJECT_ID]
    

Create an instance

When you first use Cloud Spanner, you must create an instance, which is an allocation of resources that are used by Cloud Spanner databases. When you create an instance, you choose where your data is stored and how many nodes are used for your data. (For more information, see Instance Configuration).

Create a Cloud Spanner instance and assign it the instance ID test-instance and the display name Test Instance using the regional configuration regional-us-central1 with a node count of 1 (node_count corresponds to the amount of serving resources available to databases in the instance):

gcloud spanner instances create test-instance --config=regional-us-central1 \
--description="Test Instance" --nodes=1

You should see:

Creating instance...done.

Look through sample files

The samples repo contains a sample that shows how to use Cloud Spanner with Node.js.

Take a look through the spanner/schema.js file that shows how to create a database and modify a database schema. The data uses the example schema shown in the Schema and Data Model page.

Create a database

Create a database called example-db by running the following at the command line.

node schema.js createDatabase test-instance example-db

You should see:

Created database example-db on instance test-instance.

You have just created a Cloud Spanner database. The following is the code that created the database.

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance
const instance = spanner.instance(instanceId);

// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted to strings before being inserted as INT64s
const request = {
  schema: [
    `CREATE TABLE Singers (
      SingerId    INT64 NOT NULL,
      FirstName   STRING(1024),
      LastName    STRING(1024),
      SingerInfo  BYTES(MAX)
    ) PRIMARY KEY (SingerId)`,
    `CREATE TABLE Albums (
      SingerId    INT64 NOT NULL,
      AlbumId     INT64 NOT NULL,
      AlbumTitle  STRING(MAX)
    ) PRIMARY KEY (SingerId, AlbumId),
    INTERLEAVE IN PARENT Singers ON DELETE CASCADE`
  ]
};

// Creates a database
instance.createDatabase(databaseId, request)
  .then((results) => {
    const database = results[0];
    const operation = results[1];

    console.log(`Waiting for operation on ${database.id} to complete...`);
    return operation.promise();
  })
  .then(() => {
    console.log(`Created database ${databaseId} on instance ${instanceId}.`);
  });

The code also defines two tables, Singers and Albums, for a basic music application. These tables are used throughout this page. Take a look at the example schema if you haven't already.

The next step is to write data to your database.

Create a database client

Before you can do reads or writes, you must create a Database:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Your Google Cloud Platform project ID
const projectId = 'YOUR_PROJECT_ID';

// Instantiates a client
const spanner = Spanner({
  projectId: projectId
});

// Your Cloud Spanner instance ID
const instanceId = 'my-instance';

// Your Cloud Spanner database ID
const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// The query to execute
const query = {
  sql: 'SELECT 1'
};

// Execute a simple SQL statement
database.run(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => console.log(row));
  });

You can think of a Database as a database connection: all of your interactions with Cloud Spanner must go through a Database. Typically you create a Database when your application starts up, then you re-use that Database to read, write, and execute transactions.

Each client uses resources in Cloud Spanner, so you must call Database.close() to clean up the client's resources, including network connections.

Read more in the Database reference.

Write data

You write data using a Table object. The Table.insert() method adds new rows to the table. All inserts in a single batch are applied atomically.

This code shows how to write the data:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Instantiate Spanner table objects
const singersTable = database.table('Singers');
const albumsTable = database.table('Albums');

// Inserts rows into the Singers table
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so
// they must be converted to strings before being inserted as INT64s
singersTable.insert([
  { SingerId: '1', FirstName: 'Marc', LastName: 'Richards' },
  { SingerId: '2', FirstName: 'Catalina', LastName: 'Smith' },
  { SingerId: '3', FirstName: 'Alice', LastName: 'Trentor' },
  { SingerId: '4', FirstName: 'Lea', LastName: 'Martin' },
  { SingerId: '5', FirstName: 'David', LastName: 'Lomond' }
])
.then(() => {
  // Inserts rows into the Albums table
  albumsTable.insert([
    { SingerId: '1', AlbumId: '1', AlbumTitle: 'Go, Go, Go' },
    { SingerId: '1', AlbumId: '2', AlbumTitle: 'Total Junk' },
    { SingerId: '2', AlbumId: '1', AlbumTitle: 'Green' },
    { SingerId: '2', AlbumId: '2', AlbumTitle: 'Forever Hold your Peace' },
    { SingerId: '2', AlbumId: '3', AlbumTitle: 'Terrified' }
  ]);
})
.then(() => {
  console.log('Inserted data.');
});

(For details about the data, see the example schema for the Singers and Albums tables.)

Run the sample using the insert argument.

node crud.js insert test-instance example-db

You should see:

Inserted data.

Query data using SQL

Cloud Spanner supports a native SQL interface for reading data, which you can access on the command line using the gcloud command-line tool or programmatically using the Cloud Spanner client library for Node.js.

On the command line

Execute the following SQL statement to read the values of all columns from the Albums table:

gcloud spanner databases execute-sql example-db --instance=test-instance --sql='SELECT SingerId, AlbumId, AlbumTitle FROM Albums'

The result should be:

SingerId AlbumId AlbumTitle
1        1       Total Junk
1        2       Go, Go, Go
2        1       Green
2        2       Forever Hold Your Peace
2        3       Terrified

Using the Cloud Spanner client library for Node.js

In addition to executing a SQL statement on the command line, you can issue the same SQL statement programmatically using the Cloud Spanner client library for Node.js.

Use Database.run() to run the SQL query.

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: 'SELECT SingerId, AlbumId, AlbumTitle FROM Albums'
};

// Queries rows from the Albums table
database.run(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId.value}, AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
    });
  });

Here's how to issue the query and access the data:

node crud.js query test-instance example-db

You should see the following result:

SingerId: 1, AlbumId: 1, AlbumTitle: Go, Go, Go
SingerId: 1, AlbumId: 2, AlbumTitle: Total Junk
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold your Peace
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified

Read data using the read API

In addition to Cloud Spanner's SQL interface, Cloud Spanner also supports a read interface.

Use Table.read() to read rows from the database. Use a KeySet object to define a collection of keys and/or key ranges to read.

Here's how to read the data:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Read rows from the Albums table
const albumsTable = database.table('Albums');

const query = {
  columns: ['SingerId', 'AlbumId', 'AlbumTitle'],
  keySet: {
    all: true
  }
};

albumsTable.read(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`SingerId: ${json.SingerId.value}, AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
    });
  });

Run the sample using the read argument.

node crud.js read test-instance example-db

You should see output similar to:

SingerId: 1, AlbumId: 1, AlbumTitle: Go, Go, Go
SingerId: 1, AlbumId: 2, AlbumTitle: Total Junk
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold your Peace
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified

Update the database schema

Assume you need to add a new column called MarketingBudget to the Albums table. Adding a new column to an existing table requires an update to your database schema. Cloud Spanner supports schema updates to a database while the database continues to serve traffic. Schema updates do not require taking the database offline and they do not lock entire tables or columns; you can continue writing data to the database during the schema update. Read more about supported schema updates and schema change performance in Updating schemas.

Add a column

You can add a column on the command line using the gcloud command-line tool or programmatically using the Cloud Spanner client library for Node.js.

On the command line

Use the following ALTER TABLE command to add the new column to the table:

gcloud spanner databases ddl update example-db --instance=test-instance \
--ddl='ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'

You should see:

DDL updating...done.

Using the Cloud Spanner client library for Node.js

Use Database.updateSchema to modify the schema:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const request = [
  'ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'
];

// Creates a new index in the database
database.updateSchema(request)
  .then((results) => {
    const operation = results[0];

    console.log('Waiting for operation to complete...');
    return operation.promise();
  })
  .then(() => {
    console.log('Added the MarketingBudget column.');
  });

Run the sample using the addColumn argument.

node schema.js addColumn test-instance example-db

You should see:

Added the MarketingBudget column.

Write data to the new column

The following code writes data to the new column. It sets MarketingBudget to 100000 for the row keyed by Albums(1, 1) and to 500000 for the row keyed by Albums(2, 2).

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Update a row in the Albums table
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted to strings before being inserted as INT64s
const albumsTable = database.table('Albums');

albumsTable.update([
  { SingerId: '1', AlbumId: '1', MarketingBudget: '100000' },
  { SingerId: '2', AlbumId: '2', MarketingBudget: '500000' }
])
.then(() => {
  console.log('Updated data.');
});

Run the sample using the update argument.

node crud.js update test-instance example-db

You should see:

Updated data.

You can also execute a SQL query or a read call to fetch the values that you just wrote.

Here's the code to execute the query:

// This sample uses the `MarketingBudget` column. You can add the column
// by running the `add_column` sample or by running this DDL statement against
// your database:
//    ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT SingerId, AlbumId, MarketingBudget FROM Albums`
};

// Queries rows from the Albums table
database.run(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();

      console.log(`SingerId: ${json.SingerId.value}, AlbumId: ${json.AlbumId.value}, MarketingBudget: ${json.MarketingBudget ? json.MarketingBudget.value : null}`);
    });
  });

To execute this query, run the sample using the queryNewColumn argument.

node schema.js queryNewColumn test-instance example-db

You should see:

SingerId: 1, AlbumId: 1, MarketingBudget: 100000
SingerId: 1, AlbumId: 2, MarketingBudget: null
SingerId: 2, AlbumId: 1, MarketingBudget: null
SingerId: 2, AlbumId: 2, MarketingBudget: 500000
SingerId: 2, AlbumId: 3, MarketingBudget: null

Update data using a read-write transaction

Suppose that sales of Albums(1, 1) are lower than expected and you want to move $200,000 from the marketing budget of Albums(2, 2) to it, but only if the budget of Albums(2, 2) is at least $300,000.

Because this transaction might write data depending on the values read, you should use a read-write transaction to perform the reads and writes atomically.

Use Database.runTransaction() to run a transaction.

Here's the code to run the transaction:

// This sample transfers 200,000 from the MarketingBudget field
// of the second Album to the first Album. Make sure to run the
// addColumn and updateData samples first (in that order).

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const transferAmount = 200000;
const minimumAmountToTransfer = 300000;

database.runTransaction((err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  let firstBudget, secondBudget;
  const queryOne = {
    columns: [`MarketingBudget`],
    keys: [[2, 2]] // SingerId: 2, AlbumId: 2
  };

  const queryTwo = {
    columns: ['MarketingBudget'],
    keys: [[1, 1]] // SingerId: 1, AlbumId: 1
  };

  Promise.all([
    // Reads the second album's budget
    transaction.read('Albums', queryOne).then((results) => {
      // Gets second album's budget
      // Note: MarketingBudget is an INT64, which comes from Cloud Spanner
      // as a string - so we convert it to a number with parseInt()
      const rows = results[0].map((row) => row.toJSON());
      secondBudget = parseInt(rows[0].MarketingBudget.value);
      console.log(`The second album's marketing budget: ${secondBudget}`);

      // Makes sure the second album's budget is sufficient
      if (secondBudget < minimumAmountToTransfer) {
        throw new Error(`The second album's budget (${secondBudget}) is less than the minimum required amount to transfer.`);
      }
    }),

    // Reads the first album's budget
    transaction.read('Albums', queryTwo).then((results) => {
      // Gets first album's budget
      // As above, MarketingBudget is an INT64 and comes as a string
      const rows = results[0].map((row) => row.toJSON());
      firstBudget = parseInt(rows[0].MarketingBudget.value);
      console.log(`The first album's marketing budget: ${firstBudget}`);
    })
  ]).then(() => {
    // Transfer the budgets between the albums
    console.log(firstBudget, secondBudget);
    firstBudget += transferAmount;
    secondBudget -= transferAmount;

    console.log(firstBudget, secondBudget);

    // Update the database
    // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
    // must be converted (back) to strings before being inserted as INT64s.
    transaction.update('Albums', [
      { SingerId: '1', AlbumId: '1', MarketingBudget: firstBudget.toString() },
      { SingerId: '2', AlbumId: '2', MarketingBudget: secondBudget.toString() }
    ]);
  })
  // Commits the transaction and send the changes to the database
  .then(() => transaction.commit((err) => {
    if (err) {
      console.error(err);
    } else {
      console.log(`Successfully executed read-write transaction to transfer ${transferAmount} from Album 2 to Album 1.`);
    }
  }));
});

Run the sample using the readWrite argument.

node transaction.js readWrite test-instance example-db

You should see:

The second album's marketing budget: 500000
The first album's marketing budget: 100000
100000 500000
300000 300000
Successfully executed read-write transaction to transfer 200000 from Album 2 to Album 1.

Query the data again:

node schema.js queryNewColumn test-instance example-db

You should see:

SingerId: 1, AlbumId: 1, MarketingBudget: 300000
SingerId: 1, AlbumId: 2, MarketingBudget: null
SingerId: 2, AlbumId: 1, MarketingBudget: null
SingerId: 2, AlbumId: 2, MarketingBudget: 300000
SingerId: 2, AlbumId: 3, MarketingBudget: null

Use a secondary index

Suppose you wanted to fetch all rows of Albums that have AlbumTitle values in a certain range. You could read all values from the AlbumTitle column using a SQL statement or a read call, and then discard the rows that don't meet the criteria, but doing this full table scan is expensive, especially for tables with a lot of rows. Instead you can speed up the retrieval of rows when searching by non-primary key columns by creating a secondary index on the table.

Adding a secondary index to an existing table requires a schema update. Like other schema updates, Cloud Spanner supports adding an index while the database continues to serve traffic. Cloud Spanner populates the index with data (aka "backfills") under the hood. Backfills might take a few minutes to complete, but you don't have to take the database offline or avoid writing to certain tables or columns during this process. For more details, see index backfilling.

Add a secondary index

You can add an index on the command line using the gcloud command line tool or programmatically using the Cloud Spanner client library for Node.js.

On the command line

Use the following CREATE INDEX command to add an index to the database:

gcloud spanner databases ddl update example-db --instance=test-instance \
--ddl='CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'

You should see:

DDL updating...done.

Using the Cloud Spanner client library for Node.js

Use Database.updateSchema() to add an index:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const request = [
  'CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'
];

// Creates a new index in the database
database.updateSchema(request)
  .then((results) => {
    const operation = results[0];

    console.log('Waiting for operation to complete...');
    return operation.promise();
  })
  .then(() => {
    console.log('Added the AlbumsByAlbumTitle index.');
  });

Run the sample using the createIndex argument.

node indexing.js createIndex test-instance example-db

Adding an index can take a few minutes. After the index is added, you should see:

Added the AlbumsByAlbumTitle index.

Query using the index

You can query using the new index either on the command line or using the client library.

On the command line

Execute a SQL statement using the gcloud command-line tool to fetch AlbumId, AlbumTitle, and MarketingBudget from Albums using the AlbumsByAlbumTitle index, for the range of AlbumsTitle in ["Aardvark", "Goo").

gcloud spanner databases execute-sql example-db --instance=test-instance --sql='SELECT AlbumId, AlbumTitle, MarketingBudget FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} WHERE AlbumTitle >= "Aardvark" AND AlbumTitle < "Goo"'

The result should be:

AlbumId  AlbumTitle               MarketingBudget
1        Go, Go, Go               300000
2        Forever Hold your Peace  300000

Using the Cloud Spanner client library for Node.js

The code to programmatically use the index is similar to the query code used earlier.

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Uncomment these lines to specify the start and end title(s)
// const startTitle = 'Ardvark';
// const endTitle = 'Goo';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const query = {
  sql: `SELECT AlbumId, AlbumTitle, MarketingBudget
        FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
        WHERE AlbumTitle >= @startTitle AND AlbumTitle <= @endTitle`,
  params: {
    startTitle: startTitle,
    endTitle: endTitle
  }
};

// Queries rows from the Albums table
database.run(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      const marketingBudget = json.MarketingBudget ? json.MarketingBudget.value : null; // This value is nullable
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${marketingBudget}`);
    });
  });

Run the sample using the queryIndex argument.

node indexing.js queryIndex test-instance example-db

You should see output similar to:

AlbumId: 1, AlbumTitle: Go, Go, Go, MarketingBudget: 300000
AlbumId: 2, AlbumTitle: Forever Hold your Peace, MarketingBudget: 300000

For more details, consult the reference for:

Read using the index

To read using the index, use the Table.read() method.

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle'],
  keySet: {
    all: true
  },
  index: 'AlbumsByAlbumTitle'
};

// Reads the Albums table using an index
albumsTable.read(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
    });
  });

Run the sample using the readIndex argument.

node indexing.js readIndex test-instance example-db

You should see:

AlbumId: 2, AlbumTitle: Forever Hold your Peace
AlbumId: 1, AlbumTitle: Go, Go, Go
AlbumId: 1, AlbumTitle: Green
AlbumId: 3, AlbumTitle: Terrified
AlbumId: 2, AlbumTitle: Total Junk

Add an index with a STORING clause

You might have noticed that the read example above did not include reading the MarketingBudget column. This is because Cloud Spanner's read interface does not support the ability to join an index with a data table to look up values that are not stored in the index.

Create an alternate definition of AlbumsByAlbumTitle that stores a copy of MarketingBudget in the index.

On the command line

gcloud spanner databases ddl update example-db --instance=test-instance \
--ddl='CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)'

Adding an index can take a few minutes. After the index is added, you should see:

DDL updating...done.

Using the Cloud Spanner client library for Node.js

Use Database.updateSchema() to add an index with a STORING clause:

// "Storing" indexes store copies of the columns they index
// This speeds up queries, but takes more space compared to normal indexes
// See the link below for more information:
// https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const request = [
  'CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)'
];

// Creates a new index in the database
database.updateSchema(request)
  .then((results) => {
    const operation = results[0];

    console.log('Waiting for operation to complete...');
    return operation.promise();
  })
  .then(() => {
    console.log('Added the AlbumsByAlbumTitle2 index.');
  });

Run the sample using the createStoringIndex argument.

node indexing.js createStoringIndex test-instance example-db

You should see:

Added the AlbumsByAlbumTitle2 index.

Now you can execute a read that fetches all AlbumId, AlbumTitle, and MarketingBudget columns from the AlbumsByAlbumTitle2 index:

// "Storing" indexes store copies of the columns they index
// This speeds up queries, but takes more space compared to normal indexes
// See the link below for more information:
// https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const albumsTable = database.table('Albums');

const query = {
  columns: ['AlbumId', 'AlbumTitle', 'MarketingBudget'],
  keySet: {
    all: true
  },
  index: 'AlbumsByAlbumTitle2'
};

// Reads the Albums table using a storing index
albumsTable.read(query)
  .then((results) => {
    const rows = results[0];

    rows.forEach((row) => {
      const json = row.toJSON();
      console.log(`AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}, MarketingBudget: ${json.MarketingBudget.value}`);
    });
  });

Run the sample using the readStoringIndex argument.

node indexing.js readStoringIndex test-instance example-db

You should see output similar to:

AlbumId: 2, AlbumTitle: Forever Hold your Peace, MarketingBudget: 300000
AlbumId: 1, AlbumTitle: Go, Go, Go, MarketingBudget: 300000
AlbumId: 1, AlbumTitle: Green, MarketingBudget: null
AlbumId: 3, AlbumTitle: Terrified, MarketingBudget: null
AlbumId: 2, AlbumTitle: Total Junk, MarketingBudget: null

Retrieve data using read-only transactions

Suppose you want to execute more than one read at the same timestamp. Read-only transactions observe a consistent prefix of the transaction commit history, so your application always gets consistent data. Use Database.runTransaction() for executing read-only transactions.

The following shows how to run a query and perform a read in the same read-only transaction:

// Imports the Google Cloud client library
const Spanner = require('@google-cloud/spanner');

// Instantiates a client
const spanner = Spanner();

// Uncomment these lines to specify the instance and database to use
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Gets a transaction object that captures the database state
// at a specific point in time
database.runTransaction({readOnly: true}, (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  const queryOne = 'SELECT SingerId, AlbumId, AlbumTitle FROM Albums';

    // Read #1, using SQL
  transaction.run(queryOne)
    .then((results) => {
      const rows = results[0];
      rows.forEach((row) => {
        const json = row.toJSON();
        console.log(`SingerId: ${json.SingerId.value}, AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
      });
      const queryTwo = {
        columns: ['SingerId', 'AlbumId', 'AlbumTitle'],
        keySet: {
          all: true
        }
      };

    // Read #2, using the `read` method. Even if changes occur
    // in-between the reads, the transaction ensures that both
    // return the same data.
      return transaction.read('Albums', queryTwo);
    })
    .then((results) => {
      const rows = results[0];
      rows.forEach((row) => {
        const json = row.toJSON();
        console.log(`SingerId: ${json.SingerId.value}, AlbumId: ${json.AlbumId.value}, AlbumTitle: ${json.AlbumTitle}`);
      });
      console.log('Successfully executed read-only transaction.');
      transaction.end();
    });
});

Run the sample using the readOnly argument.

node transaction.js readOnly test-instance example-db

You should see output similar to:

SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold your Peace
SingerId: 1, AlbumId: 1, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified
SingerId: 1, AlbumId: 2, AlbumTitle: Total Junk
SingerId: 1, AlbumId: 1, AlbumTitle: Go, Go, Go
SingerId: 1, AlbumId: 2, AlbumTitle: Total Junk
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold your Peace
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified
Successfully executed read-only transaction.

Cleanup

To avoid incurring additional charges to your Google Cloud Platform account for the resources used in this tutorial, drop the database and delete the instance that you created.

Delete the database

If you delete an instance, all databases within it are automatically deleted. This step shows how to delete a database without deleting an instance (you would still incur charges for the instance).

On the command line

gcloud spanner databases delete example-db --instance=test-instance

Using the Cloud Platform Console

  1. Go to the Spanner Instances page in the Google Cloud Platform Console.
    Go to the Spanner Instances page
  2. Click the instance.
  3. Click the database that you want to delete.
  4. In the Database details page, click Delete.
  5. Confirm that you want to delete the database and click Delete.

Delete the instance

Deleting an instance automatically drops all databases created in that instance.

On the command line

gcloud spanner instances delete test-instance

Using the Cloud Platform Console

  1. Go to the Spanner Instances page in the Google Cloud Platform Console.
    Go to the Spanner Instances page
  2. Click your instance.
  3. Click Delete.
  4. Confirm that you want to delete the instance and click Delete.

What's next

Send feedback about...

Cloud Spanner Documentation