title	description	author	ms.author	ms.custom	ms.date	ms.service	ms.subservice	ms.topic	monikerRange
Connect to and query Azure SQL Database using Node.js and mssql npm package	Learn how to connect to a database in Azure SQL Database and query data using Node.js and mssql npm package.	diberry	diberry	passwordless-js	05/08/2023	sql-database	security	quickstart	= azuresql \|\| = azuresql-db

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

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. You can learn more about passwordless connections on the passwordless hub.

Prerequisites

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

Configure the database server

[!INCLUDE passwordless-configure-server-networking]

Create the project

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

Create a new directory for the project and navigate into it.
Initialize the project by running the following command in the terminal:
```
npm init -y
```
Install the required packages used in the sample code in this article:
```
npm install mssql swagger-ui-express yamljs
```
Install the development package used in the sample code in this article:
```
npm install --save-dev dotenv 
```
Open the project in Visual Studio Code.
```
code .
```
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",
```

Create Express.js application code

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

File	Description
.env.development	Local development-only environment file.
index.js	Main application file, which starts the Express.js app on port 3000.
person.js	Express.js /person route API file to handle CRUD operations.
openapi.js	Express.js /api-docs route for OpenAPI explorer UI. Root redirects to this route.
openApiSchema.yml	OpenAPI 3.0 schema file defining Person API.
config.js	Configuration file to read environment variables and construct appropriate mssql connection object.
database.js	Database class to handle Azure SQL CRUD operations using the mssql npm package.
./vscode/settings.json	Ignore files by glob pattern during deployment.

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

import express from 'express';
import { config } from './config.js';
import Database from './database.js';

// Import App routes
import person from './person.js';
import openapi from './openapi.js';

const port = process.env.PORT || 3000;

const app = express();

// Development only - don't do in production
// Run this to create the table in the database
if (process.env.NODE_ENV === 'development') {
  const database = new Database(config);
  database
    .executeQuery(
      `CREATE TABLE Person (id int NOT NULL IDENTITY, firstName varchar(255), lastName varchar(255));`
    )
    .then(() => {
      console.log('Table created');
    })
    .catch((err) => {
      // Table may already exist
      console.error(`Error creating table: ${err}`);
    });
}

// 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}`);
});

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

import express from 'express';
import { config } from './config.js';
import Database from './database.js';

const router = express.Router();
router.use(express.json());

// Development only - don't do in production
console.log(config);

// Create database object
const database = new Database(config);

router.get('/', async (_, 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 {
    // Create 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;

Create an opanapi.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;

Create a openApiSchema.yml schema file and add the following YAML:

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

Configure the mssql connection object

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

Passwordless (recommended)

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';
dotenv.config({ path: `.env.${process.env.NODE_ENV}`, debug: true });

const server = process.env.AZURE_SQL_SERVER;
const database = process.env.AZURE_SQL_DATABASE;
const port = parseInt(process.env.AZURE_SQL_PORT);
const type = process.env.AZURE_SQL_AUTHENTICATIONTYPE;

export const config = {
    server,
    port,
    database,
    authentication: {
        type
    },
    options: {
        encrypt: true
    }
};

Create a .env.development file for your local environment variables and add the following text and update with your values for <YOURSERVERNAME> and <YOURDATABASENAME>.

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

Note

Passwordless configuration objects are safe to commit to source control, since they do not contain any secrets such as usernames, passwords, or access keys.

SQL authentication

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';
dotenv.config({ path: `.env.${process.env.NODE_ENV}`, debug: true });

const server = process.env.AZURE_SQL_SERVER;
const database = process.env.AZURE_SQL_DATABASE;
const port = parseInt(process.env.AZURE_SQL_PORT);
const user = process.env.AZURE_SQL_USER;
const password = process.env.AZURE_SQL_PASSWORD;

export const config = {
    server,
    port,
    database,
    user,
    password,
    options: {
        encrypt: true
    }
};

Create a .env.development file for your local environment variables and add the following text and update with your values for <YOURSERVERNAME>, <YOURDATABASENAME>, <YOURUSERNAME>, and <YOURPASSWORD>.
```
AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.net
AZURE_SQL_DATABASE=<YOURDATABASENAME>
AZURE_SQL_PORT=1433
AZURE_SQL_USER=<YOURUSERNAME>
AZURE_SQL_PASSWORD=<YOURPASSWORD>
```

Warning

Use caution when managing connection objects that contain secrets such as usernames, passwords, or access keys. These secrets shouldn't be committed to source control or placed in unsecure locations where they might be accessed by unintended users.

Create a .vscode folder and create a settings.json file in the folder.
Add the following to ignore environment variables and dependencies during the zip deployment.
```
{
    "appService.zipIgnorePattern": ["./.env*","node_modules{,/**}"]
}
```

Add the code to connect to Azure SQL Database

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

import sql from 'mssql';

export default class Database {
  config = {};
  poolconnection = null;
  connected = false;

  constructor(config) {
    this.config = config;
    console.log(`Database: config: ${JSON.stringify(config)}`);
  }

  async connect() {
    try {
      console.log(`Database connecting...${this.connected}`);
      if (this.connected === false) {
        this.poolconnection = await sql.connect(this.config);
        this.connected = true;
        console.log('Database connection successful');
      } else {
        console.log('Database already connected');
      }
    } catch (error) {
      console.error(`Error connecting to database: ${JSON.stringify(error)}`);
    }
  }

  async disconnect() {
    try {
      this.poolconnection.close();
      console.log('Database connection closed');
    } catch (error) {
      console.error(`Error closing database connection: ${error}`);
    }
  }

  async executeQuery(query) {
    await this.connect();
    const request = this.poolconnection.request();
    const result = await request.query(query);

    return result.rowsAffected[0];
  }

  async create(data) {
    await this.connect();
    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() {
    await this.connect();
    const request = this.poolconnection.request();
    const result = await request.query(`SELECT * FROM Person`);

    return result.recordsets[0];
  }

  async read(id) {
    await this.connect();

    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) {
    await this.connect();

    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) {
    await this.connect();

    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];
  }
}

Test the app locally

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.

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.
In a browser, navigate to the OpenAPI explorer at http://localhost:3000.
On the Swagger UI page, expand the POST method and select Try it.
Modify the sample JSON to include values for the properties. The ID property is ignored.

:::image type="content" source="media/passwordless-connections/api-testing-javascript.png" lightbox="media/passwordless-connections/api-testing-javascript.png" alt-text="A screenshot showing how to test the API.":::
Select Execute to add a new record to the database. The API returns a successful response.
Expand the GET method on the Swagger UI page and select Try it. Select Execute, and the person you just created is returned.

Deploy to Azure App Service

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.

Make sure the app is stopped.
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)
In Visual Studio Code's Azure Explorer window, right-click on the App Services node and select Create New Web App (Advanced).

Use the following table to create the App Service:

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.

Wait until the notification that your app was created before continuing.
In the Azure Explorer, expand the App Services node and right-click your new app.
Select Deploy to Web App.

:::image type="content" source="media/passwordless-connections/visual-studio-code-web-app-deploy.png" alt-text="Screenshot of Visual Studio Code in the Azure explorer with the Deploy to Web App highlighted.":::
Select the root folder of the JavaScript project.
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.

Connect the App Service to Azure SQL Database

Passwordless (recommended)

[!INCLUDE passwordless-connect-azure-sql]

SQL Authentication

[!INCLUDE password-connect-azure-sql]

Test the deployed application

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.

Tip

If you receive a 500 Internal Server error while testing, it may be due to your database networking configurations. Verify that your logical server is configured with the settings outlined in the Configure the database section.

[!INCLUDE passwordless-resource-cleanup]

Sample code

The sample code for this application is available on GitHub.

Next steps

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

azure-sql-javascript-mssql-quickstart.md

azure-sql-javascript-mssql-quickstart.md

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

Prerequisites

Configure the database server

Create the project

Create Express.js application code

Configure the mssql connection object

Passwordless (recommended)

SQL authentication

Add the code to connect to Azure SQL Database

Test the app locally

Deploy to Azure App Service

Connect the App Service to Azure SQL Database

Passwordless (recommended)

SQL Authentication

Test the deployed application

Sample code

Next steps

Files

azure-sql-javascript-mssql-quickstart.md

Latest commit

History

azure-sql-javascript-mssql-quickstart.md

File metadata and controls

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

Prerequisites

Configure the database server

Create the project

Create Express.js application code

Configure the mssql connection object

Passwordless (recommended)

SQL authentication

Add the code to connect to Azure SQL Database

Test the app locally

Deploy to Azure App Service

Connect the App Service to Azure SQL Database

Passwordless (recommended)

SQL Authentication

Test the deployed application

Sample code

Next steps