Working with MySQL and Node: A Beginner's Guide

MySQL, a popular relational database, can be seamlessly integrated with Node.js. In this article, we will explore the basics of working with MySQL using the mysqljs/mysql package, a widely-used package that has been around for years and has over 12,000 GitHub stars.

Installing the Node mysql package

To install the package, simply run the following command in your terminal:

npm install mysql

Initializing the connection to the database

To establish a connection to the MySQL database, you need to first include the mysql package in your code:

const mysql = require('mysql');

Next, you can create a connection by providing the necessary credentials such as the MySQL user name, password, and database name:

const options = {
  user: 'the_mysql_user_name',
  password: 'the_mysql_user_password',
  database: 'the_mysql_database_name'
};
const connection = mysql.createConnection(options);

To initiate the connection, use the connect method as shown below:

connection.connect(err => {
  if (err) {
    console.error('An error occurred while connecting to the DB');
    throw err;
  }
});

The connection options

In the above example, the options object contains three options:

const options = {
  user: 'the_mysql_user_name',
  password: 'the_mysql_user_password',
  database: 'the_mysql_database_name'
};

There are several other options you can use, such as host (the database hostname, defaults to localhost), port (the MySQL server port number, defaults to 3306), socketPath (used to specify a Unix socket instead of host and port), debug (used for debugging, disabled by default), and trace (prints stack traces when errors occur, enabled by default). The ssl option is used to set up an SSL connection to the server, but it is beyond the scope of this tutorial.

Performing a SELECT query

Once the connection is established, you can perform SQL queries on the database. The query method takes a SQL query as its first parameter and a callback function as its second parameter. The callback function receives the results and fields of the query as arguments.

connection.query('SELECT * FROM todos', (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query');
    throw error;
  }
  console.log(todos);
});

You can also pass values to the query that will be automatically escaped to prevent SQL injection:

const id = 223;
connection.query('SELECT * FROM todos WHERE id = ?', [id], (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query');
    throw error;
  }
  console.log(todos);
});

For multiple values, simply add more elements to the array passed as the second parameter:

const id = 223;
const author = 'Flavio';
connection.query('SELECT * FROM todos WHERE id = ? AND author = ?', [id, author], (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query');
    throw error;
  }
  console.log(todos);
});

Performing an INSERT query

To insert data into the database, you can pass an object to the query method:

const todo = {
  thing: 'Buy the milk',
  author: 'Flavio'
};
connection.query('INSERT INTO todos SET ?', todo, (error, results, fields) => {
  if (error) {
    console.error('An error occurred while executing the query');
    throw error;
  }
});

If the table has a primary key with auto_increment, the value of that will be returned in the results.insertId value:

const todo = {
  thing: 'Buy the milk',
  author: 'Flavio'
};
connection.query('INSERT INTO todos SET ?', todo, (error, results, fields) => {
  if (error) {
    console.error('An error occurred while executing the query');
    throw error;
  }
  const id = results.resultId;
  console.log(id);
});

Closing the connection

To gracefully terminate the connection to the database and ensure any pending queries are sent, you can call the end() method:

connection.end();

Using these basic steps, you can successfully work with MySQL in your Node.js applications.