How to SELECT data from a MySQL database using Node.js

A guide on how to use Prepared Statements in Node.js to query data from a MySQL database with multiple parameters safely and effectively.

In this article, you'll learn how to connect to a MySQL database and use Node.js with Prepared Statements to execute a SELECT statement, retrieving data from a table within the database using multiple parameters.

// Import the MySQL library
const mysql = require('mysql2');

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test_db'
});

// Connect to the database
connection.connect(err => {
  if (err) throw err;
  console.log('Connected to MySQL');
  
  // SELECT statement with Prepared Statements
  const sql = 'SELECT * FROM students WHERE id = ? AND name = ?';
  const params = [1, 'John Doe'];

  // Execute the query
  connection.execute(sql, params, (err, results) => {
    if (err) throw err;
    
    // Display the results
    console.log(results);
    
    // Close the connection
    connection.end();
  });
});

Detailed explanation:

1. const mysql = require('mysql2');: Imports the mysql2 library to allow MySQL connections and queries.
2. const connection = mysql.createConnection(...): Sets up the connection to the MySQL database using details such as host, user, password, and database.
3. connection.connect(err => {...}): Connects to the database and checks for any connection errors.
4. const sql = 'SELECT * FROM students WHERE id = ? AND name = ?';: Defines the SELECT statement with parameters.
5. const params = [1, 'John Doe'];: Declares the parameter values to be passed into the SELECT statement.
6. connection.execute(sql, params, (err, results) => {...}): Executes the SELECT statement with the specified parameters and displays the results.
7. connection.end();: Closes the connection to the database.

System Requirements:

• Node.js
• Library: mysql2

How to install the libraries needed to run the Node.js code above:

Use npm to install the library:

npm install mysql2

Tips:

• Ensure that your MySQL server is running before attempting to connect.
• Use Prepared Statements to prevent SQL injection attacks.
• Double-check your connection details to avoid any connection issues.