node - sql server接続

tediousjs.github.io

github.com

$ node index.js
db connected
{ deptno: 10, dname: 'ACCOUNTING', loc: 'NEW YORK' }
{ deptno: 20, dname: 'RESEARCH', loc: 'DALLAS' }
{ deptno: 30, dname: 'SALES', loc: 'CHICAGO' }
{ deptno: 40, dname: 'OPERATIONS', loc: 'BOSTON' }
request completed
db disconnected

const Connection = require("tedious").Connection
const Request = require("tedious").Request

/**
 * 接続設定
 */
const config = {
  server: "user-server",
  authentication: {
    type: "default",
    options: {
      userName: "user-name",
      password: "user-pass",
    },
  },
  options: {
    encrypt: true,
    database: "user-database-name",
    trustServerCertificate: true,
    rowCollectionOnDone: true,
  },
}

const connection = new Connection(config)

connection.on("connect", function (err) {
  if (err) {
    console.log(`sql server connect error: ${err}`)
    return
  }

  console.log("db connected")
  execute()
})

connection.on("end", () => {
  console.log("db disconnected")
})

connection.connect()

/**
 * sqlを取得
 */
function getQuery() {
  return "select deptno, dname, loc from dept"
}

/**
 * sql 実行
 */
function execute() {
  const request = new Request(getQuery(), function (err, rows) {
    if (err) {
      console.log(`request error: ${err}`)
    }
  })

  // 結果の行毎に実行されるイベント
  let result = {}
  request.on("row", function (columns) {
    //console.log("[on-event]")
    // console.log("columns[0]:" + columns[0].value)
    // console.log("columns[1]:" + columns[1].value)
    // console.log("columns[2]:" + columns[2].value)

    columns.forEach(function (column) {
      if (column.value === null) {
        console.log("NULL")
      } else {
        result[column.metadata.colName] = column.value
      }
    })

    console.log(result)
  })

  // リクエスト完了イベント
  request.on("requestCompleted", () => {
    console.log("request completed")
    connection.close()
  })

  // sql 実行
  connection.execSql(request)
}