Working with the BigInt Type in Node and SQL Server


Node.JS and SQL Server are a good match for creating non-blocking, event-driven database applications. Though you can use ODBC or JDBC to communicate between the two, it is possible to run such applications on platforms such as Azure, Linux, OSX and iOS by using Node together with the JavaScript TDS library called ‘Tedious’. ‘Tedious’ is a mature product but it is still possible to get things wrong in converting SQL Server datatypes such as BigInt to native Javascript data.
Your applications can interact with SQL Server in several different ways and from different platforms. When the public preview of SQL Server running on Linux is released, it seems that the possibilities will be even greater. Microsoft is making sure that SQL Server will work on just about any current platform. This is not the same old SQL Server you’re familiar with anymore.

It is not just the choice of server platform that is changing, but also the way of accessing it. From this Microsoft announcement, SQL Server has official support for Node. This got me thinking, “what are the caveats?” Coming from the culture of a run-of-the-mill .NET shop with a SQL Server backend, I decided to investigate. JavaScript, after all, is ubiquitous; it runs on every platform, and Node has a workable alternative for SQL Server.

Whatever you use to make a connection to SQL Server, whether SSMS, ODBC or JDBC, you will do it via the Tabular Data Stream (TDS) protocol. This was originally devised by Sybase in 1984 as a layer over TCP/IP for communicating with relational databases. The team at Microsoft decided to continue to use this same protocol to interact with SQL Server. There is a JavaScript implementation of this protocol, written as a Node package, called Tedious, that allows you to access SQL Server directly without using ODBC under the hood as Azure/node-sqlserver does. Tedious supports transactions, Table-Valued parameters, and bulk load, so it’s production ready. Tedious is a JavaScript implementation for the Node environment, it does not use ODBC or JDBC.

Tedious supports the SQL Server data types, including BigInt. Most of you would know about the implications of data types from working with SQL Server tables. But, what about Node? The Node environment runs JavaScript, and the native data types in JavaScript are inevitably different. This will have repercussions unless you are aware of how to deal with this difficulty. Well, time to explore those implications.

Detailed blog can be read here