Class Table (7.5.0)

Package

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

Constructors

(constructor)(database, name)

constructor(database: Database, name: string);

Properties

database

database: Database;

name

name: string;

Methods

create(schema, gaxOptions)

create(schema: Schema, gaxOptions?: CallOptions): Promise<CreateTableResponse>;

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.
  });

create(schema, callback)

create(schema: Schema, callback: CreateTableCallback): void;

create(schema, gaxOptions, callback)

create(schema: Schema, gaxOptions: CallOptions, callback: CreateTableCallback): void;

createReadStream(request, options)

createReadStream(request: ReadRequest, options?: TimestampBounds): PartialResultStream;

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(gaxOptions)

delete(gaxOptions?: CallOptions): Promise<DropTableResponse>;

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.
  });

delete(callback)

delete(callback: DropTableCallback): void;

delete(gaxOptions, callback)

delete(gaxOptions: CallOptions, callback: DropTableCallback): void;

deleteRows(keys, options)

deleteRows(keys: Key[], options?: DeleteRowsOptions | CallOptions): Promise<DeleteRowsResponse>;

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

deleteRows(keys, callback)

deleteRows(keys: Key[], callback: DeleteRowsCallback): void;

deleteRows(keys, options, callback)

deleteRows(keys: Key[], options: DeleteRowsOptions | CallOptions, callback: DeleteRowsCallback): void;

drop(gaxOptions)

drop(gaxOptions?: CallOptions): Promise<DropTableResponse>;

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.
  });

drop(callback)

drop(callback: DropTableCallback): void;

drop(gaxOptions, callback)

drop(gaxOptions: CallOptions, callback: DropTableCallback): void;

insert(rows, options)

insert(rows: object | object[], options?: InsertRowsOptions | CallOptions): Promise<InsertRowsResponse>;

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

insert(rows, callback)

insert(rows: object | object[], callback: InsertRowsCallback): void;

insert(rows, options, callback)

insert(rows: object | object[], options: InsertRowsOptions | CallOptions, callback: InsertRowsCallback): void;

read(request, options)

read(request: ReadRequest, options?: TimestampBounds): Promise<ReadResponse>;

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

read(request, callback)

read(request: ReadRequest, callback: ReadCallback): void;

read(request, options, callback)

read(request: ReadRequest, options: TimestampBounds, callback: ReadCallback): void;

replace(rows, options)

replace(rows: object | object[], options?: ReplaceRowsOptions | CallOptions): Promise<ReplaceRowsResponse>;

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

replace(rows, callback)

replace(rows: object | object[], callback: ReplaceRowsCallback): void;

replace(rows, options, callback)

replace(rows: object | object[], options: ReplaceRowsOptions | CallOptions, callback: ReplaceRowsCallback): void;

update(rows, options)

update(rows: object | object[], options?: UpdateRowsOptions | CallOptions): Promise<UpdateRowsResponse>;

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

update(rows, callback)

update(rows: object | object[], callback: UpdateRowsCallback): void;

update(rows, options, callback)

update(rows: object | object[], options: UpdateRowsOptions | CallOptions, callback: UpdateRowsCallback): void;

upsert(rows, options)

upsert(rows: object | object[], options?: UpsertRowsOptions | CallOptions): Promise<UpsertRowsResponse>;

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

upsert(rows, callback)

upsert(rows: object | object[], callback: UpsertRowsCallback): void;

upsert(rows, options, callback)

upsert(rows: object | object[], options: UpsertRowsOptions | CallOptions, callback: UpsertRowsCallback): void;