使用 Node.js 和 mssql npm 套件連線至和查詢 Azure SQL 資料庫

發行項
10/07/2024

適用於：Azure SQL 資料庫

本快速入門說明如何在 Azure SQL 資料庫中將應用程式連線至資料庫，並使用 Node.js 和 mssql 執行查詢。本快速入門遵循建議的無密碼方法與資料庫連線。

適用於開發人員的無密碼連線

無密碼連線提供更安全的機制來存取 Azure 資源。本文使用以下進階步驟，透過無密碼連線來連線至 Azure SQL 資料庫：

準備您的環境以進行無密碼驗證。
- 對於本機環境：系統會使用您的個人身分識別。可以從 IDE、CLI 或其他本機開發工具提取此身分識別。
- 對於雲端環境：系統會使用受控識別。
在環境中使用身分識別程式庫中的 DefaultAzureCredential 進行驗證，以取得經驗證的認證。
使用經驗證的認證來建立 Azure SDK 用戶端物件，以存取資源。

您可以在無密碼中樞上深入了解無密碼連線。

必要條件

Azure 訂閱
Azure SQL 資料庫中的資料庫設定為使用 Microsoft Entra ID (先前稱為 Azure AD) 進行驗證。您可以使用建立資料庫快速入門建立資料庫。
啟用 Bash 的殼層
Node.js LTS
Visual Studio Code
Visual Studio Code App Service 延伸項目
最新版本的 Azure CLI

設定資料庫伺服器

Azure SQL 資料庫的安全無密碼連線需要特定資料庫設定。確認 Azure 中邏輯伺服器上的下列設定，以在本機和託管環境中正確連線到 Azure SQL 資料庫：

針對本機開發連線，請確定您的邏輯伺服器已設定為允許本機電腦 IP 位址及其他 Azure 服務連線：
- 瀏覽至伺服器的 [網路] 頁面。
- 切換 [選取的網路]選項按鈕以顯示其他組態選項。
- 選取 [新增用戶端 IPv4 位址 (xx.xx.xx.xx)]，以新增防火牆規則，啟用來自本機電腦 IPv4 位址的連線。或者您也可以選取 [+ 新增防火牆規則] 來輸入您選擇的特定 IP 位址。
- 確定選取 [允許 Azure 服務和資源存取此伺服器] 核取方塊。
  警告
  
  對於實際執行場景，出於安全考量，不建議啟用 [允許 Azure 服務和資源存取此伺服器] 設定。實際應用程式應該實作更安全的方法，例如更強大的防火牆限制或虛擬網路設定。
  
  可以存取下列資源，深入了解資料庫安全設定：
  - 設定 Azure SQL 資料庫防火牆規則。
  - 設定具有私人端點的虛擬網路。
伺服器也必須啟用 Microsoft Entra 驗證，並已指派 Microsoft Entra 管理帳戶。針對本機開發連線，Microsoft Entra 管理帳戶應該也可以用於本機登入 Visual Studio 或 Azure CLI 的帳戶。您可以在邏輯伺服器的 [Microsoft Entra ID] 頁面確認伺服器是否已啟用 Microsoft Entra 驗證。
如果您使用個人 Azure 帳戶，請確定您已針對 Azure SQL 資料庫設定 Microsoft Entra，將您的帳戶指派為伺服器管理員。如果您使用公司帳戶，很可能已為您設定好 Microsoft Entra ID。

建立專案

本節中的步驟用於建立 Node.js REST API。

建立專案的新目錄並瀏覽至該目錄。
透過在終端機中執行以下命令來初始化專案：
```
npm init -y
```

安裝本文範例程式碼中使用的必要套件：

npm install mssql express swagger-ui-express yamljs dotenv

在 Visual Studio Code 中開啟專案。
```
code .
```
開啟 package.json 檔案並在 name 屬性後新增下列屬性和值，為 ESM 模組設定專案。
```
"type": "module",
```

建立 Express.js 應用程式程式碼

若要建立 Express.js OpenAPI 應用程式，您將建立數個檔案：

檔案	說明
.env.development	僅限本機開發的環境檔案。
index.js	主應用程式檔案，在連接埠 3000 上啟動 Express.js 應用程式。
person.js	Express.js /person 路由 API 檔案來處理 CRUD 作業。
openapi.js	OpenAPI 總管 UI 的 Express.js /api-docs 路由。根路徑重新導向至此路由。
openApiSchema.yml	定義 Person API 的 OpenAPI 3.0 結構描述檔案。
config.js	用於讀取環境變數並建構適當 mssql 連線物件的設定檔。
database.js	使用 mssql npm 套件來處理 Azure SQL CRUD 作業的資料庫類。
./vscode/settings.json	在部署期間依 Glob 模式略過檔案。

建立 index.js 檔案，並新增下列程式碼：

import express from 'express';

// Import App routes
import person from './person.js';
import openapi from './openapi.js';

const port = process.env.PORT || 3000;

const app = express();

// Connect App routes
app.use('/api-docs', openapi);
app.use('/persons', person);
app.use('*', (_, res) => {
  res.redirect('/api-docs');
});

// Start the server
app.listen(port, () => {
  console.log(`Server started on port ${port}`);
});

建立 person.js 路由檔案，並新增下列程式碼：

import express from 'express';
import { 
  passwordConfig as SQLAuthentication, 
  noPasswordConfig as PasswordlessConfig 
} from './config.js';
import { createDatabaseConnection } from './database.js';

const router = express.Router();
router.use(express.json());

const database = await createDatabaseConnection(SQLAuthentication);

router.get('/', async (req, res) => {
  try {
    // Return a list of persons

    const persons = await database.readAll();
    console.log(`persons: ${JSON.stringify(persons)}`);
    res.status(200).json(persons);
  } catch (err) {
    res.status(500).json({ error: err?.message });
  }
});

router.post('/', async (req, res) => {
  try {
    // add a person
    const person = req.body;
    console.log(`person: ${JSON.stringify(person)}`);
    const rowsAffected = await database.create(person);
    res.status(201).json({ rowsAffected });
  } catch (err) {
    res.status(500).json({ error: err?.message });
  }
});

router.get('/:id', async (req, res) => {
  try {
    // Get the person with the specified ID
    const personId = req.params.id;
    console.log(`personId: ${personId}`);
    if (personId) {
      const result = await database.read(personId);
      console.log(`persons: ${JSON.stringify(result)}`);
      res.status(200).json(result);
    } else {
      res.status(404);
    }
  } catch (err) {
    res.status(500).json({ error: err?.message });
  }
});

router.put('/:id', async (req, res) => {
  try {
    // Update the person with the specified ID
    const personId = req.params.id;
    console.log(`personId: ${personId}`);
    const person = req.body;

    if (personId && person) {
      delete person.id;
      console.log(`person: ${JSON.stringify(person)}`);
      const rowsAffected = await database.update(personId, person);
      res.status(200).json({ rowsAffected });
    } else {
      res.status(404);
    }
  } catch (err) {
    res.status(500).json({ error: err?.message });
  }
});

router.delete('/:id', async (req, res) => {
  try {
    // Delete the person with the specified ID
    const personId = req.params.id;
    console.log(`personId: ${personId}`);

    if (!personId) {
      res.status(404);
    } else {
      const rowsAffected = await database.delete(personId);
      res.status(204).json({ rowsAffected });
    }
  } catch (err) {
    res.status(500).json({ error: err?.message });
  }
});

export default router;

針對無密碼驗證，請將傳遞至 createDatabaseConnection 的參數從 SQLAuthentication 變更為 PasswordlessConfig。

const database = await createDatabaseConnection(PasswordlessConfig);

建立 openapi.js 路由檔案，並為 OpenAPI UI 總管新增下列程式碼：

import express from 'express';
import { join, dirname } from 'path';
import swaggerUi from 'swagger-ui-express';
import yaml from 'yamljs';
import { fileURLToPath } from 'url';

const __dirname = dirname(fileURLToPath(import.meta.url));

const router = express.Router();
router.use(express.json());

const pathToSpec = join(__dirname, './openApiSchema.yml');
const openApiSpec = yaml.load(pathToSpec);

router.use('/', swaggerUi.serve, swaggerUi.setup(openApiSpec));

export default router;

設定 mssql 連線物件

mssql 套件藉由提供驗證類型的組態設定，實作與 Azure SQL 資料庫的連線。

在 Visual Studio Code 中，建立 config.js 檔案，並新增下列 mssql 設定程式碼，以向 Azure SQL 資料庫驗證。

import * as dotenv from 'dotenv';

if(process.env.NODE_ENV === 'development') {
  dotenv.config({ path: `.env.${process.env.NODE_ENV}`, debug: true });
}

// TIP: Port must be a number, not a string!
const server = process.env.AZURE_SQL_SERVER;
const database = process.env.AZURE_SQL_DATABASE;
const port = +process.env.AZURE_SQL_PORT;
const type = process.env.AZURE_SQL_AUTHENTICATIONTYPE;
const user = process.env.AZURE_SQL_USER;
const password = process.env.AZURE_SQL_PASSWORD;

export const noPasswordConfig = {
  server,
  port,
  database,
  authentication: {
    type
  },
  options: {
    encrypt: true
  }
};

export const passwordConfig = {
  server,
  port,
  database,
  user,
  password,
  options: {
    encrypt: true
  }
};

建立區域環境變數檔案

為您的本機環境變數建立 .env.development 檔案

無密碼 (建議)
SQL 驗證

新增下列文字，並使用 <YOURSERVERNAME> 和 <YOURDATABASENAME> 的值進行更新。

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.net
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_AUTHENTICATIONTYPE=azure-active-directory-default

注意

無密碼設定物件可以安全地提交至原始檔控制，因為它們不包含任何機密，例如使用者名稱、密碼或存取金鑰。

新增下列文字，並使用 <YOURSERVERNAME>、<YOURDATABASENAME>、<YOURUSERNAME> 和 <YOURPASSWORD> 的值進行更新。

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.net
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_USER=<YOURUSERNAME>
AZURE_SQL_PASSWORD=<YOURPASSWORD>

警告

管理包含使用者名稱、密碼或存取金鑰等祕密的連接物件時，請小心。這些祕密不應提交至原始檔控制，或存放於非預期使用者可能存取的不安全位置。

新增程式碼以連線至 Azure SQL 資料庫

建立 database.js 檔案，並新增下列程式碼：

import sql from 'mssql';

let database = null;

export default class Database {
  config = {};
  poolconnection = null;
  connected = false;

  constructor(config) {
    this.config = config;
  }

  async connect() {
    try {
      this.poolconnection = await sql.connect(this.config);
      this.connected = true;
      console.log('Database connected successfully.');
      return this.poolconnection;
    } catch (error) {
      console.error('Error connecting to the database:', error);
      this.connected = false;
    }
  }

  async disconnect() {
    try {
      if (this.connected) {
        await this.poolconnection.close();
        this.connected = false;
        console.log('Database disconnected successfully.');
      }
    } catch (error) {
      console.error('Error disconnecting from the database:', error);
    }
  }

  async executeQuery(query) {
    const request = this.poolconnection.request();
    const result = await request.query(query);

    return result.rowsAffected[0];
  }

  async create(data) {
    const request = this.poolconnection.request();

    request.input('firstName', sql.NVarChar(255), data.firstName);
    request.input('lastName', sql.NVarChar(255), data.lastName);

    const result = await request.query(
      `INSERT INTO Person (firstName, lastName) VALUES (@firstName, @lastName)`
    );

    return result.rowsAffected[0];
  }

  async readAll() {
    const request = this.poolconnection.request();
    const result = await request.query(`SELECT * FROM Person`);

    return result.recordsets[0];
  }

  async read(id) {
    const request = this.poolconnection.request();
    const result = await request
      .input('id', sql.Int, +id)
      .query(`SELECT * FROM Person WHERE id = @id`);

    return result.recordset[0];
  }

  async update(id, data) {
    const request = this.poolconnection.request();

    request.input('id', sql.Int, +id);
    request.input('firstName', sql.NVarChar(255), data.firstName);
    request.input('lastName', sql.NVarChar(255), data.lastName);

    const result = await request.query(
      `UPDATE Person SET firstName=@firstName, lastName=@lastName WHERE id = @id`
    );

    return result.rowsAffected[0];
  }

  async delete(id) {
    const idAsNumber = Number(id);

    const request = this.poolconnection.request();
    const result = await request
      .input('id', sql.Int, idAsNumber)
      .query(`DELETE FROM Person WHERE id = @id`);

    return result.rowsAffected[0];
  }

  async createTable() {
    if (process.env.NODE_ENV === 'development') {
      this.executeQuery(
        `IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')
         BEGIN
           CREATE TABLE Person (
             id int NOT NULL IDENTITY, 
             firstName varchar(255), 
             lastName varchar(255)
           );
         END`
      )
        .then(() => {
          console.log('Table created');
        })
        .catch((err) => {
          // Table may already exist
          console.error(`Error creating table: ${err}`);
        });
    }
  }
}

export const createDatabaseConnection = async (passwordConfig) => {
  database = new Database(passwordConfig);
  await database.connect();
  await database.createTable();
  return database;
};

在本機測試應用程式

應用程式已準備好在本機進行測試。確定使用設定為資料庫管理員的帳戶，在 Visual Studio Code 中登入 Azure 雲端。

使用下列命令執行應用程式。應用程式會在連接埠 3000 上啟動。
```
NODE_ENV=development node index.js
```
當您執行此應用程式時，系統會在資料庫中建立 Person 資料表。
在瀏覽器中，瀏覽至位於 http://localhost:3000 的 OpenAPI 總管。
在 Swagger UI 頁面上，展開 POST 方法，然後選取 [試用]。
修改範例 JSON 以包含屬性的值。 ID 屬性會被略過。
選取 [執行] 以將新記錄新增至資料庫。 API 會傳回成功的回應。
在 Swagger UI 頁面上，展開 GET 方法，然後選取 [試用]。選取 [執行]，隨即傳回您剛才建立的人員。

設定要 .zip 部署的專案。

建立 .vscode 資料夾，並在資料夾中建立 settings.json 檔案。
新增下列內容以略過 zip 部署期間的環境變數和相依性。
```
{
    "appService.zipIgnorePattern": ["./.env*","node_modules{,/**}"]
}
```

部署到 Azure App Service

如此應用程式即已就緒，可開始部署至 Azure。 Visual Studio Code 可以建立 Azure App Service，並在單一工作流程中部署應用程式。

請確定應用程式已停止。
如果尚未登入 Azure，請在命令選擇區中選取 Azure: Sign In to Azure Cloud 命令 (Ctrl + Shift + P)
在 Visual Studio Code 的 [Azure 總管] 視窗中，以滑鼠右鍵按一下 App Service 節點，然後選取 [建立新的 Web 應用程式] (進階)。

使用下表建立 App Service：

提示	值
輸入新 Web 應用程式的全域唯一名稱。	輸入提示，例如 `azure-sql-passwordless`。後置唯一字串，例如 `123`。
選取新資源的資源群組。	選取 [+ 新建資源群組]，然後選取預設名稱。
選取執行階段堆疊。	選取 Node.js 堆疊的 LTS 版本。
選取作業系統。	選取 [Linux]。
選取新資源的位置。	選取您附近的位置。
選取 Linux App Service 方案。	選取 [建立新的 App Service 方案]。然後選取預設名稱。
選取價格區間。	選取 [免費] (F1)。
為應用程式選取 Application Insights 資源。	選取 [暫時略過]。

等待直至收到應用程式建立的通知後，再繼續。
在 Azure 總管中，展開 App Service 節點，然後以滑鼠右鍵按一下新的應用程式。
選取 [部署至 Web 應用程式]。
選取 JavaScript 專案的根資料夾。
當 Visual Studio Code 彈出視窗顯示時，選取 [部署]。

部署完成時，應用程式將無法在 Azure 上正常運作。您仍然需要設定 App Service 與 SQL Database 之間的安全連線，才可擷取您的資料。

若要將 App Service 執行個體連線至 Azure SQL 資料庫，必須執行下列步驟：

建立 App Service 的受控識別。
建立 SQL Database 使用者，並將其與 App Service 受控識別關聯。
將 SQL 角色指派給允許讀取、寫入和其他權限的資料庫使用者。

有多項工具可用來實作下列步驟：

服務連接器 (建議)
Azure 入口網站

服務連接器是一種工具，可簡化 Azure 中不同服務之間的已驗證連線。服務連接器目前支援使用 az webapp connection create sql 命令，透過 Azure CLI 將 App Service 連線至 Azure SQL 資料庫。這個單一命令會為您完成上述的三個步驟。

使用服務連接器建立受控識別

在 Azure 入口網站的 Cloud Shell 中執行下列命令。 Cloud Shell 具有最新版本的 Azure CLI。將 <> 中的變數替換為您自己的值。

az webapp connection create sql \
    -g <app-service-resource-group> \
    -n <app-service-name> \
    --tg <database-server-resource-group> \
    --server <database-server-name> \
    --database <database-name> \
    --system-identity

驗證 App Service 應用程式設定

您可以在 App Service 設定上確認服務連接器所做的變更。

在 Visual Studio Code 的 Azure 總管中，以滑鼠右鍵按一下您的 App Service，然後選取 [在入口網站中開啟]。
瀏覽至 App Service 的 [身分識別] 頁面。在 [系統指派] 索引標籤下，[狀態] 應設定為 [開啟]。此值表示您的應用程式已啟用系統指派的受控識別。
瀏覽至 App Service 的 [組態] 頁面。在 [應用程式設定] 索引標籤下，您應該會看到數個環境變數，這些環境變數已經在 mssql 設定物件中。
- AZURE_SQL_SERVER
- AZURE_SQL_DATABASE
- AZURE_SQL_PORT
- AZURE_SQL_AUTHENTICATIONTYPE
請勿刪除或者變更屬性名稱或值。

Azure 入口網站可讓您使用受控識別，並針對 Azure SQL 資料庫執行查詢。完成下列步驟，透過您的 App Service 執行個體與 Azure SQL 資料庫建立無密碼連線：

建立受控識別

在 Azure 入口網站中，瀏覽至您的 App Service，然後選取左側導覽上的 [身分識別]。
在 [身分識別] 頁面上，將 [系統指派] 狀態變更為 [開啟]，然後選取 [儲存]。
當系統要求您啟用身分識別時，請選取 [是]。

啟用此設定時，系統會使用與 App Service 相同的名稱建立系統指派的受控識別。系統指派的身分識別會系結至服務執行個體，並在刪除應用程式時與應用程式一起終結。

建立資料庫使用者並指派角色

在 Azure 入口網站中，瀏覽至您的 SQL 資料庫，然後選取 [查詢編輯器 (預覽)]。
選取畫面右側的 [以 <your-username> 的身分繼續]，使用您的帳戶登入資料庫。
在查詢編輯器檢視上，執行下列 T-SQL 命令。將 <your-app-service-name> 替換為您的 App Service 資源名稱。
```
CREATE USER "<your-app-service-name>" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "<your-app-service-name>";
ALTER ROLE db_datawriter ADD MEMBER "<your-app-service-name>";
ALTER ROLE db_ddladmin ADD MEMBER "<your-app-service-name>";
GO
```
此 SQL 指令碼會建立 SQL 資料庫使用者，以對應回 App Service 執行個體的受控識別。此指令碼也會將必要的 SQL 角色指派給使用者，以允許您的應用程式讀取、寫入和修改資料庫資料和架構。完成此步驟之後，您的服務隨即會連線。

重要

雖然此解決方案提供簡單的入門方法，但並非實際執行級環境的最佳做法。在這些案例中，應用程式不應該使用提升權限的單一身分識別來執行所有作業。您應該針對特定工作設定具特定權限的多個身分識別，來實作最低權限的原則。

您可以在下列資源上深入了解如何設定資料庫角色和安全性：

建立 App Service 應用程式設定

在 Azure 入口網站中，瀏覽至您的 App Service，然後選取左側導覽上的 [設定]。
選取下方每個環境變數的 [+ 新增應用程式設定]。新增您自己的適當值，為 App Service 執行個體建立必要的環境變數，以連線到您的資料庫。
```
AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.net
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_AUTHENTICATIONTYPE=azure-active-directory-default
```
當您完成新增設定時，請選取 [儲存]。

在 Visual Studio Code 的 Azure 總管中，以滑鼠右鍵按一下您的 App Service，然後選取 [在入口網站中開啟]。

瀏覽至 App Service 的 [組態] 頁面。在 [應用程式設定] 索引標籤下，使用您自己的值，為下表中的每個屬性建立環境變數。

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.net
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_USER=<YOURUSERNAME>
AZURE_SQL_PASSWORD=<YOURPASSWORD>
NODE_ENV=development

警告

管理包含使用者名稱、密碼或存取金鑰等祕密的連接物件時，請小心。這些祕密不應提交至原始檔控制，或存放於非預期使用者可能存取的不安全位置。對於實際執行級 Azure 環境中的實際應用程式，您可以將連線資訊儲存在安全的位置，例如 App Service 組態設定或 Azure Key Vault。在本機開發期間，您通常會連線到不需要儲存祕密或直接連線到 Azure 的本機資料庫。

測試已部署的應用程式

瀏覽至應用程式的 URL，以測試與 Azure SQL 資料庫間的連線是否正常運作。您可以在 App Service 概觀頁面上找到您應用程式的 URL。

您在本機建立的人員應該會顯示在瀏覽器中。恭喜！您的應用程式現在已與本機和託管環境中的 Azure SQL 資料庫建立連線。

提示

如果您在測試時收到 500 內部伺服器錯誤，可能是您的資料庫網路設定所致。驗證您的邏輯伺服器是否已使用設定資料庫章節中概述的設定進行設定。

清除資源

用完 Azure SQL 資料庫後，請刪除資源以避免非預期的成本。

Azure 入口網站
Azure CLI

在 Azure 入口網站搜尋列中，搜尋 Azure SQL 並選取相符的結果。
在資料庫清單中找到並選取您的資料庫。
在 Azure SQL 資料庫的 [概觀] 頁面，選取 [刪除]。
在開啟的 Azure 您是否確定要刪除... 頁面，輸入資料庫的名稱以進行確認，然後選取 [刪除]。

使用 az sql db delete 命令刪除資料庫。將預留位置參數替換為您自己的值。

az sql db delete --name <database-name> --resource-group <resource-group-name> --server <logical-server-name>

範例指令碼

此應用程式的範例程式碼可用：

共用方式為