Skip to main content

Access SQL Server with Nodejs Tutorial

Hello Friends, Today I am going show you how to connect SQL Server Database with nodejs. Developing a dynamic website required data to save on persistent place like database, So if you are using nodejs for your website development and using SQL Server as database to store data then you need connectivity between nodejs application to sql server database. There are many database drivers available for mssql connectivity with nodejs but I am going to use one of the popular and active driver for sql server is mssql , so lets start.

Prerequisite

  • Nodejs , npm  and SQL Server installed on your machine.

Creating table

First create a database , in my machine i have created a database in sql server name  as “JavascriptPoint”, you can create anything.

Now I am going to create a simple table “Student”

Now insert some data to your table Student.

Now our table Student and dummy has been inserted Successfully.

Student table
Student table

Now We are going to create server.js file and install expressjs and mssql module.

This is my folder structure.

and my package.json file.

Go to your project directory and rum command to  initialize your project using npm init, (if you want to  know how to create server using expressjs and nodejs )

and enter information as per your project, after this your package.json file and then now install express and mssql using npm , here i am using mssql version 3.3.0 which latest as of now.

here is my server.js file

here what I am doing is , creating a config object  which needs username , password, server name and database name, change it as per your database configuration.

Get Data from URL

I configure root request to fetch data from student table , so i am creating a SQL request object using mssql and firing a query and passing callback , this callback will get executed after completion of execution with error or resultset , check if any error then return response  with error message to user otherwise send the record set to user.

Getting result as json format
Getting result as json format

 

Running a query in database through application is not best practice , you should use stored procedure to perform database query, so  we are going to write a stored procedure get all the students from student table and execute using nodejs mssql driver, go to your sql server and write this sql query

Now we have modified our server.js file and added new endpoint to get all students with stored procedure.

if you see i added "/sp" endpoint so now again run command node server.js and hit url http://localhost:4000/sp , you will get same result as http://localhost:4000/.

so you saw both the endpoint is giving same result but first one using simple sql query and second one is using stored procedure.

Here you see how to do database connectivity with MS SQL server and nodejs. If you want to learn more then refer mssql documentation

Leave a Reply

Your email address will not be published. Required fields are marked *