{"id":5761,"date":"2017-06-15T00:07:36","date_gmt":"2017-06-15T07:07:36","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5761"},"modified":"2024-01-03T17:41:04","modified_gmt":"2024-01-04T00:41:04","slug":"connect","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-nodejs\/connect\/","title":{"rendered":"Connecting to the MySQL Server from Node.js"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to connect to the MySQL Server from a Node.js application.<\/p>\n\n\n\n

Note that this tutorial targets the MySQL 8.0 and Node.js v20.6.0 or later. Node.js v20.6.0 and newer offers built-in support for .env<\/code> configuration files.<\/p>\n\n\n\n

Installing Node.js driver for MySQL<\/h2>\n\n\n\n

First, open the Command Prompt on Windows or Terminal on Unix-like systems.<\/p>\n\n\n\n

Second, create a directory for storing the Node.js<\/code> app and use the npm init<\/code> command to create the package.json<\/code> file:<\/p>\n\n\n

npm init --yes<\/code><\/span><\/pre>\n\n\n
Third, install the MySQL package using the following npm<\/code> command:<\/p>\n\n\n
npm install mysql<\/code><\/span><\/pre>\n\n\n
Creating a sample database<\/h2>\n\n\n\n
First, connect to the MySQL server:<\/p>\n\n\n
mysql -h localhost -u root -p<\/code><\/span><\/pre>\n\n\n
Second, create a new database<\/a> called todoapp<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> todoapp;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating configuration file .env<\/h2>\n\n\n\n
First, create a new file called .env<\/code> in the project directory.<\/p>\n\n\n\n
Second, add the MySQL connection’s parameters to the .env<\/code> file:<\/p>\n\n\n
DB_HOST=localhost\nDB_PORT=3306\nDB_USER=root\nDB_PASSWORD=\nDB_NAME=todoapp<\/code><\/span><\/pre>\n\n\n
You should replace the DB_HOST<\/code>, DB_PORT<\/code>, DB_USER<\/code>, DB_NAME<\/code>, and DB_PASSWORD<\/code> with the actual ones.<\/p>\n\n\n\n
Connecting to MySQL Server from Node.js<\/h2>\n\n\n\n
First, create a connect.js<\/code> file in the project’s directory.<\/p>\n\n\n\n
Next, import the mysql<\/code> module in the connect.js<\/code> file:<\/p>\n\n\n
let<\/span> mysql = require<\/span>('mysql'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, create a connection to the MySQL server by calling the createConnection()<\/code> function:<\/p>\n\n\n
let<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that we access the MySQL parameters from the .env<\/code> file via the process.env<\/code>. This feature has been available since Node.js v20.6.0.<\/p>\n\n\n\n
After that, call the connect()<\/code> method on the connection<\/code> object to connect to the MySQL server:<\/p>\n\n\n
connection.connect((err<\/span>) =><\/span> {\r\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\r\n\r\n  console<\/span>.log('Connected to the MySQL server.'<\/span>);\r\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The connect()<\/code> method accepts a callback function that has the err<\/code> argument that provides detailed information if any error occurs.<\/p>\n\n\n\n
Here’s the complete connect.js<\/code> program:<\/p>\n\n\n
let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Connected to the MySQL server.'<\/span>);\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, run the connect.js<\/code> program that uses MySQL’s parameters from the .env file:<\/p>\n\n\n
 node --env-file .env connect.js<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Connected to the MySQL server.<\/code><\/span><\/pre>\n\n\n
To make it more convenient, you can change the start property of the package.json file to the following:<\/p>\n\n\n
...\n  \"scripts\"<\/span>: {\n    \"start\"<\/span>: \" node --env-file .env connect.js\"<\/span>\n  },\n...<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, use the npm start command to run the connect.js with information from the .env file:<\/p>\n\n\n
npm start<\/code><\/span><\/pre>\n\n\n
The output indicates that we have successfully connected to the MySQL server from the Node.js program.<\/p>\n\n\n\n
Troubleshooting<\/h2>\n\n\n\n
If you connect to MySQL 8.0 or later, you are likely getting the following error message:<\/p>\n\n\n
error<\/span>: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client\n<\/code><\/span>Code language:<\/span> HTTP<\/span> (<\/span>http<\/span>)<\/span><\/small><\/pre>\n\n\n
In MySQL 8.0, the default authentication plugin is caching_sha2_password<\/code>, unlike MySQL 5.7, which uses the mysql_native_password<\/code> plugin,  supported by most clients. <\/p>\n\n\n\n
Therefore, if you encounter compatibility issues, you must explicitly enable mysql_native_password<\/code> for a given user using the following command:<\/p>\n\n\n
ALTER USER 'user'<\/span> \nIDENTIFIED WITH mysql_native_password BY 'password'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Replace the user and password with the ones that you use to connect to MySQL.<\/p>\n\n\n\n
Closing database connection<\/h2>\n\n\n\n
To close a database connection gracefully, you call the end()<\/code> method on the connection<\/code> object.<\/p>\n\n\n\n
The end()<\/code> method ensures that all remaining queries will be executed before the database connection is closed.<\/p>\n\n\n
connection.end((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Close the database connection.'<\/span>);\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
To force the connection to close immediately, you can use the destroy()<\/code> method. The destroy()<\/code> method guarantees that no more callbacks or events will be triggered for the connection.<\/p>\n\n\n
connection.destroy();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the destroy()<\/code> method does not take any callback argument like the end()<\/code> method.<\/p>\n\n\n\n
The following shows the complete connect.js<\/code> program:<\/p>\n\n\n
let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Connected to the MySQL server.'<\/span>);\n});\n\nconnection.end((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  console<\/span>.log('Close the database connection.'<\/span>);\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Connect to a MySQL database from a Node.js application.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t