Wednesday, February 27, 2013

Access MS SQL from a node.js application using OWIN, in-process CLR hosting, .NET, and ADO.NET

Note: this post is related to the 0.4.0 version of the owin project. The project has since been renamed to edge.js and has seen major improvements. Visit edge.js for the latest.

The owin project allows hosting .NET 4.5 code in node.js applications running on Windows. In my previous posts I described how owin can be used to implement express.js request handlers and connect middleware in .NET as well as run CPU-bound computations implemented in .NET within the node.js process.

In this post I will focus on showing how to access a SQL database from a node.js application by hosting CLR in-process and using asynchronous ADO.NET mechanisms, all without writing a single line of C# code.

What you need

You need Windows x64 with node.js 0.8.x x64 installed (the owin module had been developed against node.js 0.8.19). You also need .NET Framework 4.5 on the machine.

The code snippets below assume you have a working MS SQL connection string to a sample Northwind SQL database. If the database is deployed on a development machine, the connection string most of the time looks like Data Source=(local);Initial Catalog=Northwind;Integrated Security=True.

Select

First install the owin module with

npm install owin@0.4.0

Then in your test.js:

var owin = require('owin');

owin.sql("select * from Region", function (error, result) {
if (error) throw error;
console.log(result);
});

Before you run the code, you need to set the connection string to the Northwind SQL database using the OWIN_SQL_CONNECTION_STRING environment variable, e.g:

set OWIN_SQL_CONNECTION_STRING=Data Source=(local);Initial Catalog=Northwind;Integrated Security=True

Now you are ready to run the node.js app with:

node test.js

You will see the following output:

C:\projects\owin>;node test.js
[ [ 'RegionID', 'RegionDescription' ],
[ 1, 'Eastern ' ],
[ 2, 'Western ' ],
[ 3, 'Northern ' ],
[ 4, 'Southern ' ] ]

The result of the query is a JavaScript array. The first element of the array is an array of column names. Subsequent elements of the array are rows representing the results of the SQL select query against the database.

Insert

You can insert data into a SQL database with the following code:

var owin = require('owin');

owin.sql("insert into Region values (5, 'Pacific Northwest')", function (error, result) {
if (error) throw error;
console.log(result);
});

The result of running this applicaiton indicates how many rows in a SQL table have been affected:

C:\projects\owin>node test.js
1

Now when you run the same application again, instead of the result the JavaScript callback will receive an error containing a .NET ADO.NET exception indicating a primary key violation in a SQL database, since a row with this ID already exists:  

C:\projects\owin>node test.js
C:\projects\owin\test.js:9
if (error) throw error;
^
System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KE
Y constraint 'PK_Region'. Cannot insert duplicate key in object 'dbo.Region'.
The statement has been terminated.

Update and delete

Similarly to insert, you can execute SQL update command, followed by a select showing the state of the table:

var owin = require('owin');

owin.sql("update Region set RegionDescription='Washington and Oregon' where RegionID=5", function (error, result) {
if (error) throw error;
console.log(result);
owin.sql("select * from Region", function (error, result) {
if (error) throw error;
console.log(result);
});
});

The results of running this application are:

C:\projects\owin>node test.js
1
[ [ 'RegionID', 'RegionDescription' ],
[ 1, 'Eastern ' ],
[ 2, 'Western ' ],
[ 3, 'Northern ' ],
[ 4, 'Southern ' ],
[ 5, 'Washington and Oregon ' ] ]

The delete SQL command removes rows from the table:

var owin = require('owin');

owin.sql("delete Region where RegionID > 4", function (error, result) {
if (error) throw error;
console.log(result);
});

and similarly to insert and update reports the number of rows affected:

C:\projects\owin>node test.js
1

How it works

Owin is a native node.js module implemented in C++\CLI that runs on Windows. It hosts CLR runtime within the node.js process. The owin.sql() function invokes ADO.NET asynchronously on a CLR thread within the node.exe process and uses .NET framework to perform the SQL operation. The owin module utilizes the OWIN (http://owin.org) interface to bridge between JavaScript, native, and CLR code. The module takes care of marshaling data between V8 and CLR heaps as well as reconciling threading models. The SQL operation is running asynchronously on CLR threads while the node.js event loop remains unblocked.

More

Visit the project page at https://github.com/tjanczuk/owin for the latest bits.

Make sure to check out implementing express.js request handlers and connect middleware in .NET as well as running CPU-bound computations implemented in .NET within the node.js process use cases as well.

Feedback and pull requests welcome.

Sunday, February 24, 2013

CPU bound workers for node.js applications using in-process .NET and OWIN

Note: this post is related to the 0.4.0 version of the owin project. The project has since been renamed to edge.js and has seen major improvements. Visit edge.js for the latest.

The owin project allows hosting .NET 4.5 code in node.js applications running on Windows. In my other posts I described how owin can be used to implement express.js request handlers and connect middleware in .NET as well as help in accessing SQL from node.js applications using in-process CLR/ADO.NET. In this post I will focus on running CPU-bound computations within the node.js application using owin.

The problem

Node.js is not well suited to executing blocking, CPU bound workloads. The distinguishing design trait of node.js is its single-threaded, event-loop based architecture. It allows you to very efficiently handle IO-bound workloads. But executing CPU-bound computations blocks the event loop and makes the application unresponsive to subsequent IO events. For example, you can very efficiently accept a streaming upload of an image over HTTP, which is an IO bound workload. But you cannot subsequently run face recognition algorithm on this image, as this is a CPU-bound operation. It would prevent the node.js application from processing other image uploads. 

Node.js applications typically process CPU-bound workloads by delegating the processing to an external process or service. This involves crossing the process boundary and incurs additional latency.

In-process workloads in node.js using owin

The owin module allows running CPU-bound computations implemented in .NET in-process with the node.js application without blocking the node.js event loop. The CPU bound workloads execute on CLR threads separate from the singleton V8 thread in the node executable. Owin facilitates data marshaling between node.js and .NET components of the application, as well as reconciles the threading models of the two.

Get started by importing the owin module:

npm install owin@0.4.0

Then implement your CPU-bound workload in .NET as follows and save the result in Startup.cs file:

namespace CalculateBudget
{
public class Startup : Owinjs.Worker
{
protected override IDictionary<string, string> Execute(IDictionary<string, string> input)
{
int revenue = int.Parse(input["revenue"]);
int cost = int.Parse(input["cost"]);
int income = revenue - cost;

Thread.Sleep(5000); // pretend it takes a long time

return new Dictionary<string, string> { { "income", income.ToString() } };
}
}
}

Notice the signature of the Execute method, which is the contract for specifying input to and returning results from your CPU-bound workload. This mechanism allows you to pass in a dictionary of strings and return another one. These dictionaries are marshaled from and into JavaScript objects in the node.js application.

The Execute method is called on a CLR thread allocated by the Owinjs.Worker base class. The base class implements a thin adapter layer between the OWIN interface the owin module is built around and the signature of the Execute method above.

Now compile the Startup.cs into CalculateBudget.dll and reference the Owinjs.dll that comes with the owin module:

copy node_modules\owin\lib\clr\Owinjs.dll
csc /target:library /r:Owinjs.dll /out:CalculateBudget.dll Startup.cs

Lastly, implement a node.js application which invokes the CPU-bound computation using the owin module, and save it to test.js file:

var owin = require('owin')

console.log('Starting long running operation...');
owin.worker(
'CalculateBudget.dll',
{ revenue: 100, cost: 80 },
function (error, result) {
if (error) throw error;
console.log('Result of long running operation: ', result);
}
);

setInterval(function () {
console.log('Node.js event loop is alive!')
}, 1000);

This application starts the CPU-bound computation using the OWIN module, and then initiates an interval that will print out a message on the screen every second. When you start this application with

node test.js

You will see the following output:

C:\projects\owin_test>node test.js
Starting long running operation...
Node.js event loop is alive!
Node.js event loop is alive!
Node.js event loop is alive!
Node.js event loop is alive!
Node.js event loop is alive!
Result of long running operation: { income: '20' }
Node.js event loop is alive!
Node.js event loop is alive!

Notice how the interval is able to print out messages between the time the CPU-bound worker is started and it returns the result. This proves that the node.js event loop remains responsive while the CPU-bound computation takes place on a separate CLR thread.

More

Visit the project page at https://github.com/tjanczuk/owin for the latest bits. Feedback and pull requests welcome.

Check out related posts:

Implement express.js request handlers and connect middleware using in-process hosted CLR/.NET and OWIN
SQL access from node.js applications using in-process CLR/ADO.NET and OWIN

Saturday, February 23, 2013

Hosting .NET code in node.js applications using OWIN

Note: this post is related to the 0.4.0 version of the owin project. The project has since been renamed to edge.js and has seen major improvements. Visit edge.js for the latest.

The owin project allows hosting .NET 4.5 code in node.js applications running on Windows. The goal of the project is to enable or simplify application scenarios which are hard or impossible to achieve with node.js alone, in particular:

  • implementing express.js handlers and connect middleware in .NET 4.5 to leverage existing .NET components, frameworks, and tools in node.js web applications without crossing the process boundary,
  • implementing CPU-bound workloads (workers) in-process in node.js applications by executing blocking .NET code on the CLR thread pool and marshalling the results back to the node.js event loop upon completion,
  • simplifying access to Windows specific functionality in node.js applications by enabling the use of CLR languages (C#) and .NET Framework instead of implementing native node.js modules in C/C++ and Win32.

Owin is a native node.js module implemented in C++\CLI. It hosts OWIN handlers (http://owin.org/) written in .NET 4.5 and exposes them to a node.js application. It also provides a connect wrapper around OWIN interface. This allows .NET OWIN handlers to be plugged in directly into the express pipeline either as connect middleware or as request handlers. The owin module takes care of marshaling data between V8 and CLR heapsand reconciling threading models of the two.

In this post I will show a simple Hello, world experience of using the owin module. Look for subsequent posts that will deal with more specific topics, in particular:

CPU bound workers for node.js applications using in-process .NET and OWIN
SQL access from node.js applications using in-process CLR/ADO.NET and OWIN

Hello, world

You need Windows x64 with node.js 0.8.x x64 installed (the module had been developed against node.js 0.8.19). You also need .NET Framework 4.5 on the machine.

Start by importing the express.js and owin modules from NPM:

npm install express
npm install owin@0.4.0

Then implement your OWIN handler in C# and save it to Startup.cs file:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace OwinHelloWorld
{
public class Startup
{
public Task Invoke(IDictionary<string, object> env)
{
env["owin.ResponseStatusCode"] = 200;
((IDictionary<string, string[]>)env["owin.ResponseHeaders"]).Add(
"Content-Type", new string[] { "text/html" });
StreamWriter w = new StreamWriter((Stream)env["owin.ResponseBody"]);
w.Write("Hello, from C#. Time on server is " + DateTime.Now.ToString());
w.Flush();

return Task.FromResult<object>(null);
}
}
}

Compile the Startup.cs file to OwinHelloWorld.dll (by convention the assembly name should match the namespace of the Startup class):

csc /target:library /out:OwinHelloWorld.dll Startup.cs

Finally implement your express.js application and save it to server.js file. The application imports the owin module and uses it create and register an express.js request handler created around the the OwinHelloWorld.dll you just compiled:

var owin = require('owin')
, express = require('express');

var app = express();
app.use(express.bodyParser());
app.all('/jazz', owin('OwinHelloWorld.dll'))
app.all('/rocknroll', function (req, res) {
res.send(200, 'Hello from JavaScript! Time on server ' + new Date());
});

app.listen(3000);

Note that the OwinHelloWorld.dll must be in the current directory, or a full path to the DLL must be specified in the call to the owin() function.

Now run the node.js server:

node server.js

Finally, navigate to the http://localhost:3000/rocknroll URL. As expected, you get back the response from the JavaScript handler:

rocknroll

Then navigate to http://localhost:3000/jazz. You will get a response from your .NET handler:

jazz

Debugging

You can debug the .NET code running within the node.exe process using Visual Studio or other debuggers for managed code. Since the node.exe process contains both native and managed code, when attaching the debugger to the process you must indicate you want to debug managed code:

debug

From there, you can set breakpoints and step through your managed code as if it were a regular .NET application:

debug1

Exceptions

Exceptions thrown by the .NET code are marshaled back to JavaScript application. Express.js will intercept these exceptions for you and return the exception text in the HTTP response, providing yet another mechanism to diagnose issues in the .NET code hosted in node.js:

exception 

Memory footprint of hosting CLR in node.js

The memory footprint of node.exe will increase once the owin module loads CLR into the address space of the process. Memory consumption of the node.exe will vary depending on many factors specific to your application. To give you a general idea of the impact, here are some working set numbers:

The server.js application above without the owin module imported and without the .NET handler registered (i.e. pure JavaScript express.js application)

19 220 KB

The server.js application above (i.e. one OWIN handler) 27 908 KB
The server.js application above with two OWIN handlers 27 940 KB

In the situation above one pays a one-time cost of around 8.5 MB when including the owin module and the first OWIN handler. Importing the owin module is what causes the CLR to be loaded into the address space of the node.exe process.

Adding more .NET handlers incurs only a small incremental cost (e.g. 32KB above). This is because the CLR is already loaded into memory and the extra cost is only related to loading additional, handler specific managed assemblies.

More

Visit the project page at https://github.com/tjanczuk/owin for the latest bits. Feedback and pull requests welcome.

Check out related posts:

CPU bound workers for node.js applications using in-process .NET and OWIN
SQL access from node.js applications using in-process CLR/ADO.NET and OWIN

My Photo
My name is Tomasz Janczuk. I am currently working on my own venture - Mobile Chapters (http://mobilechapters.com). Formerly at Microsoft (12 years), focusing on node.js, JavaScript, Windows Azure, and .NET Framework.