您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

快速入门:使用 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 数据库中创建数据库,然后对其进行配置: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 托管实例”页。Navigate 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. 导航到已创建的文件夹,并运行以下命令:Navigate to 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