In this post we will see how we can perform CRUD application in our SQL database using Node JS. As you all know Node JS is a run time environment built on Chrome's V8 JavaScript engine for server side and networking application. And it is an open source which supports cross platforms. Node JS applications are written in pure JavaScript. If you are new to Node JS, we strongly recommend you to read my previous post about Node JS here. Now let's begin.
There was a time where we developers depended on any server side languages to perform server side actions, few years back a company called Joyent, Inc brought us a solution for this. That is, we can do the server side actions if you know JavaScript. Because of the wonderful idea behind this, it became a great success. You can do server side actions without knowing a single code related to any server side languages like C# and PHP. Here we are going to see how you can do the database actions like Create, Read, Update, Delete using Node JS. I hope you will like this.
Before we start coding our Node JS application, we can set up Node JS tool available for Visual Studio.
You can always run your Node JS code by using a command prompt, so setting up this tool is optional. If you install it, you can easily debug and develop Node JS. So we recommend you to install it.
To download the tool, please click on this link. Once you have downloaded the set up file, you can start installing it.
We hope you have installed the application, Now you can create a Node JS application in our Visual Studio.
You can find an option as Node JS in your Add New Project window as follows. Please click on that and create a new project.
Now our Visual Studio is ready for coding, but as we mentioned earlier, we are going to use SQL Server as our database. So we need to do some configuration related to that too. Let's do it now.
You need to make sure that the following service are run.
To check the status of these service, you can always services by running services.msc in Run command window. Once you are done, you need to enables some protocols and assign a port to it. Now go to your SQL Server Configuration Manager. Most probably you can find the file in this C:\Windows\SysWOW64 location, if you can't find it start window.
Now go to SQL Server Network Configuration and click on Protocols for SQLEXPRESS(Your SQL Server) and Enable TCP/IP.
Now right click and click on Properties on TCP/IP. Go to to IP Addresses and assign port for all the IP.
If have done it, it is time to set up our database and insert some data. Please do not forget to restart your service, as it is mandatory to get updated the changes we have done in the configurations.
Here I am creating a database with name "TrialDB", you can always create a DB by running the query given below.
USE [master]
GO
/****** Object:
Database
[TrialDB] Script
Date
: 20-11-2016 03:54:53 PM ******/
CREATE
DATABASE
[TrialDB]
CONTAINMENT = NONE
ON
PRIMARY
(
NAME
= N
'TrialDB'
, FILENAME = N
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\TrialDB.mdf'
,
SIZE
= 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG
'TrialDB_log'
'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\TrialDB_log.ldf'
= 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
ALTER
SET
COMPATIBILITY_LEVEL = 130
IF (1 = FULLTEXTSERVICEPROPERTY(
'IsFullTextInstalled'
))
begin
EXEC
[TrialDB].[dbo].[sp_fulltext_database] @
action
=
'enable'
end
ANSI_NULL_DEFAULT
OFF
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
AUTO_CLOSE
AUTO_SHRINK
AUTO_UPDATE_STATISTICS
CURSOR_CLOSE_ON_COMMIT
CURSOR_DEFAULT
GLOBAL
CONCAT_NULL_YIELDS_NULL
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER
RECURSIVE_TRIGGERS
DISABLE_BROKER
AUTO_UPDATE_STATISTICS_ASYNC
DATE_CORRELATION_OPTIMIZATION
TRUSTWORTHY
ALLOW_SNAPSHOT_ISOLATION
PARAMETERIZATION SIMPLE
READ_COMMITTED_SNAPSHOT
HONOR_BROKER_PRIORITY
RECOVERY SIMPLE
MULTI_USER
PAGE_VERIFY CHECKSUM
DB_CHAINING
FILESTREAM( NON_TRANSACTED_ACCESS =
)
TARGET_RECOVERY_TIME = 60 SECONDS
DELAYED_DURABILITY = DISABLED
QUERY_STORE =
USE [TrialDB]
SCOPED CONFIGURATION
MAXDOP = 0;
FOR
SECONDARY
MAXDOP =
;
LEGACY_CARDINALITY_ESTIMATION =
PARAMETER_SNIFFING =
QUERY_OPTIMIZER_HOTFIXES =
READ_WRITE
To create a table, you can run the query below.
Table
[dbo].[Course] Script
: 20-11-2016 03:57:30 PM ******/
TABLE
[dbo].[Course](
[CourseID] [
int
]
NOT
NULL
[CourseName] [nvarchar](50)
[CourseDescription] [nvarchar](100)
CONSTRAINT
[PK_Course]
KEY
CLUSTERED
[CourseID]
ASC
WITH
(PAD_INDEX =
, STATISTICS_NORECOMPUTE =
, IGNORE_DUP_KEY =
, ALLOW_ROW_LOCKS =
, ALLOW_PAGE_LOCKS =
[
[/sql]
Now we can
insert
few data
to
our newly created
table
.
[sql]
INSERT
INTO
[dbo].[Course]
([CourseID]
,[CourseName]
,[CourseDescription])
VALUES
(1
'C#'
'Learn C# in 7 days'
(2
'Asp.Net'
'Learn Asp.Net in 7 days'
(3
'SQL'
'Learn SQL in 7 days'
(4
'JavaScript'
'Learn JavaScript in 7 days'
So our data is ready, that means we are all set to write our Node JS application. Go to the application we created and you can see a JS file there, normally named as server.js. Here I am going to change the name as App.js.
You can find many packages for our day to day life in Node JS, what you need to do all is , just install that package and start using it. Here we are going to use a package called MSSQL.
Node-MSSQL
npm install mssql
Now we can load this package by using a function called require.
//MSSQL Instance Creation
var
sqlInstance = require(
"mssql"
);
Then, you can set the database configurations as preceding.
//Database configuration
setUp = {
server:
'localhost'
database:
user:
'sa'
password:
port: 1433
};
Once you have a configuration set up, you can connect your database by using connect() function.
sqlInstance.connect(setUp)
Now we can perform the CRUD operations. Are you ready?
// To retrieve all the data - Start
new
sqlInstance.Request()
.query(
"select * from Course"
.then(
function
(dbData) {
if
(dbData ==
null
|| dbData.length === 0)
return
console.dir(
'All the courses'
console.dir(dbData);
})
catch
(error) {
console.dir(error);
});
// To retrieve all the data - End
Now run your application and see the output as preceding.
You can always select a particular record by giving an appropriate select query as follows.
// To retrieve specific data - Start
value = 2;
.input(
"param"
, sqlInstance.Int, value)
"select * from Course where CourseID = @param"
'Course with ID = 2'
// To retrieve specific data - End
So what would be the output of the above code? Any idea?
We can always perform some insert query too using Node JS, the difference will be here is, as we have Transactions in SQL we will include that too here. Following code performs an insert operation.
// Insert data - Start
dbConn =
sqlInstance.Connection(setUp,
(err) {
myTransaction =
sqlInstance.Transaction(dbConn);
myTransaction.begin(
rollBack =
false
myTransaction.on(
'rollback'
(aborted) {
true
sqlInstance.Request(myTransaction)
"INSERT INTO [dbo].[Course] ([CourseName],[CourseDescription]) VALUES ('Node js', 'Learn Node JS in 7 days')"
(err, recordset) {
(!rollBack) {
myTransaction.rollback(
console.dir(err);
}
else
{
myTransaction.commit().then(
(recordset) {
'Data is inserted successfully!'
}).
'Error in transaction commit '
+ err);
// Insert data - End
So let's run it and see the output.
As we performed insert operation, we can do the same for delete operation as below.
// Delete data - Start
delValue = 4;
"DELETE FROM [dbo].[Course] WHERE CourseID="
+ delValue,
'Data is deleted successfully!'
// Delete data - End
Now run your application and see whether the data is deleted.
The only action pending here to perform is UPDATE. Am I right? Let's do that too.
// Update data - Start
updValue = 3;
"UPDATE [dbo].[Course] SET [CourseName] = 'Test' WHERE CourseID="
+ updValue,
'Data is updated successfully!'
// Update data - End
Here goes the output.
You can always download the source code attached to see the complete code and application. Happy coding!.