August 13, 2022

Global Blog Zone

Space For Global Blogs

A Step-By-Step Guide on How to Connect Node with Database

7 min read
node.js

Introduction

Node.js is a popular technology platform for creating corporate applications. Before you commence project development in Node.js, it is essential to choose ideal database. Node.js supports all databases, whether they are Relational or Non-Relational databases. The choice of database is based on how complex is an application and what is the purpose of it. However, seeking advice from a reputed Node.js development company before beginning with application development makes a lot of sense.

Relational database

Structured data is stored in Relational databases. Structured data represents objects in the real world. The structured data can be information about a person, orders, or the contents of a shopping cart. The structured data is stored in tables.

Non-Relational Database

Non-structured data is stored in a non-relational database. In the None-Relational database information is stored in the form of hierarchical data structures. In a hierarchical data structure, we have objects with a set of attributes. In a Non-Relational database, data is stored in collections.

Picking an ideal database is among the essential things to be considered in Nodejs. Let’s learn how to use each of these databases with Node.

Node with Relational database

To connect Node with the Relational database, you should have MySql installed on your computer.

  • Install MySql Driver

To access MySql in Node Project, you should first install the MySql driver in node. You can install it from npm.

npm install mysql

You can access this module in node :

var mysql = require(‘mysql’);

  • Create Connection

After importing the MySQL driver, now we have to connect our node application to the MySql database

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

});

Here in the above code we first created a connection using the createConnection() method. In createConnection, we passed parameters host, user, and password. Then we connect it.

Now we have to run this code using the below code:

D:\React Task\Node Blog> node app.js

The output of the above code is :

  • Creating a Database

To create a database in MySQL using Node, use the “CREATE DATABASE Database_Name” statement:

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

con.query(“CREATE DATABASE myFirstDatabase”, function (err, result) {

if (err) throw err;

console.log(“Database created”);

});

});

In the above code, we created a query using the query()  method and passed our statement to create a database.

The output of the above code is :

  • Create a Table

To create a table in MySQL using Node, use the “CREATE TABLE Table_Name” statement.

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

var sql = “CREATE TABLE person (name VARCHAR(255), age INT)”;

con.query(sql, function (err, result) {

if (err) throw err;

console.log(“Table created”);

});

});

To create a table first we have to define the name of the database in the createConnection() method.

The output of the above code is :

  • Insert data into a table

To insert the new record in table, use the “INSERT INTO Table_Name  (column1,column2…..) VALUES (value1,value2…..)” statement.

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

var sql = “INSERT INTO person (name, age) VALUES (‘Poojan’, ’22’)”;

con.query(sql, function (err, result) {

if (err) throw err;

console.log(“1 record inserted”);

}); });

The output of the above code is :

To select some record from table, use the “SELECT (column_name1, coumn_name2) from Table_Name” statement.

To select all the records from the table, use the “SELECT * from Table_Name”:

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

con.query(“SELECT * FROM person”, function (err, result, fields) {

if (err) throw err;

console.log(result);

});

});

The output of the above code is:

  • Update Table

To update a record in table, use “UPDATE Table_Name SET column_name=”value” WHERE condition ” statement.

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

var sql = “UPDATE person SET name = ‘Price’ WHERE age = 21”;

con.query(sql, function (err, result) {

if (err) throw err;

console.log(result.affectedRows + ” record(s) updated”);

});

});

The output of the above code is :

In the update statement WHERE clause specifies which records should be updated. If you omit to use WHERE clause then the update statement updates all records.

  • Delete Record

To delete a record in the table, use the “DELETE FROM Table_Name WHERE condition” statement.

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

var sql = “DELETE FROM person WHERE name = ‘kevin'”;

con.query(sql, function (err, result) {

if (err) throw err;

console.log(“Number of records deleted: ” + result.affectedRows);

});

});

The output of the above code is :

In the delete statement WHERE clause specifies which records should be deleted. If you omit to use the WHERE clause then the delete statement deletes all records.

  • Drop Table

To drop a whole table in MySql using node, use the “DROP TABLE Table_Name” statement.

var mysql = require(“mysql”);

var con = mysql.createConnection({

host: “localhost”,

user: “root”,

password: “root”,

database: “myFirstDatabase”,

});

con.connect(function (err) {

if (err) throw err;

console.log(“Connected!”);

var sql = “DROP TABLE person”;

con.query(sql, function (err, result) {

if (err) throw err;

console.log(“Table deleted”);

});

});

The output of the above code is :

  • Node with Non-Relational database

MongoDB is the famous non-relational database that is mostly used with node to create applications.

  • Install MongoDB Driver

To work with MongoDB you have to first install the MongoDB driver in the node application.

npm install mongodb

After installing the MongoDB driver you can access this module.

let mongo = require(‘mongodb’);

  • Create a database

To create a database in MongoDB, you have to first create a MongoClient. After creating MongoClient create a connection and pass the URL inside that.

To get the URL you have to download MongoDB compass or you can use MongoDB Atlas.

var MongoClient = require(“mongodb”).MongoClient;

var url = “mongodb://localhost:27017/MyFirstDatabase”;

MongoClient.connect(url, (error, db) => {

if (error) throw error;

console.log(“Database created successfully!”);

db.close();

});

In the connection, URL pass the name of the database you want to create. MongoDB will automatically create a new database if it does not exist.

The output of the above code is :

  • Create a collection

In MongoDB you can create collection using createCollection() method. Creating a collection is the same as creating a table in MySql.

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

dbo.createCollection(“person”, (err, res) => {

if (err) throw err;

console.log(“Collection created successfully!”);

db.close();

});

});

The output of the above code is :

  • Insert a document in the Collection

To insert data in the collection, the insertOne() method is used. In that method, you can pass data as an object .

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

var newRecord = { name: “Poojan”, age: 23 };

dbo.collection(“person”).insertOne(newRecord, (err, res)=> {

if (err) throw err;

console.log(“Record inserted successfully!”);

db.close();

});

});

The output of the above code is :

If you want to pass many data together you can use the insertMany() method. This method accepts an array of objects.

  • Select a record from the collection

In MongoDB to select records ,use find() method. In that method, if you pass { }  empty object then it will return all the records of the collection.

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

dbo

.collection(“person”)

.find({})

.toArray((err, res) => {

if (err) throw err;

console.log(res);

db.close();

});

});

The output of the above code is :

  • Update record in the collection

To update the record in collection, use updateOne() method. The first parameter of that method is the query object, on that condition record is updated. And the second parameter is the new object that you want to update.

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

var query = { name: “Poojan” };

var newData = { $set: { name: “Poojan”, age: 50 } };

dbo.collection(“customers”).updateOne(query, newData, (error, res) => {

if (error) throw error;

console.log(“1 document updated”);

db.close();

});

});

In the above code, we updated the age to 50 where the name matches with “Poojan”.

The output of the above code is :

In the update() method it updates only the first record which matches with the condition. If you want to update all records of the collection that matches the condition then use the updateMany() method.

  • Delete record in the collection

To delete the record in collection, use deleteOne() method. The parameter of that method is the query object, on that condition record is deleted.

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

var query = { name: “Poojan” };

dbo.collection(“person”).deleteOne(query, (err, obj) => {

if (err) throw err;

console.log(“1 document deleted”);

db.close();

});

});

The output of the above code is:

In the delete() method it deletes only the first record which matches with the condition. If you want to delete all records of the collection that matches the condition then use the deleteMany() method.

  • Drop a collection

To drop a collection, use dropCollection() method. In the parameter of that method, you can pass the collection name that you want to drop.

MongoClient.connect(url, (error, db) => {

if (error) throw error;

var dbo = db.db(“MyFirstDatabase”);

dbo.dropCollection(“person”, (error, res) => {

if (error) throw error;

if (res) console.log(“Collection deleted successfully…”);

db.close();

});

});

The output of the above code is :

Conclusion

To conclude this blog, NodeJS can easily connect with a database, be it relational or non-relational. With only a few lines of code we can setup a connection between Node and our database. Thus, Node is very useful when it comes to building a full stack application/project.

Copyright © All rights reserved. | Newsphere by AF themes.

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

We (Global Blog Zone & its sister concerns) will use the information you provide on this form to be in touch with you and to provide updates and marketing.