Run MSSQL Server|Azure with Sequelize Example and Node.js

Sequelize is an ORM (Object-Relational Mapping). It allows you to connect and model data for databases like MySQL, PostgreSQL, SQLite, and MSSQL, for example. MS SQL Server/Azure SQL is one of the popular servers you will love integrating with Sequelize ORM. This gives you an intuitive approach to creating Node.js APIs without running raw SQL queries.

You only need Sequelize ORM to connect to MSSQL, abstract the queries, and use JavaScript objects. This comprehensive example tutorial will teach you how to use Node.js, Sequelize ORM, and MSSQL (Microsoft SQL Server and Azure SQL) to build APIs and create Sequelize MS SQL Server database connections.

In summary, you will learn:

• How to use MS Server (Or Azure SQL) to create a Database connection example with Sequelize ORM.
• Creating a Sequelize model to Microsoft SQL Server with Node.js
• How to use Sequelize MS SQL Server connection and create a Node.js Express API example.

Ready? Dive in and elevate the right way to run MSSQL Server|Azure with Sequelize Example and Node.js.

Sequelize MS Server Preliquisites

This example tutorial will assume you have the following:

• Node.js runtime working on your local computer.
• You will need MS SQL server installed and running on your machine.

Related: Run MS SQL Server|Azure with Sequelize Example and Node.js

• Along this tutorial, you will use the same code to access Azure SQL with Sequelize. Ensure Azure is ready as well.

Related:

• How to Run Microsoft SQL Server|MSSQL Express on Docker
• Connect SQL Server Management Studio|SSMS to Docker Container

Setting up Node.js with Sequelize ORM and MS SQL Server

Now, you need a working Node.js application. Let’s create one right here before attempting to create a Sequelize MSSQL database connection. Use the following command:

# Use Node.js defaults settings
npm init -y

Node.js needs the right packages to have the right drivers ready for the SQL server. Sequelize will use the same drivers to communicate with the database. Here, you will need the following packages:

• Express framework to create a Node.js server.
• Sequelize ORM for abstracting SQL queries when accessing MSSQL.
• MSSQL to facilitate communication between your Node.js application and the MS SQL server database.
• Dotenv will add SQL server configurations as environment variables from a .env file.

If ready, use the following command to get these packages installed:

npm install express sequelize mssql dotenv

Creating MS SQL Server Database Connection with Sequelize

MSSQL utilized Sequelize to connect to Node.js. Now, you need your MSSQL connection parameter ready. You will add them in a .env file. In your working directory, create a .env file and add the following MSSQL CONNECTION parameters:

DB_USERNAME=your_username # sa as default
DB_PASSWORD=your_password
DB_DATABASE=contactsApp
DB_HOST=localhost
PORT=5000

These parameters must align based on your SQL Server. Ensure you update them as such. Also, make sure you have created a contactsApp database within your MS SQL Server.

Go ahead and create a db.js file and use Sequelize ORM to connect to SQL Server as follows:

const { Sequelize } = require('sequelize');
require('dotenv').config();
const sequelize = new Sequelize(process.env.DB_DATABASE, process.env.DB_USERNAME, process.env.DB_PASSWORD, {
  host: process.env.DB_HOST,
  dialect: 'mssql',
  dialectOptions: {
    options: {
      enableArithAbort: false,
      cryptoCredentialsDetails: {
        minVersion: 'TLSv1'
      }
    }
  }
});

module.exports = sequelize;

This connection is ready to access MSSQL. However, if you use an Azure SQL server, let Sequelize know. Therefore, you will need to add an additional option: encrypt: true in the connection factory as follows:

const sequelize = new Sequelize({
// .....Rest of the code.....
  dialectOptions: {
    // .....Rest of the code.....
    encrypt: true
  },
  
});

module.exports = sequelize;

Likewise, Sequelize will always automatically create timestamps for createdAt and updatedAt fields in your MSSQL/ Azure SQL Server. If you don’t want that to happen, add the following option:

const sequelize = new Sequelize({
// .....Rest of the code.....
  // Disable automatic timestamp fields (createdAt, updatedAt)
  define: {
    timestamps: false,
  },
});

module.exports = sequelize;

Sequelize ORM should now be ready to connect to MS SQL Server. If you are using Azure SQL Server, you have the right way to let Sequelize connect to it.

Modelling SQL Server Table with Sequelize ORM

Sequelize uses JavaScript Objects to populate your tables automatically. In this case, a model will be a blueprint for how you want Sequelize to represent your MSSQL database table.

In this example, you will create a contact app. Therefore, you will create a Contact model that Sequelize will use to set up a Contact database within SQL Server.

Create a model.js file and define the attributes of columns of your choice as follows:

const { DataTypes } = require('sequelize');
const sequelize = require('./db');

const Contact = sequelize.define('Contact', {
  firstName: {
    type: DataTypes.STRING,
  },
  lastName: {
    type: DataTypes.STRING,
  },
  email: {
    type: DataTypes.STRING,
  },
  phone: {
    type: DataTypes.STRING,
  },
});

module.exports = Contact;

Here:

• Sequelize will access sequelize in your db.js file to connect to SQL Server and create a Contact table.
• firstName, lastName, email, and phone will be the columns the Sequelize will create in the contact table.

Adding Data to MSSQL Using Sequelize and Node.js

At this point, Sequelize will have the database connection and the table ready. Now, you can use Express and Node.js to let Sequelize add data to SQL Server.

Go ahead and create a routes.js file and add a POST request using Express as follows:

const express = require('express');
const router = express.Router();
const Contact = require('./model');

router.post('/', async (req, res) => {
  try {
    const newContact = await Contact.create(req.body);
    res.status(201).json(newContact);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

Sequelize will use create() and access your table structure from model.js to add a new contact to the MSSQL Contact table.

Getting Data from SQL Server using Sequelize and Node.js

Likewise, you should be able to let Sequelize fetch items from SQL Server while using Express. Create a route to fetch and execute GET requests to run the findAll() method from Sequelize as follows:

router.get('/', async (req, res) => {
  try {
    const contacts = await Contact.findAll();
    res.status(200).json(contacts);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

Finally, remember to export this router as we need it in the next step:

module.exports = router;

Starting the Node.js Server to run Sequelize

The app is almost ready. You only need to create a server.js file and run the server as follows:

const express = require('express');
const sequelize = require('./db');
const contactRoutes = require('./routes');

const app = express();
const PORT = process.env.PORT || 5000;

// Use built-in express.json() middleware for parsing JSON
app.use(express.json()); 

app.use('/contacts', contactRoutes);

// Code inside this block is executed after synchronization is complete
sequelize.sync().then(() => {
  app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`);
  });
});

The sequelize.sync().then(() => {...}) is the most important part here. It Initiates the Sequelize synchronization process with the MS SQL server. Here, Sequelize will check the model you’ve defined and compare it with the existing tables in the database. It will generate SQL statements to create and modify tables to match the Contact model.

.then(() => {...}) promise callback is only executed when the synchronization process is complete. This means that if Sequelize synchronization fails, the code will fail to start the Express server.

Testing Sequelize MSSQL and Node.js Example API

It’s time to run the server. Use the following command:

node server.js

Check the SQL server, the Contact table should be created as such:

Error Alert ⚠️

If the above command yields an error, for example:

You will need to confirm MSSQL Server is correctly running on your computer. Use the following command to check it:

# If on Windows, use PowerShell
Test-NetConnection -ComputerName localhost -Port 1433

# Use this command if you are on Linux or macOS
nmap -p 1433 localhost

When MSSQL is running, TcpTestSucceeded should be true. But if your results show false, the SQL server is not running, and Sequelize won’t be able to access it.

To solve this error and ensure the SQL server is running:

• Go to SQL Server Configuration Manager on the Windows Start menu. Here

• Right Click to Enable TCP/IP:

• Now you need to restart the SQL Server. Make sure you run these commands as an administrator:

# Windows Command Prompt (as Administrator)
net stop MSSQLSERVER
net start MSSQLSERVER

# Linux (using systemctl)
sudo systemctl stop mssql-server
sudo systemctl start mssql-server

# macOS (using brew services)
brew services stop mssql-server
brew services start mssql-server

Rerun Test-NetConnection -ComputerName localhost -Port 1433 and this time TcpTestSucceeded should return True as such:

You can rerun your app using node server.js and check if the error is resolved.

Adding Data to MSSQL with Postman

To POST, use http://localhost:5000/contacts on Postman with your data payload. For example:

{
  "firstName": "Eleanor",
  "lastName": "Smith",
  "email": "[email protected]",
  "phone": "555-9876"
}

Use GET request to check the added items:

Your terminal should also log these queries as follows:

Conclusion

This example tutorial helped you learn:

• How to use MS Server (Or Azure SQL) to create a Database connection example with Sequelize ORM.
• Creating a Sequelize model to Microsoft SQL Server with Node.js
• How to use Sequelize MS SQL Server connection and create a Node.js Express API example.

Happy coding 🚀