Node.js Connect to MySQL

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

If you don't have basic knowledge of MySQL yet, you can refer to our tutorial:MySQL Tutorial。

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

Install Driver

This tutorial uses theTaobao-customized cnpm commandto install:

$ cnpm install mysql

Connect to Database

In the following examples, modify the database username, password, and database name 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); });

Execute the following command; the output result is:

$ node test.js
The solution is: 2

Database connection parameter description:

ParameterDescription
hostHost address (default: localhost)
　　userUsername
　　passwordPassword
　　portPort number (default: 3306)
　　databaseDatabase name
　　charsetConnection charset (default: 'UTF8_GENERAL_CI', note that the letters of the charset must be uppercase)
　　localAddressThis IP is used for TCP connections (optional)
　　socketPathConnect to a Unix domain socket path; ignored when using host and port
　　timezoneTimezone (default: 'local')
　　connectTimeoutConnection timeout (default: unlimited; unit: milliseconds)
　　stringifyObjectsWhether to serialize objects
　　typeCastWhether to convert column values to local JavaScript type values (default: true)
　　queryFormatCustom query statement formatting method
　　supportBigNumbersWhen the database supports bigint or decimal type columns, this option needs to be set to true (default: false)
　　bigNumberStringsWhen supportBigNumbers and bigNumberStrings are enabled, force bigint or decimal columns to be returned as JavaScript string types (default: false)
　　dateStringsForce timestamp, datetime, and date types to be returned as string types instead of JavaScript Date types (default: false)
　　debugEnable debugging (default: false)
　　multipleStatementsAllow multiple MySQL statements in a single query (default: false)
　　flagsUsed to modify connection flags
　　sslUse SSL parameters (same format as crypto.createCredenitals parameters) or a string containing an SSL configuration file name. Currently only Amazon RDS configuration files are bundled.

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

Database Operations (CURD)

Before performing database operations, you need to import the Websites table SQL file provided on this sitewebsites.sqlinto your MySQL database.

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

Query Data

After importing the SQL file we provided above 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; the output result is:

$ 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: 'Example',
    url: 'http://www.example.com/',
    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 data table websties:

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 = ['Example Tools', 'https://c.example.com','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.insertId); console.log('INSERT ID:',result); console.log('-----------------------------------------------------------------\n\n'); }); connection.end();

Execute the following command; the output result is:

$ 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, view the data table and you can see the added data:

Update Data

We can also modify the 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 = ['Example Mobile Site', 'https://m.example.com',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();

Execute the following command; the output result is:

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

After successful execution, view the data table and you can 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();

Execute the following command; the output result is:

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

After successful execution, view the data table and you can see that the data with id=6 has been deleted:

Other Extensions