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.
What is a BigInt?
The BigInt data type in SQL Server is the 64-bit representation of an integer. It takes up 8 bytes of storage. It can range from -2^63 (-9,223,372,036,854,775,808) to 2^63 (9,223,372,036,854,775,807). Two raised to the power of sixty-three is about nine quintillion, a very big number. Imagine a number greater than the number of grains of sand on Earth, it is this big.
BigInt types in SQL Server are useful for the primary key in very large tables. In my own experience, I have used them as the primary key for an authentication audit table. The BigInt was used for the audit id that was used for session data in case authentication failed.
SQL Server likes indexes that are narrow, and predictable. A sequential integer type as a primary key is good in the type of table where all you insert are new rows, but don’t delete them, because this reduces index fragmentation.
Create a SQL Table with BigInt
We need to illustrate the problems of passing a BigInt between JavaScript and SQL Server, so it is time to get our hands dirty with SQL Server. We’ll use the BigIntInNodeTest table in the FunWithBigIntInNode database throughout this article. The code for everything we we will show you is available from the link at the base of the article.
First create this sample database and table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE FunWithBigIntInNode; GO USE FunWithBigIntInNode; GO CREATE TABLE dbo.BigIntInNodeTest ( Id BIGINT NOT NULL, IsAtMax BIT NOT NULL, CONSTRAINT PK_BigIntInNodeTest_Id PRIMARY KEY(Id)); GO INSERT INTO dbo.BigIntInNodeTest (Id, IsAtMax) VALUES (1, 0), (9223372036854775807, 1); GO |
This table has a primary key called Id that is of BigInt type. The IsAtMax Bit type tells me whether the BigInt has hit the limit. I have inserted two values in the test table, one at 1 and one at the limit.
The T-SQL above tells you where this table comes from, so you don’t get lost.
Stored Procedure
In my Node applications, I like to separate the Node code from T-SQL, so as to get a lean-and-mean data access layer. Tedious can execute parameterized stored procedures on SQL Server.
I’ll create two stored procedures with two separate concerns. One reads data from the table and one writes data to it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE dbo.GetBigIntById ( @Id BIGINT ) AS SELECT Id, IsAtMax FROM dbo.BigIntInNodeTest WHERE Id = @Id; GO CREATE PROCEDURE dbo.InsertBigInt ( @Id BIGINT, @IsAtMax BIT ) AS INSERT INTO dbo.BigIntInNodeTest (Id, IsAtMax) VALUES (@Id, @IsAtMax); GO |
This will help me to keep the Node data access layer nice and clean.
Read and Write Data
With the table in place in SQL Server, we can now script the data access layer in Node. Remember I inserted two rows in dbo.BigIntInNodeTest, one with a value of 1 and one with the BigInt limit. I’ll use these values to query for data using the stored procedure.
To query for the value 1 using Node:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
var Connection = require('tedious').Connection; var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; var config = require('./sqlConfig'); var connection = new Connection(config); var requestBigInt = new Request('dbo.GetBigIntById', function onRequest(err, rowCount) { if (err) { console.error(err); } if (rowCount === 0) { console.log('No rows found.'); connection.close(); } }); requestBigInt.addParameter('Id', TYPES.BigInt, 1); requestBigInt.on('row', function onRow(columns) { console.log(columns); connection.close(); }); connection.on('connect', function onConnect() { connection.callProcedure(requestBigInt); }); |
This gives you a working connection to SQL Server. The API is event-driven with asynchronous behavior. The onConnect callback, for example, fires after the initial connection establishes. This callback then calls a procedure using the request object. Tedious follows the same conventions as other Node APIs that come out of the box in the core library. The key is to know that asynchronous behavior uses humble callbacks in Node.
The sqlConfig file is a JSON configuration file that has connection information:
|
1 2 3 4 5 6 7 8 9 |
{ "userName": "username", "password": "password", "server": "server", "options": { "database": "FunWithBigIntInNode", "encrypt": true } } |
When you run this query in Node, it gives you the correct data set in columns. You get a ‘1’ back in the Id column which is what’s expected. This means that we can query for BigInt using plain old vanilla JavaScript numbers. Everything is going well so far.
Out of curiosity, we then put the BigInt limit in the parameter, for example:
|
1 |
requestBigInt.addParameter('Id', TYPES.BigInt, 9223372036854775807); |
We will expect a row back, the other one we inserted, of course. Oh boy, now it says “No rows found.” What could this mean? Tedious has the correct type, which is TYPES.BigInt. The BigInt limit in JavaScript is a plain number. This seems like bizarre erratic behavior. Is this my fault in the way it’s programmed?
Having tried reading a row that uses a BigInt with mixed success, how about inserting values into the table?
One could do:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
var Connection = require('tedious').Connection; var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; var config = require('./sqlConfig'); var connection = new Connection(config); var commandBigInt = new Request('dbo.InsertBigInt', function onRequest(err) { if (err) { console.error(err); } connection.close(); }); commandBigInt.addParameter('Id', TYPES.BigInt, '9223372036854775806'); commandBigInt.addParameter('IsAtMax', TYPES.Bit, false); connection.on('connect', function onConnect() { connection.callProcedure(commandBigInt); }); |
This time, try making the BigInt parameter a JavaScript string. Tedious says BigInts come back as strings anyway. Don’t hold your breath until you succeed, because I have zero confidence in this.
If you examine what happens in SQL Server, you’ll see this:
Well, -9223372036854775808 is not what’s expected. Why has this result missed the target with such a weird answer? Tedious has what it needs, a BigInt data type. From Node’s perspective, it’s sending a JavaScript string type as a parameter to write data. To make matters worse, the query attempts to use a JavaScript number which also fails. So far, we’ve failed to use the BigInt data type at the limit for both read/write operations. Why does Tedious not like this? What limitations am I running into? It is now time to find out what the problem is.
What’s the Problem?
At first glance, it seems I’m hitting a limit with JavaScript numbers that are below the BigInt limit. Negative nine quintillion is the negative BigInt limit. The JavaScript number must be exceeding its own Number data type limit. If true, it follows that Tedious overflows into the negative BigInt limit. My initial reaction is to focus on the JavaScript Number data type and check its behavior.
To test this theory with a quick unit test:
|
1 2 3 |
var assert = require('assert'); var number = 9223372036854775807; assert(number === 9223372036854776000); |
It looks as if the erratic bizarre behavior is JavaScript’s way of handling numbers. From examining this test, it appears that it uses exponents to represent very big numbers. Think of 9223372036854776000 as 9.223372036854776×10^18. Because the trailing three integers 5807 hit a hard limit, it got rounded up to six thousand.
It’s time to dig in a little deeper.
JavaScript Number Type
The JavaScript Number data type is a 64-bit IEEE 754 floating point value. This explains the behavior you saw when it rounded the BigInt limit to the nearest thousand. If you are a math geek, go see the ECMAScript specification on the Number type. The specification itself goes into more detail than is necessary to understand this problem.
For me to solve this problem, I needed just the detail that the ECMAScript Number type has an integer representation of 53-bits. Don’t worry if you are not into binary math because you only need to know that 53-bits equal 2^53 which equal 9007199254740992. Now, this is a number I can test with code!
To test this, alter the BigInt parameter from the insert script above to the maximum integer that JavaScript can represent exactly:
|
1 |
commandBigInt.addParameter('Id', TYPES.BigInt, 9007199254740992); |
To verify that this works, we can now test in SQL Server:
Success. A passing test brings me one step closer to a working solution I am confident in.
In retrospect, you are dealing with two limits. One limit for the SQL Server BigInt data type and one for the JavaScript Number data type. The Node environment will support BigInt integers up to the JavaScript Number limit. The JavaScript limit is about nine quadrillion, so lower than a BigInt. But, can I do better? Is there a way to use the full extent of the BigInt data type? Yes, by representing them as strings.
Use Raw Strings
Given the insight that Tedious returns a BigInt as a JavaScript string type, I’d be remiss not to swap the Tedious BigInt type for a string. In Tedious, parameter data types match SQL Server data types at a glance. But, based on the observations so far, this is JavaScript. The Tedious TYPES.BigInt type hits the same limitations you see with the JavaScript Number type. The BigInt type in Tedious has little in common with the SQL Server BigInt type.
There are many data types in Tedious that map to SQL Server. Each one is represented by a static constant used as an enumeration, such as TYPES.Bit. In looking at the list of supported data types, let me pick TYPES.VarChar. It’s the closest representation of a BigInt integer I can think of. This makes sense because a VarChar will map to a JavaScript string data type.
To test this theory, swap the Tedious data type in the parameter:
|
1 |
commandBigInt.addParameter('Id', TYPES.VarChar, '9223372036854775806'); |
If you run SQL Profiler while the script runs, you’ll see this:
|
1 |
exec dbo.InsertBigInt @Id='9223372036854775806',@IsAtMax=0 |
SQL Server doesn’t care that the BigInt parameter got wrapped around an apostrophe. It expects a BigInt type parameter. A question to ask here is, does it do any type casting?
Running this exact stored procedure with these parameters reveals this execution plan:
Phew, no evidence of any weird data type casting and it is using the primary key to insert, good.
It looks as if making everything a string works, this is good progress. If all you ever do is to use BigInt data constants, then this is sufficient.
This approach does come with limitations. A JavaScript string type is not an integer. You cannot do any math on it. It is impractical to increment a number represented by a string by one, for example, especially if it is higher than 9223372036854775806.
So, what are my options?
Use an NPM Big Integer Library
If you look at the Tedious npm package, you’ll notice an interesting dependency. The Tedious package comes with a BigNumber npm package. Note that dependencies sometimes come with even more dependencies. At times, those inner dependencies are useful to you. Often, the best place to look for functionality is to see what you already have, before adding anything new. This way you end up with a solution that has the minimum number of dependencies.
The BigNumber package touts itself as a light JavaScript library for Node that is built with performance in mind. This will work for my BigInt requirements.
In Node, it is common to see these types of dependencies. JavaScript itself is a light language with a small feature set. The design principle behind Node is to build on this idea and keep it simple. The Node core libraries, for example, are low-level APIs. The idea is to rely on user-land packages to provide for the rest of your needs. This is a conscientious decision by the Node core project team.
For example, say I want to take a BigInt number and subtract by one:
|
1 2 3 |
var bigNumber = require('big-number').n; var number = bigNumber('9223372036854775806').minus(1); commandBigInt.addParameter('Id', TYPES.VarChar, number.toString()); |
Done. Notice the number gets turned into a string while using the VarChar type parameter.
This will fulfill all your BigInt needs, all with a light-weight dependency. Keep in mind that this dependency does not add a notch to my belt. This BigNumber dependency comes with Tedious, which is great.
Looking Ahead
Node is a light alternative way to interact with SQL Server. SQL Server has a lot to offer as many professionals are familiar with its query engine. Imagine tapping into a talent pool with many years of experience with SQL Server. Turns out, the same also applies to JavaScript. JavaScript is over 25 years old and very many IT professionals have experience with this. JavaScript and SQL Server are an awesome combination.
There is a compromise trade-off for this simplicity. SQL Server’s type system is different from JavaScript’s. You can argue that JavaScript’s type system is but a small subset. You will find that some types have issues when translating between the two systems, while some don’t. The BigInt type, for example, is a prime example of one that does. The key is to stay vigilant and aware that you are working with JavaScript. Learning the JavaScript type system will enable you to overcome many obstacles.
Load comments