Painless 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

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.

const sql = require( "seriate" );

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

sql.setDefaultConfig( config );

sql.execute( {
query: "SELECT * FROM INFORMATION_SCHEMA.TABLES"
} ).then( results => {
console.log( results );
}, 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.

const myTable = "MyTable";  
sql.execute( {
query: "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName",
params: {
tableName: {
type: sql.NVARCHAR,
val: myTable,
}
}
} ).then( results => {
console.log( results );
}, 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

const sql = require( "seriate" );

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

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

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

module.exports = {
getAllTables,
getTable,
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

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

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

sql.setDefaultConfig( config );

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

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

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

schema.getColumns( tableName )
.then( results => {
console.log( results );
}, 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.

const updateMember = ( 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

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

const insertMemberTransaction = ( firstName, lastName, emailAddress, twitter ) => {
return when.promise( ( 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", ( 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( 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( () => {
var member = result.sets.insertMember[ 0 ];
member.twitter = result.sets.addTwitterProfile[ 0 ].value;
resolve( member );
}, err => {
reject( err );
} );
} )
.error( err => {
reject( err );
} );
} );
};

var getMember = memberId => {
return when.promise( ( 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", ( execute, data ) => {
execute( {
query: sql.fromFile( "./sql/getMemberProfileById" ),
params: {
memberId: { type: sql.INT, val: memberId }
}
} );
} )
.end( 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( err => {
reject( err );
} );
} );
};

module.exports = {
getMember,
insertMemberTransaction
};

index.js

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

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

sql.setDefaultConfig( config );

member.insertMemberTransaction( "David", "Neal", "test@test.com", "reverentgeek" )
.then( member => {
console.log( "member:", member );
}, 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!

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.