Using Node.js and SQL Server with Edge.js


Reading Time: 9 minutes

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

Let’s take a look.

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


INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Orla','Sweeney','','Apr 13, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Zia','Pickett','','Aug 31, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Justina','Ayala','','Jul 28, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Levi','Parrish','','Jun 21, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Pearl','Warren','','Mar 3, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Rinah','Compton','','Oct 24, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Hasad','Shepherd','','Sep 15, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Noelani','Hill','','Jun 6, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Nicole','Jacobson','','Aug 8, 2013'); INSERT INTO SampleUsers(FirstName,LastName,Email,CreateDate) VALUES('Alika','Acosta','','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


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

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

Try Codeship – The simplest Continuous Integration service out there.

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

PS: Codeship lets you test and deploy your Node.js projects. Set up Continuous Integration with Codeship today.

Posts you may also find interesting:

Subscribe via Email

Over 60,000 people from companies like Netflix, Apple, Spotify and O'Reilly are reading our articles.
Subscribe to receive a weekly newsletter with articles around Continuous Integration, Docker, and software development best practices.

We promise that we won't spam you. You can unsubscribe any time.

Join the Discussion

Leave us some comments on what you think about this topic or if you like to add something.

  • Pingback: How to leverage SQL Server with Node.js using Edge.js | Node.js Israel()

  • sibghat

    hi, this is nice to see . very usefull sibghatullah

  • David Robbins

    Very nice post. One question regarding async code: does this apply to all referenced assemblies, and can I wrap a call to a “non” async process that is wrapped in a async / thread safe caller?

    • Yes, you can wrap a call to a synchronous process. Due to the method signature required by Edge.js, you’ll end up writing a wrapper, and this wrapper could certainly call into your existing .NET libraries using an async-to-sync pattern, such as Task.Run() or something like

      Hope this helps!

  • Sergey
  • Ben Drucker

    This is pretty cool. We’ve been interested in supporting SQL Server for knex but demand hasn’t been particularly high. There’s also development challenges since all of us are Mac users and use mostly Postgres.

  • Pingback: You have more flexibility with software than you'd think - Edge.js brings Node and .NET together on three platforms - Scott Hanselman()

  • josh

    in terms of hosting, what existing cloud environment will support a node.js app that uses edge.js and some .NET DLLs

    • Any cloud hosting environment that gives you full access to a Windows or Linux virtual machine should work. I’ve tested on Windows Azure. I’m sure a VM on Amazon or Rackspace would work just as well.

  • Rainabba

    Been using for a few months now and I’m happy with it. Would love to see a robost ORM though.

  • nice post!

  • Pingback: Node to Microsoft SharePoint / Project Online via Edge.js : Digging My Blog()

  • Jojo

    Error: System.InvalidOperationException: The ConnectionString property has not been initialized.

    • You will need to set an environment variable named “EDGE_SQL_CONNECTION_STRING” to the connection string for your instance of SQL Server.

  • Pingback: Kolejne ciekawostki programistyczne | Wiadomości o technologiach IT()

  • Luis Diego Pizarro Moreno

    hi how do i pass parameters to a procedure ?? i make it like this

    var getHorarioFarmacia = edge.func(‘sql’, function () {

    /*exec getHorarioFarmacia ‘Sede Parrita’*/


    but when I run it it says that the procedure doesn’t exist, and without the parameter it says that ir requieres a parameter. Help please

    • Luis Diego Pizarro Moreno

      ok I solve it by sending the parameter when i call the function

      getHorarioFarmacia({pSede:’Sucursal Parrita’}, function (error, result){….


      • Hi Luis – I’m glad to hear you were able to figure it out. Let me know if there’s anything I can help you with!

  • The alternative to Edge is to off-load all database processes to external shells (.exe’s) that can be compiled in any language. Node gets the request, saves it to a text file in a temp folder, then kick-starts the external process; the external process reads the text file (and deletes it), does all the database processing (outside the Node.js in-memory process), and sends the result back to the Node process via a raw TCP call once done. Passing the instruction to the shell via a temp file is very crude, but the total effect is highly efficient – Node.js runs primarily as a broker and remains highly responsive. Free tutorial of this concept “minWeb” at >> Mirror Business Solutions >> Downloads >> minWeb mobile – Free Tutorial.

  • Jason McGinnis

    I’ve had excellent results with using the mssql npm module to run MSSQL Stored Procedures. Never a problem.

    var conn = new mssql.connect(sqlConfig, function(err) {
    if (err) {
    var request = new mssql.Request(conn);
    request.input(‘pid’, mssql.BigInt, id);

    request.execute(‘ExampleSelectItemSP’, function(err, recordsets, returnValue) {

    if (err || !recordsets.length) {
    } else {

    var item = recordsets[0][0];



  • Pingback: Sql Server 2016 and Node.JS | Philip Symons's Blog()

  • Vishnu Sankar

    Hi David,

    How about if i need to change the connection string, like, i have multiple servers, i need to query against them all, as per user choice.

    • The example given uses a specific environment variable for the connection string, but you could write your .NET backend to query any number of databases or servers. It would simply be your decision on how best to manage the connections and connection strings (e.g. a config file).

      The Node.js mssql module has vastly improved since this article was written. You might consider using the mssql module directly, as it supports multiple connections.

      • Vishnu Sankar

        Thanks David, i am creating an app in node, which read queries from sql server 2008, so i was looking for a package which allow me to use windows authentication. Now i got two edge-sql and mssql, i need to find out which is more suitable for me….thank you so much for the advise :)

  • Vishnu Sankar

    Sorry, i got it…

  • Jova Rochin

    I’m trying to do this, but it doesn’t work, it seems that the var connection is unreacheable from the function nombreproveedor

    • Jova Rochin

      This image is the correct

      • Jova Rochin

        I want to keep the connection open

  • HI..
    Did not understand where and how to use this statement:

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

  • Hi..
    Did not understand where and how to use the:


  • victoriaxuan

    hi thank you for sharing this with us, very quick question: I did everything as above but when i run the server-sql-query.js, i try to open the localsite, Then i received “Process is terminated due to StackOverFlowException” in cmd. any ideas guys?

  • Neil Young

    This clearly brings the ‘wonderful’ world of tons of exceptions and type errors and missing this and that known from aspnet core finally to node.js.