Connect to and query Azure SQL Database using Node.js and mssql npm package

Applies to: Azure SQL Database

This quickstart describes how to connect an application to a database in Azure SQL Database and perform queries using Node.js and mssql. This quickstart follows the recommended passwordless approach to connect to the database.

Passwordless connections offer a more secure mechanism for accessing Azure resources. The following high-level steps are used to connect to Azure SQL Database using passwordless connections in this article:

• Prepare your environment for password-free authentication.
  • For a local environment: Your personal identity is used. This identity can be pulled from an IDE, CLI, or other local development tools.
  • For a cloud environment: A managed identity is used.
• Authenticate in the environment using the DefaultAzureCredential from the Azure Identity library to obtain a verified credential.
• Use the verified credential to create Azure SDK client objects for resource access.

You can learn more about passwordless connections on the passwordless hub.

• An Azure subscription
• A database in Azure SQL Database configured for authentication with Microsoft Entra ID (formerly Azure Active Directory). You can create one using the Create database quickstart.
• Bash-enabled shell
• Node.js LTS
• Visual Studio Code
• Visual Studio Code App Service extension
• The latest version of the Azure CLI

Secure, passwordless connections to Azure SQL Database require certain database configurations. Verify the following settings on your logical server in Azure to properly connect to Azure SQL Database in both local and hosted environments:

1. For local development connections, make sure your logical server is configured to allow your local machine IP address and other Azure services to connect:

   • Navigate to the Networking page of your server.

   • Toggle the Selected networks radio button to show additional configuration options.

   • Select Add your client IPv4 address(xx.xx.xx.xx) to add a firewall rule that will enable connections from your local machine IPv4 address. Alternatively, you can also select + Add a firewall rule to enter a specific IP address of your choice.

   • Make sure the Allow Azure services and resources to access this server checkbox is selected.

     

     Warning

     Enabling the Allow Azure services and resources to access this server setting is not a recommended security practice for production scenarios. Real applications should implement more secure approaches, such as stronger firewall restrictions or virtual network configurations.

     You can read more about database security configurations on the following resources:

     • Configure Azure SQL Database firewall rules.
     • Configure a virtual network with private endpoints.

2. The server must also have Microsoft Entra authentication enabled and have a Microsoft Entra admin account assigned. For local development connections, the Microsoft Entra admin account should be an account you can also log into Visual Studio or the Azure CLI with locally. You can verify whether your server has Microsoft Entra authentication enabled on the Microsoft Entra ID page of your logical server.

   

3. If you're using a personal Azure account, make sure you have Microsoft Entra setup and configured for Azure SQL Database in order to assign your account as a server admin. If you're using a corporate account, Microsoft Entra ID will most likely already be configured for you.

The steps in this section create a Node.js REST API.

1. Create a new directory for the project and navigate into it.

2. Initialize the project by running the following command in the terminal:

   npm init -y
   

3. Install the required packages used in the sample code in this article:

   npm install mssql express swagger-ui-express yamljs dotenv
   

4. Open the project in Visual Studio Code.

   code .
   

5. Open the package.json file and add the following property and value after the name property to configure the project for ESM modules.

   "type": "module",
   

To create the Express.js OpenAPI application, you'll create several files:

1. Create an index.js file and add the following code:

   import express from 'express';
   
   // Import App routes
   import person from './person.js';
   import openapi from './openapi.js';
   
   const port = process.env.PORT || 3000;
   
   const app = express();
   
   // Connect App routes
   app.use('/api-docs', openapi);
   app.use('/persons', person);
   app.use('*', (_, res) => {
     res.redirect('/api-docs');
   });
   
   // Start the server
   app.listen(port, () => {
     console.log(`Server started on port ${port}`);
   });
   

2. Create a person.js route file and add the following code:

   import express from 'express';
   import { 
     passwordConfig as SQLAuthentication, 
     noPasswordConfig as PasswordlessConfig 
   } from './config.js';
   import { createDatabaseConnection } from './database.js';
   
   const router = express.Router();
   router.use(express.json());
   
   const database = await createDatabaseConnection(SQLAuthentication);
   
   router.get('/', async (req, res) => {
     try {
       // Return a list of persons
   
       const persons = await database.readAll();
       console.log(`persons: ${JSON.stringify(persons)}`);
       res.status(200).json(persons);
     } catch (err) {
       res.status(500).json({ error: err?.message });
     }
   });
   
   router.post('/', async (req, res) => {
     try {
       // add a person
       const person = req.body;
       console.log(`person: ${JSON.stringify(person)}`);
       const rowsAffected = await database.create(person);
       res.status(201).json({ rowsAffected });
     } catch (err) {
       res.status(500).json({ error: err?.message });
     }
   });
   
   router.get('/:id', async (req, res) => {
     try {
       // Get the person with the specified ID
       const personId = req.params.id;
       console.log(`personId: ${personId}`);
       if (personId) {
         const result = await database.read(personId);
         console.log(`persons: ${JSON.stringify(result)}`);
         res.status(200).json(result);
       } else {
         res.status(404);
       }
     } catch (err) {
       res.status(500).json({ error: err?.message });
     }
   });
   
   router.put('/:id', async (req, res) => {
     try {
       // Update the person with the specified ID
       const personId = req.params.id;
       console.log(`personId: ${personId}`);
       const person = req.body;
   
       if (personId && person) {
         delete person.id;
         console.log(`person: ${JSON.stringify(person)}`);
         const rowsAffected = await database.update(personId, person);
         res.status(200).json({ rowsAffected });
       } else {
         res.status(404);
       }
     } catch (err) {
       res.status(500).json({ error: err?.message });
     }
   });
   
   router.delete('/:id', async (req, res) => {
     try {
       // Delete the person with the specified ID
       const personId = req.params.id;
       console.log(`personId: ${personId}`);
   
      if (!personId) {
         res.status(404);
       } else {
         const rowsAffected = await database.delete(personId);
         res.status(204).json({ rowsAffected });
       }
     } catch (err) {
       res.status(500).json({ error: err?.message });
     }
   });
   
   export default router;
   

   For passwordless authentication, change the param passed into createDatabaseConnection from SQLAuthentication to PasswordlessConfig.

   const database = await createDatabaseConnection(PasswordlessConfig);
   

3. Create an openapi.js route file and add the following code for the OpenAPI UI explorer:

   import express from 'express';
   import { join, dirname } from 'path';
   import swaggerUi from 'swagger-ui-express';
   import yaml from 'yamljs';
   import { fileURLToPath } from 'url';
   
   const __dirname = dirname(fileURLToPath(import.meta.url));
   
   const router = express.Router();
   router.use(express.json());
   
   const pathToSpec = join(__dirname, './openApiSchema.yml');
   const openApiSpec = yaml.load(pathToSpec);
   
   router.use('/', swaggerUi.serve, swaggerUi.setup(openApiSpec));
   
   export default router;
   

4. Create an openApiSchema.yml file and add the following code so the OpenAPI UI explorer knows what APIs and models to display:

   openapi: 3.0.0
   info:
     version: 1.0.0
     title: Persons API
   paths:
     /persons:
       get:
         summary: Get all persons
         responses:
           '200':
             description: OK
             content:
               application/json:
                 schema:
                   type: array
                   items:
                     $ref: '#/components/schemas/Person'
       post:
         summary: Create a new person
         requestBody:
           required: true
           content:
             application/json:
               schema:
                 $ref: '#/components/schemas/Person'
         responses:
           '201':
             description: Created
             content:
               application/json:
                 schema:
                   $ref: '#/components/schemas/Person'
     /persons/{id}:
       parameters:
         - name: id
           in: path
           required: true
           schema:
             type: integer
       get:
         summary: Get a person by ID
         responses:
           '200':
             description: OK
             content:
               application/json:
                 schema:
                   $ref: '#/components/schemas/Person'
           '404':
             description: Person not found
       put:
         summary: Update a person by ID
         requestBody:
           required: true
           content:
             application/json:
               schema:
                 $ref: '#/components/schemas/Person'
         responses:
           '200':
             description: OK
             content:
               application/json:
                 schema:
                   $ref: '#/components/schemas/Person'
           '404':
             description: Person not found
       delete:
         summary: Delete a person by ID
         responses:
           '204':
             description: No Content
           '404':
             description: Person not found
   components:
     schemas:
       Person:
         type: object
         properties:
           id:
             type: integer
             readOnly: true
           firstName:
             type: string
           lastName:
             type: string
   

The mssql package implements the connection to Azure SQL Database by providing a configuration setting for an authentication type.

1. In Visual Studio Code, create a config.js file and add the following mssql configuration code to authenticate to Azure SQL Database.

   import * as dotenv from 'dotenv';
   
   if(process.env.NODE_ENV === 'development') {
     dotenv.config({ path: `.env.${process.env.NODE_ENV}`, debug: true });
   }
   
   // TIP: Port must be a number, not a string!
   const server = process.env.AZURE_SQL_SERVER;
   const database = process.env.AZURE_SQL_DATABASE;
   const port = +process.env.AZURE_SQL_PORT;
   const type = process.env.AZURE_SQL_AUTHENTICATIONTYPE;
   const user = process.env.AZURE_SQL_USER;
   const password = process.env.AZURE_SQL_PASSWORD;
   
   export const noPasswordConfig = {
     server,
     port,
     database,
     authentication: {
       type
     },
     options: {
       encrypt: true
     }
   };
   
   export const passwordConfig = {
     server,
     port,
     database,
     user,
     password,
     options: {
       encrypt: true
     }
   };
   

Create a .env.development file for your local environment variables

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.chinacloudapi.cn
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_AUTHENTICATIONTYPE=azure-active-directory-default

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.chinacloudapi.cn
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_USER=<YOURUSERNAME>
AZURE_SQL_PASSWORD=<YOURPASSWORD>

1. Create a database.js file and add the following code:

   import sql from 'mssql';
   
   let database = null;
   
   export default class Database {
     config = {};
     poolconnection = null;
     connected = false;
   
     constructor(config) {
       this.config = config;
     }
   
     async connect() {
       try {
         this.poolconnection = await sql.connect(this.config);
         this.connected = true;
         console.log('Database connected successfully.');
         return this.poolconnection;
       } catch (error) {
         console.error('Error connecting to the database:', error);
         this.connected = false;
       }
     }
   
     async disconnect() {
       try {
         if (this.connected) {
           await this.poolconnection.close();
           this.connected = false;
           console.log('Database disconnected successfully.');
         }
       } catch (error) {
         console.error('Error disconnecting from the database:', error);
       }
     }
   
     async executeQuery(query) {
       const request = this.poolconnection.request();
       const result = await request.query(query);
   
       return result.rowsAffected[0];
     }
   
     async create(data) {
       const request = this.poolconnection.request();
   
       request.input('firstName', sql.NVarChar(255), data.firstName);
       request.input('lastName', sql.NVarChar(255), data.lastName);
   
       const result = await request.query(
         `INSERT INTO Person (firstName, lastName) VALUES (@firstName, @lastName)`
       );
   
       return result.rowsAffected[0];
     }
   
     async readAll() {
       const request = this.poolconnection.request();
       const result = await request.query(`SELECT * FROM Person`);
   
       return result.recordsets[0];
     }
   
     async read(id) {
       const request = this.poolconnection.request();
       const result = await request
         .input('id', sql.Int, +id)
         .query(`SELECT * FROM Person WHERE id = @id`);
   
       return result.recordset[0];
     }
   
     async update(id, data) {
       const request = this.poolconnection.request();
   
       request.input('id', sql.Int, +id);
       request.input('firstName', sql.NVarChar(255), data.firstName);
       request.input('lastName', sql.NVarChar(255), data.lastName);
   
       const result = await request.query(
         `UPDATE Person SET firstName=@firstName, lastName=@lastName WHERE id = @id`
       );
   
       return result.rowsAffected[0];
     }
   
     async delete(id) {
       const idAsNumber = Number(id);
   
       const request = this.poolconnection.request();
       const result = await request
         .input('id', sql.Int, idAsNumber)
         .query(`DELETE FROM Person WHERE id = @id`);
   
       return result.rowsAffected[0];
     }
   
     async createTable() {
       if (process.env.NODE_ENV === 'development') {
         this.executeQuery(
           `IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')
            BEGIN
              CREATE TABLE Person (
                id int NOT NULL IDENTITY, 
                firstName varchar(255), 
                lastName varchar(255)
              );
            END`
         )
           .then(() => {
             console.log('Table created');
           })
           .catch((err) => {
             // Table may already exist
             console.error(`Error creating table: ${err}`);
           });
       }
     }
   }
   
   export const createDatabaseConnection = async (passwordConfig) => {
     database = new Database(passwordConfig);
     await database.connect();
     await database.createTable();
     return database;
   };
   

The app is ready to be tested locally. Make sure you're signed in to the Azure Cloud in Visual Studio Code with the same account you set as the admin for your database.

1. Run the application with the following command. The app starts on port 3000.

   NODE_ENV=development node index.js
   

   The Person table is created in the database when you run this application.

2. In a browser, navigate to the OpenAPI explorer at http://localhost:3000.

3. On the Swagger UI page, expand the POST method and select Try it.

4. Modify the sample JSON to include values for the properties. The ID property is ignored.

   

5. Select Execute to add a new record to the database. The API returns a successful response.

6. Expand the GET method on the Swagger UI page and select Try it. Select Execute, and the person you just created is returned.

1. Create a .vscode folder and create a settings.json file in the folder.

2. Add the following to ignore environment variables and dependencies during the zip deployment.

   {
       "appService.zipIgnorePattern": ["./.env*","node_modules{,/**}"]
   }
   

The app is ready to be deployed to Azure. Visual Studio Code can create an Azure App Service and deploy your application in a single workflow.

1. Make sure the app is stopped.

2. Sign in to Azure, if you haven't already, by selecting the Azure: Sign In to Azure Cloud command in the Command Palette (Ctrl + Shift + P)

3. In Visual Studio Code's Azure Explorer window, right-click on the App Services node and select Create New Web App (Advanced).

4. Use the following table to create the App Service:

   Expand table

   Prompt	Value
   Enter a globally unique name for the new web app.	Enter a prompt such as azure-sql-passwordless. Post-pend a unique string such as 123.
   Select a resource group for new resources.	Select +Create a new resource group then select the default name.
   Select a runtime stack.	Select an LTS version of the Node.js stack.
   Select an OS.	Select Linux.
   Select a location for new resources.	Select a location close to you.
   Select a Linux App Service plan.	Select Create new App Service plan. then select the default name.
   Select a pricing tier.	Select Free (F1).
   Select an Application Insights resource for your app.	Select Skip for now.

5. Wait until the notification that your app was created before continuing.

6. In the Azure Explorer, expand the App Services node and right-click your new app.

7. Select Deploy to Web App.

   

8. Select the root folder of the JavaScript project.

9. When the Visual Studio Code pop-up appears, select Deploy.

When the deployment finishes, the app doesn't work correctly on Azure. You still need to configure the secure connection between the App Service and the SQL database to retrieve your data.

Browse to the URL of the app to test that the connection to Azure SQL Database is working. You can locate the URL of your app on the App Service overview page.

The person you created locally should display in the browser. Congratulations! Your application is now connected to Azure SQL Database in both local and hosted environments.

When you are finished working with the Azure SQL Database, delete the resource to avoid unintended costs.

az sql db delete --name <database-name> --resource-group <resource-group-name> --server <logical-server-name>

The sample code for this application is available:

• JavaScript
• TypeScript

• Tutorial: Secure a database in Azure SQL Database
• Authorize database access to SQL Database
• An overview of Azure SQL Database security capabilities
• Azure SQL Database security best practices