Thursday, June 6, 2013

Access SQL Azure from a Node.js app deployed to Windows Azure Web Sites

In this post I will show how you can access SQL Azure data from an Express.js Node.js application deployed to Windows Azure Web Sites, and expose that data over HTTP in 31 lines of Node.js and T-SQL code.

This post demonstrates collaboration between several technologies: in-process interop between Node.js and ADO.NET using Edge.js and Edge-sql, SQL Azure, Windows Azure Web Sites, and ADO.NET.

Prerequisites

You need a Windows Azure subscription. You can get started for free here if you don’t have one.

You need to know how to deploy a simple Node.js hello, world application to Widows Azure Web Sites. If you have not done it before, here is a walkthrough. You don’t need a Windows development machine to follow through, it works from Mac, *nix, and Windows just as well.

Create SQL Azure database

If you don’t have a SQL Azure database you could experiment with, create one now through the Windows Azure Management portal. Once created, go to the management dashboard of the database and open the T-SQL console:

image

In the T-SQL console, create a simple Products table and populate it with three entries:

image

Your SQL Azure database should now have three rows in the Products table:

edgewaws-products

The last step is to capture the ADO.NET connection string to your SQL Azure database. Again this can be done from the Windows Azure Management dashboard for SQL:

image

Make sure to capture the ADO.NET connection string, and replace the {your_password_here} placeholder with the actual SQL Server password you specified when creating the SQL Server:

image

The Windows Azure Web Site

I am going to assume you have a simple Node.js application already running in the Windows Azure Web Sites, and that you know how to update it. If you don’t, follow the walkthrough.

Before you create an Express.js application that exposes SQL Azure data over HTTP, you will need to pass the connection string to that application using a Windows Azure Web Sites mechanism of app settings. App settings are key/value pairs configured on the management portal of a Windows Azure Web Site. In the context of a Node.js application, these settings are propagated as environment variables to the Node.js process running your application. As such, they provide a convenient mechanism for passing secrets, like database connections strings, to the application.

Go to the management dashboard of your Windows Azure Web Site Node.js application, navigate to the Configuration tab, and set the EDGE_SQL_CONNECTION_STRING app setting to the value of the ADO.NET connection string of the SQL Azure database you captured in the previous step:

edgewaws-connectionstring

After you enter the value, make sure to click on Save below to reset the Node.js application and enable it to access the new app setting via an environment variable.

The Node.js application

The Node.js application is going to use Express.js framework to expose two HTTP endpoints for accessing the data in the Products table of the SQL Azure database:

  • One endpoint, /products, will return a JSON array containing all products from the Products table,
  • Another endpoint, /products/<id>, will return JSON representing the product with ProductID equal to <id>.

The application is going to use the Edge.js module for Node.js to enable in-process introp with .NET. It is also going to use Edge-sql extension of Edge.js which allows scripting T-SQL inside of a Node.js application using .NET Framework’s ADO.NET client. This is the package.json of the application which declares the dependencies:

image

The actual Express.js application is using Edge.js and Edge-sql to communicate with the SQL Azure database and expose the results as JSON:

image

Lines 4-7 use Edge.js to create the getProducts JavaScript function. The function uses Edge-sql extension of Edge.js to provide in-process introp with ADO.NET functionality that executes the specified T-SQL query over a SQL database. The connection string to the SQL database is provided through the EDGE_SQL_CONNECTION_STRING environment variable set in the Node.js process as a result of configuring the Windows Azure Web Site app setting in the previous step.

Lines 9-13 create a similar getProductById JavaScript function. This function is created over a parameterized T-SQL query. The actual value of the @productId parameter is provided based on a HTTP url segment of the corresponding HTTP GET call in lines 24-25.

Lines 17-22 expose the /products endpoint which uses the getProducts function to obtain a list of all products in the Products table of the SQL Azure database identified with the EDGE_SQL_CONNECTION_STRING connection string. The list of products is returned to the caller of the HTTP API as a JSON array:

edgewaws-productsall

Lines 24-29 expose the /products/<id> endpoint which uses the getProductById function parameterized with the product ID value passed as a URL segment of the HTTP GET request to obtain information about that particular product. The information is returned to the caller of the HTTP API as JSON:

edgewaws-productsone

What’s next

The Edge.js module for Node.js allows in-process interop beween Node.js and .NET code. The Edge-sql extension of Edge.js enables executing T-SQL scripts embedded within a Node.js application using asynchronous ADO.NET running in-process with Node.js code. Therefore it enables access to MS SQL databases, including but not limited to SQL Azure databases. The Edge-sql extension currently supports the four basic CRUD operations: select, insert, update, and delete.

Enjoy. Collaboration welcome: https://github.com/tjanczuk.edge.

10 comments:

  1. Which approach would be better comparing it with Microsoft Driver for Node.JS for SQL Server?

    ReplyDelete
  2. That's easy, this one. This is 10 years+ old, battle tested and performs very well.

    ReplyDelete
  3. BOOOM! Did you hear that Tomek? That was my brain imploding.

    ReplyDelete
    Replies
    1. Wait till you see OData... No, even I would no go there ;)

      Delete
  4. Hi Tomasz, thanks for this module it helps me a lot i just wonder if you will support stored procedure in the future release? I got this error when trying to exec stored proc.
    System.Reflection.TargetInvocationException: Exception has been thrown by the ta
    rget of an invocation. ---> System.InvalidOperationException: Unsupported type o
    f SQL command. Only select, insert, update, and delete are supported.

    ReplyDelete
    Replies
    1. I have no plans to extend the functionality of edge-sql beyond basic CRUD at this point. However I am happy to take contributions: https://github.com/tjanczuk/edge-sql.

      Delete
  5. I´m getting crazy here!!
    Edge.js and edge-sql are wonderfull, but I´m having a problem to make it work with utf-16. When I do any kind of function, ex: /* select * from [dbo].table_ão */ the edge-sql comes with utf-8. It´s a node.js problem or it´s with edge-sql?

    Thank you!!

    ReplyDelete
    Replies
    1. I find a solution... if anyone had the same problem, just need to implement a utf-8 to iso-8859-1 converter. Put this code right after string command = ..... in edgecompiler.cs :)

      System.Text.Encoding iso=System.Text.Encoding.GetEncoding( "ISO-8859-1" );
      System.Text.Encoding utf8=System.Text.Encoding.UTF8;

      char[] x=command.ToCharArray( );
      int s_l=command.Length-1;
      byte[] utfBytes=new byte[ command.Length ];

      while(s_l>=0)
      {
      utfBytes[ s_l ]=Convert.ToByte( x[ s_l ] );

      s_l--;
      }

      byte[] isoBytes=System.Text.Encoding.Convert( utf8, iso, utfBytes );

      command=iso.GetString( isoBytes );

      Delete
  6. I have problems with cirilic in UTF-8... Like Русский язык!
    Can I manually set another encoding?

    ReplyDelete

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.