Node.js is a popular programming language like PHP & JAVA for web applications. Also, MySQL is the most popular database used for storing values. MySQL database driver for Node.js is available under the NPM repository. In this tutorial you will learn to how to connect MySQL database using node.js and do INSERT, UPDATE, SELECT and DELETE operations on MySQL database table.
Step 1 – Create MySQL Database and Table
First, create a database and user in MySQL for your applications. For testing purpose, we are creating mydb database and use it.
mysql> CREATE DATABASE mydb; mysql> USE mydb;
Now create an example table for doing insert, update and delete operations.
1 2 3 4 5 6 7 8 | CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, firstname varchar(30), lastname varchar(30), email VARCHAR(50), creation_date TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
Also create a mysql user for accessing database from application.
mysql> GRANT ALL on mydb.* to 'myusr'@'localhost' identified by '123456'; mysql> FLUSH PRIVILEGES;
2. Install Node.js MySQL Module
MySQL driver for node.js is available under the node package manager (NPM). Use the following command to install it.
sudo npm install mysql
3. Simple App to Connect MySQL
Below is a sample node.js program which will connect node.js application with MySQL server. It will show success and error messages according to connections results and close the connection at the end of the program. Create a JavaScript file app.js.
cd myApp vim app.js
and add the following content to above file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | var mysql = require('mysql'); var dbconn = mysql.createConnection({ host : 'localhost', user : 'myusr', password : '123456', database : 'mydb' }); dbconn.connect(function(err){ if(err){ console.log('Database connection error'); }else{ console.log('Database connection successful'); } }); dbconn.end(function(err) { // Function to close database connection }); |
Now execute the above script using nodejs and make sure that the database is connecting properly or not.
node app.js Database connection successful
3. Insert Data in MySQL Table
Now add the code in above script to insert data in users table in mydb database.
var mysql = require('mysql'); var dbconn = mysql.createConnection({ host : 'localhost', user : '<DB_USER>', password : '<DB_PASSWORD>', database : '<DB_NAME>' }); dbconn.connect(function(err){ if(err){ console.log('Database connection error'); }else{ console.log('Database connection successful'); } }); var record= { firstname: 'Rahul', lastname: 'Kumar', email: '[email protected]' }; dbconn.query('INSERT INTO users SET ?', record, function(err,res){ if(err) throw err; console.log('Last record insert id:', res.insertId); }); dbconn.end(function(err) { // Function to close database connection }
Now execute app.js script using the command line and make sure that data is properly inserted in the database. Repeat this step a few more times with updating values in the above script.
node app.js Database connection successful Last record insert id: 5
Also, check the record added in users table under the mydb database using the mysql command line.
mysql> select * from users; +----+-----------+----------+----------------+---------------------+ | id | firstname | lastname | email | creation_date | +----+-----------+----------+----------------+---------------------+ | 5 | Rahul | Kumar | [email protected] | 2016-01-22 17:16:19 | | 6 | John | Smith | [email protected] | 2016-01-22 17:16:55 | +----+-----------+----------+----------------+---------------------+ 2 row in set (0.01 sec)
4. Select Data from MySQL Table
Now edit app.js script and add the following code. This will fetch all records from users table in mydb database.
dbconn.query('SELECT * FROM users',function(err, records){ if(err) throw err; console.log('Data received from Db:n'); console.log(records); });
or you can customize search by selection values basis of specific conditions.
dbconn.query('SELECT * FROM users WHERE id = ?' [5] ,function(err, records){ if(err) throw err; console.log('Data received from Db:n'); console.log(records); });
5. Update Data from MySQL Table
Now if you required to update existing data in MySQL table, use the following code.
dbconn.query('SELECT users SET email = ? WHERE id = ?', ['[email protected]', 6], function(err, result){ if(err) throw err; console.log('Record Updated ' + result.changedRows + ' rows'); });
Now check values in database table.
mysql> select * from users; +----+-----------+----------+----------------+---------------------+ | id | firstname | lastname | email | creation_date | +----+-----------+----------+----------------+---------------------+ | 5 | Rahul | Kumar | [email protected] | 2016-01-22 17:16:19 | | 6 | John | Smith | [email protected] | 2016-01-22 17:16:55 | +----+-----------+----------+----------------+---------------------+ 2 row in set (0.01 sec)
6. Delete Data from MySQL Table
Now if you delete some record from the table, add following code in your node.js application code.
dbconn.query('DELETE FROM users WHERE id = ?', [6], function(err, result){ if(err) throw err; console.log('Record Updated ' + result.affectedRows + ' rows'); });
Now check values in database table.
mysql> select * from users; +----+-----------+----------+----------------+---------------------+ | id | firstname | lastname | email | creation_date | +----+-----------+----------+----------------+---------------------+ | 5 | Rahul | Kumar | [email protected] | 2016-01-22 17:16:19 | +----+-----------+----------+----------------+---------------------+ 2 row in set (0.01 sec)
5 Comments
this is awesome tutorial and really helpful please make a tutorial on reset password with mysql and jwt token in node.js
// include mysql module
var mysql = require(‘mysql’);
// create a connection variable with the required details
var con = mysql.createConnection({
host: “localhost”, // ip address of server running mysql
user: “root”, // user name to your mysql database
password: “pass”, // corresponding password
database: “my_info” // use the specified database
});
// make to connection to the database.
con.connect(function(err) {
if (err) throw err;
console.log(“Connected!”);
// if connection is successful
con.query(“INSERT INTO students (idname,name,marks) values (12,’Anisha’,95)”, function (err, result, fields) {
// if any error while executing above query, throw error
if (err) throw err;
// if there is no error, you have the result
console.log(result);
});
});
Getting error :
TypeError: Cannot read property ‘createConnection’ of undefined
hai very nice blog .have you created any crud application in node js and express and mysql …if so please let me know and send me the link
What is insertId? on console.log(‘Last record insert id:’, res.insertId);
insertId represent to your data’s primary key that just created.
Usually we using in case that we need to insert as a foreign key on another table.