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-node

Step 2. Navigate to the project directory:

cd sqlserver-node

Step 3. Initialize a Node.js project:

npm init --yes

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

npm install mssql

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

DB_USER=YourUser
DB_PASSWORD=YourPassword
DB_SERVER=YourServer
DB_NAME=YourDatabaseName

For example:

DB_USER=joe
DB_PASSWORD=YourPassword
DB_SERVER=localhost
DB_NAME=BookStore

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",

Additionally, change the scripts section to the following:

"scripts": {
    "start": "node --env-file=.env index.js"
  },

By doing this, you can run the command:

npm start

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

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();

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';

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

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();

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

npm start

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!

Node.js SQL Server: Connect

Create a SQL Server Database #

Creating a Node.js Project #

Download the project source code #

Summary #