Welcome!

Machine Learning Authors: William Schmarzo, Elizabeth White, Liz McMillan, Pat Romanski, James Carlini

Blog Feed Post

How to leverage SQL Server with Node.js using Edge.js

headerpic_leverage_SQL_server

We always want to bring you great articles with a broad spectrum of technologies on our blog, even some we currently don’t support on Codeship, like .Net. We recently tweeted about such an article and saw a lot of interest in it. Let us know what technologies you are interested in in the comments so we can get you the best articles and information!

David Neal is a father, geek, musician, and software developer living near Chattanooga, TN. He has spent the last several years building high-performance, scalable web applications, and currently works at LeanKit as a Developer Advocate. David served as president of the Nashville .NET User Group for 2012 and 2013. David is passionate about software craftsmanship, user experience, music, and bacon. You can find David on Twitter as @reverentgeek.


I’ve looked at Node.js many times over the past few years. It’s hard to ignore all the attention it has received. Unfortunately, being heavily invested in Microsoft technology, one of the reasons I have never got very far in learning Node.js is its lack of support for SQL Server. If you’ve ever tried connecting to MS SQL Server from Node.js, then you know that the modules currently available are incomplete and immature. Microsoft released an official SQL Server driver. However, it is still “preview” technology with a number of missing features and outstanding issues that haven’t been addressed since its release.

One compelling alternative I have discovered is Edge.js. Edge.js is a Node.js module that allows .NET code and assemblies to run in the same process with Node.js. This potentially enables a Node.js developer to leverage technologies that have traditionally been very hard or impossible to use in the past. For example:

  • SQL Server
  • Active Directory
  • Nuget packages (currently 18K+ packages available)
  • PC or Server hardware (e.g. webcam, microphone, and printers)
  • Legacy .NET code

Node.js + Edge.js Quick Start

  • Windows (desktop or server)
  • .NET Framework 4.5 (required for async support)
  • Node.js

Note: As of this writing, Edge.js works only on Windows – there’s a Beta for OS X though.

Install Node.js

If you don’t have Node.js already, go to nodejs.org and download the installer. After Node.js is installed, you can verify it’s working by opening a command prompt and typing:

> node -v

This should print the current version of Node.js.

Create a project folder

Next, create a folder for your Node.js project. For example, from the command prompt, you could enter:

> md \projects\node-edge-test1
> cd \projects\node-edge-test1

Install Edge.js

Node comes with a package manager that makes it extremely easy to download and install modules. From the command prompt, enter the following:

> npm install edge
> npm install edge-sql

The first command installs Edge.js. The second command installs additional support for SQL Server.

Hello World

Create a text file named server.js and copy in the following:

var edge = require('edge');

// The text in edge.func() is C# code
var helloWorld = edge.func('async (input) => { return input.ToString(); }');

helloWorld('Hello World!', function (error, result) {
    if (error) throw error;
    console.log(result);
});

Now, run the Node.js application at the command prompt by entering:

> node server.js

You should see “Hello World!” printed immediately to the console window.

Set up a test database

In these next examples, we need a database to query. If you do not already have SQL Server installed, I recommend you download and install the free Microsoft SQL Server 2012 Express. Also, be sure to download and install the free SQL Management Studio Express.

- In SQL Management Studio, create a new database named node-test and accept all the defaults.
- Right-click on the new database and select New Query.
- Copy & paste the following script and click Execute.

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('SampleUsers'))
BEGIN;
    DROP TABLE SampleUsers;
END;
GO

CREATE TABLE SampleUsers (
    Id INTEGER NOT NULL IDENTITY(1, 1),
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT(getdate()),
    PRIMARY KEY (Id)
);
GO

INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Orla','Sweeney','[email protected]','Apr 13, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Zia','Pickett','[email protected]','Aug 31, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Justina','Ayala','[email protected]','Jul 28, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Levi','Parrish','[email protected]','Jun 21, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Pearl','Warren','[email protected]','Mar 3, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Rinah','Compton','[email protected]','Oct 24, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Hasad','Shepherd','[email protected]','Sep 15, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Noelani','Hill','[email protected]','Jun 6, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Nicole','Jacobson','[email protected]','Aug 8, 2013');
INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Alika','Acosta','[email protected]','Nov 23, 2013');

This will create a new table named

SampleUsers

and insert 10 records.

Configure your connection string

Before you can use Edge.js with SQL Server, you must set an environment variable named

EDGE_SQL_CONNECTION_STRING

to a valid ADO.NET connection string. For example:

> set EDGE_SQL_CONNECTION_STRING=Data Source=localhost;Initial Catalog=node-test;Integrated Security=True

Note: This environment variable is only good for the current command prompt, and will go away when the window is closed. If you are using the Node.js Tools for Visual Studio, you will need to set a permanent environment variable and restart Visual Studio.

Alternatively, you can set a permanent environment variable using

SETX

.

> SETX EDGE_SQL_CONNECTION_STRING "Data Source=localhost;Initial Catalog=node-test;Integrated Security=True"

Option 1: Query SQL Server directly using Edge.js

Create a new text file named

server-sql-query.js

and copy & paste the following code.

var http = require('http');
var edge = require('edge');
var port = process.env.PORT || 8080;

var getTopUsers = edge.func('sql', function () {/*
    SELECT TOP 5 * FROM SampleUsers ORDER BY CreateDate DESC
*/});

function logError(err, res) {
    res.writeHead(200, { 'Content-Type': 'text/plain' });
    res.write("Error: " + err);
    res.end("");
}    

http.createServer(function (req, res) {
    res.writeHead(200, { 'Content-Type': 'text/html' });

    getTopUsers(null, function (error, result) {
        if (error) { logError(error, res); return; }
        if (result) {
            res.write("<ul>");
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
            });
            res.end("</ul>");
        }
        else {
        }
    });
}).listen(port);
console.log("Node server listening on port " + port);

Save your text file, and from a command prompt enter:

> node server-sql-query.js

Open your web browser, and navigate to http://localhost:8080. If all goes well, you should see a list of five users.

Option 2: Execute .NET code to query SQL Server

Edge.js supports only very basic parameterized Select, Insert, Update, and Delete statements. It does not currently support stored procedures or blocks of SQL code. So, if you need to do anything more than a trivial CRUD operation, you will need to implement that in .NET.

Remember, stay async

The Node.js execution model is a single-threaded event loop. So, it is very important that your .NET code honor this by being fully async. Otherwise, a blocking call to .NET would create havoc for Node.js.

Create a class library

Our first step is to create a sample class library in Visual Studio that we can compile to a .DLL and use with Edge.js.

- Open Visual Studio.
- Create a new Class Library project named

EdgeSampleLibrary

.
- Delete the automatically-generated

Class1.cs

file.
- Create a new class named Sample1.
- Copy & paste the following code into your

Sample1.cs

file.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace EdgeSampleLibrary
{
        public class Sample1
    {
        public async Task<object> Invoke(object input)
        {
            // Edge marshalls data to .NET using an IDictionary<string, object>
            var payload = (IDictionary<string, object>) input;
            var pageNumber = (int) payload["pageNumber"];
            var pageSize = (int) payload["pageSize"];
            return await QueryUsers(pageNumber, pageSize);
        }

        public async Task<List<SampleUser>> QueryUsers(int pageNumber, int pageSize)
        {
            // Use the same connection string env variable
            var connectionString = Environment.GetEnvironmentVariable("EDGE_SQL_CONNECTION_STRING");
            if (connectionString == null)
                throw new ArgumentException("You must set the EDGE_SQL_CONNECTION_STRING environment variable.");

            // Paging the result set using a common table expression (CTE).
            // You may rather do this in a stored procedure or use an 
            // ORM that supports async.
            var sql = @"
DECLARE @RowStart int, @RowEnd int;
SET @RowStart = (@PageNumber - 1) * @PageSize + 1;
SET @RowEnd = @PageNumber * @PageSize;

WITH Paging AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum,
            Id, FirstName, LastName, Email, CreateDate
    FROM    SampleUsers
)
SELECT  Id, FirstName, LastName, Email, CreateDate
FROM    Paging
WHERE   RowNum BETWEEN @RowStart AND @RowEnd
ORDER BY RowNum;
";
            var users = new List<SampleUser>();

            using (var cnx = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(sql, cnx))
                {
                    await cnx.OpenAsync();

                    cmd.Parameters.Add(new SqlParameter("@PageNumber", SqlDbType.Int) { Value = pageNumber });
                    cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int) { Value = pageSize });

                    using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection))
                    {
                        while (await reader.ReadAsync())
                        {
                            var user = new SampleUser
                            {
                                Id = reader.GetInt32(0), 
                                FirstName = reader.GetString(1), 
                                LastName = reader.GetString(2), 
                                Email = reader.GetString(3), 
                                CreateDate = reader.GetDateTime(4)
                            };
                           users.Add(user);
                        }
                    }
                }
            }
            return users;
        } 
    }

    public class SampleUser
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime CreateDate { get; set; }
    }
}

- Save and compile.

- Locate the assembly

([project]/bin/Debug/EdgeSampleLibrary.dll)

and copy it into the Node.js project folder.
- Create a new text file in your Node.js project named

server-dotnet-query.js

- Copy & paste the following code.

var http = require('http');
var edge = require('edge');
var port = process.env.PORT || 8080;

// Set up the assembly to call from Node.js
var querySample = edge.func({
    assemblyFile: 'EdgeSampleLibrary.dll',
    typeName: 'EdgeSampleLibrary.Sample1',
    methodName: 'Invoke'
});

function logError(err, res) {
    res.writeHead(200, { 'Content-Type': 'text/plain' });
    res.write("Got error: " + err);
    res.end("");
}

http.createServer(function (req, res) {
    res.writeHead(200, { 'Content-Type': 'text/html' });

    // This is the data we will pass to .NET
    var data = { pageNumber: 2, pageSize: 3 };

    // Invoke the .NET function
    querySample(data, function (error, result) {
        if (error) { logError(error, res); return; }
        if (result) {
            res.write("<ul>");
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
            });
            res.end("</ul>");
        }
        else {
            res.end("No results");
        }
    });
}).listen(port);

console.log("Node server listening on port " + port);

- Save the text file, and from your command prompt, enter:

> node server-dotnet-query.js

Open your web browser, and navigate to http://localhost:8080. If all goes well, you should see a list of three users. Try changing the

pageNumber

and

pageSize

values in the JavaScript file and observe how that affects the output.

Bonus homework: Use the Connect module to parse query string parameters and set the pageNumber and pageSize values dynamically!

Final thoughts

Edge.js appears to be a very promising solution to bridge the gap between Node.js and the world of .NET.

  • Although .NET code can be executed in-line, I highly recommend
    managing all .NET code in a separate assembly.
  • An ORM can make your life much easier. I prefer Micro-ORMs that
    aren’t heavy-handed and let me do my own thing. Unfortunately, not
    many ORMs have adopted async support. AsyncPoco and
    Insight.Database look promising, but I have not tried them.
  • If you use Visual Studio, download and install the Node.js Tools for
    Visual Studio
    .
  • Remember, stay async in .NET as much as possible!
  • Test, test, test! Profile your application’s memory, CPU, and
    concurrency under load to ensure something isn’t going terribly wrong
    between Node.js and .NET.
  • If your motivation for using Node.js is concurrency and scalability,
    or reducing your Microsoft licensing footprint, you may want to
    consider benchmarking Edge.js against a message queue architecture.
    Take a look at using RabbitMQ or ZeroMQ between your
    Node.js and Windows environments. A message-based architecture has
    many benefits. Use the solution that works best for you.
  • Your mileage may vary.
  • Just because you can, doesn’t mean you should.
  • Consume copious amounts of caffeine and bacon.

Further reading

Read the original blog entry...

More Stories By Manuel Weiss

I am the cofounder of Codeship – a hosted Continuous Integration and Deployment platform for web applications. On the Codeship blog we love to write about Software Testing, Continuos Integration and Deployment. Also check out our weekly screencast series 'Testing Tuesday'!

@CloudExpo Stories
"MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
In his session at 21st Cloud Expo, Carl J. Levine, Senior Technical Evangelist for NS1, will objectively discuss how DNS is used to solve Digital Transformation challenges in large SaaS applications, CDNs, AdTech platforms, and other demanding use cases. Carl J. Levine is the Senior Technical Evangelist for NS1. A veteran of the Internet Infrastructure space, he has over a decade of experience with startups, networking protocols and Internet infrastructure, combined with the unique ability to it...
Vulnerability management is vital for large companies that need to secure containers across thousands of hosts, but many struggle to understand how exposed they are when they discover a new high security vulnerability. In his session at 21st Cloud Expo, John Morello, CTO of Twistlock, addressed this pressing concern by introducing the concept of the “Vulnerability Risk Tree API,” which brings all the data together in a simple REST endpoint, allowing companies to easily grasp the severity of the ...
Agile has finally jumped the technology shark, expanding outside the software world. Enterprises are now increasingly adopting Agile practices across their organizations in order to successfully navigate the disruptive waters that threaten to drown them. In our quest for establishing change as a core competency in our organizations, this business-centric notion of Agile is an essential component of Agile Digital Transformation. In the years since the publication of the Agile Manifesto, the conn...
In his session at 21st Cloud Expo, James Henry, Co-CEO/CTO of Calgary Scientific Inc., introduced you to the challenges, solutions and benefits of training AI systems to solve visual problems with an emphasis on improving AIs with continuous training in the field. He explored applications in several industries and discussed technologies that allow the deployment of advanced visualization solutions to the cloud.
Enterprises are adopting Kubernetes to accelerate the development and the delivery of cloud-native applications. However, sharing a Kubernetes cluster between members of the same team can be challenging. And, sharing clusters across multiple teams is even harder. Kubernetes offers several constructs to help implement segmentation and isolation. However, these primitives can be complex to understand and apply. As a result, it’s becoming common for enterprises to end up with several clusters. Thi...
While some developers care passionately about how data centers and clouds are architected, for most, it is only the end result that matters. To the majority of companies, technology exists to solve a business problem, and only delivers value when it is solving that problem. 2017 brings the mainstream adoption of containers for production workloads. In his session at 21st Cloud Expo, Ben McCormack, VP of Operations at Evernote, discussed how data centers of the future will be managed, how the p...
"NetApp is known as a data management leader but we do a lot more than just data management on-prem with the data centers of our customers. We're also big in the hybrid cloud," explained Wes Talbert, Principal Architect at NetApp, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
"Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
The question before companies today is not whether to become intelligent, it’s a question of how and how fast. The key is to adopt and deploy an intelligent application strategy while simultaneously preparing to scale that intelligence. In her session at 21st Cloud Expo, Sangeeta Chakraborty, Chief Customer Officer at Ayasdi, provided a tactical framework to become a truly intelligent enterprise, including how to identify the right applications for AI, how to build a Center of Excellence to oper...
"IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
"Infoblox does DNS, DHCP and IP address management for not only enterprise networks but cloud networks as well. Customers are looking for a single platform that can extend not only in their private enterprise environment but private cloud, public cloud, tracking all the IP space and everything that is going on in that environment," explained Steve Salo, Principal Systems Engineer at Infoblox, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventio...
Large industrial manufacturing organizations are adopting the agile principles of cloud software companies. The industrial manufacturing development process has not scaled over time. Now that design CAD teams are geographically distributed, centralizing their work is key. With large multi-gigabyte projects, outdated tools have stifled industrial team agility, time-to-market milestones, and impacted P&L stakeholders.
"ZeroStack is a startup in Silicon Valley. We're solving a very interesting problem around bringing public cloud convenience with private cloud control for enterprises and mid-size companies," explained Kamesh Pemmaraju, VP of Product Management at ZeroStack, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
"We're focused on how to get some of the attributes that you would expect from an Amazon, Azure, Google, and doing that on-prem. We believe today that you can actually get those types of things done with certain architectures available in the market today," explained Steve Conner, VP of Sales at Cloudistics, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
"Codigm is based on the cloud and we are here to explore marketing opportunities in America. Our mission is to make an ecosystem of the SW environment that anyone can understand, learn, teach, and develop the SW on the cloud," explained Sung Tae Ryu, CEO of Codigm, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
Data scientists must access high-performance computing resources across a wide-area network. To achieve cloud-based HPC visualization, researchers must transfer datasets and visualization results efficiently. HPC clusters now compute GPU-accelerated visualization in the cloud cluster. To efficiently display results remotely, a high-performance, low-latency protocol transfers the display from the cluster to a remote desktop. Further, tools to easily mount remote datasets and efficiently transfer...
High-velocity engineering teams are applying not only continuous delivery processes, but also lessons in experimentation from established leaders like Amazon, Netflix, and Facebook. These companies have made experimentation a foundation for their release processes, allowing them to try out major feature releases and redesigns within smaller groups before making them broadly available. In his session at 21st Cloud Expo, Brian Lucas, Senior Staff Engineer at Optimizely, discussed how by using ne...
Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...