Integrating MySQL with Node.js Applications

Introduction

MySQL is an immensely popular Relational Database Management System (RDBMS) - and has for a long time been a staple in any back-end engineer's toolkit, right next to PostgreSQL.

With the advent of JavaScript for the back-end, via Node.js' spectacular rise in popularity and applicability - MySQL is commonly used as the database for projects and applications built with Node.

In this guide, we'll take a look at how to integrate MySQL with a Node.js application. We'll go through the setup process, perform CRUD operations programmatically and take a look at basic query security by escaping input and setting timeouts.

MySQL Setup

To work with MySQL - you'll need to download the MySQL Database and host a small server on your local machine. The community version is fully free!

You can also choose to install an open-source web server with support for the MySQL database (Xampp, Lampp) - they work great with Node's MySQL driver too.

The installer is straightforward, and once you've set up a service instance, you can create a project to connect to it!

MySQL Driver for Node Projects

Assuming that the server is running, you can communicate with it programmatically through a Node application, using a driver. For an application to have this ability, you need to install a MySQL Driver. A driver is available on npm as mysql!

To begin, let's create a new directory to host our application and initialize a new Node project, with the default settings:

$ mkdir mysql-app
$ cd mysql-app
$ npm init -y

This will generate a new package.json which contains the basic metadata for our project. Once this is done, we can install the node MySQL driver package via npm:

$ npm install mysql

We can verify that the installation was successful by checking our package.json file, where we should find a new entry for mysql added under dependencies:

 ...
 "dependencies": {
    "mysql": "^2.18.1"
  }

Establishing a Connection

A connection object can be created through the createConnection() function of the mysql instance. This object can then be used to create a connection between the server and the client/driver! It accepts three parameters:

• Host - The host of your database. The default value is 'localhost'.
• User - The authorized database administrator's username. The default value is 'root'.
• Password - The authorized database administrator's password. The default value is an empty string (no password).
• Database - An optional argument defining the database name to which you want to make modifications.

With these in mind let's open up the default index.js file - and we can connect to a MySQL server with:

var mysql = require("mysql");

var connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
});

connection.connect((err) => {
    if (err) {
      console.log("Error occurred", err);
    } else {
      console.log("Connected to MySQL Server");
    }
});

Once a connection has been configured, you can simply connect() to the server! The connect() function uses a callback which allows you to check whether the connection was successful or not.

Writing Queries through JavaScript

Almost all of the actions you need to perform in a MySQL database are done through queries, written in, naturally, SQL. The query() method of the connection instance accepts a string, representing a query we'd like to send to the database.

A query may fail or return some result, so we'll have yet another callback with either an err or result based on how the operation went.

Creating a Database

Let's start out by creating a database through Node's MySQL driver. You can create a database on the server via the CLI (if you've installed it) or through any form of GUI - official or third-party, as well.

Alternatively, you can use the connection we've created to send a query that creates a database for you! Let's create a new students_records database. In the project's root directory, in the same index.js we've used before, let's add a query() call after connecting to the database, if the connection was successful:

var mysql = require("mysql");

// Create connection configuration
var connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
});

// Connect to the server
connection.connect((err) => {
    if (err) {
      // Return error if present
      console.log("Error occurred", err);
    } else {
      // Create database
      console.log("Connected to MySQL Server");
      const query = "CREATE DATABASE students_records";
      connection.query(query, function (err, result) {
        if (err) {
          err;
        }
        console.log("New database created");
      });
    }
});

After verifying to see if our connection was successful, we executed a query with a query string - 'CREATE DATABASE students_records', which creates a new database if not already present. Now, let's run the project:

$ node index.js

Which results in:

Connected to MySQL Server
New database created

Awesome! Now that a students_records database exists, we can scrap the database creation query and connect straight to students_records instead in the configuration:

var connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "students_records",
});

Creating Records

Just like in every other relational database, data records in MySQL are stored in tables with columns and rows. A table can consist of any arbitrary number of columns and rows, but they have to be consistent. The columns in the database represent features/fields of an object, and each row represents a single entry.

Each row must have a value for each column and only those columns, even if it's just NULL. The schema must stay consistent for any relational database.

This means that we'll need to create a table upfront and define its schema (columns and types for each) in order to add new records to our database.

To create a new table in MySQL, we use the CREATE TABLE statement. Say we'd like to create a students table in our students_records database. Again, once connected, we can just execute the relevant query:

var mysql = require("mysql");

var connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "students_records",
});

connection.connect((err) => {
    if (err) {
      console.log("Error occurred", err);
    } else {
      console.log("Connected to database");
      var sql = "CREATE TABLE students (id INT, name VARCHAR(255), course VARCHAR(255))";
      connection.query(sql, function (err, result) {
        if (err) {
          console.log(err);
        }
        console.log("New table created");
      });
    }
});

This creates a new table that can hold records with the name and course fields! Each entry will have a name and course, even if some of them are null. Additionally, we've set their type to be of VARCHAR with a maximum length of 255 characters.

This is where you can additionally set other constraints, such as certain fields being mandatory (NOT NULL), or PRIMARY KEY/FOREIGN KEY:

CREATE TABLE students (
  id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  course VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
)

Here, none of the fields can be set to NULL, and each of them has some associated data type.

Create Record

The INSERT INTO query, followed by the table name and the VALUES mapped to its columns is used to insert/create records with SQL! For instance, let's add a John Doe to our students table:

var sql = "INSERT INTO students (id, name, course) VALUES (1, 'John Doe', 'Computer Science')";

This statement would, unsurprisingly, insert a record (row) into the students table, with values for the name and course. The query, as usual, can be executed via connection.query():

var sql = "INSERT INTO students (id, name, course) VALUES (1, 'John Doe', 'Computer Science')";

// Just execute
connection.query(sql);
// Or execute with callback to handle results/errors
connection.query(sql, function (err, result) {
  // ...
});

Reading Records

We don't just want to store data - we also want to be able to read it. And, as seen below, we can retrieve data from the students table with the SELECT query.

We can select specific records or use a wildcard (*) to select all of the available ones:

var sql = "SELECT * FROM students";
connection.query(sql, function (err, result) {
  console.log(result);
});

Running this code will return all of the data present in our table:

[
  RowDataPacket { id: 1, name: 'John Doe', course: 'Computer Science' },
  RowDataPacket { id: 2, name: 'Jane Doe', course: 'Art' }
]

You can additionally use the BETWEEN statement to create a range of matching rows:

SELECT * FROM students WHERE id BETWEEN 1 AND 5

Which would return all the students, whose ID is between 1 and 5.

Updating Records

We can use the UPDATE statement to update database tables, and their contents. For instance, you can use several statements to identify a certain record and update that record. Generally speaking, an id should be a PRIMARY KEY for identification of a row, as well as a unique value, so the id is typically the column by which to search for records.

Say we want to update the student, John Doe's, course from Computer Science to Art, alongside his cousin. John's id is 1:

var sql = "UPDATE students SET course = 'Art' WHERE id = 1";

connection.query(sql, function (err, result) {
  console.log(result);
});

The result of a query like this one typically includes details about how the query affects the table, which is how you can verify if the result has worked as you intended had or not:

 {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1
}

Deleting Records

Finally, to remove records - we use the DELETE statement, and specify which records are to be dropped:

var sql = "DELETE FROM students WHERE id = 1";
connection.query(sql, function (err, result) {
  console.log(result);
});

Escaping Query Values

A common early exploit was discovered when software engineers allowed user input to be used within SQL calls. For instance, you could have a search bar with an input field. The user would insert the name of a book, and that input would be used within a query such as:

SELECT * FROM books WHERE name = user_input

The user_input would, naturally, be replaced with a string, such as 'Foundation'. However, this opens up a door for huge exploits, which are as simple as escaping the query you've built and running their own.

For instance, if the system was built to naively accept the input and insert it into the query, it would be enough for the user to enter a; DROP TABLE books to exploit your query:

SELECT * FROM books WHERE name = a; DROP TABLE books

The semicolon would finish the previous statement, and the next one in line would run, dropping the entire table from the database and removing all books. This is a simple example of SQL Injection, and attacks can get a lot more complex than this. Additionally, not all attacks can be destructive - an attacker might also just read sensitive data, which is a silent attack that usually goes under the radar.

Given how prevalent these attacks could become - most packages have basic built-in security. The mysql driver built for Node provides functions and measures to help avoid SQL Injections.

The most common method is escaping values and using them as named parameters instead:

const input = 'Foundation';

connection.query('SELECT * FROM books WHERE name = ?', [input], function (err, results, fields) {
// ... 
});

By using the ? escape character - you escape the input and turn it into a literal string, which can't be evaluated. If someone entered a command into the input field that leads to this parameter - it wouldn't be evaluated as a command. It'd be treated as a literal string, and a query would be sent to find a book whose name matches that command instead.

The method accepts an array of parameters, which are sequentially input into the query. If the query has 2 annotated parameters, the first item in the list would be mapped to the first parameter, and the second items in the list would be mapped to the second parameter.

Alternatively, you can inject these values explicitly through JavaScript's string formatting and use the escape() method to escape the possible commands, though - this approach is generally less readable:

const input = mysql.escape('Foundation');

connection.query(`SELECT * FROM books WHERE name = ${input}`, function (err, results, fields) {
// ... 
});

Setting Timeout Times

Every connection-related operation will terminate after a default timeout period, and we can fix this by setting our own preferred timeout session. This is done by passing an object to the .query() function, with our query and the timeout session (in milliseconds) as the fields of that object:

connection.query({sql: 'SELECT * FROM students', timeout: 50000}, function (err, results) {
// ...
});

This code will run a SELECT query which will terminate after 50 seconds, if no result is returned.

Conclusion

MySQL is a commonly used relational database. In this guide, we've taken a look at how to install drivers for a MySQL server for a Node.js project, how to set up a connection with the server and perform CRUD operations.

Finally, we've noted the importance of escaping user input and took a look at customizing timeout periods.