Node.js and Express: Accessing SQL Server Database for CRUD Operations

-

In this article, we will see how we can use Node.js to build REST APIs using Express.js and access SQL Server Database. We will use mssql package to connect to the SQL Server database and perform CRUD Operations. The reason behind writing this article is to help the new Node.js learners design and Develop database-centric applications while they want to design and develop additional modules with existing business applications. I have already published an article on performing Database Operations using Node.js with Sequelize and PostgreSQL databases. You can read this article from this link. In the article, we will not use Sequelize but instead, we will see how we can use SQL Statements to perform database operations.


Step 1: Create a folder named node-sqlaccess. We will use this folder as a project folder.  Open this folder in Visual Studio Code. Open the Terminal window or Command Prompt and navigate to node-sqlacess folder. Run the following command to create package.json file in the project folder.

npm init -y

Make sure that the package.json is modified by adding type:"module" settings so that we can use ES 6 features with Node.js,
Step 2: We will use mssql, express, and nodemon packages for the application. Run the following command from the terminal window

npm install express mssql nodemon

Install nodemon package on global scope using the following command

npm install --global nodemon

Step 3: Open SQL Server and create a database named eShoppingCodi. In this database create the ProductInfo table using the script of which is shown in Listing 1


 CREATE TABLE [dbo].[ProductInfo](
	[ProductRowId] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [varchar](100) NOT NULL,
	[ProductName] [varchar](200) NOT NULL,
	[CategoryName] [varchar](100) NOT NULL,
	[Manufacturer] [varchar](200) NOT NULL,
	[Description] [varchar](400) NOT NULL,
	[BasePrice] [int] NOT NULL
)
Listing 1: The Table Script

Step 4: In the project folder add a new folder named dataaccess. In this folder, add a new JavaScript file named connect.js. In this file, we will add code for establishing a connection with the database. The database connection is established using the connect() method. We need to pass the database connection string to this method. The code for this file is shown in Listing 2


import sql from 'mssql';
// Data Connection
class DbConection {
    static async connect(){
        const connection = await sql.connect('Server=127.0.0.1;Database=eShoppingCodi;User 		 
            Id=sa;Password=MyPass@word;Encrypt=false;TrustServerCertificate=true');
        this.isConnectionSuccess = connection.connected;
        console.log(`Conected ${connection.connected}`);
        return connection.connected;
    }
}

export {DbConection};
Listing 2: The connect.js file to connect with the database

The code in Listing 2 shows the DbConnection class that contains the static connect() method where we have the code for establishing a connection with the database based on the connection string. The method returns Boolean values to return state as true or false based on the database connection status.     

Step 5: In the dataaccess folder, add a new file named crud.js. In this file, we will add a class name DbOperations. In this class, we will add method asynchronous methods to perform CRUD operations with the database using Insert, Update, Delete, and Select statements. The code for the class is shown in Listing 3


import { DbConection } from "./connect.js";
import sql from 'mssql';
// The DB Operation Class
class DbOperations {
     getData=async(request,response)=>{
        try {
            if(await DbConection.connect()) {
                const filter = request.header("X-Filter");
                if(filter){
                    console.log(`In if for for header = ${filter}`);
                    const result = await sql.query`select * from ProductInfo where Manufacturer=${filter}`;
                
                    return response.status(200).json(result.recordset);
                } else {

                const result = await sql.query`select * from ProductInfo `;
                
                return response.status(200).json(result.recordset);
                } 
            } else {
                console.log(`Else Conidtion`);
               throw new Error("The Connection coud not be established");
            }
           }catch(ex){
               console.log(ex.message);
               return response.status(500).json(ex.message);
           }
    }

    getDataById=async(request,response)=>{
        let id = parseInt(request.params.id);
        try {
            if(await DbConection.connect()) {
                
               const result = await sql.query`select * from ProductInfo where ProductRowId=${id}`;
              
               return response.status(200).json(result.recordset); 
            } else {
                console.log(`Else Conidtion`);
               throw new Error("The Connection coud not be established");
            }
           }catch(ex){
               console.log(ex.message);
               return response.status(500).json(ex.message);
           }
    }

     createProduct=async(request,response)=>{
        let product ={
            ProductId:request.body.ProductId,
            ProductName:request.body.ProductName,
            CategoryName:request.body.CategoryName,
            Manufacturer:request.body.Manufacturer,
            Description:request.body.Description,
            BasePrice:request.body.BasePrice
        } 
       
        try {
         if(await DbConection.connect()) {
            const result = await sql.query`insert into ProductInfo (ProductId, ProductName,CategoryName, Manufacturer, Description, BasePrice) values (${product.ProductId}, ${product.ProductName}, ${product.CategoryName}, ${product.Manufacturer}, ${product.Description},${parseInt(product.BasePrice)})`;
            console.log(result);
            return  response.status(200).json(result);
         } else {
            throw new Error("The Connection coud not be established");
         }
        }catch(ex){
            console.log(ex.message);
            return  response.status(200).json(ex.message);
        }
    }


    updateProduct=async(request,response)=>{
        let id = parseInt(request.params.id);
      
        let product ={
            ProductId:request.body.ProductId,
            ProductName:request.body.ProductName,
            CategoryName:request.body.CategoryName,
            Manufacturer:request.body.Manufacturer,
            Description:request.body.Description,
            BasePrice:request.body.BasePrice
        } 
        
        try {
         if(await DbConection.connect()) {
            const result = await sql.query`Update ProductInfo set ProductId = ${product.ProductId}, ProductName =  ${product.ProductName}, CategoryName =  ${product.CategoryName}, Manufacturer =  ${product.Manufacturer}, Description =  ${product.Description},BasePrice = ${parseInt(product.BasePrice)} where ProductRowId=${id}`;
            console.log(result);
            return  response.status(200).json(result);
         } else {
            throw new Error("The Connection coud not be established");
         }
        }catch(ex){
            console.log(ex.message);
            return  response.status(200).json(ex.message);
        }
    }

    deleteProduct=async(request,response)=>{
        let id = parseInt(request.params.id); 
        try {
         if(await DbConection.connect()) {
            const result = await sql.query`delete ProductInfo  where ProductRowId=${id}`;
            console.log(result);
            return  response.status(200).json(result);
         } else {
            throw new Error("The Connection coud not be established");
         }
        }catch(ex){
            console.log(ex.message);
            return  response.status(200).json(ex.message);
        }
    }

}

export {DbOperations}
Listing 3: The DbOperations class

Carefully read the code of the class. Each method of the class invokes the connect() method of the DbConnection class. If the connection is successfully established, then CRUD operations can be performed. Each method accepts the RequestHandler object that contains Request and Response objects to read headers and URL parameters as well as the body containing data using the POST and PUT requests.  Read the getData() method carefully. In this method, we are reading the custom header value to read data from tables based on the value of the Manufacturer column.       

Step 6: In the project folder, add a new JavaScript file named apiserver.js. In this method, we will write code for creating REST APIs using Express object and its methods like get(), post(), put(), and delete(). The REST API is exposed on port 9076. The code of the REST API is shown in Listing 4


import express from 'express';
import { DbOperations } from './dataaccess/crud.js';
async function server(){
const PORT = process.env.PORT || 9076;

const instance =  express();
instance.use(express.json());
const dbo = new DbOperations();


instance.get('/api/products',dbo.getData);
instance.get('/api/products/:id',dbo.getDataById);
 
instance.post('/api/products',dbo.createProduct);
instance.put('/api/products/:id',dbo.updateProduct);
instance.delete('/api/products/:id',dbo.deleteProduct);


instance.listen(PORT, ()=>{
    console.log(`API Server is started on PORT: ${PORT}`);
});
}
server();
 export  {
    server
  };
Listing 4: The REST API code


Step 7: Modify the package.json to use the nodemon package to run the apiserver.js as shown in Listing 5.

......
"scripts": {
    "start": "nodemon ./apiserver.js"
  },
  
  ......

Listing 5: The application execution the command

Insert sample data in the ProductInfo table.

Execute the following command from the Terminal window

npm run start

The REST API will be running on port 9076 as shown in Figure 1



Figure 1: Running the REST API

Open Postmon or Advanced REST Client and using the following URL make HTTP GET request to read data from the SQL Server database. The data will be fetched as shown in Figure 2



Figure 2: The GET Request 

         
Similarly, POST, PUT, and DELETE methods can also be tested.

The code for this article can be downloaded from this link.

Conclusion:   Building database-centric applications using Node.js is easy using ready-to-use packages like mssql.      


 


Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications. This is writt
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFileProvider service
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implement code
Read more