Node.js MySQL SELECT FROM Query

In Node.js, a MySQL SELECT FROM query is used to read rows from a table and use them inside a JavaScript program. The query may return all columns, selected columns, filtered rows, sorted rows, or a limited number of records depending on the SQL statement.

In this tutorial, we will learn how to execute a MySQL SELECT query from a Node.js program, read the result rows, access individual column values, and inspect the field metadata returned by the MySQL driver.

The examples in this page use the mysql package and callback-based queries. The same SQL ideas apply when you use a promise-based driver such as mysql2/promise, but the JavaScript syntax will be different.

Node.js MySQL SELECT FROM examples covered in this tutorial

MySQL students table used for Node.js SELECT query examples

We shall use the following MySQL table in the examples.

DATABASE : studentsDB

Table: students

mysql> select * from students;
+----------+--------+-------+
| name     | rollno | marks |
+----------+--------+-------+
| John     |      1 |    74 |
| Arjun    |      2 |    74 |
| Prasanth |      3 |    77 |
| Adarsh   |      4 |    78 |
| Raja     |      5 |    94 |
| Sai      |      6 |    84 |
| Ross     |      7 |    54 |
| Monica   |      8 |    86 |
| Lee      |      9 |    98 |
| Bruce    |     10 |    92 |
| Sukumar  |     11 |    99 |
+----------+--------+-------+
11 rows in set (0.01 sec)

To create a similar table for practice, you can use the following SQL. This block is optional if you already have the students table in your MySQL database.

</>
Copy
CREATE DATABASE IF NOT EXISTS studentsDB;
USE studentsDB;

CREATE TABLE IF NOT EXISTS students (
  name VARCHAR(50),
  rollno INT,
  marks INT
);

INSERT INTO students (name, rollno, marks) VALUES
('John', 1, 74),
('Arjun', 2, 74),
('Prasanth', 3, 77),
('Adarsh', 4, 78),
('Raja', 5, 94),
('Sai', 6, 84),
('Ross', 7, 54),
('Monica', 8, 86),
('Lee', 9, 98),
('Bruce', 10, 92),
('Sukumar', 11, 99);

Install MySQL package before running Node.js SELECT queries

If the mysql package is not installed in your Node.js project, install it from the command line.

</>
Copy
npm install mysql

The examples below use require('mysql'). If your project uses ES modules, adjust the import style according to your project configuration.

Example 1 – MySQL SELECT FROM Query via Node.js

In this example, we select all the rows and all the columns from the MySQL table. The result contains every row returned by the SELECT * query.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query Example
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Output

$ node example.js 
[ RowDataPacket { name: 'John', rollno: 1, marks: 74 },
  RowDataPacket { name: 'Arjun', rollno: 2, marks: 74 },
  RowDataPacket { name: 'Prasanth', rollno: 3, marks: 77 },
  RowDataPacket { name: 'Adarsh', rollno: 4, marks: 78 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Sai', rollno: 6, marks: 84 },
  RowDataPacket { name: 'Ross', rollno: 7, marks: 54 },
  RowDataPacket { name: 'Monica', rollno: 8, marks: 86 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Bruce', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 } ]

The first argument of con.query() is the SQL statement. The callback receives three values: err, result, and fields. For a SELECT query, result is an array-like collection of rows, and each row contains properties that match the selected column names.

Example 2 – Select only Specific Columns of MySQL Table via Node.js

In this example, we select only two columns: name and marks from the MySQL table. Selecting only the columns you need is usually better than using SELECT * in application code because the response is smaller and clearer.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query Example
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT name,marks FROM students", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Output

$ node example.js 
[ RowDataPacket { name: 'John', marks: 74 },
  RowDataPacket { name: 'Arjun', marks: 74 },
  RowDataPacket { name: 'Prasanth', marks: 77 },
  RowDataPacket { name: 'Adarsh', marks: 78 },
  RowDataPacket { name: 'Raja', marks: 94 },
  RowDataPacket { name: 'Sai', marks: 84 },
  RowDataPacket { name: 'Ross', marks: 54 },
  RowDataPacket { name: 'Monica', marks: 86 },
  RowDataPacket { name: 'Lee', marks: 98 },
  RowDataPacket { name: 'Bruce', marks: 92 },
  RowDataPacket { name: 'Sukumar', marks: 99 } ]

Because the query selects only name and marks, the result rows do not include rollno. If you try to read row.rollno from this result, it will be undefined.

Example 3 – Access Result Object of MySQL SELECT FROM Query via Node.js

In this example, we will access rows from Result Object using index, columns and DOT operator.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query Example
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
	// iterate for all the rows in result
	Object.keys(result).forEach(function(key) {
	  var row = result[key];
	  console.log(row.name)
	});
  });
});

Output

$ node example.js 
John
Arjun
Prasanth
Adarsh
Raja
Sai
Ross
Monica
Lee
Bruce
Sukumar

You can also use a regular loop or forEach() on the result rows. The following example shows a shorter way to print each student’s name and marks.

</>
Copy
result.forEach(function(row) {
  console.log(row.name + ' scored ' + row.marks);
});

If a selected column name contains spaces or special characters, use bracket notation such as row['column name']. For normal column names such as name, rollno, and marks, dot notation is simple and readable.

Example 4 – Fields Object of MySQL SELECT FROM Query via Node.js

Fields contain information about columns of table. Each field contains all information about a column.

example.js

</>
Copy
// Node.js MySQL SELECT FROM query Example
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the fields object
	// iterate for all the rows in fields object
	Object.keys(fields).forEach(function(key) {
	  var field = fields[key];
	  console.log(field)
	});
  });
});

Output

$ node example.js 
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'name',
  orgName: 'name',
  charsetNr: 33,
  length: 150,
  type: 253,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'rollno',
  orgName: 'rollno',
  charsetNr: 63,
  length: 11,
  type: 3,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }
FieldPacket {
  catalog: 'def',
  db: 'studentsDB',
  table: 'students',
  orgTable: 'students',
  name: 'marks',
  orgName: 'marks',
  charsetNr: 63,
  length: 11,
  type: 3,
  flags: 0,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true }

You may use the elements of a field object using dot operator. Example field.catalog, field.name, field.type, etc.

The fields object is useful when you need column metadata, for example while building a dynamic table display or checking the names of columns returned by a query. For ordinary data access, you usually work with the result rows instead.

Node.js MySQL SELECT with WHERE, ORDER BY, and LIMIT

A real application rarely reads every row from a table. You may need to filter records with WHERE, sort them with ORDER BY, or restrict the number of rows with LIMIT.

</>
Copy
con.query(
  "SELECT name, rollno, marks FROM students WHERE marks >= 80 ORDER BY marks DESC LIMIT 5",
  function(err, result, fields) {
    if (err) throw err;
    console.log(result);
  }
);

The SQL statement above returns a maximum of five students whose marks are greater than or equal to 80, ordered from highest marks to lowest marks.

[ RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Bruce', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Monica', rollno: 8, marks: 86 } ]

Parameterized SELECT query in Node.js MySQL

When a value comes from a user, form, URL parameter, or API request, do not join it directly into the SQL string. Use placeholders so that values are handled separately from the SQL statement.

The mysql package supports ? placeholders. The values are passed as an array in the second argument to con.query().

</>
Copy
var minimumMarks = 80;

con.query(
  "SELECT name, rollno, marks FROM students WHERE marks >= ? ORDER BY marks DESC",
  [minimumMarks],
  function(err, result, fields) {
    if (err) throw err;
    console.log(result);
  }
);

For multiple values, add one placeholder for each value and pass them in the same order.

</>
Copy
var minimumMarks = 80;
var maximumMarks = 95;

con.query(
  "SELECT name, rollno, marks FROM students WHERE marks BETWEEN ? AND ? ORDER BY marks DESC",
  [minimumMarks, maximumMarks],
  function(err, result, fields) {
    if (err) throw err;
    console.log(result);
  }
);

Placeholders make the query easier to read and reduce the risk of SQL injection when handling input values. For more details, refer to the mysqljs/mysql package documentation and MySQL’s article on parameterizing MySQL queries in Node.

Close the MySQL connection after a Node.js SELECT query

For small scripts, close the connection after the query completes. This helps the Node.js process exit cleanly.

</>
Copy
con.query("SELECT name, marks FROM students", function(err, result) {
  if (err) throw err;

  console.log(result);

  con.end(function(endErr) {
    if (endErr) throw endErr;
  });
});

In server applications, you may use a connection pool instead of opening and closing a single connection for every request. A pool lets the application reuse database connections.

Common Node.js MySQL SELECT query errors

The following checks help when a Node.js MySQL SELECT query does not return the expected rows.

  • ER_ACCESS_DENIED_ERROR: Check the MySQL username, password, host, and database privileges.
  • ER_BAD_DB_ERROR: Make sure the database name in database: "studentsDB" exists.
  • ER_NO_SUCH_TABLE: Confirm that the table name is students and that the connection is using the correct database.
  • Empty result array: The query ran successfully, but no rows matched the condition. Check the WHERE clause.
  • undefined column value: The column may not be included in the SELECT list, or the JavaScript property name may not match the column name.

Node.js MySQL SELECT FROM query editorial QA checklist

  • The database name in the connection object matches the MySQL database used in the examples.
  • The table name is written consistently as students in SQL and JavaScript.
  • Every new JavaScript code block uses language-javascript with syntax where it is a syntax demonstration.
  • Output-only blocks use the output class.
  • User-supplied values are shown with ? placeholders instead of string concatenation.
  • The tutorial explains both result rows and fields metadata.

FAQs on Node.js MySQL SELECT FROM query

How do I select all rows from a MySQL table in Node.js?

Use con.query("SELECT * FROM table_name", callback). In the callback, the result argument contains the rows returned by the query.

How do I select only specific columns in Node.js MySQL?

Write the required column names in the SQL statement, such as SELECT name, marks FROM students. The returned row objects will contain only those selected columns.

What is the result object in a Node.js MySQL SELECT query?

For a SELECT query, the result object is a collection of rows. Each row can be accessed by index and each column value can be read using dot notation or bracket notation.

What is the fields object in Node.js MySQL?

The fields object contains metadata about the selected columns, such as column name, table name, type, and length. It is useful when building dynamic displays or inspecting query output.

How do I safely pass values to a Node.js MySQL SELECT query?

Use ? placeholders and pass values as an array to con.query(). For example, use WHERE marks >= ? and pass [minimumMarks].

Node.js MySQL SELECT FROM Query Summary

In this Node.js Tutorial – Node.js MySQL – Node.js MySQL SELECT FROM query, we have learnt to fetch records from a MySQL table, select specific columns, read values from the result object, inspect the fields object, filter rows with WHERE, sort rows with ORDER BY, limit rows with LIMIT, and pass values safely using placeholders.