Querying Data in MySQL Database from Node.js

Summary: in this tutorial, you will learn how to query data from a table in MySQL from a Node.js application.

This tutorial picks up where the Inserting Rows into a Table from Node.js tutorial left off.

The steps for querying data in the MySQL database from a Node.js application are as follows:

• Establish a connection to the MySQL server.
• Execute a SELECT statement and process the result set.
• Close the database connection.

Executing a simple query

Create a new file called select.js in the project directory and add the following code to query data from the todos table of the todoapp database:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  let sql = `SELECT * FROM todos`;

  connection.query(sql, [true], (error, results, fields) => {
    if (error) return console.error(error.message);
    console.log(results);
  });

  // close the database connection
  connection.end();
});Code language: JavaScript (javascript)

How it works.

First, import the mysql.js module:

let mysql = require('mysql');Code language: JavaScript (javascript)

Second, create a database connection using the createConnection() method and provide connection details like host, port, user, password, and database name:

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});Code language: JavaScript (javascript)

Note that we store the values of the connection details in the .env file:

DB_HOST=localhost
DB_PORT=3306
DB_USER=user
DB_PASSWORD=password
DB_NAME=todoappCode language: plaintext (plaintext)

Third, create a connection to the MySQL server. If there’s an during the connection process, log the error message to the console:

connection.connect((err) => {
  if (err) return console.error(err.message);
  // Connection successful, proceed with queries
  // ...
});
Code language: JavaScript (javascript)

Fourth, execute a SELECT query:

let sql = `SELECT * FROM todos`;

connection.query(sql, [true], (error, results, fields) => {
  if (error) return console.error(error.message);
  console.log(results);
});Code language: JavaScript (javascript)

In this code, we prepare a SELECT statement that retrieves data from the todos table. Then we use the query() method to execute the query.

If the error occurs during the query execution, we log the error message. Otherwise, we display the rows to the console.

Finally, close the database connection:

connection.end();Code language: JavaScript (javascript)

Let’s run the select.js program.

node --env-file .env select.jsCode language: plaintext (plaintext)

Output:

[
  RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1},
  RowDataPacket { id: 2, title: 'Insert a new row with placeholders', completed: 0
  RowDataPacket { id: 3, title: 'Master Node.js MySQL', completed: 0 },
  RowDataPacket { id: 4, title: 'Build Node.js / MySQL App', completed: 1 }
]Code language: plaintext (plaintext)

It returned 4 rows with each row wrapped in a RowDataPacket object.

Passing data to the query

The following creates a select_completed.js program that retrieves completed todos from the todos table:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  let sql = `SELECT * FROM todos WHERE completed=?`;

  connection.query(sql, [true], (error, results, fields) => {
    if (error) return console.error(error.message);
    console.log(results);
  });

  // close the database connection
  connection.end();
});
Code language: JavaScript (javascript)

Output:

[
  RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1 },
  RowDataPacket { id: 4, title: 'Build Node.js / MySQL App', completed: 1}
]Code language: plaintext (plaintext)

In this example, we use the placeholder (?) in the SELECT statement:

let sql = `SELECT * FROM todos WHERE completed=?`;Code language: JavaScript (javascript)

and bind values to it in the query() method:

connection.query(sql, [true], (error, results, fields) => {
  if (error) return console.error(error.message);
  console.log(results);
});Code language: JavaScript (javascript)

Node.js will replace the ? int the SELECT statement by the true argument in the query() method when executing the query.

Preventing SQL injection

Suppose, you want to query a todo based on a specified ID, you might come up with the following code:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

let id = process.argv[2]; // pass argument to query

connection.connect((err) => {
  if (err) return console.error(err.message);

  let sql = `SELECT * FROM todos WHERE id=` + id;

  connection.query(sql, [true], (error, results, fields) => {
    if (error) return console.error(error.message);
    console.log(results);
  });

  // close the database connection
  connection.end();
});Code language: JavaScript (javascript)

For example, you can select the todo with id 1:

 node --env-file .env select_by_id.js 1Code language: CSS (css)

Output:

[ RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1 } ]Code language: JavaScript (javascript)

The program returns the expected data but it has a security issue called SQL injection.

This means a malicious user could manipulate the program by passing SQL code as an argument, potentially causing unauthorized access or data manipulation in the database.

For example, the malicious may pass the following argument to the program to retrieve all rows from the todos table:

node --env-file .env select_by_id.js '1 OR 1 = 1;' Code language: JavaScript (javascript)

In this command, the '1 OR 1 = 1;' is SQL code not the id of the todo.

To address the SQL injection, you need to use either the placeholder (?) and bind the value to the parameter:

let sql = `SELECT * FROM todos WHERE completed=?`;Code language: JavaScript (javascript)

or the escape() method of the mysql or connection object as follows:

let sql = `SELECT * FROM todos WHERE id = ` + mysql.escape(id);Code language: JavaScript (javascript)

In this tutorial, you have learned how to query data in the MySQL database from a Node.js program.