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=todoapp
Code 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.js
Code 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 1
Code 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.