Database
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 |
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 |
The transaction identifier. |
options |
Optional |
- See also
- BatchTransaction#identifier to generate an identifier.
- Returns
-
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 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 Configuration object. |
callback |
Optional 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 |
callback |
Optional 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 Callback function. |
- See also
- v1.SpannerClient#createSession
- 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 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 Callback function. |
- See also
- v1.DatabaseAdminClient#dropDatabase
- 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 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
|
||||
callback |
Optional 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 Callback function. |
- See also
- v1.DatabaseAdminClient#getDatabase
- 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 Callback function. |
- See also
- v1.DatabaseAdminClient#getDatabaseDdl
- 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 Options object for listing sessions. |
callback |
Optional Callback function. |
- See also
- v1.SpannerClient#listSessions
- 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 |
callback |
Optional 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
|
||||||||||||
options |
Optional |
||||||||||||
callback |
Optional Callback function. |
- See also
- v1.SpannerClient#executeStreamingSql
- 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:
Querying data with an index:
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
|
||||||
options |
Optional |
- See also
- v1.SpannerClient#executeStreamingSql
- 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 |
callback |
A function to execute in the context of a transaction. |
- See also
- 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:
Read-write transaction:
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
-
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
|
callback |
Optional Callback function. |
- See also
- v1.DatabaseAdminClient#updateDatabaseDdl
- 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:
Creating an index:
Creating a storing index: