Node.js MySQL Select From

In Node.js, you can query data from a MySQL database using the mysql2 package. The SELECT statement allows you to retrieve data from one or more tables.

 

Key Features of Node.js MySQL Select From

1. Basic Select Query: Retrieve data from a single table.
2. Select with Conditions: Use WHERE clause to filter results.
3. Select with Multiple Columns: Fetch specific columns or all columns.
4. Order Results: Use ORDER BY to sort the data.
5. Asynchronous Operations: Use callbacks or promises to handle asynchronous queries.

 

Step 1 Prerequisites

Ensure that MySQL is installed and running on your system and that the mysql2 package is installed in your Node.js project.

npm install mysql2

Step 2 Select All Records from a Table

You can fetch all records from a table by using the SELECT * query.

Example Code

const mysql = require('mysql2');

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

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

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

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Records retrieved:', results);
  });

  // Close the connection
  connection.end();
});

Output:

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

Step 3 Select Specific Columns

You can select specific columns by specifying their names instead of using *.

Example Code

const mysql = require('mysql2');

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

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select specific columns from the table
  const selectQuery = 'SELECT name, email FROM users';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Selected columns retrieved:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Selected columns retrieved: [
  { name: 'John Doe', email: '[email protected]' },
  { name: 'Jane Doe', email: '[email protected]' },
  ...
]

Step 4 Select with Conditions

You can filter the results using a WHERE clause to retrieve records that match specific criteria.

Example Code

const mysql = require('mysql2');

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

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where name is 'John Doe'
  const selectQuery = 'SELECT * FROM users WHERE name = ?';
  const values = ['John Doe'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Records retrieved:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Records retrieved: [
  { id: 1, name: 'John Doe', email: '[email protected]' }
]

Step 5 Select with Ordering

You can order the results using ORDER BY to sort data in ascending or descending order.

Example Code

const mysql = require('mysql2');

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

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select all records and order by 'name' in ascending order
  const selectQuery = 'SELECT * FROM users ORDER BY name ASC';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Ordered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Ordered records: [
  { id: 2, name: 'Alice', email: '[email protected]' },
  { id: 1, name: 'Bob', email: '[email protected]' },
  ...
]

Step 6 Using Promises with Select Query

For a more modern approach, you can use promises to work with the query results in a cleaner way.

Example Code with Promises

const mysql = require('mysql2/promise');

async function selectData() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',        // Replace with your MySQL username
    password: '',        // Replace with your MySQL password
    database: 'testdb'    // Replace with your database name
  });

  try {
    // Select all records from the table
    const [rows, fields] = await connection.execute('SELECT * FROM users');
    console.log('Records retrieved:', rows);
  } catch (err) {
    console.error('Error fetching records:', err);
  } finally {
    await connection.end();
  }
}

selectData().catch(console.error);

Output:

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

 

Summary

Selecting data from a MySQL database using Node.js is easy with the mysql2 package. You can fetch all records, specific columns, or use conditions to filter results. The ORDER BY clause can be used for sorting data. Asynchronous operations are handled through callbacks or promises, allowing for cleaner and more manageable code. Proper error handling and connection management ensure smooth database interactions.