Muokkaa

Jaa

Quickstart: Use Node.js to query a database in Azure SQL Database or Azure SQL Managed Instance

  • Artikkeli

Applies to: Azure SQL Database Azure SQL Managed Instance

In this quickstart, you use Node.js to connect to a database and query data.

Prerequisites

To complete this quickstart, you need:

Important

The scripts in this article are written to use the AdventureWorks database.

Get server connection information

Get the connection information you need to connect to the database. You'll need the fully qualified server name or host name, database name, and login information for the upcoming steps.

  1. Sign in to the Azure portal.

  2. 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 database in Azure SQL Database or the fully qualified server name (or IP address) next to Host for an Azure SQL Managed Instance or SQL Server on Azure VM. To copy the server name or host name, hover over it and select the Copy icon.

Note

For connection information for SQL Server on Azure VM, see Connect to SQL Server.

Create the project

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 mssql

Add code to query the database

  1. In your favorite text editor, create a new file, sqltest.js, in the folder where you created the project (sqltest).

  2. Replace its contents with the following code. Then add the appropriate values for your server, database, user, and password.

    const sql = require('mssql');

const config = {
    user: 'username', // better stored in an app setting such as process.env.DB_USER
    password: 'password', // better stored in an app setting such as process.env.DB_PASSWORD
    server: 'your_server.database.windows.net', // better stored in an app setting such as process.env.DB_SERVER
    port: 1433, // optional, defaults to 1433, better stored in an app setting such as process.env.DB_PORT
    database: 'AdventureWorksLT', // better stored in an app setting such as process.env.DB_NAME
    authentication: {
        type: 'default'
    },
    options: {
        encrypt: true
    }
}

/*
    //Use Azure VM Managed Identity to connect to the SQL database
    const config = {
        server: process.env["db_server"],
        port: process.env["db_port"],
        database: process.env["db_database"],
        authentication: {
            type: 'azure-active-directory-msi-vm'
        },
        options: {
            encrypt: true
        }
    }

    //Use Azure App Service Managed Identity to connect to the SQL database
    const config = {
        server: process.env["db_server"],
        port: process.env["db_port"],
        database: process.env["db_database"],
        authentication: {
            type: 'azure-active-directory-msi-app-service'
        },
        options: {
            encrypt: true
        }
    }
*/

console.log("Starting...");
connectAndQuery();

async function connectAndQuery() {
    try {
        var poolConnection = await sql.connect(config);

        console.log("Reading rows from the Table...");
        var resultSet = await poolConnection.request().query(`SELECT TOP 20 pc.Name as CategoryName,
            p.name as ProductName 
            FROM [SalesLT].[ProductCategory] pc
            JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid`);

        console.log(`${resultSet.recordset.length} rows returned.`);

        // output column headers
        var columns = "";
        for (var column in resultSet.recordset.columns) {
            columns += column + ", ";
        }
        console.log("%s\t", columns.substring(0, columns.length - 2));

        // output row contents from default record set
        resultSet.recordset.forEach(row => {
            console.log("%s\t%s", row.CategoryName, row.ProductName);
        });

        // close connection only when we're certain application is finished
        poolConnection.close();
    } catch (err) {
        console.error(err.message);
    }
}

Note

For more information about using managed identity for authentication, complete the tutorial to access data via managed identity. Details about the Tedious configuration options for Microsoft Entra ID (formerly Azure Active Directory) are available in the Tedious documentation.

Run the code

  1. At the command prompt, run the program.

    node sqltest.js

  2. Verify the top 20 rows are returned and close the application window.

Next steps