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 exporteddb
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);
}
Need Some MSSQL Help?
Search fiverr for freelance MSSQL 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!