MySQL queries in Node.js

This page explains how run SQL queries to read / write data in a MySQL database. In the following, we assume a MySQL server is running and credentials are username/password.

You can check your MySQL in properly installed and configure by using the mysql CLI interface :

mysql --host=127.0.0.1 --port=6033 -u username -p

Do not proceed until you are able to connect to the database. Otherwise, the Node.js code will never work.

The following has been performed with these versions :

Node.js v18.13.0
npm 8.19.2
npm mysql2 3.0.0

Install mysql2

First we need a MySQL client for Node.js. There are two npm packet named mysql and mysql2. mysql2 is faster and secure, so let's install the last one:

npm install mysql2

First query

Create a file named app.js for you project.

First import the MySQL client:

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

Now, let's connect to the database (this is where the credentials are important). Let's create an object dbConfig with the connection configuration.

const dbConfig = {
    host: "127.0.0.1",
    port: 6033,
    user: 'username',  
    database: 'esp32',
    password: 'password'
}

Let's now create a new connection to the database:

// create the connection to database
const connection = mysql.createConnection(dbConfig);

Here is the code for the first query:

// Simple query
connection.query(
    'SELECT * FROM `table`',
    function(err, results, fields) {
        if (err) console.log (err);
        console.log(results);       
    }
  );

The above code should return an object containing the whole content of table:

[
  {
    id: 1,
    name: 'Jim Morrison',
    age: 28,
    timestamp: 2023-01-12T08:06:25.000Z
  },
  {
    id: 2,
    name: 'Paul Smith',
    age: 32,
    timestamp: 2023-01-12T08:07:40.000Z
  }
]

To prevent SQL injection, I do not recommend using connection.query(). Prefer prepared statements.

Using Prepared Statements

Prepared statement sends the query and the parameters separately. If you don't know why they are important, check this page on SQL injections. To run a prepared statement, use connection.execute()`` instead ofconnection.query()`. The second argument of the function is an array containing the parameters :

connection.execute(
    'INSERT INTO `data` (`firstname`, `age`) VALUES (?,?);',    
    ['Jim Morrison', 28],
    (err, results, fields) => {
        // Check for error
        if (err) console.log (err);
        console.log(results); 
    }
  );

Close connection

If you run the above codes, you probably noticed that the application never ends. This is because the connection to the database is asynchronous and left open.

To close the MySQL connection, use:

connection.end();

Download

You can download the Node.js source code here: