YouTip

\n

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

\n

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

\n

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

\n

Install Driver

\n

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

\n

$ cnpm install mysql

\n

Connect to Database

\n

In the following example, modify the database username, password, and database name according to your actual configuration:

\n

test.js File Code:

\n

var mysql = require('mysql');\nvar connection = mysql.createConnection({\n  host : 'localhost',\n  user : 'root',\n  password : '123456',\n  database : 'test'\n});\n\nconnection.connect();\n\nconnection.query('SELECT 1 + 1 AS solution', function(error, results, fields){\n  if(error) throw error;\n  console.log('The solution is: ', results.solution);\n});

\n

Execute the following command, the output result is:

\n

$ node test.js\n\nThe solution is: 2

\n

Database Connection Parameter Description:

\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n

Parameter	Description
host	Host address (default: localhost)
user	Username
password	Password
port	Port number (default: 3306)
database	Database name
charset	Connection character set (default: 'UTF8_GENERAL_CI', note that the letters of the character set must be in uppercase)
localAddress	This IP is used for TCP connections (optional)
socketPath	Connect to unix domain path, ignored when using host and port
timezone	Timezone (default: 'local')
connectTimeout	Connection timeout (default: unlimited; unit: milliseconds)
stringifyObjects	Whether to serialize objects
typeCast	Whether to convert column values to native JavaScript type values (default: true)
queryFormat	Custom query statement formatting method
supportBigNumbers	When the database supports bigint or decimal type columns, this option needs to be set to true (default: false)
bigNumberStrings	When supportBigNumbers and bigNumberStrings are enabled, force bigint or decimal columns to be returned as JavaScript string types (default: false)
dateStrings	Force timestamp, datetime, data types to be returned as string types instead of JavaScript Date types (default: false)
debug	Enable debugging (default: false)
multipleStatements	Whether to allow multiple MySQL statements in one query (default: false)
flags	Used to modify connection flags
ssl	Use ssl parameters (same format as crypto.createCredentials parameters) or a string containing the name of an SSL configuration file. Currently only Amazon RDS configuration files are bundled

\n

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

\n

---

\n

Database Operations (CRUD)

\n

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

\n

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

\n

Query Data

\n

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

\n

Query Data

\n

var mysql = require('mysql');\nvar connection = mysql.createConnection({\n  host : 'localhost',\n  user : 'root',\n  password : '123456',\n  port: '3306',\n  database: 'test'\n});\n\nconnection.connect();\n\nvar sql = 'SELECT * FROM websites';\n\nconnection.query(sql,function(err, result){\n  if(err){\n    console.log(' - ',err.message);\n    return;\n  }\n  console.log('--------------------------SELECT----------------------------');\n  console.log(result);\n  console.log('------------------------------------------------------------nn');\n});\n\nconnection.end();

\n

Execute the following command, the output result is:

\n

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

\n

Insert Data

\n

We can insert data into the websites table:

\n

Insert Data

\n

var mysql = require('mysql');\nvar connection = mysql.createConnection({\n  host : 'localhost',\n  user : 'root',\n  password : '123456',\n  port: '3306',\n  database: 'test'\n});\n\nconnection.connect();\n\nvar addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';\nvar addSqlParams = ['Runoob Tools', '','23453', 'CN'];\n\nconnection.query(addSql,addSqlParams,function(err, result){\n  if(err){\n    console.log(' - ',err.message);\n    return;\n  }\n  console.log('--------------------------INSERT----------------------------');\n  console.log('INSERT ID:',result);\n  console.log('-----------------------------------------------------------------nn');\n});\n\nconnection.end();

\n

Execute the following command, the output result is:

\n

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

\n

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

\n

\n

Update Data

\n

We can also modify the data in the database:

\n

Update Data

\n

var mysql = require('mysql');\nvar connection = mysql.createConnection({\n  host : 'localhost',\n  user : 'root',\n  password : '123456',\n  port: '3306',\n  database: 'test'\n});\n\nconnection.connect();\n\nvar modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';\nvar modSqlParams = ['Runoob Mobile Site', '',6];\n\nconnection.query(modSql,modSqlParams,function(err, result){\n  if(err){\n    console.log(' - ',err.message);\n    return;\n  }\n  console.log('--------------------------UPDATE----------------------------');\n  console.log('UPDATE affectedRows',result.affectedRows);\n  console.log('-----------------------------------------------------------------nn');\n});\n\nconnection.end();

\n

Execute the following command, the output result is:

\n

--------------------------UPDATE----------------------------\nUPDATE affectedRows 1\n-----------------------------------------------------------------

\n

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

\n

\n

Delete Data

\n

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

\n

Delete Data

\n

var mysql = require('mysql');\nvar connection = mysql.createConnection({\n  host : 'localhost',\n  user : 'root',\n  password : '123456',\n  port: '3306',\n  database: 'test'\n});\n\nconnection.connect();\n\nvar delSql = 'DELETE FROM websites where id=6';\n\nconnection.query(delSql,function(err, result){\n  if(err){\n    console.log(' - ',err.message);\n    return;\n  }\n  console.log('--------------------------DELETE----------------------------');\n  console.log('DELETE affectedRows',result.affectedRows);\n  console.log('-----------------------------------------------------------------nn');\n});\n\nconnection.end();

\n

Execute the following command, the output result is:

\n

--------------------------DELETE----------------------------\nDELETE affectedRows 1\n-----------------------------------------------------------------

\n

After successful execution, check the data table to see that the data with id=6 has been deleted:

\n