Pushing Compression from node.js to SQL Server

Introduction

GZIP compression is commonly used in web applications. If you compress text that should be returned to browsers, you can increase your app performance. Usually, you will get raw results from data layer and compress them in your web application (e.g. node.js).

One of the new things that is coming with new SQL Server 2016 are built-in functions for GZip compression/decompression that enables you to compress data directly in database layer. SQL Server exposes functions that compress/decompress data using standard GZip algorithm. One benefit of compatibility with GZip algorithm is the fact that you can compress data in SQL Server and transfer compressed data directly to clients. This might help you if network bandwidth is the main problem in your web application and you want gzipped content to be sent from data layer to browser.

In this example, I will use browser as a client, simple node.js web application that just reads compressed data from SQL server and stream it to the browser.

Using the Code

In this example, I will demonstrate how COMPRESS function can be used in one web application implemented using node.js, express as web server component, and tedious as data access component.

If you don’t know how to setup node.js site, here is short introduction. Skip this section if you are familiar with node.js:

1. Install node.js if you don’t already have it. Here is good article that has more details that explain node.js installation. Here, we will have short instructions with default settings.
2. Create folder where you want to put your node.js web application
3. Go to folder and run npm init to setup node.js application
   1. npm command will ask you to enter some project settings such as start-up file, author, description, etc. Accept all defaults and default package.json configuration file will be created.
4. Run npm install tedious from command line in the folder that you created to install tedious connector for SQL Server.

Tedious is simple framework for connecting to SQL Server. You just need to create new connection object using configuration object and wait for connect event:

new Connection(config).on('connect', function (err) {    });

In event handler, you can handle errors and execute SQL query. You can create Request object that represents SQL query and attach callback that will be executed when results are fetched from database:

connection.execSql( new Request(sqlQuery, function (err, rowCount, rows) {  })  );

Note that rows parameter will be available only if you setup rowCollectionOnRequestCompletion: true in config object that will be provided as an argument to Connection (see examples below).

1. Run npm install express from command line in the folder that you created to install express framework that handles Http requests. Express is simple framework that attaches event handlers on URL end points and listen on some port:

var app = express();
app.get('/version', function (req, res) {  });
app.listen(3000);

Now you are ready to start. You can use any other frameworks, but in this example I’m using tedious and express.

Demo App

Now when we have setup application, we can add some code. If you have accepted all defaults, you can put this code in index.js file that is generated by npm init command.

First, I will configure tedious:

var Connection = require('tedious').Connection;

var Request = require('tedious').Request;

var config = {
    userName: 'sa',
    password: 'pwd',
    server: 'localhost',
    options: { database: 'AdventureWorks2016CTP3',
               rowCollectionOnRequestCompletion : true }
};

Configuration includes required modules, and specifies connection configuration. The only specific parameter is rowCollectionOnRequestCompletion. This parameter specifies that rows will be returned in request handler function provided as the second parameter of Request constructor.

Then, I will initialize express app, initialize it to listen on port 3000, and set handler that listens on /version URL and creates tedious connection. On connect event, connection object will execute some custom sql2http function that executes SQL query on specified connection (this variable) and provide Http response reference where this function will write results:

var express = require('express');
var app = express();

app.get('/version',
        function (req, res) {
              new Connection(config)
                  .on(  'connect',
                        function () {
                                sql2http(this,
                                         "select COMPRESS(@@VERSION)",
                                         res);
                        });
        });

var server = app.listen(3000);

You can see that query is simple – it just returns compressed SQL server version stored in @@VERSION server variable. Since COMPRESS function uses GZip algorithm, many standard applications will be able to decompress this message.

Finally, I will need sql2http function that executes SQL query on connection and send results to Http request:

function sql2http(connection, sqlQuery, httpResponse) {

    connection.execSql(
        new Request(sqlQuery,
                    function (err, rowCount, rows) {

                        httpResponse.writeHead(200, {
                            'Content-Type': 'text/html',
                            'Content-Encoding': 'gzip'
                        });

                        httpResponse.write(rows[0][0].value);
                        httpResponse.end();
                        connection.close();
                    })
     ); // end execSql

 }; // end sql2http

Here, I have set Content-Encoding: gzip in order to notify browser that response will be compressed as gzip algorithm. Browser will know that content needs to be decompressed before it is displayed.

This code just executed SQL query on connection and sends column value (first row/first column because I’m returning one scalar value in select COMPRESS(@@VERSION) query) to httpResponse.

Now, we can start application from cmd prompt using the following command:

node index.js

Now, we can try it if we type http://localhost:3000/version in the browser and we will see decoded version.

You might notice that the text that is compressed in database layer can be successfully decompressed in regular web browser and displayed in the page. I don’t need any additional layer for decompression since both browser and SQL Server use the same GZip algorithm.

Note that this is the simplest example – in the real use case, you can compress some real content from tables and decrease bandwidth between application and SQL Server.

The main use case might be Ajax requests that read data fromatted as JSON from web server. If you want to decrease bandwith between client (web browser) and web server, you might gzip JSON response in web server.

As an alternative, you can format query results as JSON in database layer and compress it with COMPRESS function:

SELECT COMPRESS (
   SELECT *
   FROM Sales.SalesOrderHeader
        LEFT JOIN Sales.SalesOrderDetail
             ON Sales.SalesOrderHeader.SalesOrderID
                = Sales.SalesOrderDetail.SalesOrderID
   WHERE Sales.SalesOrderHeader.SalesOrderID = @id
   FOR JSON AUTO )

Inner query will return information formatted as JSON that you need to return via Ajax response. This JSON text can be compressed in database layer and just sent to web browser.

Conclusion

SQL Server 2016 provides built-in functions for compression and decompression text using standard GZip algorithm. You can compress data on the client side and send it to SQL Server, or you can compress response in the database layer and just stream it to the client.

In this example, I have web application that just streams GZipped content directly from database to standard browser that can unpack data and display it correctly. If you just need to decrease bandwidth between database layer and application layer, you can get compressed data from SQL server, and then decompress them in application layer.

With this approach, you have tradeoff between increased CPU cycles (because SQL server will need to do additional processing for compression) in database layer and network bandwidth (you can send 5-20 times less data to client). If CPU usage is not your bottleneck and you have problem with network bandwidth, you can use COMPRESS function to speed-up your application. Another alternative is to return raw data from SQL Server to node.js and to use gzip compression in web application layer.

GZip compression might help you if you generate content that wll be returned, e.g., if you format query results as JSON or XML directly in SQL server and you want to return compressed content to browser via some Ajax call.