Published: June 20 2022

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

Path: /_helpers/db.js

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 tedious db 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 User model and attaches it to the exported db object.
  • 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

Path: /users/user.model.js

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

Path: /users/user.service.js

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

On Twitter or RSS.


When I'm not coding...

Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!


Comments


Supported by