Constructor

Table

new Table(database, name)

Create a Table object to interact with a table in a Cloud Spanner database.

Parameter

database

Database

Database instance.

name

string

Name of the table.

Extends
TransactionRequest

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = database.table('my-table');

Properties

api

object

Properties

Parameter

Database

v1.DatabaseAdminClient

Reference to an instance of the low-level v1.DatabaseAdminClient class used by this Table instance.

Instance

v1.InstanceAdminClient

Reference to an instance of the low-level v1.InstanceAdminClient class used by this Table instance.

Spanner

v1.SpannerClient

Reference to an instance of the low-level v1.SpannerClient class used by this Table instance.

database

Database

The Database instance of this Table instance.

name

string

The name of this table.

Methods

create

create(schema, callback) returns Promise containing CreateTableResponse

Create a table.

Parameter

schema

string

See Database#createTable.

callback

Optional

CreateTableCallback

Callback function.

Returns

Promise containing CreateTableResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const schema =
  'CREATE TABLE Singers (' +
  '  SingerId INT64 NOT NULL,' +
  '  FirstName STRING(1024),' +
  '  LastName STRING(1024),' +
  '  SingerInfo BYTES(MAX),' +
  ') PRIMARY KEY(SingerId)';

table.create(schema, function(err, table, operation, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  operation
    .on('error', function(err) {})
    .on('complete', function() {
      // Table created successfully.
    });
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.create(schema)
  .then(function(data) {
    const table = data[0];
    const operation = data[1];

    return operation.promise();
  })
  .then(function() {
    // Table created successfully.
  });

createReadStream

createReadStream(table, query, options) returns ReadableStream

Create a readable object stream to receive rows from the database using key lookups and scans.

Parameter

table

string

The table to read from.

query

ReadStreamRequestOptions

Configuration object. See ReadRequest.

options

Optional

TransactionOptions

Transaction options.

See also

StreamingRead API Documentation

ReadRequest API Documentation

Returns

ReadableStream 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

table.createReadStream({
    keys: ['1'],
    columns: ['SingerId', 'name']
  })
  .on('error', function(err) {})
  .on('data', function(row) {
    // row = {
    //   SingerId: '1',
    //   Name: 'Eddie Wilson'
    // }
  })
  .on('end', function() {
    // All results retrieved.
  });

//-
// Provide an array for `query.keys` to read with a composite key.
//-
const query = {
  keys: [
    [
      'Id1',
      'Name1'
    ],
    [
      'Id2',
      'Name2'
    ]
  ],
  // ...
};

//-
// If you anticipate many results, you can end a stream early to prevent
// unnecessary processing and API requests.
//-
table.createReadStream({
    keys: ['1'],
    columns: ['SingerId', 'name']
  })
  .on('data', function(row) {
    this.end();
  });

delete

delete(callback) returns Promise containing LongRunningOperationResponse

Delete the table.

Wrapper around Database#updateSchema.

Parameter

callback

Optional

LongRunningOperationCallback

Callback function.

See also
Database#updateSchema
Returns

Promise containing LongRunningOperationResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

table.delete(function(err, operation, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  operation
    .on('error', function(err) {})
    .on('complete', function() {
      // Table deleted successfully.
    });
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.delete()
  .then(function(data) {
    const operation = data[0];
    return operation.promise();
  })
  .then(function() {
    // Table deleted successfully.
  });

deleteRows

deleteRows(keys, callback) returns Promise containing BasicResponse

Delete rows from this table.

Parameter

keys

array

The keys for the rows to delete. If using a composite key, provide an array within this array. See the example below.

callback

Optional

BasicCallback

Callback function.

See also

Commit API Documentation

Returns

Promise containing BasicResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const keys = ['Id1', 'Id2', 'Id3'];

table.deleteRows(keys, function(err, apiResponse) {});

//-
// Provide an array for `keys` to delete rows with a composite key.
//-
const keys = [
  [
    'Id1',
    'Name1'
  ],
  [
    'Id2',
    'Name2'
  ]
];

//-
// If the callback is omitted, we'll return a Promise.
//-
table.deleteRows(keys)
  .then(function(data) {
    const apiResponse = data[0];
  });

insert

insert(keyVals, callback) returns Promise containing BasicResponse

Insert rows of data into this table.

Parameter

keyVals

(object or Array of object)

A map of names to values of data to insert into this table.

callback

Optional

BasicCallback

Callback function.

See also

Commit API Documentation

Returns

Promise containing BasicResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const row = {
  SingerId: 'Id3',
  Name: 'Eddie Wilson'
};

table.insert(row, function(err, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // Rows inserted successfully.
});

//-
// Multiple rows can be inserted at once.
//-
const row2 = {
  SingerId: 'Id3b',
  Name: 'Joe West'
};

table.insert([
  row,
  row2
], function(err, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.insert(row)
  .then(function(data) {
    const apiResponse = data[0];
  });

Full example:

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

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud 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
    return albumsTable.insert([
      {SingerId: '1', AlbumId: '1', AlbumTitle: 'Total Junk'},
      {SingerId: '1', AlbumId: '2', AlbumTitle: 'Go, Go, Go'},
      {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.');
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

read

read(query, options, callback) returns Promise containing TableReadResponse

Receive rows from the database using key lookups and scans.

Performance Considerations:

This method wraps the streaming method, Table#createReadStream for your convenience. All rows will be stored in memory before being released to your callback. If you intend on receiving a lot of results from your query, consider using the streaming method, so you can free each result from memory after consuming it.

Parameter

query

TableReadRequestOptions

Configuration object, describing what to read from the table.

options

TransactionOptions

Transaction options.

callback

Optional

TableReadCallback

Callback function.

Returns

Promise containing TableReadResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const query = {
  keys: ['1'],
  columns: ['SingerId', 'name']
};

table.read(query, function(err, rows) {
  if (err) {
    // Error handling omitted.
  }

  const firstRow = rows[0];

  // firstRow = [
  //   {
  //     name: 'SingerId',
  //     value: '1'
  //   },
  //   {
  //     name: 'Name',
  //     value: 'Eddie Wilson'
  //   }
  // ]
});

//-
// Provide an array for `query.keys` to read with a composite key.
//-
const query = {
  keys: [
    [
      'Id1',
      'Name1'
    ],
    [
      'Id2',
      'Name2'
    ]
  ],
  // ...
};

//-
// Rows are returned as an array of object arrays. Each object has a `name`
// and `value` property. To get a serialized object, call `toJSON()`.
//
// Alternatively, set `query.json` to `true`, and this step will be performed
// automaticaly.
//-
table.read(query, function(err, rows) {
  if (err) {
    // Error handling omitted.
  }

  const firstRow = rows[0];

  // firstRow.toJSON() = {
  //   SingerId: '1',
  //   Name: 'Eddie Wilson'
  // }
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.read(query)
  .then(function(data) {
    const apiResponse = data[0];
  });

Full example:

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

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

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

// Reads 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}, AlbumId: ${json.AlbumId}, AlbumTitle: ${
          json.AlbumTitle
        }`
      );
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

Reading stale data:

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

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

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

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

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

const options = {
  // Guarantees that all writes committed more than 15 seconds ago are visible
  exactStaleness: 15,
};

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

    rows.forEach(row => {
      const json = row.toJSON();
      const id = json.SingerId;
      const album = json.AlbumId;
      const title = json.AlbumTitle;
      const budget = json.MarketingBudget ? json.MarketingBudget : '';
      console.log(
        `SingerId: ${id}, AlbumId: ${album}, AlbumTitle: ${title}, MarketingBudget: ${budget}`
      );
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

Reading data using an index:

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

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// 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}, AlbumTitle: ${json.AlbumTitle}`);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

Reading data using a storing 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');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// 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();
      let rowString = `AlbumId: ${json.AlbumId}`;
      rowString += `, AlbumTitle: ${json.AlbumTitle}`;
      if (json.MarketingBudget) {
        rowString += `, MarketingBudget: ${json.MarketingBudget}`;
      }
      console.log(rowString);
    });
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

replace

replace(keyVals, callback) returns Promise containing BasicResponse

Replace rows of data within this table.

Parameter

keyVals

(object or Array of object)

A map of names to values of data to insert into this table.

callback

Optional

BasicCallback

Callback function.

See also

Commit API Documentation

Returns

Promise containing BasicResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const row = {
  SingerId: 'Id3',
  Name: 'Joe West'
};

table.replace(row, function(err, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // Row replaced successfully.
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.replace(row)
  .then(function(data) {
    const apiResponse = data[0];
  });

update

update(keyVals, callback) returns Promise containing BasicResponse

Update rows of data within this table.

Parameter

keyVals

(object or Array of object)

A map of names to values of data to insert into this table.

callback

Optional

BasicCallback

Callback function.

See also

Commit API Documentation

Returns

Promise containing BasicResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const row = {
  SingerId: 'Id3',
  Name: 'Joe West'
};

table.update(row, function(err, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // Row updated successfully.
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.update(row)
  .then(function(data) {
    const apiResponse = data[0];
  });

Full example:

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

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// 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.');
  })
  .catch(err => {
    console.error('ERROR:', err);
  })
  .then(() => {
    // Close the database when finished.
    return database.close();
  });

upsert

upsert(keyVals, callback) returns Promise containing BasicResponse

Insert or update rows of data within this table.

Parameter

keyVals

(object or Array of object)

A map of names to values of data to insert into this table.

callback

Optional

BasicCallback

Callback function.

See also

Commit API Documentation

Returns

Promise containing BasicResponse 

Example

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();

const instance = spanner.instance('my-instance');
const database = instance.database('my-database');
const table = instance.database('Singers');

const row = {
  SingerId: 'Id3',
  Name: 'Joe West'
};

table.update(row, function(err, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // Row inserted or updated successfully.
});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.update(row)
  .then(function(data) {
    const apiResponse = data[0];
  });