Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Programming»Nodejs»Node.js with MySQL Examples

    Node.js with MySQL Examples

    RahulBy RahulJanuary 26, 20164 Mins ReadUpdated:July 29, 2019

    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.

    PgSQL
    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.

    JavaScript
    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)
    
    MySQL node.js NodeJs
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleHow To Enable or Disable CGI Scripts in Apache 2.4
    Next Article How To Install Symfony 3 Framework on CentOS / RHEL / Fedora

    Related Posts

    How To Install Node.js on Ubuntu 22.04

    Updated:April 16, 20223 Mins Read

    How To Install NVM on Ubuntu 22.04

    Updated:April 16, 20223 Mins Read

    How To Install Linux, Nginx, MySQL, & PHP (LEMP Stack) on Ubuntu 22.04

    Updated:April 7, 20227 Mins Read

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How To Install LAMP Stack on Ubuntu 22.04 LTS

    Updated:April 20, 20225 Mins Read

    How To Install NVM on Windows

    Updated:April 16, 20223 Mins Read

    5 Comments

    1. Deepak kumar on September 5, 2018 6:42 am

      this is awesome tutorial and really helpful please make a tutorial on reset password with mysql and jwt token in node.js

      Reply
    2. Sanja Sameer on May 29, 2018 12:47 pm

      // 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

      Reply
    3. Kumuda M N on March 12, 2018 7:54 am

      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

      Reply
    4. Bryan on March 12, 2017 11:35 pm

      What is insertId? on console.log(‘Last record insert id:’, res.insertId);

      Reply
      • Chanai Moentit on May 2, 2017 11:31 pm

        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.

        Reply

    Leave A Reply Cancel Reply

    Recent Posts
    • How to Enable / disable Firewall in Windows
    • How to Install JAVA on Ubuntu 22.04
    • Switching Display Manager in Ubuntu – GDM, LightDM & SDDM
    • Changing the Login Screen Background in Ubuntu 22.04 & 20.04
    • How To Install PHP (8.1, 7.4 or 5.6) on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.