Node.js + MSSQL - Connect to SQL Server with Sequelize & Tedious
Tutorial built with Node.js, Sequelize and MS SQL Server
Other versions available:
This is a quick post to show how to connect from Node.js to MS SQL Server using Sequelize & Tedious, and automatically create/update the SQL Server database from code.
The below code snippets are from a Node.js + SQL Server CRUD API tutorial I posted recently, for the full project and instructions on how to run it locally see Node.js + MS SQL Server - CRUD API Example and Tutorial.
MSSQL Server Database Wrapper
The MSSQL database wrapper connects to MSSQL using Sequelize & the tedious library. It exports an object that exposes all of the database models for the application (currently only User), and provides an easy way to access any part of the database from a single point.
Tedious is the MSSQL connector library used by Sequelize to connect to SQL Server. The DB wrapper uses tedious directly to connect to SQL Server and create the database if it doesn't exist. For more info on tedious see https://tediousjs.github.io/tedious/.
The initialize() function is executed once on API startup and performs the following actions:
- Connects to MS SQL Server using the
tediousdb client and executes a query to create the API database if it doesn't already exist. - Connects to the API database with the Sequelize ORM.
- Initializes the
Usermodel and attaches it to the exporteddbobject. - Automatically creates/updates tables in the SQL Server database to match the Sequelize model (if required) by calling
await sequelize.sync({ alter: true }). For more info on Sequelize model synchronization options see https://sequelize.org/master/manual/model-basics.html#model-synchronization.
const tedious = require('tedious');
const { Sequelize } = require('sequelize');
const { dbName, dbConfig } = require('config.json');
module.exports = db = {};
initialize();
async function initialize() {
const dialect = 'mssql';
const host = dbConfig.server;
const { userName, password } = dbConfig.authentication.options;
// create db if it doesn't already exist
await ensureDbExists(dbName);
// connect to db
const sequelize = new Sequelize(dbName, userName, password, { host, dialect });
// init models and add them to the exported db object
db.User = require('../users/user.model')(sequelize);
// sync all models with database
await sequelize.sync({ alter: true });
}
async function ensureDbExists(dbName) {
return new Promise((resolve, reject) => {
const connection = new tedious.Connection(dbConfig);
connection.connect((err) => {
if (err) {
console.error(err);
reject(`Connection Failed: ${err.message}`);
}
const createDbQuery = `IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '${dbName}') CREATE DATABASE [${dbName}];`;
const request = new tedious.Request(createDbQuery, (err) => {
if (err) {
console.error(err);
reject(`Create DB Query Failed: ${err.message}`);
}
// query executed successfully
resolve();
});
connection.execSql(request);
});
});
}
Sequelize User Model
The user model uses Sequelize to define the schema for the users table in the SQL Server database. The exported Sequelize model object gives full access to perform CRUD (create, read, update, delete) operations on users in MSSQL, see the user service below for examples of it being used (via the db helper).
The defaultScope configures the model to exclude the password hash from query results by default. The withHash scope can be used to query users and include the password hash in results. For more info on Sequelize scopes see https://sequelize.org/master/manual/scopes.html.
const { DataTypes } = require('sequelize');
module.exports = model;
function model(sequelize) {
const attributes = {
email: { type: DataTypes.STRING, allowNull: false },
passwordHash: { type: DataTypes.STRING, allowNull: false },
title: { type: DataTypes.STRING, allowNull: false },
firstName: { type: DataTypes.STRING, allowNull: false },
lastName: { type: DataTypes.STRING, allowNull: false },
role: { type: DataTypes.STRING, allowNull: false }
};
const options = {
defaultScope: {
// exclude password hash by default
attributes: { exclude: ['passwordHash'] }
},
scopes: {
// include hash with this scope
withHash: { attributes: {}, }
}
};
return sequelize.define('User', attributes, options);
}
User Service
The user service is responsible for all database interaction and core business logic related to user CRUD operations, it encapsulates all interaction with the Sequelize user model and exposes a simple set of methods to access and manage the data in SQL Server.
The top of the file contains the exported service object with just the method names to make it easy to see all the methods at a glance, the rest of the file contains the implementation functions for each service method, followed by local helper functions.
const bcrypt = require('bcryptjs');
const db = require('_helpers/db');
module.exports = {
getAll,
getById,
create,
update,
delete: _delete
};
async function getAll() {
return await db.User.findAll();
}
async function getById(id) {
return await getUser(id);
}
async function create(params) {
// validate
if (await db.User.findOne({ where: { email: params.email } })) {
throw 'Email "' + params.email + '" is already registered';
}
const user = new db.User(params);
// hash password
user.passwordHash = await bcrypt.hash(params.password, 10);
// save user
await user.save();
}
async function update(id, params) {
const user = await getUser(id);
// validate
const usernameChanged = params.username && user.username !== params.username;
if (usernameChanged && await db.User.findOne({ where: { username: params.username } })) {
throw 'Username "' + params.username + '" is already taken';
}
// hash password if it was entered
if (params.password) {
params.passwordHash = await bcrypt.hash(params.password, 10);
}
// copy params to user and save
Object.assign(user, params);
await user.save();
}
async function _delete(id) {
const user = await getUser(id);
await user.destroy();
}
// helper functions
async function getUser(id) {
const user = await db.User.findByPk(id);
if (!user) throw 'User not found';
return user;
}
Need Some NodeJS Help?
Search fiverr for freelance NodeJS developers.
Follow me for updates
When I'm not coding...
Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!