CRUD demo in Node.js with Windows Azure SQL Database
Microsoft has recently announced the preview release of Microsoft Driver for Node.JS for SQL Server which allows the Node.js developers to connect Microsoft SQL Server database and Windows Azure SQL database. This is a great addition to the tool set for Node.js developers. NoSQL databases are widely using with Node.js for building high performance apps. However, in many situations we might be required a relational database where we can use Microsoft Driver for Node.JS for SQL Server, to working with Microsoft SQL Server database and Windows Azure SQL database. I have a created CRUD demo in Node.js with Express framework which can be available on Github at https://github.com/shijuvar/NodeOnAzure/tree/master/SqlNode . The demo app is built with Node.js, Express.js, Jade View Engine and the node-sqlserver module.
The Microsoft Driver for Node.JS for SQL Server is available on Github and the binary version is available from here . The native module is available as the node-sqlserver which is currently not available as a NPM module.
How to install node-sqlserver module
The following steps will install the node-sqlserver onto your Node.js apps
- Download the binary version of Microsoft Driver for Node.JS for SQL Server from here
- Extract the downloaded .exe file to a folder.
- After extracting the files to a folder, you can see a file named node-sqlserver-install.cmd. Run the node-sqlserver-install.cmd to generate the directories for node-sqlserver module.
- Copy the node-sqlserver directory into your Node.js application's node_modules directory
After following the above steps, you can use the node-sqlserver module as similar like a NPM module. I hope that this module will be available through NPM in the future. You can import the node-sqlserver module similar like a Node module as shown in the below
var sql = require('node-sqlserver');
Demo app with Express framework
I have created the CRUD demo in Node.js app with Express framework. The following T-SQL script is used in the demo for table structure.
CREATE TABLE [dbo].[Categories](
[CategoryId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [nvarchar](max) NOT NULL,
[Description] [nvarchar](max) NULL
)
In this demo app, I have specified the connecting string in the config.js file as shown in the below code block
module.exports = {
"sqlExpressConnection": "Driver={SQL Server Native Client 10.0};
Server=./SQLEXPRESS;Database=EFMVC;Trusted_Connection={Yes};",
"sqlAzureConnection" : "Driver={SQL Server Native Client 10.0};
Server=tcp:{dbservername}.database.windows.net,1433;
Database={database};Uid={username};Pwd={password};
Encrypt=yes;Connection Timeout=30;"
};
In the config file, I have created two connection strings for a SQL Express database and for Windows Azure SQL Database. You can replace the connection string information with your own database information.
Query the SQL Database
In the below code block, we are getting the connection string from the config file and creating an connection to the SQL database by using the open method of the node-sqlserver module. We are querying from the Categories table by using queryRow method and creating an array for Categories information and finally rendering a View with array of Categories as the model object.
1: var conn_str = config.sqlAzureConnection;
2: var categories = [];
3: var query = "SELECT CategoryID, Name, Description
4: FROM dbo.Categories";
5: sql.open(conn_str, function(err, conn) {
6: if(err) {
7: res.render('error',
8: {
9: title: 'Error opening the connection',
10: layout: false,
11: error: 'Could not open the database'
12: });
13: return;
14: }
15:
16: conn.queryRaw(query, function(err, results) {
17: if(err) {
18: res.render('error',
19: {
20: title: 'Error running query',
21: layout: false,
22: error: 'Error running the query'
23: });
24: return;
25: }
26:
27: for(var i = 0; i < results.rows.length; i++) {
28: categories[i] = {
29: ID: results.rows[i][0],
30: Name: results.rows[i][1],
31: Description: results.rows[i][2]
32: };
33: }
34: res.render('index', { title: 'CategoryList',
35: categories: categories });
36: }); //conn.queryRaw
37: }); // sql.open
Query the Database for editing a single record
In the below code block, we are query the single Category data based on the given CategoryID and rendering a View for editing the data
1: //Edit Category
2: controller.editCategory = function(req, res) {
3: var params = [req.params.id];
4: var query = "SELECT CategoryID, Name, Description
5: FROM dbo.Categories WHERE CategoryID = ?";
6: sql.open(conn_str, function(err, conn) {
7: if(err) {
8: res.render('error',
9: {
10: title: 'Error opening the connection!',
11: layout: false,
12: error: 'Could not open the database'
13: });
14: return;
15: }
16: conn.queryRaw(query, params, function(err, results) {
17: if(err) {
18: res.render('error',
19: {
20: title: 'Error running query!',
21: layout: false,
22: error: 'Error running the query'
23: });
24: return;
25: }
26: var category = {
27: ID: results.rows[0][0],
28: Name: results.rows[0][1],
29: Description: results.rows[0][2]
30: };
31: res.render('edit', { title: 'Edit Category',
32: category: category });
33: }); //conn.queryRaw
34: }); // sql.open
35: };//end edit category
36:
In the config file, I have created two connection strings for a SQL Express database and for Windows Azure SQL Database. You can replace the connection string information with your own database information.
Update the SQL Database
In the below code block, we are updating the posted data to the SQL Database for a single Category which we have taken for editing in the previous step.
1: //Update Category
2: controller.updateCategory = function(req, res) {
3: if(req.body) {
4: var query = "UPDATE dbo.Categories
5: SET Name=?,Description=? WHERE CategoryID=?";
6: var params = [req.body.name,
7: req.body.description,
8: req.body.id];
9: sql.open(conn_str, function(err, conn) {
10: if(err) {
11: res.render('error.jade',
12: {
13: title: 'Error running query!',
14: layout: false,
15: error: 'Could not open database'
16: });
17: return;
18: }
19: conn.queryRaw(query, params, function(err, results) {
20: if(err) {
21: res.render('error.jade',
22: {
23: title: 'Error running query',
24: layout: false,
25: error: 'Database update failed'
26: });
27: return;
28: }
29: res.redirect('/category');
30:
31: });
32: }); // sql.open
33: }
34: else {
35: res.render('error.jade',
36: {
37: title: 'Error posting Category',
38: layout: false,
39: error: 'Posted data not found'
40: });
41: }
42: };
Demo App in Github
The completed demo app available on Github at https://github.com/shijuvar/NodeOnAzure/tree/master/SqlNode which provides the all CRUD operation against the Category table. The demo Node.js app is built with Express and Jade view engine.