Practical 9 Working with Database in Node js
Database is used to store the data from the user in a specific database and in a specific table. It is used to perform operations as create, read, update, delete. All these operations are performed in database. For example: If the user buys the product from amazon so it would be able to display the price from the database to the user.
Linking of MySql Database in Node.js
We can link our MySQL Database in Node.js Backend Side with the mysql module. This module helps in connecting our database with the backend server for storing the data.
Prerequisites:
Properly installed node in your system.
Properly installed npm (node package manager) in your system.
MySQL server installed in your system.
Step 1 - Module installation: To download and install the mysql module, open the Command Terminal, and execute the following command:
Step 2 – XAMPP installation
The name is an acronym, with each letter representing one of the five key components. The software packet contains the web server Apache, the relational database management system MySQL (or MariaDB), and the scripting languages Perl and PHP. The initial X stands for the operating systems that it works with: Linux, Windows, and Mac OS X.
Apache: ihe open source web server Apache is the most widely used server worldwide for delivery of web content. The server application is made available as a free software by the Apache Software Foundation.
MySQL/MariaDB: in MySQL, XAMPP contains one of the most popular relational database management systems in the world. In combination with the web server Apache and the scripting language PHP, MySQL offers data storage for web services. Current XAMPP versions have replaced MySQL with MariaDB (a community-developed fork of the MySQL project, made by the original developers).
PHP: the server-side programming language PHP enables users to create dynamic websites or applications. PHP can be installed on all platforms and supports a number of diverse database systems.
Perl: the scripting language Perl is used in system administration, web development, and network programming. Like PHP, Perl also enables users to program dynamic web applications.
Alongside these core components, this free-to-use Apache distribution contains some other useful tools, which vary depending on your operating system. These tools include the mail server Mercury, the database administration tool phpMyAdmin, the web analytics software solutions Webalizer, OpenSSL, and Apache Tomcat, and the FTP servers FileZilla or ProFTPd.
Step 1: Download
XAMPP is a release made available by the non-profit project Apache Friends. Versions with PHP 5.5, 5.6, or 7 are available for download on the Apache Friends website.
Step 2: Run .exe file
Once the software bundle has been downloaded, you can start the installation by double clicking on the file with the ending .exe.
Step 3: Deactivate any antivirus software
Since an active antivirus program can negatively affect the installation process, it’s recommended to temporarily pause any antivirus software until all XAMPP components have successfully been installed.
Before installing XAMPP, it is advisable to disable the anti-virus program temporarily
Step 4: Deactivate UAC
User Account Control (UAC) can interfere with the XAMPP installation because it limits writing access to the C: drive, so we recommend you deactivate this too for the duration of the installation process. To find out how to turn off your UAC, head to the Microsoft Windows support pages.
User account control can affect the installation of XAMPP
Step 5: Start the setup wizard
After you’ve opened the .exe file (after deactivating your antivirus program(s) and taken note of the User Account Control, the start screen of the XAMPP setup wizard should appear automatically. Click on ‘Next’ to configure the installation settings.
You can start the setup on the startup screen
Step 6: Choose software components
Under ‘Select Components’, you have the option to exclude individual components of the XAMPP software bundle from the installation. But for a full local test server, we recommend you install using the standard setup and all available components. After making your choice, click ‘Next’.
In the dialog window entitled ‘select components’, you can choose the software components before installation
Step 7: Choose the installation directory
In this next step, you have the chance to choose where you’d like the XAMPP software packet to be installed. If you opt for the standard setup, then a folder with the name XAMPP will be created under C:\ for you. After you’ve chosen a location, click ‘Next’.
For the next step, you need to select the directory where XAMPP should be installed
Step 8: Start the installation process
Once all the aforementioned preferences have been decided, click to start the installation. The setup wizard will unpack and install the selected components and save them to the designated directory. This process can take several minutes in total. You can follow the progress of this installation by keeping an eye on the green loading bar in the middle of the screen.
According to the default settings, the selected software components are unpacked and installed in the target folder
Step 9: Windows Firewall blocking
Your Firewall may interrupt the installation process to block the some components of the XAMPP. Use the corresponding check box to enable communication between the Apache server and your private network or work network. Remember that making your XAMPP server available for public networks isn’t recommended.
Step 10: Complete installation
Once all the components are unpacked and installed, you can close the setup wizard by clicking on ‘Finish’. Click to tick the corresponding check box and open the XAMPP Control Panel once the installation process is finished.
By clicking on ‘finish’, the XAMPP Setup Wizard is completed
Controls for the individual components of your test server can be reached through the XAMPP Control Panel. The clear user interface logs all actions and allows you to start or stop individual modules with a single. The XAMPP Control Panel also offers you various other buttons, including:
Config: allows you to configure the XAMPP as well as the individual components
Netstat: shows all running processes on the local computer
Shell: opens a UNIX shell
Explorer: opens the XAMPP folder in Windows Explorer
Services: shows all services currently running in the background
Help: offers links to user forums
Quit: closes the XAMPP Control Panel
In the Control Panel, you can start and stop individual modules
Individual modules can be started or stopped on the XAMPP Control Panel through the corresponding buttons under ‘Actions’. You can see which modules have been started because their names are highlighted green under the ‘Module’ title.
An active module is marked in green in the Control Panel
If a module can’t be started as a result of an error, you’ll be informed of this straight away in red font. A detailed error report can help you identify the cause of the issue.
A common source of error connected with Apache is blocked ports. If you’re using the standard setup, then XAMPP will assign the web server to main port 80 and the SSL port 443. The latter of these particularly is often blocked by other programs. In the example above, it’s likely that the Tomcat port is being blocked, meaning the web server can’t be started. There are three ways to solve this issue:
Change the conflicting port: Let’s assume for the sake of example that the instant messenger program Skype is blocking SSL port 443 (this is a common problem). One way to deal with this issue is to change Skype’s port settings. To do this, open the program and navigate via ‘Actions’, ‘Options’, and ‘Advanced’, until you reach the ‘Connections’ menu. You should find a box checked to allow Skype access to ports 80 and 443. Deselect this checkbox now.
Change the XAMPP module port settings: Click the Config button for the module in question and open the files httpd.conf and httpd-ssl.conf. Replace port number 80 in httpd.conf and port number 443 in httpd-ssl.conf with any free ports, before saving the file data. Now click on the general Config button on the right-hand side and select ‘Services and Ports Settings’. Customize the ports for the module server to reflect the changes in the conf files.
End the conflicting program: The simplest way to avoid port conflicts in the short term is to end the conflicting program (Skype in this case). If you restart Skype after your XAMPP module servers are already running, it will select a different port and your issue will be resolved.
Modules that can’t be started will be shown in red. The user will also receive an error report to help solve the problem
You have an ‘Admin’ option located on the Control Panel for every module in your XAMPP.
Click on the Admin button of your Apache server to go to the web address of your web server. The Control Panel will now start in your standard browser, and you’ll be led to the dashboard of your XAMPP’s local host. The dashboard features numerous links to websites for useful information as well as the open source project BitNami, which offers you many different applications for your XAMPP, like WordPress or other content management systems. Alternatively, you can reach the dashboard through localhost/dashboard/.
By clicking on the ‘admin’ button of the Apache module, the user will be redirected to the local dashboard of XAMPP
You can use the Admin button of your database module to open phpMyAdmin. Here, you can manage the databases of your web projects that you’re testing on your XAMPP. Alternatively, you can reach the administration section of your MySQL database via localhost/phpmyadmin/.
The web project’s databases are managed by the user in phpMyAdmin (accessible via the ‘Admin’ button in the database module)
Step 3 – perform the database operation as per the below instructions
Create Connection & Database: Start by creating a connection to the database by creating a database name as mydb Use the username and password from your MySQL database mydb
Create and connect to mydb
Step 1- conn.js
var mysql = require(‘mysql’);
var con = mysql.createConnection
({
host: “localhost”,
user: “root”,
password: ““
});
// Created the Connection
/*con.connect(function(err) {
if (err) throw err;
console.log(“Connected!”);
});*/
// Created the Database named as “mydb”
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”);
});
});
Create Table
Create file createtable.js and write following code.
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 to server successfully!!!!!!!!!!!’);
// now we will create table customers1000
var sql = “CREATE TABLE customers1000 (name VARCHAR(25), address VARCHAR(50))”;
con.query(sql, function(err,result){
if (err) throw err;
console.log(‘Table Created’);
con.end();
});
});
Drop 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 to server successfully!!!!!!!!!!!’);
// now we will create table customer_detail
var sql = “DROP TABLE customers100”;
con.query(sql, function(err,result){
if (err) throw err;
console.log(‘Table deleted’);
con.end();
});
});
Insert data into table
Single data
Insert.js
const {SIGBREAK} = require(‘constants’);
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 to server successfully!!!!!!!!!!!’);
// now we will create table customers1000
var sql = “CREATE TABLE customers1000 (name VARCHAR(25), address VARCHAR(50))”;
con.query(sql, function(err,result){
if (err) throw err;
console.log(‘Table Creatd’);
var sql1= “INSERT INTO customers1000(name, address) VALUES (‘avantika’,’andheri’)”;
con.query(sql1,function(err,result){
if(err) throw err;
console.log(‘one row inserted’);
con.end();
});
});
});
Multiple rows inserted
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 to server successfully!!!!!!!!!!!’);
// now we will create table customers1000
var sql = “CREATE TABLE customers1000 (name VARCHAR(25), address VARCHAR(50))”;
con.query(sql, function(err,result){
if (err) throw err;
console.log(‘Table Creatd’);
var record=[
[‘avantika’, ‘mahadik’],
[‘rashmi’, ‘bhandup’],
[‘rashmita’,’thane’]
];
var sql1= “INSERT INTO customers1000(name, address) VALUES?”;
con.query(sql1,[record],function(err,result){
if(err) throw err;
console.log(‘one row inserted’);
con.end();
});
});
});
Select from 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 to server successfully!!!!!!!!!!!’);
// now we will create table customer_detail
//explain LIMIT and OFFSET here
//example SELECT * FROM customers1000 LIMIT 5 OFFSET 2
var sql = “SELECT * FROM customers1000”;
con.query(sql, function(err,result,fields){
if (err) throw err;
console.log(result);
con.end();
});
});
Update the table
Update.js
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 to server successfully!!!!!!!!!!!’);
// now we will create table customers1000
var sql = “UPDATE customers1000 set address=‘pune’ where address= ‘goa’”;
con.query(sql, function(err,result){
if (err) throw err;
//console.log(result.affectedRows,+“one row updated”);
console.log(result.affectedRows + “ record(s) updated”);
con.end();
});
});
Delete the row from table
Delete.js
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 to server successfully!!!!!!!!!!!’);
// now we will create table customers1000
var sql = “DELETE FROM customers1000 where name = ‘avantika’”;
con.query(sql, function(err,result){
if (err) throw err;
//console.log(result.affectedRows,+“one row updated”);
console.log(result.affectedRows + “ record(s) deleted”);
con.end();
});
});
Tweets
Web Technology with Node js, Angular js and MySQL
Twitter
Facebook
WhatsApp
LinkedIn
