Releases: TimelordUK/node-sqlserver-v8
Releases · TimelordUK/node-sqlserver-v8
v1.1.8: multiple interactions with driver and info messages
v1.1.7
v1.1.6: fix for bulk methods which wrongly callback immediately
v1.1.5
TVP Date failure
TVP numeric vector crash
v1.1.2
Node v 14 released
bulk insert int32/int64 + proc schema on params
connection pool
you can now submit queries through a native library connection pool. This pool creates a set of connections and queues work submitting items such that all connections are busy providing work exists. A keep alive is sent periodically to check connection integrity and idle connections beyond a threshold are closed and re-created when queries submitted at a later point in time. Queries can be cancelled and paused / resumed regardless of where they are in the work lifecycle
examples can be seen here and here
export interface PoolOptions {
floor: number
ceiling: number
heartbeatSecs: number
heartbeatSql: string
inactivityTimeoutSecs: number
connectionString: string
}
const pool = new sql.Pool(options)the following example shows the pool being used.
const sql = require('msnodesqlv8')
const pool = new sql.Pool({
connectionString: 'Driver={ODBC Driver 13 for SQL Server};Server=(localdb)\\node;Database=scratch;Trusted_Connection=yes;'
})
pool.on('open', (options) => {
console.log(`ready options = ${JSON.stringify(options, null, 4)}`)
})
pool.on('debug', msg => {
console.log(`\t\t\t\t\t\t${new Date().toLocaleTimeString()} <pool.debug> ${msg}`)
})
pool.on('status', s => {
console.log(`status = ${JSON.stringify(s, null, 4)}`)
})
pool.on('error', e => {
console.log(e)
})
const testSql = 'waitfor delay \'00:00:10\';'
function submit (sql) {
const q = pool.query(sql)
console.log(`send ${new Date().toLocaleTimeString()}, sql = ${sql}`)
q.on('submitted', d => {
console.log(`query submitted ${new Date().toLocaleTimeString()}, sql = ${d.query_str}`)
q.on('done', () => console.log(`query done ${new Date().toLocaleTimeString()}`))
})
return q
}
for (let i = 0; i < 7; ++i) {
const q = submit(testSql)
switch (i) {
case 5:
console.log('cancel a query')
q.cancelQuery()
break
case 6:
q.pauseQuery()
setTimeout(() => {
console.log('resume a paused query')
q.resumeQuery()
}, 50000)
break
default:
break
}
}
setInterval(() => {
submit(testSql)
}, 60000)
pool.open()"C:\Program Files\nodejs\node.exe" C:\dev\js\sql\node_modules\msnodesqlv8\samples\javascript\pooling.js
send 20:29:46, sql = waitfor delay '00:00:10';
send 20:29:46, sql = waitfor delay '00:00:10';
send 20:29:46, sql = waitfor delay '00:00:10';
send 20:29:46, sql = waitfor delay '00:00:10';
send 20:29:46, sql = waitfor delay '00:00:10';
send 20:29:46, sql = waitfor delay '00:00:10';
cancel a query
send 20:29:46, sql = waitfor delay '00:00:10';
20:29:46 <pool.debug> grow creates 4 connections for pool idle = 0, busy = 0, pending = 4
status = {
"time": "2020-04-04T19:29:46.278Z",
"parked": 0,
"idle": 1,
"busy": 0,
"pause": 0,
"workQueue": 7,
"op": "checkin"
}
20:29:46 <pool.debug> [0] checkin idle = 1, parked = 0, busy = 0, pause = 0, workQueue = 7
status = {
"time": "2020-04-04T19:29:46.278Z",
"parked": 0,
"idle": 2,
"busy": 0,
"pause": 0,
"workQueue": 7,
"op": "checkin"
}
20:29:46 <pool.debug> [1] checkin idle = 2, parked = 0, busy = 0, pause = 0, workQueue = 7
status = {
"time": "2020-04-04T19:29:46.278Z",
"parked": 0,
"idle": 3,
"busy": 0,
"pause": 0,
"workQueue": 7,
"op": "checkin"
}
20:29:46 <pool.debug> [2] checkin idle = 3, parked = 0, busy = 0, pause = 0, workQueue = 7
status = {
"time": "2020-04-04T19:29:46.278Z",
"parked": 0,
"idle": 4,
"busy": 0,
"pause": 0,
"workQueue": 7,
"op": "checkin"
}
20:29:46 <pool.debug> [3] checkin idle = 4, parked = 0, busy = 0, pause = 0, workQueue = 7
ready options = {
"floor": 0,
"ceiling": 4,
"heartbeatSecs": 20,
"heartbeatSql": "select @@SPID as spid",
"inactivityTimeoutSecs": 60,
"connectionString": "Driver={ODBC Driver 13 for SQL Server};Server=(localdb)\\node;Database=scratch;Trusted_Connection=yes;"
}
status = {
"time": "2020-04-04T19:29:46.279Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 0,
"workQueue": 6,
"op": "checkout"
}
20:29:46 <pool.debug> [0] checkout idle = 3, parked = 0, busy = 1, pause = 0, workQueue = 6
20:29:46 <pool.debug> [0] query work id = 0, workQueue = 6
status = {
"time": "2020-04-04T19:29:46.280Z",
"parked": 0,
"idle": 2,
"busy": 2,
"pause": 0,
"workQueue": 5,
"op": "checkout"
}
20:29:46 <pool.debug> [1] checkout idle = 2, parked = 0, busy = 2, pause = 0, workQueue = 5
20:29:46 <pool.debug> [1] query work id = 1, workQueue = 5
status = {
"time": "2020-04-04T19:29:46.280Z",
"parked": 0,
"idle": 1,
"busy": 3,
"pause": 0,
"workQueue": 4,
"op": "checkout"
}
20:29:46 <pool.debug> [2] checkout idle = 1, parked = 0, busy = 3, pause = 0, workQueue = 4
20:29:46 <pool.debug> [2] query work id = 2, workQueue = 4
status = {
"time": "2020-04-04T19:29:46.280Z",
"parked": 0,
"idle": 0,
"busy": 4,
"pause": 0,
"workQueue": 3,
"op": "checkout"
}
20:29:46 <pool.debug> [3] checkout idle = 0, parked = 0, busy = 4, pause = 0, workQueue = 3
20:29:46 <pool.debug> [3] query work id = 3, workQueue = 3
query submitted 20:29:46, sql = waitfor delay '00:00:10';
20:29:46 <pool.debug> [0] submitted work id 0
query submitted 20:29:46, sql = waitfor delay '00:00:10';
20:29:46 <pool.debug> [1] submitted work id 1
query submitted 20:29:46, sql = waitfor delay '00:00:10';
20:29:46 <pool.debug> [2] submitted work id 2
query submitted 20:29:46, sql = waitfor delay '00:00:10';
20:29:46 <pool.debug> [3] submitted work id 3
query done 20:29:56
status = {
"time": "2020-04-04T19:29:56.291Z",
"parked": 0,
"idle": 1,
"busy": 3,
"pause": 0,
"workQueue": 3,
"op": "checkin"
}
20:29:56 <pool.debug> [0] checkin idle = 1, parked = 0, busy = 3, pause = 0, workQueue = 3
20:29:56 <pool.debug> [0] free work id 0
query done 20:29:56
status = {
"time": "2020-04-04T19:29:56.292Z",
"parked": 0,
"idle": 2,
"busy": 2,
"pause": 0,
"workQueue": 3,
"op": "checkin"
}
20:29:56 <pool.debug> [1] checkin idle = 2, parked = 0, busy = 2, pause = 0, workQueue = 3
20:29:56 <pool.debug> [1] free work id 1
query done 20:29:56
status = {
"time": "2020-04-04T19:29:56.293Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 0,
"workQueue": 3,
"op": "checkin"
}
20:29:56 <pool.debug> [2] checkin idle = 3, parked = 0, busy = 1, pause = 0, workQueue = 3
20:29:56 <pool.debug> [2] free work id 2
query done 20:29:56
status = {
"time": "2020-04-04T19:29:56.294Z",
"parked": 0,
"idle": 4,
"busy": 0,
"pause": 0,
"workQueue": 3,
"op": "checkin"
}
20:29:56 <pool.debug> [3] checkin idle = 4, parked = 0, busy = 0, pause = 0, workQueue = 3
20:29:56 <pool.debug> [3] free work id 3
status = {
"time": "2020-04-04T19:29:56.294Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 0,
"workQueue": 2,
"op": "checkout"
}
20:29:56 <pool.debug> [0] checkout idle = 3, parked = 0, busy = 1, pause = 0, workQueue = 2
20:29:56 <pool.debug> [0] query work id = 4, workQueue = 2
20:29:56 <pool.debug> query work id = 5 has been cancelled waiting in pool to execute, workQueue = 1
query submitted 20:29:56, sql = waitfor delay '00:00:10';
20:29:56 <pool.debug> [0] submitted work id 4
query done 20:30:06
status = {
"time": "2020-04-04T19:30:06.297Z",
"parked": 0,
"idle": 4,
"busy": 0,
"pause": 1,
"workQueue": 0,
"op": "checkin"
}
20:30:06 <pool.debug> [0] checkin idle = 4, parked = 0, busy = 0, pause = 1, workQueue = 0
20:30:06 <pool.debug> [0] free work id 4
status = {
"time": "2020-04-04T19:30:06.314Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 1,
"workQueue": 0,
"op": "checkout"
}
20:30:06 <pool.debug> [3] checkout idle = 3, parked = 0, busy = 1, pause = 1, workQueue = 0
status = {
"time": "2020-04-04T19:30:06.317Z",
"parked": 0,
"idle": 4,
"busy": 0,
"pause": 1,
"workQueue": 0,
"op": "checkin"
}
20:30:06 <pool.debug> [3] checkin idle = 4, parked = 0, busy = 0, pause = 1, workQueue = 0
20:30:06 <pool.debug> [3] heartbeat response = '56', 20:30:06, keepAliveCount = 1 inactivePeriod = 20, inactivityTimeoutSecs = 60
status = {
"time": "2020-04-04T19:30:06.564Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 1,
"workQueue": 0,
"op": "checkout"
}
20:30:06 <pool.debug> [2] checkout idle = 3, parked = 0, busy = 1, pause = 1, workQueue = 0
status = {
"time": "2020-04-04T19:30:06.566Z",
"parked": 0,
"idle": 4,
"busy": 0,
"pause": 1,
"workQueue": 0,
"op": "checkin"
}
20:30:06 <pool.debug> [2] checkin idle = 4, parked = 0, busy = 0, pause = 1, workQueue = 0
20:30:06 <pool.debug> [2] heartbeat response = '58', 20:30:06, keepAliveCount = 1 inactivePeriod = 20, inactivityTimeoutSecs = 60
status = {
"time": "2020-04-04T19:30:06.814Z",
"parked": 0,
"idle": 3,
"busy": 1,
"pause": 1,
"workQueue": 0,
"op": "checkout"
}
20:30:06 <pool.debug> [1] checkout idle = 3, parked = 0, busy = 1, pause = 1, workQueue = 0
status = {
"time": "2020-04-04T19:30:06.815Z",
"parked": 0,
"idle"...v1.0.1: Merge pull request #149 from TimelordUK/prebuild
Prebuild binaries - this looks good, fantastic indeed. Going to merge - how do we actually upload the binaries onto release for prebuild to work.?