const Connection = require('tedious').Connection;
const Request = require('tedious').Request;
const TYPES = require('tedious').TYPES

// Azure SQL Database に接続する
async function ConnectDataBase(config) {
  return new Promise((resolve, reject) => {
    const connection = new Connection(config);
    connection.on('connect', function(err) {
      if (err) {
        reject(err);
      }
      resolve(connection);
    });
  });
}

// SQLを実行して結果セットを取得する
async function getRows(connection, sql, params) {
  return new Promise((resolve, reject) => {
    const result = [];
    const request = new Request(sql, function(err, rows) {
      if (err) {
        reject(err);
      }
      resolve(result);
    });
    if (Array.isArray(params)) {
      for (const p of params) {
        request.addParameter(p.name, p.dataType, p.value);
      }
    }
    request.on('row', function(columns) {
      result.push(columns);
    });
    connection.execSql(request);
  });
}

(async () => {
  const config = {
    server:'DB_HOST',
    userName:'DB_USER',
    password:'DB_PASSS',
    options:{
        database:'DB_CATALOG',
        encrypt:true
    }
  };
  try {
    const cn = await ConnectDataBase(config);
    try {
      const sql = 'SELECT TOP 10 Name FROM test_table WHERE Name like @Name';
      const params = [ { name: 'Name', dataType: TYPES.NVarChar, value: '%Foo%' } ];
      for (const row of await getRows(cn, sql, params)) {
        console.log(row[0].value);
      }
    }
    finally {
      cn.close();
    }
  }
  catch (e) {
    console.log(`Error!! ${e.message}`);
  }
})();