Use Node.js to query an Azure SQL database

This quickstart tutorial demonstrates how to use Node.js to create a program to connect to an Azure SQL database and use Transact-SQL statements to query data.

Prerequisites

To complete this quickstart tutorial, make sure you have the following:

  • A server-level firewall rule for the public IP address of the computer you use for this quickstart tutorial.

  • You have installed Node.js and related software for your operating system:

    • MacOS: Install Homebrew and Node.js, and then install the ODBC driver and SQLCMD. See Step 1.2 and 1.3.
    • Ubuntu: Install Node.js, and then install the ODBC driver and SQLCMD. See Step 1.2 and 1.3 .
    • Windows: Install Chocolatey and Node.js, and then install the ODBC driver and SQL CMD. See Step 1.2 and 1.3.

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.

    server-name

  4. If you forget your server login information, navigate to the SQL Database server page to view the server admin name. If necessary, reset the password.

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 are on a different computer or have a different public IP address, create a server-level firewall rule using the Azure portal.

Create a Node.js 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

Insert code to query SQL database

  1. In your development environment or favorite text editor, create a new file, sqltest.js.

  2. Replace the contents with the following code and 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: 'someuser', // update me
         password: 'somepassword', // update me
         server: 'edmacasqlserver.database.windows.net', // update me
         options: 
            {
               database: 'somedb' //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
         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);
       }
    

Run the code

  1. At the command prompt, run the following commands:

    node sqltest.js
    
  2. Verify that the top 20 rows are returned and then close the application window.

Next steps