Selecting and initializing the database

Exclusive offer: get 50% off this eBook here
Node.js Blueprints

Node.js Blueprints — Save 50%

Develop stunning web and desktop applications with the definitive Node.js with this book and ebook

£20.99    £10.50
by Krasimir Tsonev | June 2014 | Open Source Web Development

To build a blog application, we need a database that will store the published articles. In most cases, the choice of the database depends on the current project. There are factors such as performance and scalability and we should keep them in mind. In order to have a better look at the possible solutions, we will have a look at the two of the most popular databases, MongoDB and MySQL, in this article by Krasimir Tsonev, the author of Node.js Blueprints. The first one is a NoSQL type of database. According to the Wikipedia entry (http://en.wikipedia.org/wiki/NoSQL) on NoSQL databases:

"A NoSQL or Not Only SQL database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases."

(For more resources related to this topic, see here.)

In other words, it's simpler than a SQL database, and very often stores information in the key value type. Usually, such solutions are used when handling and storing large amounts of data. It is also a very popular approach when we need flexible schema or when we want to use JSON. It really depends on what kind of system we are building. In some cases, MySQL could be a better choice, while in some other cases, MongoDB. In our example blog, we're going to use both.

In order to do this, we will need a layer that connects to the database server and accepts queries. To make things a bit more interesting, we will create a module that has only one API, but can switch between the two database models.

Using NoSQL with MongoDB

Let's start with MongoDB. Before we start storing information, we need a MongoDB server running. It can be downloaded from the official page of the database https://www.mongodb.org/downloads.

We are not going to handle the communication with the database manually. There is a driver specifically developed for Node.js. It's called mongodb and we should include it in our package.json file. After successful installation via npm install, the driver will be available in our scripts. We can check this as follows:

"dependencies": { "mongodb": "1.3.20" }

We will stick to the Model-View-Controller architecture and the database-related operations in a model called Articles. We can see this as follows:

var crypto = require("crypto"), type = "mongodb", client = require('mongodb').MongoClient, mongodb_host = "127.0.0.1", mongodb_port = "27017", collection; module.exports = function() { if(type == "mongodb") { return { add: function(data, callback) { ... }, update: function(data, callback) { ... }, get: function(callback) { ... }, remove: function(id, callback) { ... } } } else { return { add: function(data, callback) { ... }, update: function(data, callback) { ... }, get: function(callback) { ... }, remove: function(id, callback) { ... } } } }

It starts with defining a few dependencies and settings for the MongoDB connection. Line number one requires the crypto module. We will use it to generate unique IDs for every article. The type variable defines which database is currently accessed. The third line initializes the MongoDB driver. We will use it to communicate with the database server. After that, we set the host and port for the connection and at the end a global collection variable, which will keep a reference to the collection with the articles. In MongoDB, the collections are similar to the tables in MySQL. The next logical step is to establish a database connection and perform the needed operations, as follows:

connection = 'mongodb://'; connection += mongodb_host + ':' + mongodb_port; connection += '/blog-application'; client.connect(connection, function(err, database) { if(err) { throw new Error("Can't connect"); } else { console.log("Connection to MongoDB server successful."); collection = database.collection('articles'); } });

We pass the host and the port, and the driver is doing everything else. Of course, it is a good practice to handle the error (if any) and throw an exception. In our case, this is especially needed because without the information in the database, the frontend has nothing to show. The rest of the module contains methods to add, edit, retrieve, and delete records:

return { add: function(data, callback) { var date = new Date(); data.id = crypto.randomBytes(20).toString('hex'); data.date = date.getFullYear() + "-" + date.getMonth() + "-"
+ date.getDate(); collection.insert(data, {}, callback || function() {}); }, update: function(data, callback) { collection.update( {ID: data.id}, data, {}, callback || function(){ } ); }, get: function(callback) { collection.find({}).toArray(callback); }, remove: function(id, callback) { collection.findAndModify( {ID: id}, [], {}, {remove: true}, callback ); } }

The add and update methods accept the data parameter. That's a simple JavaScript object. For example, see the following code:

{ title: "Blog post title", text: "Article's text here ..." }

The records are identified by an automatically generated unique id. The update method needs it in order to find out which record to edit. All the methods also have a callback. That's important, because the module is meant to be used as a black box, that is, we should be able to create an instance of it, operate with the data, and at the end continue with the rest of the application's logic.

Using MySQL

We're going to use an SQL type of database with MySQL. We will add a few more lines of code to the already working Articles.js model. The idea is to have a class that supports the two databases like two different options. At the end, we should be able to switch from one to the other, by simply changing the value of a variable. Similar to MongoDB, we need to first install the database to be able use it. The official download page is http://www.mysql.com/downloads.

MySQL requires another Node.js module. It should be added again to the package.json file. We can see the module as follows:

"dependencies": { "mongodb": "1.3.20", "mysql": "2.0.0" }

Similar to the MongoDB solution, we need to firstly connect to the server. To do so, we need to know the values of the host, username, and password fields. And because the data is organized in databases, a name of the database. In MySQL, we put our data into different databases. So, the following code defines the needed variables:

var mysql = require('mysql'), mysql_host = "127.0.0.1", mysql_user = "root", mysql_password = "", mysql_database = "blog_application", connection;

The previous example leaves the password field empty but we should set the proper value of our system. The MySQL database requires us to define a table and its fields before we start saving data. So, consider the following code:

CREATE TABLE IF NOT EXISTS `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` longtext NOT NULL, `text` longtext NOT NULL, `date` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Once we have a database and its table set, we can continue with the database connection, as follows:

connection = mysql.createConnection({ host: mysql_host, user: mysql_user, password: mysql_password }); connection.connect(function(err) { if(err) { throw new Error("Can't connect to MySQL."); } else { connection.query("USE " + mysql_database, function(err, rows, fields)
{ if(err) { throw new Error("Missing database."); } else { console.log("Successfully selected database."); } }) } });

The driver provides a method to connect to the server and execute queries. The first executed query selects the database. If everything is ok, you should see Successfully selected database as an output in your console. Half of the job is done. What we should do now is replicate the methods returned in the first MongoDB implementation. We need to do this because when we switch to the MySQL usage, the code using the class will not work. And by replicating them we mean that they should have the same names and should accept the same arguments.

If we do everything correctly, at the end our application will support two types of databases. And all we have to do is change the value of the type variable:

return { add: function(data, callback) { var date = new Date(); var query = ""; query += "INSERT INTO articles (title, text, date) VALUES ("; query += connection.escape(data.title) + ", "; query += connection.escape(data.text) + ", "; query += "'" + date.getFullYear() + "-" + date.getMonth() + "-"
+ date.getDate() + "'"; query += ")"; connection.query(query, callback); }, update: function(data, callback) { var query = "UPDATE articles SET "; query += "title=" + connection.escape(data.title) + ", "; query += "text=" + connection.escape(data.text) + " "; query += "WHERE id='" + data.id + "'"; connection.query(query, callback); }, get: function(callback) { var query = "SELECT * FROM articles ORDER BY id DESC"; connection.query(query, function(err, rows, fields) { if(err) { throw new Error("Error getting."); } else { callback(rows); } }); }, remove: function(id, callback) { var query = "DELETE FROM articles WHERE id='" + id + "'"; connection.query(query, callback); } }

The code is a little longer than the one generated in the first MongoDB variant. That's because we needed to construct MySQL queries from the passed data. Keep in mind that we have to escape the information, which comes to the module. That's why we use connection.escape(). With these lines of code, our model is completed. Now we can add, edit, remove, or get data.

Summary

In this article, we saw how to select and initialize database using NoSQL with MongoDB and using MySQL required for writing a blog application with Node.js and AngularJS.

Resources for Article:


Further resources on this subject:


Node.js Blueprints Develop stunning web and desktop applications with the definitive Node.js with this book and ebook
Published: June 2014
eBook Price: £20.99
Book Price: £34.99
See more
Select your format and quantity:

About the Author :


Krasimir Tsonev

Krasimir Tsonev is a coder with over 10 years of experience in web development. With a strong focus on quality and usability, his interests lie in delivering cuttingedge applications. He enjoys working in the industry and has a passion for creating and discovering new and effective digital experiences. Currently, Krasimir works with technologies such as HTML5 or CSS3, JavaScript, PHP, and Node.js, although he started off as a graphic designer. Later, he spent several years as a flash developer using ActionScript3 and frameworks such as RobotLegs. After that, he continued delivering, as a freelancer, full-stack web services for his clients' graphic design as well as frontend and backend programming. With the present surge in mobile development, Krasimir is enthusiastic to work on responsive applications targeted at various devices. Having lived and worked in Bulgaria, he graduated from The Technical University of Varna with a Bachelor's and Master's degree in Computer Science.

Books From Packt


Instant Node Package Manager [Instant]
Instant Node Package Manager [Instant]

Getting Started with Grunt: The JavaScript Task Runner
Getting Started with Grunt: The JavaScript Task Runner

Node Web Development - Second Edition
Node Web Development - Second Edition

Using Node.js for UI Testing
Using Node.js for UI Testing

Node Cookbook
Node Cookbook

Node Security
Node Security

Mastering Node.js
Mastering Node.js

CoffeeScript Programming with jQuery, Rails, and Node.js
CoffeeScript Programming with jQuery, Rails, and Node.js


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software