next.js - 使用Tedious存取SQL Server

為 next.js 練習紀錄。使用 Tedious 模組連接 SQL Server 與下 Query 。

參考文章

GitHub - tediousjs/tedious: Node TDS module for connecting to SQL Server databases.GitHub

原始碼紀錄

後端

api/datahub.ts

import type { NextApiHandler, NextApiRequest, NextApiResponse } from 'next'
import { Request, Connection } from 'tedious'

/// SQL Server 連結組態
const config = {
  "server": "192.168.0.xxx",
  "authentication": {
    "type": "default",
    "options": {
      "userName": "sa",
      "password": "sa_pwd"
    }
  },
  "options": {
    "port": 1433,
    "database": "YourDB",
    "trustServerCertificate": true,
    "rowCollectionOnRequestCompletion": true, /// ※注意。
  }
}

/// NextApiHandler
export default async (req: NextApiRequest, res: NextApiResponse<any>) => {
  const { qryParams } = req.body

  // file.js
  const connection = new Connection(config);
  connection.connect((err: Error) => {
    if (err) {
      res.json({ message: 'Connect SQL Server failed!', err })
      //throw err;
    }

    // If no error, then good to go...
    executeStatement();
  });

  function executeStatement() {
    const request = new Request("select sn = 42, text = 'hello world'", (err, rowCount, rows) => {
      if (err) {
        res.json({ message: 'Execute SQL statement failed!', err })
        //throw err;
      }

      // DONE
      connection.close()
      
      // convert rows as dataList
      // ※注意：需打開rowCollectionOnRequestCompletion才能用rows取到查詢結果。      
      const dataList = rows.map(columns =>
        Object.fromEntries(columns.map(column => ([column.metadata.colName, column.value]))))

      // SUCCESS & response
      res.json({ message: 'Execute SQL statement success.', rowCount, dataList })
    });

    connection.execSql(request);
  }
}

前端

// apiClient
async function checkSqlConn(qryParams): Promise<any> {
  const res = await fetch('/api/datahub', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ qryParams }),
  })

  if (res.status !== 200) {
    const { status, statusText } = res
    return { status, statusText }
  }

  // success
  const result = await res.json()
  return result
}