SQL Server CRUD Actions Using Node.js

You all know that Node JS is a run time environment, built on Chrome’s V8 JavaScript engine, for Server side and networking applications. It is an open source framework that supports cross platforms. Node JS Applications are written in pure JavaScript. If you are new to Node JS, I strongly recommend you to read my previous post about Node JS here. Now, let’s begin.

Download source code

Background

There was a time when the developers were dependent on any Server-Side languages to perform server-side actions. A few years back, a company called Joyent Inc. brought us a solution for this; i.e,. we can do the server-side actions, if you know JavaScript. Due to the wonderful idea behind this, it became a great success. You can perform server-side actions without knowing a single line of code related to any server-side language, like C# and PHP. Here, we are going to see how you can perform 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 need to set up the Node JS tool available for Visual Studio.

Node JS tool 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. Thus, I 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.


node_js_tool_for_visual_studio

Thus, I hope you have installed the application. Now, you can create a Node JS Application in Visual Studio.

Creating Node JS Application In Visual Studio

You can find an option as Node JS in your "Add New Project" window, as shown below. Please click on it and create a new project.


new_node_js_project

Now, our Visual Studio is ready for coding, but as I mentioned earlier, we are going to use SQL Server as our database. Thus, we need to do some configuration related to that too. Let’s do it now.

Configure SQL Server For Node JS Development

You need to make sure that the following services are running.

  • SQL Server
  • SQL Server Agent (Skip it if you are using SQLEXPRESS
  • SQL Server Browser

To check the status of these services, you can always go to Services by running services.msc in Run command window. Once you are done, you need to enable 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\SysWOW64location, if you can't find it in Start window.


sql_server_manager_location

Now, go to SQL Server Network Configuration and click Protocols for SQLEXPRESS (Your SQL Server) and enable TCP/IP.


protocols_for_sql_express

Now, right click and click on Properties on TCP/IP. Go to to IP Addresses and assign the port for all IP.


assigning_tcp_ip_port_in_sql_server

If you have done it, it is the time to set up our database and insert some data. Please do not forget to restart your Service, as it is mandatory to update the changes, which we have done in the configurations.


restart_sql_express

Creating database

Here, I am creating a database with name “TrialDB”. You can always create a DB by running the query, mentioned below.

  1. USE [master]  
  2. GO  
  3. /****** Object: Database [TrialDB] Script Date: 20-11-2016 03:54:53 PM ******/  
  4. CREATE DATABASE [TrialDB]  
  5. CONTAINMENT = NONE  
  6. ON PRIMARY  
  7. NAME = N'TrialDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\TrialDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )  
  8. LOG ON  
  9. NAME = N'TrialDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\TrialDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )  
  10. GO  
  11. ALTER DATABASE [TrialDB] SET COMPATIBILITY_LEVEL = 130  
  12. GO  
  13. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  14. begin  
  15. EXEC [TrialDB].[dbo].[sp_fulltext_database] @action = 'enable'  
  16. end  
  17. GO  
  18. ALTER DATABASE [TrialDB] SET ANSI_NULL_DEFAULT OFF  
  19. GO  
  20. ALTER DATABASE [TrialDB] SET ANSI_NULLS OFF  
  21. GO  
  22. ALTER DATABASE [TrialDB] SET ANSI_PADDING OFF  
  23. GO  
  24. ALTER DATABASE [TrialDB] SET ANSI_WARNINGS OFF  
  25. GO  
  26. ALTER DATABASE [TrialDB] SET ARITHABORT OFF  
  27. GO  
  28. ALTER DATABASE [TrialDB] SET AUTO_CLOSE OFF  
  29. GO  
  30. ALTER DATABASE [TrialDB] SET AUTO_SHRINK OFF  
  31. GO  
  32. ALTER DATABASE [TrialDB] SET AUTO_UPDATE_STATISTICS ON  
  33. GO  
  34. ALTER DATABASE [TrialDB] SET CURSOR_CLOSE_ON_COMMIT OFF  
  35. GO  
  36. ALTER DATABASE [TrialDB] SET CURSOR_DEFAULT GLOBAL  
  37. GO  
  38. ALTER DATABASE [TrialDB] SET CONCAT_NULL_YIELDS_NULL OFF  
  39. GO  
  40. ALTER DATABASE [TrialDB] SET NUMERIC_ROUNDABORT OFF  
  41. GO  
  42. ALTER DATABASE [TrialDB] SET QUOTED_IDENTIFIER OFF  
  43. GO  
  44. ALTER DATABASE [TrialDB] SET RECURSIVE_TRIGGERS OFF  
  45. GO  
  46. ALTER DATABASE [TrialDB] SET DISABLE_BROKER  
  47. GO  
  48. ALTER DATABASE [TrialDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
  49. GO  
  50. ALTER DATABASE [TrialDB] SET DATE_CORRELATION_OPTIMIZATION OFF  
  51. GO  
  52. ALTER DATABASE [TrialDB] SET TRUSTWORTHY OFF  
  53. GO  
  54. ALTER DATABASE [TrialDB] SET ALLOW_SNAPSHOT_ISOLATION OFF  
  55. GO  
  56. ALTER DATABASE [TrialDB] SET PARAMETERIZATION SIMPLE  
  57. GO  
  58. ALTER DATABASE [TrialDB] SET READ_COMMITTED_SNAPSHOT OFF  
  59. GO  
  60. ALTER DATABASE [TrialDB] SET HONOR_BROKER_PRIORITY OFF  
  61. GO  
  62. ALTER DATABASE [TrialDB] SET RECOVERY SIMPLE  
  63. GO  
  64. ALTER DATABASE [TrialDB] SET MULTI_USER  
  65. GO  
  66. ALTER DATABASE [TrialDB] SET PAGE_VERIFY CHECKSUM  
  67. GO  
  68. ALTER DATABASE [TrialDB] SET DB_CHAINING OFF  
  69. GO  
  70. ALTER DATABASE [TrialDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )  
  71. GO  
  72. ALTER DATABASE [TrialDB] SET TARGET_RECOVERY_TIME = 60 SECONDS  
  73. GO  
  74. ALTER DATABASE [TrialDB] SET DELAYED_DURABILITY = DISABLED  
  75. GO  
  76. ALTER DATABASE [TrialDB] SET QUERY_STORE = OFF  
  77. GO  
  78. USE [TrialDB]  
  79. GO  
  80. ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;  
  81. GO  
  82. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;  
  83. GO  
  84. ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
  85. GO  
  86. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;  
  87. GO  
  88. ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;  
  89. GO  
  90. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;  
  91. GO  
  92. ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;  
  93. GO  
  94. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;  
  95. GO  
  96. ALTER DATABASE [TrialDB] SET READ_WRITE  
  97. GO 

Create a table and insert data in database

To create a table, you can run the query, mentioned below.

  1. USE [TrialDB]  
  2. GO  
  3. /****** Object: Table [dbo].[Course] Script Date: 20-11-2016 03:57:30 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[Course](  
  9. [CourseID] [intNOT NULL,  
  10. [CourseName] [nvarchar](50) NOT NULL,  
  11. [CourseDescription] [nvarchar](100) NULL,  
  12. CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED  
  13. (  
  14. [CourseID] ASC  
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  16. ON [PRIMARY]  
  17. GO 

Now, we can insert few data to our newly created table.

  1. USE [TrialDB]  
  2. GO  
  3. INSERT INTO [dbo].[Course]  
  4. ([CourseID]  
  5. ,[CourseName]  
  6. ,[CourseDescription])  
  7. VALUES  
  8. (1  
  9. ,'C#'  
  10. ,'Learn C# in 7 days')  
  11. INSERT INTO [dbo].[Course]  
  12. ([CourseID]  
  13. ,[CourseName]  
  14. ,[CourseDescription])  
  15. VALUES  
  16. (2  
  17. ,'Asp.Net'  
  18. ,'Learn Asp.Net in 7 days')  
  19. INSERT INTO [dbo].[Course]  
  20. ([CourseID]  
  21. ,[CourseName]  
  22. ,[CourseDescription])  
  23. VALUES  
  24. (3  
  25. ,'SQL'  
  26. ,'Learn SQL in 7 days')  
  27. INSERT INTO [dbo].[Course]  
  28. ([CourseID]  
  29. ,[CourseName]  
  30. ,[CourseDescription])  
  31. VALUES  
  32. (4  
  33. ,'JavaScript'  
  34. ,'Learn JavaScript in 7 days')  
  35. GO

Thus, our data is ready, which means that we are all set to write our Node JS Application. Go to the Application, which 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.

MSSQL – Microsoft SQL Server client for Node.js

You can find many packages for our day to day life in Node JS, what you need to do is just install the package and start using it. Here, we are going to use a package called MSSQL.

Node-MSSQL

  • Has unified interface for multiple TDS drivers.
  • Has built-in connection pooling.
  • Supports built-in JSON serialization introduced in SQL Server 2016.
  • Supports stored procedures, transactions, prepared statements, bulk load and TVP.
  • Supports serialization of Geography and Geometry CLR types.
  • Has smart JS data type to SQL data type mapper.
  • Supports Promises, Streams and standard callbacks.
  • Supports ES6 tagged template literals.
  • Is stable and tested in production environment.
  • Is well documented.

You can find more about the package here. You can easily install this package by running the following command in your Nuget Package Manager Console.

1 npm install mssql

mssql_node_js_install

Now, we can load this package by using a function called require.

  1. //MSSQL Instance Creation  
  2. var sqlInstance = require("mssql"); 

Then, set the database configurations as preceding.

  1. /Database configuration  
  2. var setUp = {  
  3. server: 'localhost',  
  4. database'TrialDB',  
  5. user'sa',  
  6. password'sa',  
  7. port: 1433  
  8. }; 

Once you have a configuration set up, you can connect your database by using connect() function.

  1. sqlInstance.connect(setUp) 

Now, we can perform the CRUD operations. Are you ready?

Select all the data from database using Node JS

  1. // To retrieve all the data - Start  
  2. new sqlInstance.Request()  
  3. .query("select * from Course")  
  4. .then(function (dbData) {  
  5. if (dbData == null || dbData.length === 0)  
  6. return;  
  7. console.dir('All the courses');  
  8. console.dir(dbData);  
  9. })  
  10. .catch(function (error) {  
  11. console.dir(error);  
  12. });  
  13. // To retrieve all the data - End 

Now, run your application and see the output.


node_js_select_all_data_from_database

Select data with where condition from database using Node JS

You can always select a particular record by giving an appropriate Select query, as follows.

  1. // To retrieve specicfic data - Start  
  2. var value = 2;  
  3. new sqlInstance.Request()  
  4. .input("param", sqlInstance.Int, value)  
  5. .query("select * from Course where CourseID = @param")  
  6. .then(function (dbData) {  
  7. if (dbData == null || dbData.length === 0)  
  8. return;  
  9. console.dir('Course with ID = 2');  
  10. console.dir(dbData);  
  11. })  
  12. .catch(function (error) {  
  13. console.dir(error);  
  14. });  
  15. // To retrieve specicfic data - End

So what would be the output of the above code? Any idea?


node_js_select_particular_data_from_database

Insert data to database using Node JS

We can always perform some insert query too using Node JS, the difference will be - as we have Transactions in SQL , we will include that too here. The following code performs an insert operation.

  1. // Insert data - Start  
  2. var dbConn = new sqlInstance.Connection(setUp,  
  3.     function(err) {  
  4.         var myTransaction = new sqlInstance.Transaction(dbConn);  
  5.         myTransaction.begin(function(error) {  
  6.             var rollBack = false;  
  7.             myTransaction.on('rollback',  
  8.                 function(aborted) {  
  9.                     rollBack = true;  
  10.                 });  
  11.             new sqlInstance.Request(myTransaction)  
  12.                 .query("INSERT INTO [dbo].[Course] ([CourseName],[CourseDescription]) VALUES ('Node js', 'Learn Node JS in 7 days')",  
  13.                     function(err, recordset) {  
  14.                         if (err) {  
  15.                             if (!rollBack) {  
  16.                                 myTransaction.rollback(function(err) {  
  17.                                     console.dir(err);  
  18.                                 });  
  19.                             }  
  20.                         } else {  
  21.                             myTransaction.commit().then(function(recordset) {  
  22.                                 console.dir('Data is inserted successfully!');  
  23.                             }).catch(function(err) {  
  24.                                 console.dir('Error in transaction commit ' + err);  
  25.                             });  
  26.                         }  
  27.                     });  
  28.         });  
  29.     });  
  30. // Insert data - End

So, let’s run it and see the output.


node_js_insert_data_to_database

Delete data from database using Node JS

As we performed insert operation, we can do the same for delete operation as well.

  1. // Delete data - Start  
  2. var delValue = 4;  
  3. var dbConn = new sqlInstance.Connection(setUp,  
  4.     function(err) {  
  5.         var myTransaction = new sqlInstance.Transaction(dbConn);  
  6.         myTransaction.begin(function(error) {  
  7.             var rollBack = false;  
  8.             myTransaction.on('rollback',  
  9.                 function(aborted) {  
  10.                     rollBack = true;  
  11.                 });  
  12.             new sqlInstance.Request(myTransaction)  
  13.                 .query("DELETE FROM [dbo].[Course] WHERE CourseID=" + delValue,  
  14.                     function(err, recordset) {  
  15.                         if (err) {  
  16.                             if (!rollBack) {  
  17.                                 myTransaction.rollback(function(err) {  
  18.                                     console.dir(err);  
  19.                                 });  
  20.                             }  
  21.                         } else {  
  22.                             myTransaction.commit().then(function(recordset) {  
  23.                                 console.dir('Data is deleted successfully!');  
  24.                             }).catch(function(err) {  
  25.                                 console.dir('Error in transaction commit ' + err);  
  26.                             });  
  27.                         }  
  28.                     });  
  29.         });  
  30.     });  
  31. // Delete data - End

Now, run your application and see whether the data is deleted or not.


node_js_delete_data_from_database

Update data from database using Node JS

The only action pending here to perform is UPDATE. Am I right? Let’s do that too.

  1. // Update data - Start  
  2. var updValue = 3;  
  3. var dbConn = new sqlInstance.Connection(setUp,  
  4.     function(err) {  
  5.         var myTransaction = new sqlInstance.Transaction(dbConn);  
  6.         myTransaction.begin(function(error) {  
  7.             var rollBack = false;  
  8.             myTransaction.on('rollback',  
  9.                 function(aborted) {  
  10.                     rollBack = true;  
  11.                 });  
  12.             new sqlInstance.Request(myTransaction)  
  13.                 .query("UPDATE [dbo].[Course] SET [CourseName] = 'Test' WHERE CourseID=" + updValue,  
  14.                     function(err, recordset) {  
  15.                         if (err) {  
  16.                             if (!rollBack) {  
  17.                                 myTransaction.rollback(function(err) {  
  18.                                     console.dir(err);  
  19.                                 });  
  20.                             }  
  21.                         } else {  
  22.                             myTransaction.commit().then(function(recordset) {  
  23.                                 console.dir('Data is updated successfully!');  
  24.                             }).catch(function(err) {  
  25.                                 console.dir('Error in transaction commit ' + err);  
  26.                             });  
  27.                         }  
  28.                     });  
  29.         });  
  30.     });  
  31. // Update data - End

Here goes the output.


node_js_update_data_from_database

You can always download the source code, attached, to see the complete code and application. Happy coding!.

See also

Conclusion

Did I miss anything that you think may be needed? I hope you liked this article. Please share with me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.

Please see this article in my blog here.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now