Cloud Spanner And Node.js
What is Google Cloud Spanner ?
From the official documentation of Google Cloud Spanner — Cloud Spanner is a fully managed, mission-critical, relational database service that offers transactional consistency at global scale, automatic, synchronous replication for high availability, and support for two SQL dialects: Google Standard SQL (ANSI 2011 with extensions) and PostgreSQL.
Schema and Data Model
Cloud spanner databases that contain more than one table. Tables might looks like relational database table and have rows, columns and primary key.
Parent Child relationships between tables can be defined two ways — table interleaving and foreign keys. Basically table interleaving means the child table primary key includes the primary key of parent table. The reason of doing this is that it improves the performance. For example If you have customer table and invoices table, You have to fetch invoices of customer more frequently then invoices table (child)can be interleaved with customers table (parent).
Choosing a primary key — Primary key identifies the unique row. Spanner does not support auto-increment of primary keys. The reason of doing this is it create hotspot in table and results to more numbers of read write request to single split which decrease the performance. Ways to create primary keys -
1. Logic To create primary keys that are not sequential like this 1,2,3,…, you might use timestamp with it.
2. By Using UUID version 4 (Universally Unique Identifier) — for using in javascript project you can install it from node package manager, here is the quick link for documentation— https://www.npmjs.com/package/uuidv4
3. Bit Reversed sequential values
4. Swap the order of the columns in the primary key.
Database Split — Cloud Spanner divides your data into chunks called “splits”, where individual splits can move independently from each other and get assigned to different servers, which can be in different physical locations. A split holds a range of contiguous rows. The start and end keys of this range are called “split boundaries”. Cloud Spanner automatically adds and removes split boundaries based on size and/or load, which changes the number of splits in the database.
Getting Started In Node.js
- Connecting node.js project with cloud spanner
// 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
const instance = spanner.instance(instanceId);
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted to strings before being inserted as INT64s
const request = {
schema: [
`CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)`,
`CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,
],
};
// Creates a database
const [database, operation] = await instance.createDatabase(
databaseId,
request
);
console.log(`Waiting for operation on ${database.id} to complete...`);
await operation.promise();
console.log(`Created database ${databaseId} on instance ${instanceId}.`);Here is quick link to get project id, instance id and database id — https://cloud.google.com/spanner/docs/getting-started/nodejs
How to Insert data into table
// 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);
database.runTransaction(async (err, transaction) => {
if (err) {
console.error(err);
return;
}
try {
const [rowCount] = await transaction.runUpdate({
sql: `INSERT Singers (SingerId, FirstName, LastName) VALUES
(12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw')`,
});
console.log(`${rowCount} records inserted.`);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});How to update data in table In Google Cloud Spanner
// This sample transfers 200,000 from the MarketingBudget field
// of the second Album to the first Album, as long as the second
// Album has enough money in its budget. 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;
database.runTransaction((err, transaction) => {
if (err) {
console.error(err);
return;
}
let firstBudget, secondBudget;
const queryOne = `SELECT MarketingBudget FROM Albums
WHERE SingerId = 2 AND AlbumId = 2`;
const queryTwo = `SELECT MarketingBudget FROM Albums
WHERE SingerId = 1 AND AlbumId = 1`;
Promise.all([
// Reads the second album's budget
transaction.run(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 large enough
if (secondBudget < transferAmount) {
throw new Error(
`The second album's budget (${secondBudget}) is less than the transfer amount (${transferAmount}).`
);
}
}),
// Reads the first album's budget
transaction.run(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(() => {
// Transfers the budgets between the albums
console.log(firstBudget, secondBudget);
firstBudget += transferAmount;
secondBudget -= transferAmount;
console.log(firstBudget, secondBudget);
// Updates the database
// Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they
// must be converted (back) to strings before being inserted as INT64s.
return transaction
.runUpdate({
sql: `UPDATE Albums SET MarketingBudget = @Budget
WHERE SingerId = 1 and AlbumId = 1`,
params: {
Budget: firstBudget,
},
})
.then(() =>
transaction.runUpdate({
sql: `UPDATE Albums SET MarketingBudget = @Budget
WHERE SingerId = 2 and AlbumId = 2`,
params: {
Budget: secondBudget,
},
})
);
})
.then(() => {
// Commits the transaction and send the changes to the database
return transaction.commit();
})
.then(() => {
console.log(
`Successfully executed read-write transaction using DML to transfer ${transferAmount} from Album 2 to Album 1.`
);
})
.then(() => {
// Closes the database when finished
database.close();
});
});Note:- In update query in where clause always give primary key so that write transaction to be efficient
Also for read optimisation always give primary key in where clause
Thanks for Reading :)
If You like my article on cloud spanner, follow me for more articles and suggestions and comments are welcome.
