Database

class

Constructor

Database

new Database(name, options)

Create a Database object to interact with a Cloud Spanner database.

Parameter

name

string

Name of the database.

options

SessionPoolOptions

Session pool configuration options.

Example 

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();
const instance = spanner.instance('my-instance');
const database = instance.database('my-database');

Methods

batchTransaction

batchTransaction(identifier, options) returns BatchTransaction

Get a reference to a BatchTransaction object.

Parameter

identifier

TransactionIdentifier

The transaction identifier.

options

Optional

TransactionOptions

Transaction options.
See also
BatchTransaction#identifier to generate an identifier.
Returns

BatchTransaction 

A batch transaction object.

Example 

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

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

const transaction = database.batchTransaction({
  session: 'my-session',
  transaction: 'my-transaction',
  readTimestamp: 1518464696657
});

close

close(callback) returns Promise

Close the database connection and destroy all sessions associated with it.

Parameter

callback

Optional

CloseDatabaseCallback

Callback function.
Returns

Promise 

Example 

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

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

database.close(function(err) {
  if (err) {
    // Error handling omitted.
  }
});

//-
// In the event of a session leak, the error object will contain a
// `messages` field.
//-
database.close(function(err) {
  if (err && err.messages) {
    err.messages.forEach(function(message) {
      console.error(message);
    });
  }
});

create

create(options, callback) returns Promise containing CreateDatabaseResponse

Create a database.

Parameter

options

Optional

CreateDatabaseRequest

Configuration object.

callback

Optional

CreateDatabaseCallback

Callback function.
Returns

Promise containing CreateDatabaseResponse 

Example 

const Spanner = require('@google-cloud/spanner');
const spanner = new Spanner();
const instance = spanner.instance('my-instance');
const database = instance.database('my-database');

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

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

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

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

createBatchTransaction

createBatchTransaction(options, callback) returns Promise containing CreateTransactionResponse

Create a transaction that can be used for batch querying.

Parameter

options

Optional

TransactionOptions

Transaction options.

callback

Optional

CreateTransactionCallback

Callback function.
Returns

Promise containing CreateTransactionResponse 

createSession

createSession(options, callback) returns Promise containing CreateSessionResponse

Create a new session, which can be used to perform transactions that read and/or modify data.

Sessions can only execute one transaction at a time. To execute multiple concurrent read-write/write-only transactions, create multiple sessions. Note that standalone reads and queries use a transaction internally, and count toward the one transaction limit.

It is unlikely you will need to interact with sessions directly. By default, sessions are created and utilized for maximum performance automatically.

Wrapper around v1.SpannerClient#createSession.

Parameter

options

Optional

object

Configuration object.

callback

Optional

CreateSessionCallback

Callback function.
See also
v1.SpannerClient#createSession

CreateSession API Documentation

Returns

Promise containing CreateSessionResponse 

Example 

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

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

database.createSession(function(err, session, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // `session` is a Session object.
});

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

createTable

createTable(schema, callback) returns Promise containing CreateTableResponse

Create a table.

Wrapper around Database#updateSchema.

Parameter

schema

string

A DDL CREATE statement describing the table.

callback

Optional

CreateTableCallback

Callback function.
See also
Database#updateSchema
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 schema =
  'CREATE TABLE Singers (' +
  '  SingerId INT64 NOT NULL,' +
  '  FirstName STRING(1024),' +
  '  LastName STRING(1024),' +
  '  SingerInfo BYTES(MAX),' +
  ') PRIMARY KEY(SingerId)';

database.createTable(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.
//-
database.createTable(schema)
  .then(function(data) {
    const table = data[0];
    const operation = data[1];

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

delete

delete(callback) returns Promise containing BasicResponse

Delete the database.

Wrapper around v1.DatabaseAdminClient#dropDatabase.

Parameter

callback

Optional

BasicCallback

Callback function.
See also
v1.DatabaseAdminClient#dropDatabase

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

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

  // Database was deleted successfully.
});

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

exists

exists(callback) returns Promise containing DatabaseExistsResponse

Check if a database exists.

Parameter

callback

Optional

DatabaseExistsCallback

Callback function.
Returns

Promise containing DatabaseExistsResponse 

Example 

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

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

database.exists(function(err, exists) {});

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

get

get(options, callback) returns Promise containing GetDatabaseResponse

Get a database if it exists.

You may optionally use this to "get or create" an object by providing an object with autoCreate set to true. Any extra configuration that is normally required for the create method must be contained within this object as well.

Parameter

options

Optional

options

Configuration object.

Values in options have the following properties:

Parameter

autoCreate

Optional

boolean

Automatically create the object if it does not exist.

callback

Optional

GetDatabaseCallback

Callback function.
Returns

Promise containing GetDatabaseResponse 

Example 

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

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

database.get(function(err, database, apiResponse) {
  // `database.metadata` has been populated.
});

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

getMetadata

getMetadata(callback) returns Promise containing GetDatabaseMetadataResponse

Get the database's metadata.

Wrapper around v1.DatabaseAdminClient#getDatabase.

Parameter

callback

Optional

GetDatabaseMetadataCallback

Callback function.
See also
v1.DatabaseAdminClient#getDatabase

GetDatabase API Documentation

Returns

Promise containing GetDatabaseMetadataResponse 

Example 

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

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

database.getMetadata(function(err, metadata, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  // Database was deleted successfully.
});

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

getSchema

getSchema(callback) returns Promise containing GetSchemaResponse

Get this database's schema as a list of formatted DDL statements.

Wrapper around v1.DatabaseAdminClient#getDatabaseDdl.

Parameter

callback

Optional

GetSchemaCallback

Callback function.
See also
v1.DatabaseAdminClient#getDatabaseDdl

Data Definition Language (DDL)

GetDatabaseDdl API Documentation

Returns

Promise containing GetSchemaResponse 

Example 

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

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

database.getSchema(function(err, statements, apiResponse) {});

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

getSessions

getSessions(options, callback) returns Promise containing GetSessionsResponse

Geta a list of sessions.

Wrapper around v1.SpannerClient#listSessions

Parameter

options

Optional

GetSessionsRequest

Options object for listing sessions.

callback

Optional

GetSessionsCallback

Callback function.
See also
v1.SpannerClient#listSessions

ListSessions API Documentation

Returns

Promise containing GetSessionsResponse 

Example 

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

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

database.getSessions(function(err, sessions) {
  // `sessions` is an array of `Session` objects.
});

//-
// To control how many API requests are made and page through the results
// manually, set `autoPaginate` to `false`.
//-
function callback(err, sessions, nextQuery, apiResponse) {
  if (nextQuery) {
    // More results exist.
    database.getSessions(nextQuery, callback);
  }
}

database.getInstances({
  autoPaginate: false
}, callback);

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

getTransaction

getTransaction(options, callback) returns Promise containing GetTransactionResponse

Get a read/write ready Transaction object.

Wrapper around v1.SpannerClient#beginTransaction.

Parameter

options

Optional

TransactionOptions

Transaction options.

callback

Optional

GetTransactionCallback

Callback function.
See also
v1.SpannerClient#beginTransaction
Returns

Promise containing GetTransactionResponse 

Example 

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

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

database.getTransaction(function(err, transaction) {});

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

run

run(query, options, callback) returns Promise containing RunResponse

Execute a SQL statement on this database.

Wrapper around v1.SpannerClient#executeStreamingSql.

Parameter

query

(string or object)

A SQL query or query object. See an ExecuteSqlRequest object.

Values in query have the following properties:

Parameter

json

Optional

boolean

Receive the rows as serialized objects. This is the equivalent of calling toJSON() on each row.

jsonOptions

Optional

object

Configuration options for the serialized objects.

jsonOptions.wrapNumbers

Optional

boolean

Protect large integer values outside of the range of JavaScript Number.

params

Optional

object

A map of parameter name to values.

types

Optional

object

A map of parameter types.

options

Optional

DatabaseRunRequest

Transaction options.

callback

Optional

RunCallback

Callback function.
See also
v1.SpannerClient#executeStreamingSql

Query Syntax

ExecuteSql API Documentation

Returns

Promise containing RunResponse 

Example 

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

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

const query = 'SELECT * FROM Singers';

database.run(query, function(err, rows) {
  if (err) {
    // Error handling omitted.
  }

  const firstRow = rows[0];

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

//-
// Rows are returned as an array of object arrays. Each object has a `name`
// and `value` property. To get a serialized object, call `toJSON()`.
//-
database.run(query, function(err, rows) {
  if (err) {
    // Error handling omitted.
  }

  const firstRow = rows[0];

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

//-
// Alternatively, set `query.json` to `true`, and this step will be performed
// automaticaly.
//-
database.run(query, function(err, rows) {
  if (err) {
    // Error handling omitted.
  }

  const firstRow = rows[0];

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

//-
// The SQL query string can contain parameter placeholders. A parameter
// placeholder consists of '@' followed by the parameter name.
//-
const query = {
  sql: 'SELECT * FROM Singers WHERE name = @name',
  params: {
    name: 'Eddie Wilson'
  }
};

database.run(query, function(err, rows) {});

//-
// If you need to enforce a specific param type, a types map can be provided.
// This is typically useful if your param value can be null.
//-
const query = {
  sql: 'SELECT * FROM Singers WHERE name = @name AND id = @id',
  params: {
    id: spanner.int(8),
    name: null
  },
  types: {
    id: 'int64',
    name: 'string'
  }
};

database.run(query, function(err, rows) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
database.run(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);

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

Querying data with 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';
// const startTitle = 'Ardvark';
// const endTitle = 'Goo';

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

runStream

runStream(query, options) returns ReadableStream

Create a readable object stream to receive resulting rows from a SQL statement.

Wrapper around v1.SpannerClient#executeStreamingSql.

Parameter

query

(string or object)

A SQL query or query object. See an ExecuteSqlRequest object.

Values in query have the following properties:

Parameter

params

Optional

object

A map of parameter name to values.

types

Optional

object

A map of parameter types.

options

Optional

DatabaseRunRequest

Transaction options.
See also
v1.SpannerClient#executeStreamingSql

Query Syntax

ExecuteSql API Documentation

Returns

ReadableStream 

A readable stream that emits rows.

Example 

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

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

const query = 'SELECT * FROM Singers';

database.runStream(query)
  .on('error', function(err) {})
  .on('data', function(row) {
    // row = [
    //   {
    //     name: 'SingerId',
    //     value: '1'
    //   },
    //   {
    //     name: 'Name',
    //     value: 'Eddie Wilson'
    //   }
    // ]
  // ]
  })
  .on('end', function() {
    // All results retrieved.
  });

//-
// Rows are returned as an array of objects. Each object has a `name` and
// `value` property. To get a serialized object, call `toJSON()`.
//-
database.runStream(query)
  .on('error', function(err) {})
  .on('data', function(row) {
    // row.toJSON() = {
    //   SingerId: '1',
    //   Name: 'Eddie Wilson'
    // }
  })
  .on('end', function() {
    // All results retrieved.
  });

//-
// Alternatively, set `query.json` to `true`, and this step will be performed
// automaticaly.
//-
query.json = true;

database.runStream(query)
  .on('error', function(err) {})
  .on('data', function(row) {
    // row = {
    //   SingerId: '1',
    //   Name: 'Eddie Wilson'
    // }
  })
  .on('end', function() {
    // All results retrieved.
  });

//-
// The SQL query string can contain parameter placeholders. A parameter
// placeholder consists of '@' followed by the parameter name.
//-
const query = {
  sql: 'SELECT * FROM Singers WHERE name = @name',
  params: {
    name: 'Eddie Wilson'
  }
};

database.runStream(query)
  .on('error', function(err) {})
  .on('data', function(row) {})
  .on('end', function() {});

//-
// If you need to enforce a specific param type, a types map can be provided.
// This is typically useful if your param value can be null.
//-
const query = {
  sql: 'SELECT * FROM Singers WHERE name = @name',
  params: {
    name: 'Eddie Wilson'
  },
  types: {
    name: 'string'
  }
};

database.runStream(query)
  .on('error', function(err) {})
  .on('data', function(row) {})
  .on('end', function() {});

//-
// If you anticipate many results, you can end a stream early to prevent
// unnecessary processing and API requests.
//-
database.runStream(query)
  .on('data', function(row) {
    this.end();
  });

runTransaction

runTransaction(options, callback) returns Promise containing RunTransactionResponse

A transaction in Cloud Spanner is a set of reads and writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.

Note that Cloud Spanner does not support nested transactions. If a new transaction is started inside of the run function, it will be an independent transaction.

The callback you provide to this function will become the "run function". It will be executed with either an error or a Transaction object. The Transaction object will let you run queries and queue mutations until you are ready to Transaction#commit.

In the event that an aborted error occurs, we will re-run the runFn in its entirety. If you prefer to handle aborted errors for yourself please refer to Database#getTransaction.

For a more complete listing of functionality available to a Transaction, see the Transaction API documentation. For a general overview of transactions within Cloud Spanner, see Transactions from the official Cloud Spanner documentation.

Parameter

options

Optional

TransactionOptions

Transaction options.

callback

RunTransactionCallback

A function to execute in the context of a transaction.
See also

Transactions

Timestamp Bounds

Returns

Promise containing RunTransactionResponse 

Example 

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

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

database.runTransaction(function(err, transaction) {
  if (err) {
    // Error handling omitted.
  }

  // Run a transactional query.
  transaction.run('SELECT * FROM Singers', function(err, rows) {
    if (err) {
      // Error handling omitted.
    }

    // Queue a mutation (note that there is no callback passed to `insert`).
    transaction.insert('Singers', {
      SingerId: 'Id3b',
      Name: 'Joe West'
    });

    // Commit the transaction.
    transaction.commit(function(err) {
      if (!err) {
        // Transaction committed successfully.
      }
    });
  });
});

//-
// For read-only transactions, use the `transaction.end()` function to
// release the transaction.
//-
const options = {
  readOnly: true,
  strong: true
};

database.runTransaction(options, function(err, transaction) {
  if (err) {
    // Error handling omitted.
  }

  transaction.run('SELECT * FROM Singers', function(err, rows) {
    if (err) {
      // Error handling omitted.
    }

    // End the transaction. Note that no callback is provided.
    transaction.end();
  });
});

Read-only transaction:

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

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

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

/**
 * 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 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
      const rows = results[0].map(row => row.toJSON());
      secondBudget = rows[0].MarketingBudget;
      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
      const rows = results[0].map(row => row.toJSON());
      firstBudget = rows[0].MarketingBudget;
      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(),
        },
      ]);
    })
    .then(() => {
      // Commits the transaction and send the changes to the database
      return transaction.commit();
    })
    .then(() => {
      console.log(
        `Successfully executed read-write transaction to transfer ${transferAmount} from Album 2 to Album 1.`
      );
    })
    .catch(err => {
      console.error('ERROR:', err);
    })
    .then(() => {
      // Close the database when finished.
      return database.close();
    });
});

table

table(name) returns Table

Get a reference to a Table object.

Parameter

name

string

The name of the table.
Throws

Error 

If a name is not provided.

Returns

Table 

A Table object.

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

updateSchema

updateSchema(statements, callback) returns Promise containing LongRunningOperationResponse

Update the schema of the database by creating/altering/dropping tables, columns, indexes, etc.

This method immediately responds with an Operation object. Register event handlers for the "error" and "complete" events to see how the operation finishes. Follow along with the examples below.

Wrapper around v1.DatabaseAdminClient#updateDatabaseDdl.

Parameter

statements

(string, Array of string, or object)

An array of database DDL statements, or an UpdateDatabaseDdlRequest object.

callback

Optional

LongRunningOperationCallback

Callback function.
See also
v1.DatabaseAdminClient#updateDatabaseDdl

Data Definition Language (DDL)

Schema and Data Model

UpdateDatabaseDdl API Documentation

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 statements = [
  'CREATE TABLE Singers (' +
  '  SingerId INT64 NOT NULL,' +
  '  FirstName STRING(1024),' +
  '  LastName STRING(1024),' +
  '  SingerInfo BYTES(MAX),' +
  ') PRIMARY KEY(SingerId)'
];

database.updateSchema(statements, function(err, operation, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  operation
    .on('error', function(err) {})
    .on('complete', function() {
      // Database schema updated successfully.
    });
});

//-
// If the callback is omitted, we'll return a Promise.
//-
database.updateSchema(statements)
  .then(function(data) {
    const operation = data[0];
    return operation.promise();
  })
  .then(function() {
    // Database schema updated successfully.
  });

Adding a column:

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

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

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