快速入門:使用 Node.js 查詢 Azure SQL 資料庫Quickstart: Use Node.js to query an Azure SQL database

本文示範如何使用 Node.js 連線至 Azure SQL 資料庫。This article demonstrates how to use Node.js to connect to an Azure SQL database. 您可以接著使用 T-SQL 陳述式來查詢資料。You can then use T-SQL statements to query data.

必要條件Prerequisites

若要完成此範例,請確定您具有下列必要條件:To complete this sample, make sure you have the following prerequisites:

  • Azure SQL 資料庫。An Azure SQL database. 您可以使用其中一個快速入門,在 Azure SQL Database 中建立資料庫並加以設定:You can use one of these quickstarts to create and then configure a database in Azure SQL Database:

    單一資料庫Single database 受控執行個體Managed instance
    建立Create 入口網站Portal 入口網站Portal
    CLICLI CLICLI
    PowerShellPowerShell PowerShellPowerShell
    設定Configure 伺服器層級 IP 防火牆規則Server-level IP firewall rule VM 的連線能力Connectivity from a VM
    現場的連線能力Connectivity from on-site
    載入資料Load data 每個快速入門載入的 Adventure WorksAdventure Works loaded per quickstart 還原 Wide World ImportersRestore Wide World Importers
    GitHub 中的 BACPAC 檔案還原或匯入 Adventure WorksRestore or import Adventure Works from BACPAC file from GitHub

    重要

    本文中已撰寫的指令碼會使用 Adventure Works 資料庫。The scripts in this article are written to use the Adventure Works database. 對於受控執行個體,您必須將 Adventure Works 資料庫匯入執行個體資料庫中,或將本文中的指令碼修改為使用 Wide World Importers 資料庫。With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.

  • 您作業系統的 Node.js 相關軟體:Node.js-related software for your operating system:

    • MacOS,安裝 Homebrew 和 Node.js,然後安裝 ODBC 驅動程式和 SQLCMD。MacOS, install Homebrew and Node.js, then install the ODBC driver and SQLCMD. 請參閱步驟 1.2 和 1.3See Step 1.2 and 1.3.

    • Ubuntu,安裝 Node.js,然後安裝 ODBC 驅動程式和 SQLCMD。Ubuntu, install Node.js, then install the ODBC driver and SQLCMD. 請參閱步驟 1.2 和 1.3See Step 1.2 and 1.3.

    • Windows,安裝 Chocolatey 和 Node.js,然後安裝 ODBC 驅動程式和 SQLCMD。Windows, install Chocolatey and Node.js, then install the ODBC driver and SQLCMD. 請參閱步驟 1.2 和 1.3See Step 1.2 and 1.3.

取得 SQL Server 連線資訊Get SQL server connection information

取得連線到 Azure SQL 資料庫所需的連線資訊。Get the connection information you need to connect to the Azure SQL database. 在後續程序中,您將需要完整的伺服器名稱或主機名稱、資料庫名稱和登入資訊。You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. 登入 Azure 入口網站Sign in to the Azure portal.

  2. 移至 [SQL 資料庫] 或 [SQL 受控執行個體] 頁面。Go to the SQL databases or SQL managed instances page.

  3. 在 [概觀] 頁面上,針對單一資料庫檢閱 [伺服器名稱] 旁的完整伺服器名稱,若為受控執行個體,則檢閱 [主機] 旁的完整伺服器名稱。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要複製伺服器名稱或主機名稱,請將滑鼠暫留在其上方,然後選取 [複製] 圖示。To copy the server name or host name, hover over it and select the Copy icon.

建立專案Create the project

開啟命令提示字元,並建立名為 sqltest 的資料夾。Open a command prompt and create a folder named sqltest. 開啟您建立的資料夾,然後執行下列命令:Open the folder you created and run the following command:

npm init -y
npm install tedious@5.0.3
npm install async@2.6.2

新增程式碼以查詢資料庫Add code to query database

  1. 在您慣用的文字編輯器中,建立新的檔案 sqltest.jsIn your favorite text editor, create a new file, sqltest.js.

  2. 以下列程式碼取代其內容。Replace its contents with the following code. 然後,為您的伺服器、資料庫、使用者和密碼新增適當的值。Then add the appropriate values for your server, database, user, and password.

    var Connection = require('tedious').Connection;
    var Request = require('tedious').Request;
    
    // Create connection to database
    var config =
    {
        authentication: {
            options: {
                userName: 'userName', // update me
                password: 'password' // update me
            },
            type: 'default'
        },
        server: 'your_server.database.windows.net', // update me
        options:
        {
            database: 'your_database', //update me
            encrypt: true
        }
    }
    var connection = new Connection(config);
    
    // Attempt to connect and execute queries if connection goes through
    connection.on('connect', function(err)
        {
            if (err)
            {
                console.log(err)
            }
            else
            {
                queryDatabase()
            }
        }
    );
    
    function queryDatabase()
    {
        console.log('Reading rows from the Table...');
    
        // Read all rows from table
        var request = new Request(
            "SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc "
                + "JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid",
            function(err, rowCount, rows)
            {
                console.log(rowCount + ' row(s) returned');
                process.exit();
            }
        );
    
        request.on('row', function(columns) {
            columns.forEach(function(column) {
                console.log("%s\t%s", column.metadata.colName, column.value);
            });
        });
        connection.execSql(request);
    }
    

注意

程式碼範例會使用適用於 Azure SQL 的 AdventureWorksLT 範例資料庫。The code example uses the AdventureWorksLT sample database for Azure SQL.

執行程式碼Run the code

  1. 在命令提示字元中,執行程式。At the command prompt, run the program.

    node sqltest.js
    
  2. 請確認前 20 個資料列已傳回,然後關閉應用程式視窗。Verify the top 20 rows are returned and close the application window.

後續步驟Next steps