Skip to content

connect-query-nodejs.md

Latest commit

 

History

History
212 lines (160 loc) · 9.24 KB

connect-query-nodejs.md

File metadata and controls

212 lines (160 loc) · 9.24 KB
title Use Node.js to query a database
titleSuffix Azure SQL Database & SQL Managed Instance
description How to use Node.js to create a program that connects to a database in Azure SQL Database or Azure SQL Managed Instance, and query it using T-SQL statements.
author dzsquared
ms.author drskwier
ms.reviewer wiassaf, mathoma, v-masebo
ms.date 05/19/2021
ms.service sql-database
ms.subservice connect
ms.topic quickstart
ms.custom
seo-javascript-september2019
seo-javascript-october2019
sqldbrb=2
devx-track-js
mode-api
ms.devlang javascript
monikerRange = azuresql || = azuresql-db || = azuresql-mi

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

[!INCLUDEappliesto-sqldb-sqlmi]

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 Adventure Works database.

Note

You can optionally choose to use an Azure SQL Managed Instance.

To create and configure, use the Azure portal, PowerShell, or CLI, and then set up on-premises or VM connectivity.

To load data, see restore with BACPAC with the Adventure Works file, or see restore the Wide World Importers database.

Get server connection information

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

  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 tedious

Add code to query the database

  1. In 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.

    const { Connection, Request } = require("tedious");

// Create connection to database
const 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
  }
};

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

*/

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
  if (err) {
    console.error(err.message);
  } else {
    queryDatabase();
  }
  connection.close();
});

connection.connect();

function queryDatabase() {
  console.log("Reading rows from the Table...");

  // Read all rows from table
  const 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`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log(`${rowCount} row(s) returned`);
      }
    }
  );

  request.on("row", columns => {
    columns.forEach(column => {
      console.log("%s\t%s", column.metadata.colName, column.value);
    });
  });

  connection.execSql(request);
}

Note

For more information about using managed identity for authentication, complete the tutorial to access data via managed identity.

Note

The code example uses the AdventureWorksLT sample database in Azure SQL Database.

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