Package Exports

azuresql

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (azuresql) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

A tiny Azure SQL database Node.js client utility to help you focus on just writing your SQL query.

Installation

npm install azuresql

Usage

const { sqlQuery, closePool } = require('azureSql')
try {
  var result = await sqlQuery('select * from tableName where id = @id', { k: 'id', v: 1 })
  ...
} catch (e) {
  ...
}

Only call closePool() at the end of application where no further queries to the database are to be performed.

Configuration

The following environment variables need to be set up:

sqlUser

Azure SQL database user
sqlPass

Azure SQL database password
sqlServer

Azure SQL database server name in a format like: .database.windows.net
sqlDb

Azure SQL database name
sqlPoolMax

Connection pool maximum connections. Default 1
sqlPoolMin

Connection pool minimum connections. Default 0
sqlPoolIdleTimeout

Connection pool idle timeout in miliseconds. Default 30000 (30 seconds)

Also make sure your Azure SQL's firewall allows the IPs that are trying to connect.

Examples

Multiple queries in one go

var r = await sqlQuery(`SELECT userId FROM ${tableUser} WHERE id = @id; SELECT beginTime FROM ${tableSession} WHERE session = @session`,
  { k: 'id', v: data.userId }, { k: 'session', v: data.sessionNo })
r.recordsets[0] ... // first query results
r.recordsets[1] ... // second query results

Insert and SQL Data Types

const { sql, sqlQuery } = require('azuresql')
var r = await sqlQuery(`insert into ${tableName} (id, notes, amount) vaules(@id, @comment, @money)`,
  { k: 'id', v: 3 }, { k: 'comment', v: 'blah...', type: sql.VarChar(sql.MAX) }, { k: 'money', v: 9876543210.1234, type: sql.Money })

Check more SQL data types.

An upsert example

A slightly complex example using T-SQL's upsert/merge syntax. The code is succinct as the data object has the same property names as the table comlumns.

const columns = ['orderNo', 'details', 'quantity'] // table columns
const sql =
  `merge ${tableName} target
  using (values (@orderNo)) as src (orderNo)
  on target.orderNo = src.orderNo
  when matched then
    update set ${columns.slice(1).map(r => 'target.' + r + '=@' + r).join()}
  when not matched by target then
    insert (${columns.join()})
    values (@${columns.join(',@')})`

// When the data object has the same property names as the table columns, the prepared statement will be easy:
var params = columns.map(r => ({ k: r, v: data[r] }))
var result = await sqlQuery(sql, ...params)

Enable debug info

Set the DEBUG envrionment variable to azuresql.

On Windows CMD:

set DEBUG=azuresql & node app.js

PowerShell (VS Code default)

$env:DEBUG='azuresql'; node app.js

Functions

sqlQuery(sql, ...params) ⇒ `object`

Query Azure SQL database

Kind: global function Returns: object - an object like: { recordsets: [[{...}]], recordset:[{...}], output: {}, rowsAffected: [] }

Param	Type	Description
sql	`string`	T-SQL
...params	`object`	rest parameters
param.k	`string`	key for prepared statements
param.v	`string`	value for prepared statements
[param.type]	`object`	SQL data types, optional in normal cases

closePool()

NOTE: Should only be called at the end of application!

Kind: global function

getRequest() ⇒ `object`

NOTE: Only when you need to extend and play with other features in mssql package

Kind: global function Returns: object - Request object

azuresql