Table - Documentation

Table

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

Constructor

new Table(database, name)

Parameters:

Name	Type	Description
`database`	Database	Database instance.
`name`	string	Name of the table.

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');

Members

database

The Database instance of this Table instance.

name

The name of this table.

Methods

create(schema, gaxOptionsopt, callbackopt) → {Promise.<CreateTableResponse>}

Create a table.

Parameters:

Name	Type	Attributes	Description
`schema`	string		See Database#createTable.
`gaxOptions`	object	<optional>	Call options. See CallOptions for more details.
`callback`	CreateTableCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<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 = database.table('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(query, optionsopt) → {PartialResultStream}

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

Parameters:

Name	Type	Attributes	Description
`query`	ReadRequest		Configuration object, describing what to read from the table..
`options`	TimestampBounds	<optional>	Transaction options.

Returns:

Type	Description
PartialResultStream	A readable stream that emits rows.

See:

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('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(gaxOptionsopt, callbackopt) → {Promise.<DropTableResponse>}

Delete the table. Not to be confused with Table#deleteRows.

Wrapper around Database#updateSchema.

Parameters:

Name	Type	Attributes	Description
`gaxOptions`	object	<optional>	Call options. See CallOptions for more details.
`callback`	DropTableCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<DropTableResponse>

See:

Database#updateSchema

Throws:

If any arguments are passed in.
Type TypeError

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('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(keys, optionsopt, callbackopt) → {Promise.<DeleteRowsResponse>}

Delete rows from this table.

Parameters:

Name	Type	Attributes	Description
`keys`	array		The keys for the rows to delete. If using a composite key, provide an array within this array. See the example below.
`options`	DeleteRowsOptions \| CallOptions	<optional>	Options for configuring the request. See CallOptions for more details.
`callback`	DeleteRowsCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<DeleteRowsResponse>

See:

Commit API Documentation

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('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];
  });

drop(gaxOptionsopt, callbackopt) → {Promise.<DropTableResponse>}

Drop the table.

Parameters:

Name	Type	Attributes	Description
`gaxOptions`	object	<optional>	Request configuration options. See CallOptions for more details.
`callback`	DropTableCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<DropTableResponse>

See:

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('Singers');

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

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

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

insert(rows, optionsopt, callbackopt) → {Promise.<InsertRowsResponse>}

Insert rows of data into this table.

Parameters:

Name	Type	Attributes	Description
`rows`	object \| Array.<object>		A map of names to values of data to insert into this table.
`options`	InsertRowsOptions \| CallOptions	<optional>	Options for configuring the request. See CallOptions for more details.
`callback`	InsertRowsCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<InsertRowsResponse>

See:

Commit API Documentation

Examples

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('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
  try {
    await 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'},
    ]);

    await 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'},
    ]);

    console.log('Inserted data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    await database.close();
  }

read(query, options, callbackopt) → {Promise.<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.

Parameters:

Name	Type	Attributes	Description
`query`	ReadRequest		Configuration object, describing what to read from the table.
`options`	TimestampBounds		Transaction options.
`callback`	TableReadCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<TableReadResponse>

Examples

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('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
// automatically.
//-
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 rows = 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,
    },
  };

  try {
    const [rows] = await albumsTable.read(query);

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(
        `SingerId: ${json.SingerId}, AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`
      );
    });
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await 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,
  };

  try {
    const [rows] = await albumsTable.read(query, options);

    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);
  } finally {
    // Close the database when finished.
    await 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
  try {
    const [rows] = await albumsTable.read(query);

    rows.forEach(row => {
      const json = row.toJSON();
      console.log(`AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`);
    });
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    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
  try {
    const [rows] = await albumsTable.read(query);

    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);
  } finally {
    // Close the database when finished.
    database.close();
  }

replace(rows, optionsopt, callbackopt) → {Promise.<ReplaceRowsResponse>}

Replace rows of data within this table.

Parameters:

Name	Type	Attributes	Description
`rows`	object \| Array.<object>		A map of names to values of data to insert into this table.
`options`	ReplaceRowsOptions \| CallOptions	<optional>	Options for configuring the request. See CallOptions for more details.
`callback`	ReplaceRowsCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<ReplaceRowsResponse>

See:

Commit API Documentation

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('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(rows, optionsopt, callbackopt) → {Promise.<UpdateRowsResponse>}

Update rows of data within this table.

Parameters:

Name	Type	Attributes	Description
`rows`	object \| Array.<object>		A map of names to values of data to insert into this table.
`options`	UpdateRowsOptions \| CallOptions	<optional>	Options for configuring the request. See CallOptions for more details.
`callback`	UpdateRowsCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<UpdateRowsResponse>

See:

Commit API Documentation

Examples

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('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');

  try {
    await albumsTable.update([
      {SingerId: '1', AlbumId: '1', MarketingBudget: '100000'},
      {SingerId: '2', AlbumId: '2', MarketingBudget: '500000'},
    ]);
    console.log('Updated data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }

upsert(rows, optionsopt, callbackopt) → {Promise.<UpsertRowsResponse>}

Insert or update rows of data within this table.

Parameters:

Name	Type	Attributes	Description
`rows`	object \| Array.<object>		A map of names to values of data to insert into this table.
`options`	UpsertRowsOptions \| CallOptions	<optional>	Options for configuring the request. See CallOptions for more details.
`callback`	UpsertRowsCallback	<optional>	Callback function.

Returns:

Type	Description
Promise.<UpsertRowsResponse>

See:

Commit API Documentation

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('Singers');

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

table.upsert(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.upsert(row)
  .then(function(data) {
    const apiResponse = data[0];
  });