Database

Database

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

Constructor

new Database(name, options)

Parameters:
Name Type Description
name string

Name of the database.

options SessionPoolOptions | SessionPoolInterface

Session pool configuration options or custom pool interface.

Source:
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(identifier, optionsopt) → {BatchTransaction}

Get a reference to a BatchTransaction object.

Parameters:
Name Type Attributes Description
identifier TransactionIdentifier

The transaction identifier.

options TransactionOptions <optional>

Transaction options.

Source:
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 transaction = database.batchTransaction({
  session: 'my-session',
  transaction: 'my-transaction',
  readTimestamp: 1518464696657
});

close(callbackopt) → {Promise}

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

Parameters:
Name Type Attributes Description
callback CloseDatabaseCallback <optional>

Callback function.

Source:
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(optionsopt, callbackopt) → {Promise.<CreateDatabaseResponse>}

Create a database.

Parameters:
Name Type Attributes Description
options CreateDatabaseRequest <optional>

Configuration object.

callback CreateDatabaseCallback <optional>

Callback function.

Source:
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(optionsopt, callbackopt) → {Promise.<CreateTransactionResponse>}

Create a transaction that can be used for batch querying.

Parameters:
Name Type Attributes Description
options TransactionOptions <optional>

Transaction options.

callback CreateTransactionCallback <optional>

Callback function.

Source:

createSession(optionsopt, callbackopt) → {Promise.<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.

Parameters:
Name Type Attributes Description
options object <optional>

Configuration object.

callback CreateSessionCallback <optional>

Callback function.

Source:
See:
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(schema, callbackopt) → {Promise.<CreateTableResponse>}

Create a table.

Wrapper around Database#updateSchema.

Parameters:
Name Type Attributes Description
schema string

A DDL CREATE statement describing the table.

callback CreateTableCallback <optional>

Callback function.

Source:
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 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(callbackopt) → {Promise.<BasicResponse>}

Delete the database.

Wrapper around v1.DatabaseAdminClient#dropDatabase.

Parameters:
Name Type Attributes Description
callback BasicCallback <optional>

Callback function.

Source:
See:
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) {
  const apiResponse = data[0];
});

exists(callbackopt) → {Promise.<DatabaseExistsResponse>}

Check if a database exists.

Parameters:
Name Type Attributes Description
callback DatabaseExistsCallback <optional>

Callback function.

Source:
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(optionsopt, callbackopt) → {Promise.<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.

Parameters:
Name Type Attributes Description
options options <optional>

Configuration object.

Properties
Name Type Attributes Default Description
autoCreate boolean <optional>
false

Automatically create the object if it does not exist.

callback GetDatabaseCallback <optional>

Callback function.

Source:
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) {
  const database = data[0];
  const apiResponse = data[0];
});

getMetadata(callbackopt) → {Promise.<GetDatabaseMetadataResponse>}

Get the database's metadata.

Wrapper around v1.DatabaseAdminClient#getDatabase.

Parameters:
Name Type Attributes Description
callback GetDatabaseMetadataCallback <optional>

Callback function.

Source:
See:
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(callbackopt) → {Promise.<GetSchemaResponse>}

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

Wrapper around v1.DatabaseAdminClient#getDatabaseDdl.

Parameters:
Name Type Attributes Description
callback GetSchemaCallback <optional>

Callback function.

Source:
See:
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(optionsopt, callbackopt) → {Promise.<GetSessionsResponse>}

Gets a list of sessions.

Wrapper around v1.SpannerClient#listSessions

Parameters:
Name Type Attributes Description
options GetSessionsRequest <optional>

Options object for listing sessions.

callback GetSessionsCallback <optional>

Callback function.

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

getSnapshot(optionsopt, callbackopt) → {Promise.<GetSnapshotResponse>}

Get a read only Snapshot transaction.

Wrapper around v1.SpannerClient#beginTransaction.

NOTE: When finished with the Snapshot, Snapshot#end should be called to release the underlying Session. Failure to do could result in a Session leak.

Parameters:
Name Type Attributes Description
options TimestampBounds <optional>

Timestamp bounds.

callback GetSnapshotCallback <optional>

Callback function.

Source:
See:
Examples
const {Spanner} = require('@google-cloud/spanner');
const spanner = new Spanner();

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

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

  // Should be called when finished with Snapshot.
  transaction.end();
});

If the callback is omitted, we'll return a Promise.

database.getSnapshot().then(function(data) {
  const transaction = data[0];
});

include:samples/transaction.js

region_tag:spanner_read_only_transaction
Read-only transaction:

getTransaction(callbackopt) → {Promise.<GetTransactionResponse>}

Get a read/write ready Transaction object.

NOTE: In the event that you encounter an error while reading/writing, if you decide to forgo calling Transaction#commit or Transaction#rollback, then you need to call Transaction#end to release the underlying Session object. Failure to do could result in a Session leak.

Wrapper around v1.SpannerClient#beginTransaction.

Parameters:
Name Type Attributes Description
callback GetTransactionCallback <optional>

Callback function.

Source:
See:
Examples
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(query, optionsopt, callbackopt) → {Promise.<RunResponse>}

Execute a SQL statement on this database.

Wrapper around v1.SpannerClient#executeStreamingSql.

Parameters:
Name Type Attributes Description
query string | ExecuteSqlRequest

A SQL query or ExecuteSqlRequest object.

options TimestampBounds <optional>

Snapshot timestamp bounds.

callback RunCallback <optional>

Callback function.

Source:
See:
Examples
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
// automatically.
//-
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];
});

include:samples/crud.js

region_tag:spanner_query_data
Full example:

include:samples/indexing.js

region_tag:spanner_query_data_with_index
Querying data with an index:

runPartitionedUpdate(query, callbackopt) → {Promise.<RunUpdateResponse>}

Partitioned DML transactions are used to execute DML statements with a different execution strategy that provides different, and often better, scalability properties for large, table-wide operations than DML in a Transaction transaction. Smaller scoped statements, such as an OLTP workload, should prefer using Transaction transactions.

Parameters:
Name Type Attributes Description
query string | object

A DML statement or ExecuteSqlRequest object.

Properties
Name Type Attributes Description
params object <optional>

A map of parameter name to values.

types object <optional>

A map of parameter types.

callback RunUpdateCallback <optional>

Callback function.

Source:
See:

runStream(query, optionsopt) → {ReadableStream}

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

Wrapper around v1.SpannerClient#executeStreamingSql.

Parameters:
Name Type Attributes Description
query string | ExecuteSqlRequest

A SQL query or ExecuteSqlRequest object.

options TimestampBounds <optional>

Snapshot timestamp bounds.

Source:
See:
Fires:
  • PartialResultStream#event:response
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
// automatically.
//-
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(optionsopt, callback)

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.

NOTE: In the event that you encounter an error while reading/writing, if you decide to forgo calling Transaction#commit or Transaction#rollback, then you need to call Transaction#end to release the underlying Session object. Failure to do could result in a Session leak.

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.

If you would like to run a transaction and receive a promise or use async/await, use Database#runTransactionAsync.

Parameters:
Name Type Attributes Description
options RunTransactionOptions <optional>

Transaction runner options.

callback RunTransactionCallback

A function to execute in the context of a transaction.

Source:
See:
Examples
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.
      }
    });
  });
});

include:samples/transaction.js

region_tag:spanner_read_write_transaction
Read-write transaction:

(async) runTransactionAsync(optionsopt, callback) → {Promise}

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 async function you provide will become the "run function". It will be executed with 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.

NOTE: In the event that you encounter an error while reading/writing, if you decide to forgo calling Transaction#commit or Transaction#rollback, then you need to call Transaction#end to release the underlying Session object. Failure to do could result in a Session leak.

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.

Parameters:
Name Type Attributes Description
options RunTransactionOptions <optional>

Transaction runner options.

callback AsyncRunTransactionCallback

A function to execute in the context of a transaction.

Source:
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 data = await database.runTransactionAsync(async (transaction) => {
  const [rows] = await transaction.run('SELECT * FROM MyTable');
  const data = rows.map(row => row.thing);

  await transaction.commit();
  return data;
});

session(nameopt) → {Session}

Create a Session object.

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

Parameters:
Name Type Attributes Description
name string <optional>

The name of the session. If not provided, it is assumed you are going to create it.

Source:
Example
var session = database.session('session-name');

table(name) → {Table}

Get a reference to a Table object.

Parameters:
Name Type Description
name string

The name of the table.

Source:
Throws:

If a name is not provided.

Type
Error
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(statements, callbackopt) → {Promise.<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.

Parameters:
Name Type Attributes Description
statements string | Array.<string> | object

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

callback LongRunningOperationCallback <optional>

Callback function.

Source:
See:
Examples
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.
  });

include:samples/schema.js

region_tag:spanner_add_column
Adding a column:

include:samples/indexing.js

region_tag:spanner_create_index
Creating an index:

include:samples/indexing.js

region_tag:spanner_create_storing_index
Creating a storing index: