Although data persistence is almost always a fundamental element of applications, Node.js has no native integration with databases. Everything is delegated to third-party libraries to be included manually, in addition to the standard APIs.

Although MongoDB and other non-relational databases are the most common choice with Node because if you need to scale an application, the use of traditional databases increases the complexity of the system, forcing us to create more and more relationships between tables, thus losing performance and making maintenance difficult. However, many programmers, usually from a PHP background, know and use MySQL which makes it hard for them to migrate.

Also, as folks at Rising Stack say, with good indexes and proper planning, MySQL can be great. Therefore, we believe this post will help those who are migrating from PHP, but do not want to switch the database.

Since it is a very popular database, there are several ways to use MySQL with Node.js. In this tutorial, we will talk about two methods to use MySQL with Node.js which includes using node-mysql module and Node-db.

Connecting Mysql with Node.Js Using the Node-mysql Module

Installing and Connecting MySQL

Node-mysql is a client solution designed specifically for the implementation of the protocol associated with the well-known DBMS.

The node-mysql module can be installed via the Node.js package manager. To install the module it will be sufficient to write the following command in the Terminal:

npm install MySQL

Once this is done, you can begin to make queries against the managed databases by sending them directly via Node.js; as a first step it will be necessary to call up the module just installed:

var mysql = require('mysql');

Secondly, you will have to go to the connection phase with the Database engine and then to the selection of the database to manipulate:

var connection = mysql.createConnection( {
  host : 'localhost',
  user : 'username',
  password : 'user_password',
  database : 'name_db'
} );
connection.connect();

Now the way for interaction will be cleared and you can proceed with a first query on a table of your choice:

var queryString = 'SELECT cmp FROM tbl ORDER By id DESC';

So all that remains is to move on to the record extraction phase without forgetting the handling of exceptions:

connection.query(queryString, function(err, rows, fields) {
  if (err) throw err;
  for (var i in rows) {
      console.log('Values: ', rows[i].cmp);
  }
});

Finally, once the expected results have been obtained, it will be possible to close the connection, freeing up the occupied resources:

connection.end();

Node.js and SQL Server

Even Microsoft has developed a driver to connect to SQL Server from applications based on Node.js; these drivers are available under an Open Source license and can be used freely once they have been downloaded for free from the appropriate repository.

var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};
Server=(local);
Database= archive;
Trusted_Connection={Yes}";
sql.open(conn_str, function (err, conn) {
  if (err) {
    console.log("Unable to establish a connection!");
    return;
  }
  conn.queryRaw("SELECT A,B FROM Tb.Tb", function (err, results) {
    if (err) {
      console.log("Error executing the query!");
      return;
    }
    for (var i = 0; i < results.rows.length; i++) {
      console.log("A: " + results.rows[i][0] + " B: " + results.rows[i][1]);
    }
  });
});

For the success of the operation, it is necessary to download the Client Native for accessing the SQL driver necessary for the connection.

Creating a Project

Create a folder for the project, with whatever name you want. Inside, the folder creates db.js and index.js files, both empty.

Open the terminal and inside that folder run, an npm init to initialize the project and create package.json. Still in the terminal, have the mysql2 dependency installed, as below.

npm i mysql2

Why mysql2 and not the default MySQL package? Because mysql2 has far superior performance and promises support, which allows us to write more modern and elegant JavaScript code. That said, let’s go ahead!

Creating the Connection

Now let’s open our db.js and create the connection to our database, using the package we just installed.

async function connect(){
    if(global.connection && global.connection.state !== 'disconnected')
        return global.connection;
    const mysql = require("mysql2/promise");
    const connection = await mysql.createConnection("mysql://root:hostpresto@localhost:3306/crud");
    console.log("Connected to MySQL!");
    global.connection = connection;
    return connection;
}

We start the connection by loading our package, more specifically the mysql2/promise wrapper which is just what gives us the promises support we mentioned before.

With this MySQL constant, we can call the createConnection function that waits for our connection string with the database. Here you must replace the data from your MySQL installation, in the format mysql://username: password@server:port.

Note that we used the await keyword before createConnection. This is because the createConnection is asynchronous, which forces us to use callback, promise or async/await to ensure that the following statement only happens after the connection is already established.

We chose await because it is the most modern form (ES6), but it forces us to use the async keyword before our function declaration, as you may have noticed too.

At the end of the connection function, we store it in a global variable. As we don’t want to keep creating a bunch of connections with MySQL (this is slow and consumes a lot of server resources), we will just create one, share it in a global variable, and when the connection is called for the second time, it will go down in that initial if that checks that a global connection isn’t already connected. If it exists, it will be reused instead of creating a new one.

A more advanced technique than this is using connection pooling like Sequelize, but that is beyond the scope of this blog.

To test this code, create a call to this connection at the end of the module and import it into index.js to trigger it.

//index.js
const db = require("./db");

As a result, it will print to the console the message that you have successfully connected to MySQL.

SELECT

Okay, we made the connection, and now how are we going to use it to make a basic CRUD in MySQL?

In the same db.js module, create another function, this time for SELECT, just below the previous one.

async function selectCustomers(){
    const conn = await connect();
    const [rows] = await conn.query('SELECT * FROM CUSTOMERS;');
    return rows;
}
module.exports = {selectCustomers}

This function is much simpler than the other one. In it, we perform the connection (which internally will decide to reuse or not) and then use the query function with this newly created connection object.

SQL itself doesn’t need applications, but the query’s return is an array of arrays full of things, where we only want the array named rows, which brings the resulting rows of the query.

Note the use of await to make it easier to manage both connection return and query return, making them visually synchronous (they’re not asynchronous anymore, it’s just visual effect or syntax sugar).

At the end of the module, unlike the connect function, we export this function because we want to use it in our index.js.

//index.js
(async () => {
    const db = require("./db");
    console.log('Started!');

    console.log('SELECT * FROM CUSTOMERS');
    const customers = await db.selectCustomers();
    console.log(customers);
})();

First, we have our DB, some markup logs, and then we call the selectCustomers function exported in the db module which will return our customer array, which we will just play on the console, but in a real application, we are sure that you will be more creative.

Since we are using await here too, and it exists to be used in async functions, we have created an anonymous async function around all the code that automatically gets called when we tell it to run index.js.

INSERT

Now it’s time to insert clients into MySQL via Node.js!

Go back to our db.js and insert a new function:

async function insertCustomer(customer){
    const conn = await connect();
    const sql = 'INSERT INTO CUSTOMERS(name,age,uf) VALUES (?,?,?);';
    const values = [customer.name, customer.age, customer.uf];
    return await conn.query(sql, values);
}

This function has some new features compared to the previous one.

First, SQL has dynamic content, which is the values to be inserted into the customer table row. And while it’s very tempting to concatenate the SQL string by hand, don’t do it at risk of SQL Injection!

Instead, put ‘?’ instead of the fields in when calling conn.query, the second parameter accepts an array of values that will be correctly replaced in your SQL string, already predicting SQL Injection.

In addition, the construction of this function follows the rule of the previous ones and don’t forget to export it in your db.js for use in index.js, as below.

//index.js
(async () => {
    const db = require("./db");
    console.log('Connected!');

    console.log('INSERT INTO Customer');
    const result = await db.insertCustomer({name: "Joe", age: 18, uf: "SP"});
    console.log(result);

    console.log('SELECT * FROM CUSTOMERS');
    const customers = await db.selectCustomers();
    console.log(customers);
})();

We chose to put it before SELECT, to make it easier to see if it worked or not and we also chose to print your result, so you can see what it returns, although most of the information is not very useful, only the number of affected rows in the table might interest you because, in the end, if there is an error in the execution of the SQL, will trigger an exception that will bring down our very simple application. Yes, you can wrap everything up with try/catch to handle errors.

UPDATE

Fast forwarding, updateCustomer would look like below.

async function updateCustomer(id, customer){
    const conn = await connect();
    const sql = 'UPDATE customers SET name=?, age=?, uf=? WHERE id=?';
    const values = [customer.name, customer.age, customer.uf, id];
    return await conn.query(sql, values);
}

Note the same construction pattern as INSERT, with the slight difference that the update requires an ID, which is very important in this type of SQL command.

And in index.js, we follow the same INSERT structure too, just be careful to enter an existing ID in your database instead of my ‘6’, or even modify the code to get one of the ids returned by the previous SELECT.

console.log('UPDATE CUSTOMERS');
const result2 = await db.updateCustomer(6, {name: "John", age: 19, uf: "SP"});
console.log(result2);

DELETE

And finally, our deleteCustomer in db.js.

async function deleteCustomer(id){
    const conn = await connect();
    const sql = 'DELETE FROM CUSTOMERS where id=?;';
    return await conn.query(sql, [id]);
}

module.exports = {selectCustomers, insertCustomer, updateCustomer, deleteCustomer}

Here, it’s even simpler than the previous ones and our index.js has just one more addition that’s more of the same.

console.log('DELETE FROM CUSTOMERS');
const result3 = await db.deleteCustomer(7);
console.log(result3);

Connecting MySQL with Node.js Using Node-db

Node-db

Node-db is a project that allows you to use a framework to manage data persistence, delegating to it the direct management of queries to the database. The underlying concept is that of decoupling. The code exposed by the library does not depend on the chosen database, also allowing a quick change without the need to rewrite the code.

Currently, the project only supports two engines: MySQL and Drizzle. Each engine has its own module hosted on the npm repository: db-myqsle db-drizzle.

We can download the project directly from the home page or from the relative github repository.

The library is developed for node.js, so it inherits the asynchronous logic of Javascript: any query performed on the database will be asynchronous and will be managed with ad hoc callbacks.

Another feature of the library is the method called chain that allows you to build even complex queries directly from code, without having to write the query manually, but using the framework API.

Let’s look at some examples:

var mysql = require('db-mysql');
new mysql.Database({
hostname: 'localhost',
user: 'userdb',
password: 'passworddb',
database: 'testdb'
}).connect(function(error) {
if(error) return console.log("Connection error");
 this.query().select("*").from("books")
.where("read = ?", [true])
.order({title: true })
.execute(function(error, rows, cols) {
if(error) return console.log("Query error");
for(var i in rows) console.log(rows[i]);
       });
});

In this code snippet, we can analyze the characteristics described above. First of all, the asynchronous mechanism envisages having a callback linked to the connection to the database (method connect) and a callback linked to the execution of a query (method query). In addition, they are easily identifiable chain methods such as SELECT, FROM, WHERE, and ORDER that if called in the right order allow you to create a real query completely from code to be as decoupled as possible by the engine chosen (the engine may have different SQL languages slightly different from each other).

Finally, note the auto-escaping mechanism used in the where method where the boolean variable true is not set manually within the condition but an internal replace is used to replace the question marks with the values present in the array passed as the second parameter. This approach will be used a lot in insert and update queries that we will see shortly.

It query is also possible to pass a manually written query to the method thanks to the utility methods name and escape:

this.query('SELECT * FROM ' + this.name('book') + ' WHERE ' + this.name('read') + ' = ' +

this.escape(true)).execute(function(error, result) { … } );

This solution is to be preferred over the previous one in cases where the performance of the application requires less processing time or in the case of customized queries (for example complex JOIN or GROUP BY or when the LIKE clause is present).

Insert, update, and remove queries always follow this approach.

If we want to execute one INSERT we can write:

this.query().insert('book', ['title', 'read'], ['The Pillars of the Earth', true], ['1984',false])

.execute(function(error, rows, cols) { … } );

For one UPDATE instead:

this.query().update('book').set({ read: true })
     .where("id = ?", [10])
      .execute(function(error, result)
{ … } );

Finally the DELETE:

this.query().delete().from('book').where("id = ?", [10])

.execute(function(error, result) { … } );

And with that, we finish the CRUD that uses MySQL as data persistence.

The ORMs and Node.js

ORM stands for Object Relational Mapping, which is a layer that serves to map, or to represent relational entities as objects and vice versa to persist objects with relational tools.

In other words, ORMs offer the possibility to think of data simply as a set of objects, the ORM will then create all the tables necessary to represent those objects on the database. This abstraction is such that in most cases we can even do it without knowing what kind of database we use.

Node-db is not an ORM, but it already helps us to make the first abstraction from the DBMS. At the moment among the libraries present in the main NPM repository there are not particularly reliable or interesting ORMs to dedicate a part of the article to it. This can be seen as a lack of the still young node.js but also as an opportunity to give birth to new ideas or possibilities.

Best Practices

Self-Managed Connections

In the code we have used above we have full control over opening and closing connections. As a good practice, you can leave it to the query function itself to open and close these connections, getting rid of this “micromanagement”.

In our tests, we noticed that this can be better or worse, considering the peculiarities of your MySQL infrastructure. where we don’t have as much control of MySQL settings.

SELECT *

Unless you are always going to use all the information in your table, never use SELECT *, but rather the extension version of the SQL query where you pass all the columns of the table you want to return. Think of a SELECT as a download from your database, the fewer columns you include in the SELECT, the faster this download will be because fewer bytes will be returned.

Conclusion

Just a few years ago it would have been inconceivable even to think of being able to interact with a database starting from a client-side environment based on JavaScript, features like this were only possible through server-side technologies; a small revolution made possible by Node.js. Normally, MongoDB is used as a database with Node.js as it is a non-relational database. NoSQL (short for Not Only SQL ) is a term used to refer to non-relational databases created from Web 2.0 to handle a large volume of data without loss of performance. MongoDB is currently the most popular on the market. However, you can also use MySQL with Node.js.

Certainly, this tutorial was just a very basic example, but it can help to get an initial understanding for those who want to start a complete MySQL integration with Node.js. There are alternative modules for MySQL integration like node-mysql-libmysqlclient . ORMs can also be a good choice for integrating relational databases into NodeJS applications, which create an abstraction of handling SQL commands, such as Sequelize.js. There are pros and cons regarding the use or not of ORM, and it will all depend on the demand of the project.