MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
MySQL Document Store CRUD Quick Start

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.

  1. Download (if you have not yet) MySQL 8.0
  2. 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: