How to Query a MySQL Database Using Node JS

How to Query a MySQL Database Using Node JS

Here you will learn how to Query a MySQL Database Using Node JS. To Query the MySQL Database we will use the mysql2 module.

But, before going further, I suggest you first check out this – How to create MySQL Database Connection with Node JS using mysql2 module.



const mysql = require('mysql2');
const db_connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'db_name',
    password:'db_password'
});

The Database Connection object has the query() method for querying the database.

The query() method takes three parameters –

  • SQL Query
  • Array (Optional)
  • Callback Function

db_connection.query(
"SELECT * FROM `table_name`",// SQL Query
() => {}// callback function
);

And the callback function accepts three parameters –

  • error – It will give you query execution errors
  • results – It will give you the query result
  • fields (optional) – It will give you all the column details

db_connection.query(
"SELECT * FROM `table_name`",// SQL Query
(error, result, fields) => {}// callback function
);

Array parameter of the query method

The second parameter of the query() method is an array and that is optional. It is used, when you add a placeholder or placeholders (?) to your SQL query and inside the array, you pass the placeholder values.


db_connection.query(
"SELECT * FROM `table_name` WHERE `id`=? AND `name`=?",
[12, 'john'],
(error, result, fields) => {
  console.log(result);
}
);

How to use Prepared Statements

You also get the prepared statements with this mysql2 module. I suggest you always use the prepared statement. Because it protects you from SQL Injection attacks.

The Database Connection object also has the execute() method for performing the prepared statement.


db_connection.execute(
"SELECT * FROM `table_name` WHERE `id`=? AND `name`=?",
[12, 'john'],
(error, result, fields) => {
  console.log(result);
}
);

How to use Promise

This mysql2 module also supports Promise API.

db_connection.promise()
.execute("SELECT * FROM `table_name` WHERE `id`=? AND `name`=?", [12, 'john'])
.then(([result,fields]) => {
    console.log(result);
}).catch(err => {
    console.log(err);
});

Leave a Reply

Your email address will not be published. Required fields are marked *