Develop by Example – Document Store: Working with Express.js, AngularJS and Node.js

In previous blog posts we explained how to perform certain actions in a MySQL database set up as a document store using Connector/Node.js. In this blog post we are going to use some of the examples covered to explain how to start working with an application created with Express.js, AngularJS, Node.js, and MySQL Connector/Node.js.

Required

Optional

Before we start we need to install MySQL Server, Node.js and download MySQL Connector/Node.js.

Configuring MySQL Server

If you are using Windows and you installed MySQL Server using the MySQL Installer you might have already enabled the MySQL X Protocol plugin or you can enable it through a re-configuration of the MySQL Server, for more details go to the Plugins and Extensions section in the official documentation.

If you are not using windows or the MySQL Installer, once MySQL Server is installed and running we need to enable the X Plugin to be able to use MySQL as a document store.

To enable the X Plugin in the MySQL server using the MySQL command-line client you need to use the root account or an account with INSERT privilege on the mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Note: Replace mysqlx.so with mysqlx.dll for Windows.

Please refer to the official documentation for more information about setting up MySQL as a document store.

Installing and using Express

After Node.js is also installed we need to open a terminal and navigate to the path where we want to create our project. Once we are in the desired path, we are going to install the Express application generator, which will help us to create the skeleton for our application. To install it, run the following command in the terminal:

$ npm install express-generator g

The -g flag specifies that the package will be installed globally.

Now we are going to create the skeleton for our application by running the following command the in terminal:

$ express mysql-news --ejs

The –ejs flag is to specify that we are going to use EJS as our template language to use standard HTML instead of Jade, which is the default view engine used by Express. The name for our application was specified as mysql-news. After running the command, we are going to see a folder with the same name as our application with the following structure:

mysql-news/
├───app.js
├───package.json
├───bin/
└─www
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Now we need to install the dependencies of the template created. Run the following command in the terminal at the path level were our application folder were created:

$ cd mysql-news && npm install

To start the newly created application, run the following command in the terminal:

$ npm start

To verify that our application is running, open a browser and type: http://localhost:3000.
If everything it is ok, we will be able to see a webpage with the text: “Welcome to Express”

Installing MySQL Connector/Node.js

In our application folder copy the MySQL Connector/Node.js tar.gz file that you may have downloaded previously. Once it has copied, run the following command to add the package to our application:

$ npm install mysql-connector-nodejs-1.0.4.tar.gz

Note: verify that the version of the file downloaded matches the version of the command.

Once MySQL Connector/Node.js is installed, we can navigate to the path /node_modules/@mysql/xdevapi to see where it is located.

Installing AngularJS and Angular-Route

In this blog post we are going to use AngularJS and the Angular-Route module both using the 1.5.8 version. To install them, run the following command:

$ npm install angular@1.5.8 angular-route@1.5.8

Once the packages are downloaded and installed in the node_modules folder we are going to copy the folders angular and angular-route inside our public/javascripts folder.

Note: to avoid doing this manually you can install Bower, use the CDN for the AngularJS modules, or any other alternative.

Optional Software

Now just the optional software is left. If you want a visual representation of the schema and collections in the application, download and install MySQL Workbench. And if you want to add a boost to the front-end development, install Bootstrap. In this blog post we are using just the CDN for the CSS, but you can install the complete package.

The folder structure

The last thing before we start working on the code of our application is to create two folders in the root path of our application: models and data. In the end, our application will have something like the following structure:
mysql-news/
├───app.js
├───mysql-connector-nodejs-1.0.4.tar.gz
├───package.json
├───bin/
└─www
├───data/
├───models/
├───node_modules/
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Building the application

Uploading initial data

As you may have guessed by its name, this application will display news about MySQL. We will have the ability to create new posts or add comments to the news items that are already in the database.

Now we can start building out our application. The first thing that we are going to do is to add a JSON file in the data folder. We are going to name it news.json or you can choose another name. Copy and paste the following data to the file:

This initial data will help us to visualize our progress. To continue we need to update our application to create our schema and collection where we are going to upload the initial data.

Open the www file that it is inside the bin folder. This file contains the configuration for the web server that will host our application. The first thing that we are going to add is a function that creates our schema, our collection, and then upload the data to the collection. Add the following code at the end of the file:

In the previous code we define the configureDataBase function where we create a session to our local instance of MySQL server. Once we have the session we verify if the schema mysqlNews exists. If not, we create it and the news collection. Finally, we load the initial data into the news collection.

We have the function that initializes our schema and now we need to add another function that calls it just one time when the application starts. In the www file you will find a section that creates the http server below the line:

var server = http.createServer(app);

We are going to add the following code below that line:

In the previous code first we load our initial data into the initialData variable as well as the xdevapi module into the mysql variable. These variables are used by the configureDataBase function. The variables need to be defined before the function is called. Then we create an instance of an EventEmitter, and we configure the event that is in charge of calling the function that creates our schema and collection.

To complete the configuration, we just need to add the following lines inside the initializeDatabase function:

Note: the lines should be below our initializeDatabase function, verify that are not duplicated

Since we are going to load some data when the application starts, we need to first configure our database before the server accepts connections, that is the reason we are moving the above lines inside our function. In the end, our function should look like this:

Now that we have the code that configures our schema and loads some initial data, it is time to create the function that retrieves the data from the collection.


Getting the data

In our model folder we are going to add a new file called news.js. This module will contain all the methods used to perform actions in our collections. The first method that we are going to add to this module is the one that gets the documents from our collection.

First we are going to define two variables, one to load the MySQL xdevapi module and other one to store the configuration used to create connections to our server.

Now we are going to create the method that returns all the documents stored in our collection:

First we added the method getAll to the exports object of our module and then we call the getSession method to create a connection to our server. Once we have the session we get the schema and the collection where our documents are stored. We define an array variable to store the documents returned from the collection and we execute the find method without a filter to return all the documents. When the execute method is reached every document will be added to the array variable that will be returned. Then when the execute method is completed, if we received any document; we return null as the error message and the array of documents, or an error message and null as the array of documents otherwise.

Now we need to create the required files to call the method and display the documents from the database in the application.

In this blog post we are going to follow the folder structure suggested by AngularJS, putting each feature/entity in its own file and having a directory for feature. First we are going to create an AngularJS component named app.module.js inside the folder path public\javascripts, we are not creating a folder for this component because it will be used by all the features we are going to add.

In this file we are going to configure the modules required by the application and a factory to access the methods that we are going to add to our model file (news.js). For more information about a factory please see the AngularJS documentation.

We are going to add the following code in the file:

In the first line we set a name for our application (mysqlNews), and specify the modules that we are going to use: ngRoute and newsList. The newsList module is going to be the module that loads the documents in our application. And the ngRoute is an AngularJS module that provides the route and deeplinking services.

Then we create a factory that is going to be used by the other modules, the factory is named as newsFactory and we specify the module $http as the module that is going to be used by our factory. The $http module is core service that facilities the communication with the HTTP servers. Inside the factory we define an object that will have a property named news that will store the documents of our application and a method called getAll that will get the documents and copy them into the news property.

Now we are going to create another component named app.config.js, and also this component does not have a directory because will be used by all the features too. In this file we are going to configure the routes of our application.

In the previous code we set ‘!‘ as our prefix for our routing configuration and also we configure the template that is going to be used to load the news in our application.

To continue we are going to create a folder named newsList, this folder will contain the components and the template to display the news in our application. To this folder we are going to add a component named news-list.module.js with the following content:

angular.module('newsList', []);

The previous file just contains the name of this module, if we require additional modules we can set it here in this file. Now add another component named news-list.component.js with the following content:

The code we have just seen configures the template and also the controller that loads the documents into the template via the factory we created earlier. The news from the factory are copied to a property of the controller and then the method to get all the news is called, and also a property named order that is going to be used to sort the news, is added to the controller. The last file that we are going to add to this folder is the template file to display the documents. The file will be named news-list.template.html with the following content:

In the template we have a search and a filter section, and a list where we will display our documents. The search input is set to the controller’s search property that used in this template ($ctrl.search), the property is applied to the list of items to filter them. The sort select is set to the controller’s order property that is also applied to the list of items to sort them by title or by date published.

To complete this section code and have something we can test, we need to do just two more things. The first one is to update the file index.js that is inside the routes folder. We need to load our model, append the following line to the section where the variables to do that are defined:

var mysql = require('../models/news.js');

And we are going to add a new get method:

In the above method we are calling our model’s getAll method and sending back the information in JSON format. The last step to test our application is to update the index.ejs file inside the views folder.

And the second thing is to update our index file index.ejs. To start with, we update the html tag to include the language and the application name. The name is as defined in the app.module.js file: mysqlNews. If we do not set the ng-app directive AngularJS will not find the root element of our application and our components will not be loaded. The html tag should appear as follow:

<html lang="en" ng-app="mysqlNews">

Then we are going to add the link to our scripts and to the bootstrap CSS in the header:

And finally we are going to update the body adding the ng-view directive that is used by $route service to render the template configured by the current route loaded:

To test the application and verify that everything is configured properly, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We should see a search/sort section on the left-hand side of your browser and a list of the initial documents loaded to our collection next to it.

index

Adding Comments

At this time, we have an application that list the documents from a collection and let us do a search to filter the information and sort the documents by the published field. But the Comments section is still empty, so let us add the functionality to add comments.

Go to our model file news.js, and add the following method at the end:

In the previous method, first we create the session to work in our server then we get the schema and the collection. Once we have the collection we are calling the modify method specifying the _id of the post where we want to add the new comment. The comment is added to the document using the arrayAppend method which appends the comment to the end of the array. Once the execute method finishes, we verify how many documents were affected and sent the results back basis on it.

Then we need to update our factory (app.module.js), adding the new module that will be used to add comments newComment:

Also we need to add the AngularJS $location and $filter modules. We are going to use the $location module to change the URL of the application, and the $filter module to know to which post a new comment belongs.

Finally, we are going to add the method to create comments or cancel the addition:

In the addComment method, we send the data to the /comments route. Then the result returned is added to our local copy of the data. The $filter module is used to find by _id the post to which belongs the new comment. To finish we change the location of the application to the main page.

The next step is to update our route provider in the app.config.js file to handle the add comments route:

Now as we did for the components to display the news, we are going to create a folder in the public/javascripts path with the name comments, this folder will contain the components and the template to add new comments. We are going to start adding the new-comment.module.js component to the folder with the following content:

angular.module('newComment', ['ngRoute']);

In the file we are specifying that our new module has the name newComment and it will use the ngRoute module. To continue we are going to add another component named new-comment.component.js with the following code:

The previous code configures the template that is going to be used to add new comments and defines the controller to be used in the view. The controller receives the _id of the news to update and is stored in a controller property, then two methods are defined: addComment that sends the new comment to be added to our factory and cancelAddComment that cancels the current action.

The template that we are going to use to add new comments is going to be called new-comment.template.html and is defined next:

The item template consists only of a text area where the user can enter a comment and some basic validation. The Submit button is enabled only when the comment passes validation, this is done by the ng-disabled directive that check if all the inputs in the form (addComment) has valid values. The ng-model directive is used to set the value of the input to a controller’s property, comment is the property’s name in this case.

Then we need to update our route file (routes/index.js) adding a new method to save the new comments:

This method sends the new comment to the model to be stored in collection. Once it has been stored successfully, it returns the comment in JSON format.

We are going to add a link that will load the new view when it is clicked, we are going to update the template in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that lists the comments:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test these changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add coments:

add_comment

And when we click on it, we see a form to write new comments:

submit_comment

We see the new comment below the news item in the index page:

display_comment

Adding new posts

To finish the application, we are going to add the feature to add new posts to the application. We are going to start adding a new method to our model (models/news.js) at the end:

This new method accepts as a parameter a JSON object to be added to the collection. Once the new document is stored in the collection it is returned to the application. If the document cannot be stored, the method returns null and display an error message to the user.

Then we need to update our factory (app.module.js), and again the first thing to update is to add the new module that will be used to add new posts addNews:

Now we are going to add the new methods that add a new post or cancel the process:

The first method receives the data for the new post and sends it to the /addNews route, the document returned is added to our local copy of the data and the app location is changed to the index.

Next we are going to update our route provider in the app.config.js file to handle the add news route:

Now we are going to create a folder in the public/javascripts path with the name news. This folder will contain the components and the template used to add new posts. Then we continue adding the add-news.module.js component to the folder with the following content:

angular.module('addNews', ['ngRoute']);

In the component we are specifying that our new module has the name addNews and it will use the ngRoute module. Then we add another component named add-news.component.js with the following code:

The previous code configures the template that is going to be used to add new posts and defines the controller to be used in the view. The controller receives the data to create a JSON object and set the published date to be the current date. The addNews method sends the JSON object to the factory and the cancelAddNews method cancels the current action.

The last file for this folder is going to be the view template named add-news.template.html, and is defined with the following:

The item template contains the text inputs to set the Title, Link and Introduction for the new post. It also contains some validation checks to enable the Submit button just when the required fields are valid. Also the value of the inputs is set to the controller’s properties: title, link and introduction.

Then we need to update our route file (routes/index.js) adding a new method to save the new post:

This method sends the new post to our model and it returns the new document added in JSON format once it is saved in the collection.

We are going to add a link that will load the new view when it is clicked, we are going to update the file in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that has the search and sort options:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test the latest changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add new posts:

add_post

And when we click on it, we see a form to add new posts:

submit_post

After we add a new post, it is displayed in the index page:

display_post

In this post we learned how to create a full stack JavaScript application using MySQL, Node.js, Express, AngularJS. We hope you enjoyed the post and please stay tuned because we have a lot of new content to share with you.

See you in the next blog post.

Develop By Example – Working with custom queries using Node.js

In all the previous blog posts we covered a lot of examples of how to perform actions in a MySQL server set up as a document store. In all the code examples we used the XSession to communicate to the MySQL server.

The XSession is a logical session that abstracts our connection. With it, we can be connected to one or more servers and also give us the required support to work with collections and relational tables. But there is something that the XSession does not provide, full SQL language support.

When you need to execute a query that is not supported by an XSession, use a NodeSession instead. The NodeSession object can be useful, for example, when you want to connect to a specific MySQL server and do operations specifically on this server, or when you want to execute a specific function or create a stored procedure.

The following example demonstrates how to use a NodeSession object.

In the previous example, we use the method getNodeSession to get the NodeSession object (nodesession). Then we call the nodesession objects’s executeSql method to set the schema that we are going to use. To execute the query, we need to call the execute method. When the query that sets the current database completes, it returns a Result object (result) that contains information about the execution of the query. In this instance, the result object does not contain any useful information other than reporting the success or failure of the operation. Then, we call the executeSql method again, setting a query that may return one record. Once the query is executed, a result object is received, but in this case, it contains a row returned from the query represented as an array object. Each item in the array represents a column of a row. We access the array object by indexes to get the information we require, in the code we get the value of the column 0 (index 0) to write it in the console.

Of course, the previous example can be accomplished without using a NodeSession object. For that reason, in the next example we are going to create a more complex query that cannot be handled by an XSession object.

Suppose that we have a collection where we store the reviews for a movie, and the review contains a rating. We want to get the 10 best rating movies based on the average of the rating that the movie has. Currently, we do not have support to calculate the average using an XSession object, and so for this case, we need a NodeSession.

The next example demonstrates how to accomplish what we want.

In the previous example we get the NodeSession object (nodesession). Then we call the nodesession object’s executeSql method to define the query we want to execute. As you can see in this example, we don’t set the schema we want to use, but specify it in the query (mySchema.reviews). Once the query has executed, we log the results returned to the console and at the end the node session is closed.

In the query you can notice that we are using a function called “JSON_EXTRACT”, which is one of the many functions introduced in MySQL Server 5.7 that enable you to create and query JSON documents. For more information about these functions, see the MySQL Reference Guide articles JSON Functions and Manipulation JSON Data.

See you in the next blog post.

Develop By Example – Document Store: Working with documents using Node.js

In the previous blog post we explained how work with the collection CRUD operations. In this blog post we are going to explain other functions that are related to document management.

We already know how to create collections, as well as how to add, delete, update and retrieve documents from them. But, how can we add a new field to a document or documents that are in a collection?

The following code demonstrates how to do it:

In the previous code, first we get the schema and the collection objects (schema and coll). Then we define the query variable that contains the where clause, and newDoc with the document used in the example. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field name and the second one is the value for the field. If you have already read the previous blog post, you may notice that the code is the same as the one used to update the value of a field in a document, except for the line that adds the new document we want to work with. The set method is used to add new fields or to update their value. Once the execute method is finished, the number of documents updated are written to the console as well as the updated document, and finally the connection is closed.

The following code demonstrates how to add multiple fields to a document:

As you can see in the previous code, we just chained the set method two times to add multiple fields to a document. You can chain the set method as many times as needed. If the query passed to the modify method finds multiple documents, all the documents found will be updated adding the new fields sent; so be careful with it.

What if we want to remove a field? We just need to use the unset method to accomplish this. In the next example we demonstrate how to do it:

The previous code looks almost identical to the code used to add fields to a document; the difference is that we use the unset method instead of the set method. The rest of the code is the same.

In the previous examples we used an array as the value for one of the fields of the documents we added. Connector/Node.js also has methods to handle the items inside the array.

If we want to add items to an array we have several options. One such option is demonstrated in the following example:

The code looks quite similar to the code of the previous examples; there is just a new method: appendArray. The method receives two parameters; the first one is the field that has an array as its value. The second parameter is the value for the item to be added, which will be added at the end of the array.

As well as for the set method, if you send a query to the modify method that returns multiple documents; all the documents will be updated as well.

Another way of adding items to an array field is by using the arrayInsert method:

As you can see the previous code is almost equal to the code where we used the appendArray method. The only difference is that we now use the arrayInsert method instead. The arrayInsert method receives two parameters; the first one is the name of the field including the index of the array where you want to insert the new item, if the index is out of bounds it will be inserted at the end of the array. The second parameter is the value of the new item.

If you remember, in previous examples we used the set method to update field values and to add new fields to a document.

Well we can also use set to add new items to an array. The following code demonstrates how to do it.

The code above is identical to previous examples which used the set method. In this example when calling the set method we use an index that is out of bounds, by doing that the method understands that the item does not exist in the array; so it will be added to the end of the array. But if the index passed to the method is the index of an existing item, the method will update it instead of adding a new item.

At this point we know how to add and update items in an array, but also we can remove items. The following code is demonstrates this:

In this example we pass the name of the field and the index position of the item we want to remove as parameters to the unset method. If we pass an index that is out of bounds of the array, no item will be removed from the array and we will not receive an exception, so be careful with that.

See you in the next blog post.

Develop By Example – Document Store: working with collections using Node.js

In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.

The following code demonstrates how to add a single document to an existing collection:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added.

But, what if you want to add multiple documents?

You can do it with almost no changes in your code. The following code adds two documents at the same time:

As you can see, the previous code is almost identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents.

Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.

The previous code is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection.

Updating a field in a document is also very easy to do. The following code is an example of how to do it:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method.

Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates it.

The previous code defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection.

Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:

The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it.

But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:

Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code to do it:

The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have  ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names.

See you in the next blog post.

Develop By Example – Creating schemas and collections using Node.js

In a previous post we explained how to connect to a MySQL server configured as a document store using the new MySQL Connector/Node.js. In this post we are going to explain how to create a schema, create a collection and add documents to the collection.

Creating a new schema is very easy; the following code demonstrates how to do it:

In the previous code example we created a connection and then used the XSession object to create a schema, finally we closed the connection.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession() method to get the object that we are going to use to create the schema. We then call the createSchema method to create our schema; once the schema is created we call the session object’s close method to close our connection.

But, what will happen if we try to create a schema that already exists? An error will be thrown.

To avoid that situation we can verify if the schema exists before trying to create it.

In the previous code, before trying to create the schema we call the session object’s getSchema method. The object (schema) returned represents the schema we want to create, we then call its existsInDatabase method which will return false if the schema does not exist in the server or true otherwise. The following lines of code attempt to create the schema. The program notifies the user if the schema is created successfully, or if the schema already exists. Then it closes the session.

Now that we know how to create a schema and verify if it already exists in the database, it is time to work with collections. When creating a collection, we need to know which schema object it will belong to. This can be a new schema, or an existing one. If we try to create the collection in a schema where that collection is already present, the program returns an error. The following example demonstrates how to check if a collection already exists before creating it.

In the previous code, the first lines are the same for the code to verify if a schema exists if not it is created. Then we call the schema object’s getCollection method which returns an object that represents the collection (coll) we want to create. As we did for the schema object, we call the coll object’s existsInDatabase method. If the collection does not exist, it is created by calling the schema object’s createCollection method and we receive a confirmation message. If the collection already exists we are notified of the fact. Finally, the session is closed.

To add new documents to a collection we need to use a JSON notation for the document to create. In the following code, we are going to demonstrate how to add a document to a collection, bear in mind that in the code we assume that a schema and a collection already exist in the server.

In the previous code to add a new document, first, we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. Then we created an object using JSON (newDoc). To add the document to the collection we call the coll object’s add method followed by the execute method, once the execute method has finished we receive an object (added) that contains information about the document added. By calling the added object’s getAffectedItemsCount method we know if the document was added.

In the next blog post, we are going to explain in more detail how to work with collections.
See you then.

Develop By Example – Document Store Connections using Node.js

In this post we are going to explain how to connect a Node.js application to a MySQL server using the new MySQL Connector/Node.js; needless to say that we will be using the MySQL server as a document store.

There are two types of session that a connection can provide: XSession and NodeSession.
An XSession encapsulates access to a single MySQL server running the X Plugin or
multiple MySQL Cluster nodes; and the NodeSession serves as an abstraction for a physical connection to exactly one MySQL server running the X Plugin. To enable the XPlugin in the MySQL server using the MySQL Client command line you need to use the root account or an account with INSERT privilege to mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Click here for more information about how to setting up MySQL as document store.

Creating a connection to a MySQL server as a document store is quite similar to create a connection to a traditional MySQL server; we require the following connection parameters: host, database user, user password, and port.

The following example demonstrates how to connect to a single MySQL Server using XSession:

In the previous code example, we created and closed a connection to a server using an XSession; as you can see the code is very simple and easy to read.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession method. This method implements a promise. If the connection to the MySQL server is successful the promise is fulfilled by returning an XSession (session) object. We then call the session object’s close method to close the connection.

In the previous code there are two important things to note. The first one is that we do not specify a schema because the XSession works similar to a traditional session: You do not need to specify a schema because, at the time you connect, your working schema might not exist yet. The second one is the port. By default the X DevAPI uses the port 33060; we are assuming that the running server is using the default port for TCP/IP connections. The port can be configured when the server starts and is stored in a server variable.

The following example demonstrates how to connect to a single MySQL Server using NodeSession:

The NodeSession example code is almost the same code used to get an XSession object, the difference is the method that is called to get the session object and the port; the code does exactly the same.

You might need to use a NodeSession in certain scenarios where you require access to SQL features that are not supported by an XSession. In a subsequent post we are going to cover some examples about how to use the NodeSession.

To work with schemas and collections we need to add some extra lines of code. The following code demonstrates how to do it.

In the last code example; from the session object, we call the getSchema method to get an object (schema) that represents the schema in which we want to work. Once we have the schema object we execute the method getCollection to get an object (coll) that represents the collection we want to work with. In this example, we want to retrieve the document with an id value of ‘1’ from the collection. First we call the find method passing the JSON path and value we are searching for. Then we call the execute method to perform the query. The execute method returns a promise which supplies the requested document when the method completes.

See you in the next blog post where we are going to explain more about the operations that can be performed using collections.

Develop By Example – New MySQL Document Store Series

Examples are a great way to learn new things. As many of you may or may not know we’ve added some new things to MySQL Server 5.7.12  and the ecosystem around it, extending it  to allow you to use the MySQL as a Document Store. Meeting the challenge meant expanding Developer Interfaces and Database tools.

  • Addressing information with a both classic and modern data architectures
  • For all types of data – structured, semi, and unstructured
  • Empowering developers – Simpler, Faster, Flexible
  • Leveraging latest NoSQL oriented tools/methods – JavaScript, Node.js, JSON, CRUD, Methods chaining, and more

From the developer side the MySQL Document Store new APIs by introducing a JSON/Document Store oriented called the MySQL X DevAPI. This programming API provides the option for accessing MySQL; and its design unifies JSON document and table access; and it includes SQL support as well. Since the API features a popular fluent interface style, you will be able to use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

This new API is provided to developers in our latest MySQL Connectors. As again as learning by example is often the best way to get started with new things we’re also providing an example application we’re calling Movie Review to show you how it’s used in real applications with use cases that likely map to how you’d want to develop.

As there are a range of languages supported by the MySQL Connectors and Drivers – New Connector/Node.js as well as connectors for Java, Python, .NET, C, C++, and PHP – we plan to do Movie Review examples applications across the various languages.
Were calling our new example application – Movie Review – and as you might have guessed it revolves around developing web applications that allows users to review movies via a simple application that demonstrates the usage of the new features available in the X Dev API and connectors.

We will have 2 user types within the application – users and administrators.

The users can:

  • Search for a movie to view its description and any existing reviews
  • Review a movie
  • Update or delete a review they have written

The administrators can:

  • See the movies to view its description and any existing reviews
  • Upload new information to the database manually or from a JSON file
  • View the current data in the collections to edit it or delete it
  • View some simple reports.

With these use cases we hope to quickly and simply take you through the key development concepts quickly using CRUD type programming.

The Document Store Data Model

Since the application will be kept simple, we’re also including simple document store database you can easy load. It includes four collections: Actors, Movies, Reviews, and Users. It comes with the entire example data loaded that you’ll need as well. This will help to teach you some of the basics of document store style modeling.

We’ll provide the steps to install, etc in the example blogs and have you up and running and developing with MySQL Document Store in no time.

See you in the next blog post – where we get into the Movie Review application written with Node.js.