Using MySQL with NodeJS. - Information Services

NodeJS brings JavaScript to the server side. Personally, I use NodeJS on my home servers, and for side projects because I enjoy writing JavaScript and am very impressed with the collection of modules that exist, and how easy it is to install and start using them.

Storing information in databases can be complex, especially relational databases that can require complex connection string data like pooling, timeouts, time zones, ect. Frequently, database access is one of the main things implemented in a language and more often than not, we can use pre-built libraries like Knex to do it.

Knex can create the connections, retrieve and store data in a similar fashion to using LINQ with EntitiyFramework in C#, it also functions as an ORM which lets you treat your data coming to/from the DB as a regular JavaScript object.

Installing NodeJS, NPM and MySQL is out of the scope of the guide, but using Node Version Manager to install both NodeJS and NPM on a Linux box like Ubuntu is highly recommended. Or just grab a free account on https://codeanywhere.com and get started straight away without any server setup required. Also moderate to advanced knowledge of JavaScript is recommended especially with Promises. But I will try and explain the best I can.

Tutorial:

Create a new directory for your module, and inside create a file for your code to be stored in. I named mine nodejstest/index.js.
Initialize your project as a new module. (in NodeJS everything is done via modules). To get started, you can use:
1. ```
$ npm  init
```
This will walk you through the resulting file. For now you can add any info you want. When you’re done you will see a brand new file called ‘package.json’. This file describes your module in the event you want to push it up live for others to download and use. After the init I like to add ‘private: true’ to the end of the configuration file to keep the module for my eyes only. Full documentation of package.json here.
Create a test database and table. I called mine ‘nodejstest’ and 'Persons', I borrowed this create table statement from w3schools. Bear in mind that typically with my tables I recommend creating an auto-incrementing primary key. We will create a random number to serve as our ID for simplicity.
1. ```
CREATE TABLE Persons
(
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
```
Now for the node code. Here is where we use the power of npm to grab some modules for us. We need the MySQL provider, and we need Knex. MySQL2 is a fork of Node-MySQL that has some security enhancements. So go ahead and install them with these commands (you may need to be root):
1. ```
$ npm imstall mysql2 –-save
$ npp imstall knex --save
```
Once those are installed we can begin to write our script. Its important to note that all this can be done using only the MySQL2 module, but Knex grants us some easy out of the box functionality that will speed up your development quite a bit. At the top of your script go ahead and import the modules like so:
1. ```
var mysql = require(‘mysql2’);
var mysql = require(‘knex’);
```
You are now able to begin using the Knex module! The Knex module immediately returns a function when its imported, so on the line where you required it, edit it to pass the function your connection data. Its important to know, the object you pass in as connections value, is the same object you would pass the MySQL connection object. This means you are able to use any connection parameters that it specifies, see the Node-MySQL documentation for more info.
1. ```
var knex = require('knex')({
client: 'mysql2',connection: {
host     : '127.0.0.1',
user     : 'root',
//password : 'your_database_password', // CodeAnywhere doesn’t require a password for root
database : 'nodejstest'
}

});
```
Now that you have a connection, you can make inserts and read data using the Knex query builder. To make an insert and retrieve all entries, you can use the knex constructor as shown below:
1. ```
knex('Persons').insert({
    PersonID: Math.floor((Math.random() * 100000) + 1),
    Lastname: 'Doe',
    Firstname: 'John',
    Address: '555 Test Ave',
    City: 'Test Ville'
})
.then(function(){
    return knex.select().from('Persons');
})
.then(function(data){
     console.log(data);
})
.catch(function(err){
     console.log(err);
});
```
  There’s a lot going on in the above statement, but it can be broken into separate parts. Knex constructor takes the table name, which leads us to the .insert() method that takes an object closely resembling our table. Insert returns a promise. Knex uses bluebird under the hood, which is a promise library. When you have code that might take a while to return (especially with IO operations) it is generally best to perform them asynchronously. That’s why the insert has a .then() method. Because .insert() immediately returns a promise, that promise will either reject, or resolve.
  
  .then(); can take a couple functions. The first is a function that fires when things went well (promise resolves). The second fires when things don’t go well (promise rejected), and generally it is advised to chain up what you can and then run catch() after everything. This is done in case the code somewhere up this long nested chain fails. As you can see, I am returning the promise that .from() returns. This way I can chain up another .then(). If I wanted I could simply use a .then() right after .from() and return the data there. However, this looks easier to read IMO.
  
  In our particular example, we catch but no where do we throw. Somewhere inside Knex or MySQL could possibly throw, and we will catch it.

Full code here.

Feel free to comment with any questions or corrections!

Tutorial:

Full code here.

Like this: