Various Data Access

By ukmodak | April 29th 2021 06:09:08 AM | viewed 144 times

Data Access in Node.js

Node.js supports all kinds of databases no matter if it is a relational database or NoSQL database. However, NoSQL databases like MongoDb are the best fit with Node.js.

To access the database from Node.js, you first need to install drivers for the database you want to use.

The following table lists important relational databases and respective drivers.

Relational Databases Driver NPM Command
MS SQL Server mssql npm install mssql
Oracle oracledb npm install oracledb
MySQL MySQL npm install mysql
PostgreSQL pg npm install pg
SQLite node-sqlite3 npm install node-sqlite

The following table lists important NoSQL databases and respective drives .

NoSQL Databases Driver NPM Command
MongoDB mongodb npm install mongodb
Cassandra cassandra-driver npm install cassandra-driver
LevelDB leveldb npm install level levelup leveldown
RavenDB ravendb npm install ravendb
Neo4j neo4j npm install neo4j
Redis redis npm install redis
CouchDB nano npm install nano

The above database list is not limited. There are many other databases and drivers available to be used with Node.js. Also, there are many drivers available for each database. So, choose a driver carefully based on your need.

Access SQL Server in Node.js

Learn how to access relational database MS SQL Server 2012 in Node.js application using Express.js in this section.

In order to access MS SQL database, we need to install drivers for it. There are many drivers available for SQL server in NPM. We will use mssql driver here.

Install Driver

Install mssql driver using npm command, npm install mssql in the command prompt. This will add mssql module folder in node_modules folder in your Node.js application. This tutorial uses mssql v2.3.1, which is latest version as of now.

After installing the driver, we are ready to access MS SQL server database. We will connect to a local SQLExpress database server and fetch all the records from Student table in SchoolDB database shown below.

Server.js

var express = require('express');
var app = express();

app.get('/', function (req, res) {
   
    var sql = require("mssql");

    // config for your database
    var config = {
        user: 'sa',
        password: 'mypassword',
        server: 'localhost', 
        database: 'SchoolDB' 
    };

    // connect to your database
    sql.connect(config, function (err) {
    
        if (err) console.log(err);

        // create Request object
        var request = new sql.Request();
           
        // query to the database and get the records
        request.query('select * from Student', function (err, recordset) {
            
            if (err) console.log(err)

            // send records as a response
            res.send(recordset);
            
        });
    });
});

var server = app.listen(5000, function () {
    console.log('Server is running..');
});

Run the above example using node server.js command and point your browser to http://localhost:5000 which displays an array of all students from Student table.

Access MongoDB in Node.js

In order to access MongoDB database, we need to install MongoDB drivers. To install native mongodb drivers using NPM, open command prompt and write the following command to install MongoDB driver in your application.

npm install mongodb --save 

his will include mongodb folder inside node_modules folder. Now, start the MongoDB server using the following command. (Assuming that your MongoDB database is at C:\MyNodeJSConsoleApp\MyMongoDB folder.)

mongod -dbpath C:\MyNodeJSConsoleApp\MyMongoDB 
Connecting MongoDB

The following example demonstrates connecting to the local MongoDB database.

app.js

var MongoClient = require('mongodb').MongoClient;

// Connect to the db
MongoClient.connect("mongodb://localhost:27017/MyDb", function (err, db) {
   
     if(err) throw err;

     //Write databse Insert/Update/Query code here..
                
});

In the above example, we have imported mongodb module (native drivers) and got the reference of MongoClient object. Then we used MongoClient.connect() method to get the reference of specified MongoDB database. The specified URL "mongodb://localhost:27017/MyDb" points to your local MongoDB database created in MyMongoDB folder. The connect() method returns the database reference if the specified database is already exists, otherwise it creates a new database.

Now you can write insert/update or query the MongoDB database in the callback function of the connect() method using db parameter.

Insert Documents
app.js

var MongoClient = require('mongodb').MongoClient;

// Connect to the db
MongoClient.connect("mongodb://localhost:27017/MyDb", function (err, db) {
    db.collection('Persons', function (err, collection) {
        collection.insert({ id: 1, firstName: 'Steve', lastName: 'Jobs' });
        collection.insert({ id: 2, firstName: 'Bill', lastName: 'Gates' });
        collection.insert({ id: 3, firstName: 'James', lastName: 'Bond' });
        db.collection('Persons').count(function (err, count) {
            if (err) throw err;
            
            console.log('Total Rows: ' + count);
        });
    });
                
});

In the above example, db.collection() method creates or gets the reference of the specified collection. Collection is similar to table in relational database. We created a collection called Persons in the above example and insert three documents (rows) in it. After that, we display the count of total documents stored in the collection.

Running the above example displays the following result.

> node app.js
Total Rows: 3 
Update/Delete Documents
app.js

var MongoClient = require('mongodb').MongoClient;

// Connect to the db
MongoClient.connect("mongodb://localhost:27017/MyDb", function (err, db) {
    
    db.collection('Persons', function (err, collection) {
        
        collection.update({id: 1}, { $set: { firstName: 'James', lastName: 'Gosling'} }, {w:1},
                                                     function(err, result){
                                                                if(err) throw err;    
                                                                console.log('Document Updated Successfully');
                                                        });

        collection.remove({id:2}, {w:1}, function(err, result) {
        
            if(err) throw err;    
        
            console.log('Document Removed Successfully');
        });

    });
                
});

Query Database

The following example demonstrates executing a query in the MongoDB database.

app.js

var MongoClient = require('mongodb').MongoClient;

// Connect to the db
MongoClient.connect("mongodb://localhost:27017/MyDb", function (err, db) {
    
    db.collection('Persons', function (err, collection) {
        
         collection.find().toArray(function(err, items) {
            if(err) throw err;    
            console.log(items);            
        });
        
    });
                
});

Mongoose

Mongoose is a very popular ODM for MongoDB in Node.js. Mongoose provides a straight-forward, schema-based solution to model your application data. It includes built-in type casting, validation, query building, business logic hooks and more. Visit MongooseJS.com for more information.

bONEandALL