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.

Create a package.json manifest file using the following command:

Next, install the MySQL Node.js Connector from npm:

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.

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).

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:

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.

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:

Running the app should yield the following output:

Retrieving all documents

You can use the following code to retrieve all the existing documents in the collection.

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.

Finding documents with a given criteria

You can narrow down the results by specifying a query criteria like the following:

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.

Update the main function to use the updateDocument method like the following:

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.

To test this behavior, call the removeDocument method in the main function.

Creating secondary indexes

You can create a secondary index for any property of a document in a collection using the following code:

Just like before, update the main function to use the createIndex method.

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: