Contents hide

Beginning With NodeJS And MySql Queries, In this tutorial, we will see the different MySql queries (i.e insert, update, delete, drop, create etc) applying through nodeJS.

Topics :

Setup npm
SQL Database connection
Database And Table Creation
Inserting And Viewing Data
Updating And Deleting Data

Setup npm

Let’s create a directory with any name to create the app through the terminal.


$ mkdir node_sql_demo

After creating directory lets begin with npm setup for the package.json file. So enter the details for file creation.


$ npm init


// filing up the procedure
name: (node_sql_demo)  //default folder name
version: (1.0.0) 0.0.1 // your version number
description: Tutorial on Nodejs With Mysql Queries
entry point: (index.js) app.js // your main file
test command:
git repository:   // git project if any 
keywords: geekstrick
author: rehmaanali
license: (ISC)

As it created the package.json file. So now we just need to install the MySql package by just a single command in terminal.


$ npm install mysql --save

The command will install the MySql node Package as well as it will make changes in the package.json file. Here it shows the dependencies and its version number.


{
  "name": "node_sql_demo",
  "version": "0.0.1",
  "description": "Tutorial On NodeJS with mySql Queries",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "geekstrick"
  ],
  "author": "RehmaanAli",
  "license": "ISC",
	"dependencies": {
		"mysql": "^2.9.0"
	}
}

SQL Database connection

First import the MySql package in the app file by the require method and also start the MySql localhost server.As we declare the main as app.js so the file must be named as that only.


var mysql = require('mysql');
// Creating the connection
var con = mysql.createConnection({
  host: "localhost",
  user: "username",
  password: "password"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!"); // message
});

Now to run the app.js file simply write the command


$ node app.js

conected! // Successfully connected message

Database And Table Creation

And Now will create the database as well a table in that particular Database. But first, we will create the database


var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query("CREATE DATABASE mydb", function (err, result) {
    if (err) throw err;
    console.log("Database created");
  });
});

This will connect database as well as creates the database of name mydb. So run the file as we run earlier i.e $ node app.js.

Creating Table

Creating table is as similar as it was creating a database, but all we need to do is to give the database name in the ‘con’ variable. let’s see:


var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb" // Declare the database name
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
// Create table Query
  var sql = "CREATE TABLE customers (id VARCHAR(12), name VARCHAR(30))";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table created");
  });
});

Inserting And Viewing Data

Let us see the further procedure for inserting data and viewing in the terminal. So first we have to insert data into the table.


var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO customers (id, name) VALUES (0001, 'MR. Bean')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

After the data is inserted successfully, now we can display the data of table on the terminal. So just add the SELECT Query to get the data.


con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query("SELECT * FROM customers", function (err, result) {
    if (err) throw err;
    console.log(result);
	});
});

And the data which is displayed are in raw (json format).


$ node app.js

Connected!
[ RowDataPacket { id: '1', name: 'MR. Bean' }]

Updating And Deleting Data

For Editing the table you just need to change the query. so first we will update the table


var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  var sql = "UPDATE customers SET name = 'MR.Cruze' WHERE id = 0001";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated"); // Shows the number of updated table
  });
});

And at last for deleting the record or table from the database and that is similar to all above queries.


con.connect(function(err) {
  if (err) throw err;
  var sql = "DELETE FROM customers WHERE id = 0001";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Number of records deleted: " + result.affectedRows);
  });
});

Conclusion

This was the simple data handling from database, but we can furthermore classify the working of the MySql in big project’s

Tags: JavaScript, MySql, nodejs

Beginning With NodeJS And MySql Queries