This blog will show you how to use Node.js to connect to Azure SQL Database and perform read operations. Although there are several ways to connect to SQL Server from Node, the top two most popular packages are msnodesql and mssql.

• msnodesql – Microsoft’s SQL Server connector for Node.js. This enables Node.js apps on Windows and Azure platform to connect to SQL Server and Azure SQL.

• mssql – This a popular third-party SQL Server connector for Node.js that is very easy to use. This package supports Promises, Streams and callbacks to perform database operations. It also supports SQL statements, Stored Procedures, Transactions and Connection Pooling.

We will be using mssql for our sample application. Before we get started creating our application, you need to have a Microsoft Azure account. If you don’t have an account, click here to get a free trial.  

Getting Started

Use Azure Management Portal to create a server and database.

1. Sign into the Azure Management portal
   1. Click SQL databases from the left navigation menu.
   2. In the SQL Database blade, click Add at the top.

2. Enter a Name for your Database.
3. Use an existing Resource group, or create a new one.
   1. A Resource group is a collection of resources that share the same permissions and policies.
4. Click Select Source to specify the source of the database.
   1. For our example, we will use the AdventureWorks sample database
   2. You can also use a backup copy of an existing SQL database.
5. Click Server, then Create a new server.
   1. Make sure the server name is unique
   2. Enter a Server Admin Login and Password.
   3. Choose a geographical location that’s close to you

6. Select no for SQL elastic pool.
7. Click Pricing tier, then select the Basic option.

Next, we will configure the firewall so the database can be accessed from a local client.

1. Click SQL databases from the left navigation menu.
2. Select the database we previously created.
3. Click Set server firewall from the top menu.
4. In the Firewall Settings blade, enter a Rule Name and paste in the your public IP address that is provided.

We can now view the new SQL database within Visual Studio.

1. Click Tools from the top menu of our database.
2. Then select Open In Visual Studio.

3. Once Visual Studio is open, select the AdventureWorks database from SQL Server Object Explorer menu.
4. We will be using the Customer table for our sample application.

Setup the Node.js app

1. Open the command prompt as administrator.
2. Create a directory to store the Node project.
3. Enter npm init -y inside the created directory to create a package.json file with default settings.

4. Then enter npm install –save mssql to install the mssql package.
5. Next, we will need to open the project in your favorite IDE or text editor. I will be using Atom.

The Code

1. Create a new file called index.js within the project.
2. Then paste the following code with comments, and save the file.

Brief explanation of the code:

• First we create a variable for the “mssql” npm package. This makes it easy to connect to SQL Server using Node.js.
• Then we create a configuration object for connecting to the Azure SQL database.
• Within the getCustomers() function,  we use the “Connection” object to connect to SQL Server. Once connected, the “Request” object executes the SELECT statement and fetches the results.
• We also handle both SQL Server connection errors and SQL statement execution errors.

Test in Console

1. Within the command prompt run “Node index.js”.
2. You should now see an array of Customer records within the console.

Conclusion

We just built a simple Node.js app with an Azure SQL Database. The mssql package makes it pretty easy to connect SQL Server and Node.js.