Node + MSSQL - Auto Create/Update SQL Server Database with Sequelize and Tedious

Tutorial built with Node.js, Sequelize and MS SQL Server

This is a quick post to show how to automatically create and update (sync) a SQL Server database on app startup using Sequelize and Tedious.

Sequelize

Sequelize is a Node.js ORM (Object Relational Mapper) used to connect, query and manage data in a relational database. It is compatible with several databases including MS SQL Server.

Tedious

Tedious is a database client library specific to MSSQL that's used to interact with instances of SQL Server. It's the underlying connector library used by Sequelize for MSSQL. In the below example it's also used directly to automatically create a SQL Server database if the db doesn't already exist.

The below code snippet is 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 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

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.

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