Facebook X (Twitter) Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook X (Twitter) Instagram
    TecAdmin
    You are at:Home»Programming»Nodejs»Node.js with MySQL Examples

    Node.js with MySQL Examples

    By RahulJuly 29, 20194 Mins Read

    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)
    

    MySQL node.js NodeJs
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Working with Python FastAPI and MySQL

    Resolving MySQL ERROR 1041 (HY000): Out of Memory

    A Practical Guide for Reading ‘.env’ Files in Node.js

    View 5 Comments

    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

    Advertisement
    Recent Posts
    • Using .env Files in Django
    • Using .env File in FastAPI
    • Setting Up Email Notifications for Django Error Reporting
    • How to Enable Apache Rewrite (mod_rewrite) Module
    • What are Microservices?
    Facebook X (Twitter) Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

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