Node.js MySQL: Select Data from a Table

Welcome to The Coding College! In this tutorial, you’ll learn how to retrieve data from a MySQL database using Node.js. The ability to query and display data is essential for creating dynamic, data-driven applications.

Prerequisites

Ensure you have:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. A Populated Table: Follow our Node.js MySQL Insert Into tutorial to add data to your table.

Step 1: Install the MySQL Module

First, install the MySQL module:

npm install mysql

Step 2: Connect to the MySQL Database

Create a file named selectData.js and establish a connection:

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase', // Replace with your database name
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

Step 3: Write a SELECT Query

Use the SELECT statement to fetch data. For example, to retrieve all records from a table named users:

const selectQuery = 'SELECT * FROM users';

Step 4: Execute the Query

Execute the SELECT query using the query() method and handle the result:

connection.query(selectQuery, (err, results, fields) => {
  if (err) throw err;
  console.log('Data retrieved:', results);
});

The results array contains the rows returned by the query, while fields provides metadata about the table columns.

Step 5: Close the Connection

After the operation, close the database connection:

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Full Code

Here’s the complete script to retrieve data from a MySQL table:

const mysql = require('mysql');

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

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

// Select data from the table
const selectQuery = 'SELECT * FROM users';

connection.query(selectQuery, (err, results, fields) => {
  if (err) throw err;
  console.log('Data retrieved:', results);
});

// Close the connection
connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Step 6: Run the Script

Execute the script using Node.js:

node selectData.js

If successful, the output will display the rows in your users table:

[
  { id: 1, name: 'Jane Doe', email: '[email protected]' },
  { id: 2, name: 'John Doe', email: '[email protected]' }
]

Advanced: Fetch Specific Columns

To fetch only certain columns, specify them in the query:

const selectQuery = 'SELECT name, email FROM users';

Advanced: Add Conditions

Use the WHERE clause to filter results:

const selectQuery = 'SELECT * FROM users WHERE id = 1';

Advanced: Sort and Limit Results

Sort results using ORDER BY and limit the number of rows with LIMIT:

const selectQuery = 'SELECT * FROM users ORDER BY name ASC LIMIT 5';

Best Practices

  1. Sanitize Inputs: Use placeholders (?) to protect against SQL injection.
  2. Error Handling: Always handle potential errors in your queries.
  3. Pagination: For large datasets, implement pagination to fetch results efficiently.

Conclusion

Retrieving data from a MySQL database with Node.js is straightforward and highly customizable. With the techniques covered in this tutorial, you can fetch and display data in your applications effectively.

At The Coding College, we’re dedicated to helping you learn practical coding skills with clear examples and best practices.

Leave a Comment