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
Node.js
Express.js
AngularJS
MySQL Connector/Node.js
MySQL Server 5.7.12 or higher
Optional
Bootstrap CSS
MySQL Workbench
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:
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:
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:
To start the newly created application, run the following command in the terminal:
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:
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:
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:
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:
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:
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:
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/:
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.
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:
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/:
We see the new option to add coments:
And when we click on it, we see a form to write new comments:
We see the new comment below the news item in the index page:
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:
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 new-comment.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/:
We see the new option to add new posts:
And when we click on it, we see a form to add new posts:
After we add a new post, it is displayed in the index page:
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.