Node.js SQL Server Connect

Summary: in this tutorial, you will learn how to connect to the SQL Server from Node.js using the mssql package.

Create a SQL Server Database

Before continuing, you need to follow this tutorial to create a BookStore database and set up a user with full access permissions.

Creating a Node.js Project

Step 1. Open your terminal and create a new directory such as sqlserver-node to store the project files:

mkdir sqlserver-nodeCode language: JavaScript (javascript)

Step 2. Navigate to the project directory:

cd sqlserver-nodeCode language: JavaScript (javascript)

Step 3. Initialize a Node.js project:

npm init --yesCode language: JavaScript (javascript)

Step 4. Install the mssql package using the following npm command:

npm install mssqlCode language: JavaScript (javascript)

Step 5. Create a .env file that stores the SQL server configuration:

DB_USER=YourUser
DB_PASSWORD=YourPassword
DB_SERVER=YourServer
DB_NAME=YourDatabaseNameCode language: JavaScript (javascript)

For example:

DB_USER=joe
DB_PASSWORD=YourPassword
DB_SERVER=localhost
DB_NAME=BookStoreCode language: JavaScript (javascript)

Step 6. Modify the package.json file and add the type="module" to the top-level key/value pairs to use the ES6 module:

 "type": "module",Code language: JavaScript (javascript)

Additionally, change the scripts section to the following:

  "scripts": {
    "start": "node --env-file=.env index.js"
  },Code language: JavaScript (javascript)

By doing this, you can run the command:

npm startCode language: JavaScript (javascript)

Node.js will load the .env file and run the index.js file.

Step 7. Create the config.js file to store the database configuration:

export const config = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  server: process.env.DB_SERVER,
  options: {
    encrypt: true,
    trustServerCertificate: true,
  },
};Code language: JavaScript (javascript)

In the config.js file we create a config object used to connect to the SQL Server. The config object uses the process.env object to access the database configuration stored in the .env file.

Step 8. Create index.js file that connects to the SQL Server:

import sql from 'mssql';
import { config } from './config.js';

const connect = async () => {
  try {
    await sql.connect(config);
    console.log('Connected to the database!');
  } catch (err) {
    console.error(err);
  }
};

connect();Code language: JavaScript (javascript)

How it works.

First, import sql object from mssql module and config object from the config.js module:

import sql from 'mssql';
import { config } from './config.js';Code language: JavaScript (javascript)

Second, define a connect() function that connects to the SQL Server:

const connect = async () => {
  try {
    await sql.connect(config);
    console.log('Connected to the database!');
  } catch (err) {
    console.error(err);
  }
};Code language: JavaScript (javascript)

To connect to the SQL Server, you call the connect function of the sql object and pass the config object. Since the sql.connect() function returns a promise, we can use the await keyword.

Since we use the await keyword inside the connect function, we must also mark the function as an async function.

If any error occurs during the connection process, we catch the exception using the try…catch statement and display the error in the catch block.

Third, call the connect() function to establish a connection to the SQL Server:

connect();Code language: JavaScript (javascript)

Step 9. Run the npm start command in the terminal to execute the index.js file:

npm startCode language: JavaScript (javascript)

If you see the following message, meaning that you have successfully connected to the SQL Server from the Node.js program:

Connected to the database!Code language: JavaScript (javascript)

Download the project source code

Summary

Use the mssql package to connect to an SQL Server from Node.js

Was this tutorial helpful?