For the most part, it seems to be that NodeSQL databases are the defacto standard with Node and JavaScript developers. Using MySQL is still an option and can make a lot of sense if the project already has an existing database.

There’s a lot of times where NoSQL databases make sense: dynamic table creation, real time inserts, etc. If your project is dealing with queries that involves joining data, complex queries or using transactions than a SQL database probably makes a lot more sense.

For those not aware, MySQL is an open source relational database management system – it is one of the most widely deployed SQL databases. MySQL focuses on speed, reliability and usability and generally follows the ANSI SQL Standard really well.

For connecting to a MySQL database with Node.js the standard is the mysql module which is available from npm. I like to use an object relationship manager (ORM) instead of directly writing SQL queries but sometimes using raw sql queries makes more sense.

The MySql package works with callbacks, so if you want to use promises you will need to use another package like “promise-mysql” or wrap the library with Bluebird or promisfy yourself. If you need promises instead of callbacks simply install promise-mysql and use promises instead. 🙂

Installing the MySQL Package

Installing the MySQL package works the same way most other packages work. If you have Node and npm installed correctly you shouldn’t have any problems.

In your project root, assuming you are using npm, you can just simply run the following at your terminal

npm i mysql

This will run for a minute or so and add a bunch of packages to your package-lock.json and update your package.json. We’ve now installed the MySQL package as a project dependency which tells npm that this is required for the application to run. Whereas, a dev dependency tells npm that it’s only required for developers or tests.

Connecting to the Database

Connecting to the database is relatively easy for MySQL. You’ll need your host’s ip address, host’s port, the username, the password and finally the database name.

I highly recommend creating a .env file and using that whenever you run the app so you don’t accidentally push credentials into source control.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'my_user',
  password: 'my_password',
  database: 'my_database_name'
});

connection.connect((err) => {
  if (err) {
    throw err;
  }

  console.log('Connected!');
});

I’ve previously blogged about Environment Variables and how to use them but basically all of the variables in createConnection would be replaced with environment variables so it looks like this

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
});

connection.connect((err) => {
  if (err) {
    throw err;
  }

  console.log('Connected!');
});

Executing Queries

In an application, we typically use queries to create, read, update, or delete data known as CRUD. There’s two formats for doing queries.

The basic way of doing a query is to put the values into the SQL string where you would like them. The basic signature is connection.query(sqlString, callback).

Our query using the basic approach would look something like this:

dbConnection.query(`select d.id, first_name, last_name, email, d.name 
from employees e INNER JOIN departments d on e.department_id = d.id
where d.id = ${someId}`, function (error, results) {
  if (error) {
   // handle the error
  } else {
   // do something...
  }
})

While this is pretty easy for one or two values in the query it can start to get really difficult to follow and it’s not very safe.

For simple SELECT or read queries we can pass in parameters we can use the question mark operator (“?”) as part of an array like this:

dbConnection.query(`select d.id, first_name, last_name, email, d.name 
from employees e INNER JOIN departments d on e.department_id = d.id 
where d.id = ?`, [1],  function (error, results) {
  if (error) {
   // handle the error
  } else {
   // do something...
  }
})

The “?” tells the package to put the first parameter into that spot.

The MySQL package supports using queries and stored procedures. A stored procedure is SQL code that is stored in the database server and can be reused over and over again. The beauty of stored procedures is that they often have improved performance, and are often much safer to execute. Parameters can also. be passed to a stored procedure

Avoiding SQL Injection Attacks

By using question mark placeholder (“?”) we can avoid a lot of the risk of SQL Injection attacks which are really easy to do and really risky for businesses.

I like to take this a step further and use an Objection Relationship Mapping (ORM) library which reduces the number of manually created queries. A really well chosen ORM can dramatically improve the development team’s speed. You can read more about ORM’s from my blog post “What is ORM”.

Conclusion

As you can see, connecting to MySQL from NodeJS is pretty easy. It’s possible to use Promises or async / await and callbacks depending on the use case and preference.

When I need or want to use Promises or async/await I use the promise-mysql package instead of the raw mysql package.

In general, it’s best to avoid using mysql directly and use an abstraction like an ORM which can abstract away a lot of writing raw sql code.