Machine Learning Authors: Pat Romanski, Yeshim Deniz, Jason Bloomberg, Liz McMillan, William Schmarzo

Blog Feed Post

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


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;

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'))
    DROP TABLE SampleUsers;

CREATE TABLE SampleUsers (
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT(getdate()),

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


and insert 10 records.

Configure your connection string

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


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 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


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);

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) {
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
        else {
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


- Delete the automatically-generated


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



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
            Id, FirstName, LastName, Email, CreateDate
    FROM    SampleUsers
SELECT  Id, FirstName, LastName, Email, CreateDate
FROM    Paging
WHERE   RowNum BETWEEN @RowStart AND @RowEnd
            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)
            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


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


- 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);

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) {
            result.forEach(function(user) {
                res.write("<li>" + user.FirstName + " " + user.LastName + ": " + user.Email + "</li>");
        else {
            res.end("No results");

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




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
@DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises - and delivering real results.
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
DXWorldEXPO LLC announced today that Dez Blanchfield joined the faculty of CloudEXPO's "10-Year Anniversary Event" which will take place on November 11-13, 2018 in New York City. Dez is a strategic leader in business and digital transformation with 25 years of experience in the IT and telecommunications industries developing strategies and implementing business initiatives. He has a breadth of expertise spanning technologies such as cloud computing, big data and analytics, cognitive computing, m...
Digital Transformation and Disruption, Amazon Style - What You Can Learn. Chris Kocher is a co-founder of Grey Heron, a management and strategic marketing consulting firm. He has 25+ years in both strategic and hands-on operating experience helping executives and investors build revenues and shareholder value. He has consulted with over 130 companies on innovating with new business models, product strategies and monetization. Chris has held management positions at HP and Symantec in addition to ...
Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
DXWorldEXPO LLC announced today that Kevin Jackson joined the faculty of CloudEXPO's "10-Year Anniversary Event" which will take place on November 11-13, 2018 in New York City. Kevin L. Jackson is a globally recognized cloud computing expert and Founder/Author of the award winning "Cloud Musings" blog. Mr. Jackson has also been recognized as a "Top 100 Cybersecurity Influencer and Brand" by Onalytica (2015), a Huffington Post "Top 100 Cloud Computing Experts on Twitter" (2013) and a "Top 50 C...
There is a huge demand for responsive, real-time mobile and web experiences, but current architectural patterns do not easily accommodate applications that respond to events in real time. Common solutions using message queues or HTTP long-polling quickly lead to resiliency, scalability and development velocity challenges. In his session at 21st Cloud Expo, Ryland Degnan, a Senior Software Engineer on the Netflix Edge Platform team, will discuss how by leveraging a reactive stream-based protocol,...
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities - ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups.
Daniel Jones is CTO of EngineerBetter, helping enterprises deliver value faster. Previously he was an IT consultant, indie video games developer, head of web development in the finance sector, and an award-winning martial artist. Continuous Delivery makes it possible to exploit findings of cognitive psychology and neuroscience to increase the productivity and happiness of our teams.
Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...
Predicting the future has never been more challenging - not because of the lack of data but because of the flood of ungoverned and risk laden information. Microsoft states that 2.5 exabytes of data are created every day. Expectations and reliance on data are being pushed to the limits, as demands around hybrid options continue to grow.
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
The IoT Will Grow: In what might be the most obvious prediction of the decade, the IoT will continue to expand next year, with more and more devices coming online every single day. What isn’t so obvious about this prediction: where that growth will occur. The retail, healthcare, and industrial/supply chain industries will likely see the greatest growth. Forrester Research has predicted the IoT will become “the backbone” of customer value as it continues to grow. It is no surprise that retail is ...
Evan Kirstel is an internationally recognized thought leader and social media influencer in IoT (#1 in 2017), Cloud, Data Security (2016), Health Tech (#9 in 2017), Digital Health (#6 in 2016), B2B Marketing (#5 in 2015), AI, Smart Home, Digital (2017), IIoT (#1 in 2017) and Telecom/Wireless/5G. His connections are a "Who's Who" in these technologies, He is in the top 10 most mentioned/re-tweeted by CMOs and CIOs (2016) and have been recently named 5th most influential B2B marketeer in the US. H...
Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of bus...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...