This post serves as a quick start guide on how to use the MySQL Document Store with the official Node.js connector. It mainly highlights how easy it is to leverage CRUD-style operations and getting up and running with the X DevAPI.
Before you jump in
Make sure you have Node.js 7.6.0 (or higher) and MySQL 8.0.11 (or higher) installed on your machine.
Setting up your Node.js project
First, using the command line, let’s start by creating a directory for our sample application.
$ mkdir myproject $ cd myproject
Create a package.json manifest file using the following command:
$ npm init -y
Next, install the MySQL Node.js Connector from npm:
$ npm install --save-exact @mysql/xdevapi
Starting the MySQL Server
For additional tips on how to install and secure your MySQL server, check the documentation.
- Download (if you have not yet) MySQL 8.0
- Install and set up a basic (and insecure) server process.
$ mysqld --initialize-insecure $ mysqld
We are using –initialize-insecure here for the sake of simplicity, however, make sure you use –initialize (and set up users and privileges accordingly) on production environments and/or “real-world” scenarios.
Connecting to MySQL
Create a new app.js file and write the following code for establishing a connection to the MySQL server using the myproject database/schema (which will be created by default).
const mysqlx = require('@mysql/xdevapi');
const assert = require('assert');
// Database/Schema Name
const dbName = 'myproject';
// Connection URL
const url = `mysqlx://root@localhost:33060/${dbName}`;
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
main();
The getSession method should return a JavaScript Promise which resolves with a Session object containing the connection details or fails with a Node.js Error if the connection could not be established.
Run the app using the following command:
$ node app.js
You should be able to see a Successful server connection message printed in the console.
Inserting documents
Use the following code to create (or re-use if it exists) documents collection and add three new documents to it.
const insertDocuments = async function (db) {
// Create or re-use the collection
const collection = await db.createCollection('documents', { ReuseExistingObject: true });
// Add some documents
const result = await collection.add([{a : 1}, {a : 2}, {a : 3}]).execute();
assert.equal(3, result.getAffectedItemsCount());
console.log('Inserted 3 documents into the collection');
return result;
};
The add method returns a Promise which resolves with a Result object containing details such as:
- the number of items affected by the operation
- a list of document _ids auto-generated by the server
- any server-side warnings resulting from the operation
Add an insertDocuments call in the existing main function like the following:
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
await insertDocuments(db);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
Running the app should yield the following output:
Successful server connection Inserted 3 documents into the collection
Retrieving all documents
You can use the following code to retrieve all the existing documents in the collection.
const findDocuments = async function (db) {
const docs = [];
// Get the local collection instance
const collection = db.getCollection('documents');
// Get all documents from the collection
await collection.find().execute(function (doc) {
docs.push(doc);
});
console.log('Found the following documents');
console.log(docs);
return docs;
};
To collect and process records from the result set, you should provide a callback in the execute method.
Use the findDocuments method in the main function.
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
await insertDocuments(db);
await findDocuments(db);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
Finding documents with a given criteria
You can narrow down the results by specifying a query criteria like the following:
const findDocuments = async function (db) {
const docs = [];
// Get the local collection instance
const collection = db.getCollection('documents');
// Find documents based on a specific criteria
await collection.find('a = 3').execute(function (doc) {
docs.push(doc);
});
console.log('Found the following documents');
console.log(docs);
return docs;
};
The result set will now include just the documents containing ‘a’: 3.
Updating documents
To modify an existing document i.e. updating specific properties or adding new ones, you can also provide a criteria (or true to modify all) for the operation.
const updateDocument = async function (db) {
// Get the local collection instance
const collection = db.getCollection('documents');
// Update document with a equal to 2, set b equal to 1
const result = await collection.modify('a = 2').set('b', 1).execute();
assert.equal(1, result.getAffectedItemsCount());
console.log('Updated the document with the field a equal to 2');
return result;
};
Update the main function to use the updateDocument method like the following:
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
await insertDocuments(db);
await updateDocument(db);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
Removing documents from a collection can also be done based on a given criteria (or true to remove all). The following code removes the document where the field a is greater than 2.
const removeDocument = async function (db) {
// Get the local collection instance
const collection = db.getCollection('documents');
// Delete document where a is greater than 2
const result = await collection.remove('a > 2').execute()
assert.equal(1, result.getAffectedItemsCount());
console.log('Removed the document with the field a greater than 2');
return result;
};
To test this behavior, call the removeDocument method in the main function.
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
await insertDocuments(db);
await updateDocument(db);
await removeDocument(db);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
Creating secondary indexes
You can create a secondary index for any property of a document in a collection using the following code:
const createIndex = async function (db) {
// Get the local collection instance
const collection = db.getCollection('documents');
// Create the index
await collection.createIndex('idx_a', {fields: [{field: '$.a', type: 'INT'}]})
console.log('Created an INT index idx_a for the field a');
};
Just like before, update the main function to use the createIndex method.
const main = async function () {
try {
// Establish the server connection
const session = await mysqlx.getSession(url);
console.log('Successful server connection');
const db = session.getSchema(dbName);
await insertDocuments(db);
await createIndex(db);
session.close();
} catch (err) {
console.error(err.stack);
process.exit(1);
}
};
Wrapping up
This is just an overview of some of the features and API methods for taping into the MySQL document store using the Node.js connector and the X Dev API. For additional details, check the following links: