Skip to content

Latest commit

 

History

History
135 lines (101 loc) · 4.85 KB

sql-database-connect-query-nodejs.md

File metadata and controls

135 lines (101 loc) · 4.85 KB
title description services ms.service ms.subservice ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
Use Node.js to query Azure SQL Database | Microsoft Docs
How to use Node.js to create a program that connects to an Azure SQL database and query it using T-SQL statements.
sql-database
sql-database
development
nodejs
quickstart
CarlRabeler
carlrab
v-masebo
craigg
11/26/2018

Quickstart: Use Node.js to query an Azure SQL database

This article demonstrates how to use Node.js to connect to an Azure SQL database. You can then use T-SQL statements to query data.

Prerequisites

To complete this sample, make sure you have the following prerequisites:

[!INCLUDE prerequisites-create-db]

  • A server-level firewall rule for the public IP address of the computer you're using

  • Node.js-related software for your operating system:

    • MacOS, install Homebrew and Node.js, then install the ODBC driver and SQLCMD. See Step 1.2 and 1.3.

    • Ubuntu, install Node.js, then install the ODBC driver and SQLCMD. See Step 1.2 and 1.3.

    • Windows, install Chocolatey and Node.js, then install the ODBC driver and SQLCMD. See Step 1.2 and 1.3.

Get database connection

[!INCLUDE prerequisites-server-connection-info]

Important

You must have a firewall rule in place for the public IP address of the computer on which you perform this tutorial. If you're on a different computer or have a different public IP address, create a server-level firewall rule using the Azure portal.

Create the project

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
npm install async

Add code to query 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.

    var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

// Create connection to database
var config =
{
    userName: 'your_username', // update me
    password: 'your_password', // update me
    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);
}

Note

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. Verify the top 20 rows are returned and close the application window.

Next steps