Painless Microsoft SQL Server with Node.js and seriate

As a developer who has spent significant time working on Microsoft's stack of technologies, I'm glad to see support for Microsoft SQL Server on the Node.js platform continue to mature. I'm excited to introduce seriate, which greatly simplifies working with MS SQL from Node.js.

At a glance

Features of seriate include:

  • Easily execute queries, prepared SQL, and stored procedures
  • Pass parameters
  • Supports transactions
  • Organize SQL into separate .sql files
  • Chain multiple SQL commands together as "steps"
  • Supports Integrated Windows (NTLM) authentication

Hello world MS SQL

Let's look at a quick example using seriate. First, open a terminal/command window, create a folder for the test project, and install the seriate module.

mkdir test-seriate
cd test-seriate
npm init
npm install --save seriate

Next, create a file named hello-sql.js using your favorite editor, and add the following JavaScript.

var sql = require("seriate");

// Change the config settings to match your
// SQL Server and database
var config = {
    "server": "127.0.0.1",
    "user": "nodejs",
    "password": "mypassword",
    "database": "mydatabase"
};

sql.setDefaultConfig( config );

sql.execute( {
        query: "SELECT * FROM INFORMATION_SCHEMA.TABLES"
    } ).then( function( results ) {
        console.log( results );
    }, function( err ) {
        console.log( "Something bad happened:", err );
    } );

Last, save the file, return to the terminal/command window, and run the application.

node ./hello-sql.js

If all goes well, the output should be a list of tables in the database. If something goes wrong, such as a connection error, we should see "Something bad happened" and the error.

Let's step through the hello-sql.js code to see how this works. Line 1 brings in the reference to seriate. Lines 5-10 sets up an object literal with our SQL Server connection information. More connection options are available, such as port number, domain, and connection pool settings. Line 12 sets seriate's default configuration, such that any reference to seriate from this point forward will use the same default connection settings. Lines 14-20 executes the SQL query and processes the results. Since seriate returns a promise, lines 16-20 sets up the promise's success and reject functions.

Parameterized queries

There's no party like a SQL injection party.

-- I just made that up.

Don't build your SQL by concatenating strings together! Use named parameters to send values to your SQL statements. With seriate, this is very easy to do.

var myTable = "MyTable";
sql.execute( {
        query: "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName",
        params: {
            tableName: {
                type: sql.NVARCHAR,
                val: myTable,
            }
        }
    } ).then( function( results ) {
        console.log( results );
    }, function( err ) {
        console.log( "Something bad happened:", err );
    } );

Loading SQL from a file

Embedding SQL queries in JavaScript can become a real pain, especially as statements grow and become more complex. Thankfully, seriate includes an easy way to load SQL directly from .sql files. Let's refactor the examples so far into a separate module, and move the SQL queries to individual files located in a /sql folder.

Create the following files:

schema.js

var sql = require( "seriate" );

var getAllTables = function() {
    return sql.execute( {
        query: sql.fromFile( "./sql/getAllTables" )
    } );
};

var getTable = function( tableName ) {
    return sql.execute( {
        query: sql.fromFile( "./sql/getTableByName" ),
        params: {
            tableName: {
                type: sql.NVARCHAR,
                val: tableName
            }
        }
    } );
};

var getColumns = function( tableName ) {
    return sql.execute( {
        query: sql.fromFile( "./sql/getColumnsByTableName" ),
        params: {
            tableName: {
                type: sql.NVARCHAR,
                val: tableName
            }
        }
    } );
};

module.exports = {
    getAllTables: getAllTables,
    getTable: getTable,
    getColumns: getColumns
};

sql/getAllTables.sql

SELECT    TABLE_CATALOG AS databaseName
        , TABLE_SCHEMA AS schemaName
        , TABLE_NAME AS tableName
        , TABLE_TYPE AS tableType
FROM     INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME

sql/getTableByName.sql

SELECT    TABLE_CATALOG AS databaseName
        , TABLE_SCHEMA AS schemaName
        , TABLE_NAME AS tableName
        , TABLE_TYPE AS tableType
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_NAME = @tableName
ORDER BY TABLE_NAME

sql/getColumnsByTableName.sql

SELECT    COLUMN_NAME AS name
        , DATA_TYPE AS dataType
        , IS_NULLABLE AS isNullable
        , CHARACTER_MAXIMUM_LENGTH AS maxLength
        , NUMERIC_PRECISION AS numPrecision
        , NUMERIC_PRECISION_RADIX AS numRadix
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_NAME = @tableName
ORDER BY ORDINAL_POSITION

index.js

var sql = require( "seriate" );
var schema = require( "./schema" );

// Change the config settings to match your SQL database
var config = {
    "server": "127.0.0.1",
    "user": "nodejs",
    "password": "mypassword",
    "database": "mydatabase"
};

sql.setDefaultConfig( config );

schema.getAllTables()
    .then( function( results ) {
        console.log( results );
    }, function( err ) {
            console.log( "Something bad happened:", err );
        } );

// Change to match the name of a table
var tableName = "Members";

schema.getTable( tableName )
    .then( function( results ) {
        console.log( results );
    }, function( err ) {
            console.log( "Something bad happened:", err );
        } );

schema.getColumns( tableName )
    .then( function( results ) {
        console.log( results );
    }, function( err ) {
            console.log( "Something bad happened:", err );
        } );

Saving all files, return to the terminal/command window, and run the application.

node ./index.js

We should now see a list of tables, a single table, and the columns for the table specified (not necessarily in that order).

Calling all stored procedures!

With seriate, it is just as easy to call a stored procedure. For example, let's say we have a stored procedure named updateMember that expects the parameters memberId, firstName, lastName, and emailAddress. The function in our module might look something like the following.

var updateMember = function( memberId, firstName, lastName, emailAddress ) {
    return sql.execute( {
        procedure: "updateMember",
        params: {
            memberId: {
                type: sql.INT,
                val: memberId
            },
            firstName: {
                type: sql.NVARCHAR,
                val: firstName
            },
            lastName: {
                type: sql.NVARCHAR,
                val: lastName
            },
            emailAddress: {
                type: sql.NVARCHAR,
                val: emailAddress
            }
        }
    } );
};

Step by step

Seriate supports all the basics, including transactions. However, seriate has one more trick up its sleeve. You can easily chain SQL statements together, and data will flow from one step to the next. Typical use cases might include retrieving and composing views from multiple, independent record sets, or transaction workflows where several statements need to execute within the same transaction.

For example, imagine a Member table and a MemberProfile table. We would like to insert a new member, and add to the member's profile information in the same transaction.

/sql/insertMember.sql

INSERT INTO [dbo].[Member]
    ( [FirstName], [LastName], [EmailAddress] )
VALUES
    ( @firstName, @lastName, @emailAddress );

SELECT     Id AS id
        , FirstName AS firstName
        , LastName AS lastName
        , EmailAddress AS emailAddress
FROM     [dbo].[Member]
WHERE     Id = SCOPE_IDENTITY();

/sql/insertMemberProfile.sql

INSERT INTO [dbo].[MemberProfile]
    ( [MemberId], [Key], [Value] )
VALUES
    ( @memberId, @key, @value );

SELECT    MemberId AS [memberId]
        , [Key] AS [key]
        , [Value] AS [value]
FROM     [dbo].[MemberProfile]
WHERE     MemberId = @memberId
  AND    [Key] = @key;

member.js

var sql = require( "seriate" );
var when = require( "when" );

var insertMemberTransaction = function( firstName, lastName, emailAddress, twitter ) {
    return when.promise( function( resolve, reject ) {
        // getTransactionContext() returns a SQL connection with
        // a transaction
        sql.getTransactionContext()
            // The first step takes an alias and query object
            .step( "insertMember", {
                query: sql.fromFile( "./sql/insertMember" ),
                params: {
                    firstName: { type: sql.NVARCHAR, val: firstName },
                    lastName: { type: sql.NVARCHAR, val: lastName },
                    emailAddress: { type: sql.NVARCHAR, val: emailAddress }
                }
            } )
            // Each step afterwards expects an alias and a function
            // The current execute context is passed along with the
            // results from the previous step. The results are available
            // using the alias name.
            .step( "addTwitterProfile", function( execute, data ) {
                var memberId = data.insertMember[ 0 ].id;
                execute( {
                    query: sql.fromFile( "./sql/insertMemberProfile" ),
                    params: {
                        memberId: { type: sql.INT, val: memberId },
                        key: { type: sql.NVARCHAR, val: "Twitter" },
                        value: { type: sql.NVARCHAR, val: twitter }
                    }
                } );
            } )
            .end( function( result ) {
                // In the end, result has commit() and rollback()
                // functions, and a property named "sets" that contains
                // the results of all previous steps.
                result.transaction
                    .commit()
                    .then( function() {
                        var member = result.sets.insertMember[ 0 ];
                        member.twitter = result.sets.addTwitterProfile[ 0 ].value;
                        resolve( member );
                    }, function( err ) {
                            reject( err );
                        } );
            } )
            .error( function( err ) {
                reject( err );
            } );
    } );
};

var getMember = function( memberId ) {
    return when.promise( function( resolve, reject ) {
        // getPlainContext() returns a SQL connection without
        // a transaction
        sql.getPlainContext()
            .step( "getMemberById", {
                query: sql.fromFile( "./sql/getMemberById" ),
                params: {
                    id: { type: sql.INT, val: memberId }
                }
            } )
            .step( "getProfile", function( execute, data ) {
                execute( {
                    query: sql.fromFile( "./sql/getMemberProfileById" ),
                    params: {
                        memberId: { type: sql.INT, val: memberId }
                    }
                } );
            } )
            .end( function( sets ) {
                // In the end, a plain context will return 'sets' with
                // a property named after each of the aliased steps
                var member = sets.getMemberById[ 0 ];
                member.profile = {};
                var profile = sets.getProfile;
                for (var i = 0; i < profile.length; i++) {
                    member.profile[ profile[ i ].key ] = profile[ i ].value;
                }
                resolve( member );
            } )
            .error( function( err ) {
                reject( err );
            } );
    } );
};

module.exports = {
    getMember: getMember,
    insertMemberTransaction: insertMemberTransaction
};

index.js

var sql = require( "seriate" );
var member = require( "./member" );

// Change the config settings to match your SQL database
var config = {
    "server": "127.0.0.1",
    "user": "nodejs",
    "password": "mypassword",
    "database": "mydatabase"
};

sql.setDefaultConfig( config );

member.insertMemberTransaction( "David", "Neal", "test@test.com", "reverentgeek" )
    .then( function( member ) {
        console.log( "member:", member );
    }, function( err ) {
            console.log( "err:", err );
        } );

In closing...

Hopefully these examples give you a good idea of what's possible with seriate. For more information, check out the documentation. Give it a try, and let me know how it goes!

David Neal

David is the Developer Advocate at LeanKit. He is a family man, geek, musician, motorcyclist, speaker, software developer, and Microsoft MVP living in North Georgia. He runs on a high-octane mixture of caffeine and bacon.