@concorde2k/ccsql

CCSql Concorde SQL Driver/Utilities

[TOC]

Intro

The preferred protocol for communicating with MS SQL Server is the Tabular Data Stream (TDS). Tedious is an implementation of the protocol available from NPM. Unlike other data streams, such as MongoDB, MySql, etc, Tedious is a more of a driver than a tool, functioning at a low level that distinguishes between connections and operations. The team that built Tedious also created a higher level tool called node-msql. But is riddled with issues. Let's be honest, for all but the simplest activities it is...well..dreadful. It adds one useful feature, the ability to stream data using node native streams. But even that feature is fussy and needlessly complex, and it is not a real stream. Tedious and node-mssql are the closest thing to an official MS SQL library for node, but it is not an actual MSFT product and lacks the polish we would expect from them.

Whatever. Right?

This library attenpts to overcome the limitations of Tedious and to pull in some of the features node-mssql without creating a specific dependency on it.

Design Goals/Features

• Provide real node native streams by impementng a Read and Write stream that adhere to stream norms, such as raising errors only by events, raising finish and end and other events as per other streams
• Syntactic sugar to make simple queries easy to work with by abstracting the seperate Connection and Request events cycles and rigidly synchronizing the lifetimes (The Tedious implementation lacks a connection pool)
• Syntactic sugar to help format columns for writing by formatting values as safe strings with appropriate format for the data type in INSERT and UPDATE statements
• Full support for async/await and Promises.
• Utilities for handling collections of columns, for instance to establish INSERT order and other formats
• A robust new object called the Command which combines Requests and Connections in a one abstract container that manages both items, recreating Requests as necessary to reuse an existing connection
• The ability to pull a single table's schema (whose output can be fed directly to the columns utilities above)
• Transactions
• Sprocs
• Pepared statements
• Statement Parameters

What's missing

• Connection re-use. In Tedious a connection can only be used by one query at a time. It makes sure you don't try to violate that, but a real connection pool would be nice. There is a plugin pool implementation but it has not been updated in 2 years. For now, CCSql treats the connection as a cheap, disposable thing.
• Bulk insert
• Batch commands. It does support statement separators with ;, but not large batches with shared parameters.

Installation

npm install @concorde/ccsql --save

Quick Starts

Let's get a list of Accidents from the server

Immediate Queries

const { immediate } = require( "ccsql" );

// we add our query here as well as our connection
// definition
const res = await immediate( "select * from Accidents", {
    server   : "sql3",
    password : "mypassword",
    login    : "my-user",
    db       : "IMS"
} );

// hey! we ran a query and the results were returned directly. I thumb my nose at
// asynchronous functions. Hah!
console.log(`number of rows returned ${res.rowCount}`);
console.log(`And introducing the rows ${res.rows}`);

// When done with this, it is best to toss the
// command. It has closed the connection and cleared
// the request out.

Immediate Queries with Parameters

Pretty cool for a small data set with no parameters. Now lets' try some params.

const { immediate, SqlTypes } = require( "ccsql" );
// we define the parameter in the query and use that name in parameter definitions which
// is the contents of the third parameter to the `immediate` function. The type is
// important to get right because the `tedious` library validate the parameter
// value by scrubbing invalid or dangerous sequences, like semicolons ";",
// comments "--" and such.
const res = await immediate( "select * from Accidents where ERID=@erid", config.sql, [
		{
			name : "erid",
			type : SqlTypes.Int,
			value: 1712
		}
	] );

So, this stuff is cool, but only for small datasets since the entirety of the result set is kept in memory at all times. Large data sets will definitely crash your process. Think of immediate as a handy thing for calling lookups, or filtered queries that return, say, 1000 rows. The exact number will be defined by the contents of the table. A table that holds PDFs in multiple columns, probably won't survive a hundred records.

Streams

Node streams are powerful tools for dealing with a lot of information. It can also be handy when doing a simple transform from the DB to the front end.

Working with a Query stream is a lot like working with a Command.

const { query } = require( "ccsql" );
const q = await query( "select * from Accidents", config.sql );
const myDownStreamStream = new SomeOtherStreamThing();

q.pipe( myDownStreamStream );
// at this point, you just have a regular stream. We can manage this using regular stream events.
q.on( "end", ( r ) => {
	 // do something interesting, like clean up some memory. Jeez, look at all this memory
	 // laying around. Clean this up before your father gets home!!!

} );

Query streams are single use devices. Once your command is complete and all rows have been returned, you can't use it again.

Building CCSql

CCSql uses make to control the build. On Windows you can get make from Cygwin/Swan or build-essential on Ubuntu.

# Build will put the output in the ./dist directory. This will compile the Typescript down
# to regular old javascript, copy over static files and generally make the output
# consistent
make build

# to blow away the current build and clean up the output directory
make clean

# to blow the current build out and also blow out the node_modules directory
make dist-clean  # that name has a long history in make dating back to early unix

# To clean up the output directory and force the system to perform a full rebuild
make build-all

# Watch changes and compile
make watch

# A fast node_modules refresh
make node_modules

# To build the documentation
make techdocs

Versioning

The version increment uses NPM which will tag git with the version number. Because of that git has to be consistent, checked in with no unversioned files not accounted for in an .ignore file or NPM will complain. Major versions are not automated since a major version is a big decision and probably should managed seperately.

It is reccomended that you update the version number after you have published the feature to the master branch.

# To increment the version number you can either update the patch
make patch-up
# or
make minor-up

Publishing

You can publish any version at any time (IOW, testing artifacts and production version can live simultaneously provided you are careful with the version number), but you can't overwrite an existing version. You will need write access to the NPM organization.

make publish