Node.js Connecting to MySQL

In this section, we will introduce how to use Node.js to connect to MySQL and perform database operations.

If you do not have basic knowledge of MySQL, you can refer to our tutorial: MySQL Tutorial.

The SQL file for the Websites table used in this tutorial: websites.sql.

Installing the Driver

This tutorial uses the Taobao-customized cnpm command for installation:

$ cnpm install mysql

Connecting to the Database

Modify the database username, password, and database name in the following example according to your actual configuration:

test.js File Code:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'test'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

Executing the following command will output the result:

$ node test.js
The solution is: 2

Database Connection Parameters Explanation:

For more details, see: https://github.com/mysqljs/mysql

Database Operations (CRUD)

Before performing database operations, you need to import the Websites table SQL file websites.sql provided by this site into your MySQL database.

The MySQL username used in this tutorial for testing is root, the password is 123456, and the database is test. You need to modify these according to your own configuration.

Querying Data

After importing the SQL file we provided into the database, execute the following code to query the data:

Query Data

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'test'
});

connection.connect();

var sql = 'SELECT * FROM websites';
// Query
connection.query(sql, function (err, result) {
  if (err) {
    console.log('[SELECT ERROR] - ', err.message);
    return;
  }

  console.log('--------------------------SELECT----------------------------');
  console.log(result);
  console.log('------------------------------------------------------------\n\n');
});

connection.end();

Execute the following command to output the results:

$ node test.js
--------------------------SELECT----------------------------
[ RowDataPacket {
    id: 1,
    name: 'Google',
    url: 'https://www.google.cm/',
    alexa: 1,
    country: 'USA' },
  RowDataPacket {
    id: 2,
    name: '淘宝',
    url: 'https://www.taobao.com/',
    alexa: 13,
    country: 'CN' },
  RowDataPacket {
    id: 3,
    name: 'tutorialpro.org',
    url: 'http://www.tutorialpro.org/',
    alexa: 4689,
    country: 'CN' },
  RowDataPacket {
    id: 4,
    name: '微博',
    url: 'http://weibo.com/',
    alexa: 20,
    country: 'CN' },
  RowDataPacket {
    id: 5,
    name: 'Facebook',
    url: 'https://www.facebook.com/',
    alexa: 3,
    country: 'USA' } ]
------------------------------------------------------------

Insert Data

We can insert data into the websties table:

Insert Data

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'test'
});

connection.connect();

var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var addSqlParams = ['tutorialpro', 'https://c.tutorialpro.org', '23453', 'CN'];
// Insert
connection.query(addSql, addSqlParams, function (err, result) {
  if (err) {
    console.log('[INSERT ERROR] - ', err.message);
    return;
  }

  console.log('--------------------------INSERT----------------------------');
  console.log('INSERT ID:', result);
  console.log('------------------------------------------------------------\n\n');
});

connection.end();

console.log('--------------------------INSERT----------------------------');
//console.log('INSERT ID:', result.insertId);
console.log('INSERT ID:', result);
console.log('-----------------------------------------------------------------\n\n');
});

connection.end();

Executing the following command outputs the result:

$ node test.js
--------------------------INSERT----------------------------
INSERT ID: OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 6,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
-----------------------------------------------------------------

After successful execution, check the database table to see the added data:

Update Data

We can also modify data in the database:

Update Data

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'test'
});

connection.connect();

var modSql = 'UPDATE websites SET name = ?, url = ? WHERE Id = ?';
var modSqlParams = ['Mobile Site', 'https://m.tutorialpro.org', 6];
// Update
connection.query(modSql, modSqlParams, function (err, result) {
  if (err) {
    console.log('[UPDATE ERROR] - ', err.message);
    return;
  }
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows', result.affectedRows);
  console.log('-----------------------------------------------------------------\n\n');
});

connection.end();

Executing the following command outputs the result:

--------------------------UPDATE----------------------------
UPDATE affectedRows 1
-----------------------------------------------------------------

After successful execution, check the database table to see the updated data:

Delete Data

We can use the following code to delete the data with id 6:

Delete Data

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  port: '3306',
  database: 'test'
});

connection.connect();

var delSql = 'DELETE FROM websites where id=6';
// Delete
connection.query(delSql, function (err, result) {
  if (err) {
    console.log('[DELETE ERROR] - ', err.message);
    return;
  }

console.log('--------------------------DELETE----------------------------');
console.log('DELETE affectedRows', result.affectedRows);
console.log('-----------------------------------------------------------------\n\n');
});

connection.end();

Executing the following command outputs the result:

--------------------------DELETE----------------------------
DELETE affectedRows 1
-----------------------------------------------------------------

After successful execution, checking the database table will show that the data with id=6 has been deleted.