Node.js is an exciting technology that has been widely adopted. For those starting out, one of the key requirements is the ability to connect node.js with an enterprise RDBMS such as MS SQL Server.

In this post, we will guide you through the process of connecting your Node.js app with SQL Server successfully, and hopefully, without any errors, doubts or confusions.

Let’s get started!

1. Download

Before getting started on the mission, we need a couple of things:

1. SQL Server 2017 Express Edition* from here, and
2. SQL Server Management Studio (SSMS) 17.8* from here.

I am assuming you have Node.js installed on your PC.

*Version number might differ.

2. Install

Installation is easy. Double-click the SQL Server installer downloaded earlier, named something like SQLServer2017-SSEI-Expr*.

*Again, version number might differ.

Click Basic, then click Accept, and finally click Install.

After successful installation, you are greeted with a final screen containing information like Instance Name, SQL Administrators, Features Installed, Version, and also locations of various things including helpful Resources.

A row of four buttons is present at the bottom, containing: Connect Now, Customize, Install SSMS, and Close.

Close is pretty obvious, and we don’t need to touch Customize.

a. Connect Now

An instance of SQL Server starts running in the background automatically after successful installation (until you stop it manually).

The Connect Now button is a way to connect to that instance without any login. You can execute T-SQL statements right in the terminal.

Press the button, a new SQLCMD terminal window will open up. Terminal is all yours. T-SQL away!

b. Install SSMS

The Install SSMS button will take you to the same download page mentioned in Download above.

If you didn’t download SSMS earlier, now is the time. And then, just install it. Simple install, no worries.

3. Configure

OK! It’s time for some configurations:

1. Enable TCP/IP to allow remote connections, and
2. Enable default login or create a new one.

The default login in SQL Server is sa, stands for System Administrator (aka, sysadmin). It is disabled by default (I don’t know why). You need to enable it, or create a new sysadmin login for yourself.

1. Enable TCP/IP to Allow Remote Connections

Search in Start Menu for SQL Server Configuration Manager. Open it.

You can see that SQL Server (SQLEXPRESS) service is running, and it’s Start Mode is Automatic. Like I said earlier.

If you observe the left pane, you are in SQL Server Services section. Expand SQL Server Network Configuration, and click on Protocols for SQLEXPRESS. You can see TCP/IP is disabled by default. Right-click and Enable it.

Now, we need to set the default TCP port, which for SQL Server is 1433. Double-click on TCP/IP. Click on IP Addresses tab. Scroll down to the bottom to reach IPAII section. Clear TCP Dynamic Ports field and leave it empty. Set TCP Port to 1433. Click OK.

Restart SQL Server (SQLEXPRESS) service, and you are done with first configuration. Onto next one!

2. Enable Default Login or Create a New One

Search in Start Menu for SQL Server Management Studio. Open it.

You are greeted with a dialog box to connect to the server. You have to connect via Windows Authentication because you don’t have a sysadmin login right now to connect via SQL Server Authentication. Exactly the point of this configuration. Click Connect.

On the left, there is an Object Explorer pane. Here you can manage your server: creating and deleting logins, creating and deleting databases, and loads of other admin things, so to say.

Let’s enable the sa login. Expand Security. Expand Logins. You can see a little red cross on sa’s icon. This shows that the login is disabled.

Double-click sa. In the left pane, click Status. Select Enabled under Login in Settings. Click General in the left pane, change password, and click OK. Bam! You have a sysadmin login now.

You can try re-connecting to the server with this newly enabled login, or the one you create. Click File > Disconnect Object Explorer to disconnect. Click File > Connect Object Explorer…, this time, selecting SQL Server Authentication in the Authentication drop-down menu. Enter sa as Login, and the password you chose earlier as Password.

If you want to create a new login:

1. Connect to server, if not already.
2. Expand Security in the left pane.
3. Right-click Logins.
4. Select New Login…
5. Enter Login name.
6. Select SQL Server authentication.
7. Enter and re-enter Password.
8. Click Server Roles in the left pane.
9. Select sysadmin.
10. Click OK.

You have successfully configured your SQL Server.

Errors

Nobody wants errors. But sometimes, they are inevitable. You may encounter one of the two errors when you are trying to connect your Node.js app with SQL Server:

1. ESOCKET: TCP/IP is disabled. Perform first configuration to get rid of this error.
2. ELOGIN: Unable to login. Perform second configuration to get rid of this error.

4. Connect

Let’s create the simplest Node.js app, and connect it to SQL Server.

Create a new folder, say node-sql. Execute npm init in this folder to create package.json.

We need a Node.js driver for SQL Server. tedious is one such driver. Execute:

npm install tedious --save

Create a new index.js file (which will be the main entry point for our app) in node-sql. Open index.js with your favourite text editor.

‘Require’ required modules in the app.

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;

Create a configuration object (config) to be used while connecting to the database.

const config = {
  userName: 'sa', // update
  password: 'your_password', // update
  server: 'localhost',
  options: {
      database: 'SampleDB' // update
  }
}

Use your preferred userName, password and database. Create new Connection object with the earlier created config object.

const connection = new Connection(config);

Try to connect to the database with newly created connection object.

connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    console.log('Connected');
  }
});

Your simplest Node.js app looks like this:

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;

const config = {
  userName: 'sa', // update
  password: 'your_password', // update
  server: 'localhost',
  options: {
      database: 'SampleDB' // update
  }
}

const connection = new Connection(config);

connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    console.log('Connected');
  }
});

Execute:

node index.js

If you see this in console:

Connected

Congrats! You have successfully connected your Node.js app with SQL Server. If you are getting any errors, then refer the Errors section above.

I hope this article was helpful in giving you a quick overview of connecting your node.js application with MS SQL Server.

Note: This post is authored by Mr. Abhay Kumar, interning with GTM Catalyst (distributor of Telerik controls in India).