@google-cloud/spanner 2.2.1 » Class: Table

Table

class

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

Constructor

Table

new Table(database, name)

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 = 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

createReadStream(query, options) returns ReadableStream

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

Parameter
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 = 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

delete(callback) returns Promise containing LongRunningOperationResponse

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

Wrapper around Database#updateSchema.

Parameter
callback	Optional LongRunningOperationCallback Callback function.

See also

Database#updateSchema

Throws

TypeError 

If any arguments are passed in.

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 = 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

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 = 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

drop(callback) returns Promise containing LongRunningOperationResponse

Drop the table.

Parameter
callback	Optional LongRunningOperationCallback Callback function.

See also

Table#delete

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 = 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

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 = 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:

View on GitHub

// 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 {
  database.close();
}

queue_

queue_(mutation)

This probably should be implemented as an abstract method, but it doesn't appear to be implemented in the Table class which extends it.

Parameter
mutation

Inherited from

TransactionRequest#queue_

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 = 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
// 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:

View on GitHub

// 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.
  database.close();
}

Reading stale data:

View on GitHub

// 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.
  database.close();
}

Reading data using an index:

View on GitHub

// 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:

View on GitHub

// "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

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 = 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

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 = 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:

View on GitHub

// 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

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 = database.table('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];
  });