Node.js + MS SQL Server - Simple API for Authentication, Registration and User Management

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

Other versions available:

• Node: Node + MySQL, Node + MongoDB
• .NET: .NET 6.0, 5.0, ASP.NET Core 3.1, 2.2

In this tutorial we'll cover how to build a Node.js API that stores data in Microsoft SQL Server and supports the following features: user registration, login with JWT authentication, user management/CRUD operations. The example API uses Sequelize and Tedious to connect to SQL Server.

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. Sequelize also supports model synchronization to automatically generate database tables and columns based on models defined in code (e.g. the user model).

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. It's also used directly in the MSSQL database wrapper to automatically create a SQL Server database if the db doesn't already exist.

Connect API with React, Angular or Vue

The Node.js API can be easily connected to an example front end app built with React, Angular or Vue. See below for instructions.

Code on GitHub

The project is available on GitHub at https://github.com/cornflourblue/node-mssql-registration-login-api.

Node + SQL Server Tutorial Contents

• Tools required for this tutorial
• Run the Node + MSSQL API locally
• Test the API with Postman
• Connect a React App with the Node.js API
• Connect an Angular App with the Node.js API
• Connect a Vue App with the Node.js API
• Node + MSSQL API Project Structure

Tools required for this tutorial

To follow the steps in this tutorial you'll need the following:

• Node.js & npm - includes the Node.js runtime, command line tools and package manager, install it from https://nodejs.org/.
• MS SQL Server - you'll need access to running SQL Server instance for the API to connect to, it can be remote (e.g. Azure, AWS etc) or on your local machine. The Express edition of SQL Server is available for free at https://www.microsoft.com/sql-server/sql-server-downloads. You can also run it in a Docker container, the official docker images for SQL Server are available at https://hub.docker.com/_/microsoft-mssql-server.
• A code editor to view and edit the API code, it doesn't matter which one, personally I use Visual Studio Code which is a free editor that runs on Windows, Mac and Linux, you can download it at https://code.visualstudio.com/.

Run the Node.js + MSSQL API Locally

1. Download or clone the project source code from https://github.com/cornflourblue/node-mssql-registration-login-api
2. Install all required npm packages by running npm install or npm i from the command line in the project root folder (where the package.json is located).
3. Update the database credentials in /config.json to connect to your MS SQL Server instance, and ensure MSSQL server is running.
4. Start the API by running npm start (or npm run dev to start with nodemon) from the command line in the project root folder, you should see the message Server listening on port 4000.
5. Follow the instructions below to test with Postman or connect the Node API with one of the example single page applications available (React, Angular or Vue).

Before running in production

Before running in production also make sure that you update the secret property in the config.json file, it is used to sign and verify JWT tokens for authentication. Change it to a random string to ensure nobody else can generate a JWT with the same secret and gain unauthorized access to your api. A quick and easy way is join a couple of GUIDs together to make a long random string (e.g. from https://www.guidgenerator.com/).

Sequelize model synchronization

On startup you should also see output like below from the Sequelize model synchronization that is executed in the MSSQL database wrapper, it shows the creation and syncing of the Users table based on the Sequelize user model.

Executing (default): SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = N'Users' AND TABLE_SCHEMA = N'dbo'
Executing (default): SELECT c.COLUMN_NAME AS 'Name', c.DATA_TYPE AS 'Type', c.CHARACTER_MAXIMUM_LENGTH AS 'Length', c.IS_NULLABLE as 'IsNull', COLUMN_DEFAULT AS 'Default', pk.CONSTRAINT_TYPE AS 'Constraint', COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') as 'IsIdentity', CAST(prop.value AS NVARCHAR) AS 'Comment' FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA LEFT JOIN (SELECT tc.table_schema, tc.table_name,  cu.column_name, tc.CONSTRAINT_TYPE  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  cu  ON tc.table_schema=cu.table_schema and tc.table_name=cu.table_name  and tc.constraint_name=cu.constraint_name  and tc.CONSTRAINT_TYPE='PRIMARY KEY') pk  ON pk.table_schema=c.table_schema  AND pk.table_name=c.table_name  AND pk.column_name=c.column_name  INNER JOIN sys.columns AS sc ON sc.object_id = object_id(t.table_schema + '.' + t.table_name) AND sc.name = c.column_name LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id AND prop.minor_id = sc.column_id AND prop.name = 'MS_Description' WHERE t.TABLE_NAME = 'Users'
Executing (default): SELECT constraint_name = OBJ.NAME, constraintName = OBJ.NAME, constraintCatalog = 'node-mssql-registration-login-api', constraintSchema = SCHEMA_NAME(OBJ.SCHEMA_ID), tableName = TB.NAME, tableSchema = SCHEMA_NAME(TB.SCHEMA_ID), tableCatalog = 'node-mssql-registration-login-api', columnName = COL.NAME, referencedTableSchema = SCHEMA_NAME(RTB.SCHEMA_ID), referencedCatalog = 'node-mssql-registration-login-api', referencedTableName = RTB.NAME, referencedColumnName = RCOL.NAME FROM sys.foreign_key_columns FKC INNER JOIN sys.objects OBJ ON OBJ.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN sys.tables TB ON TB.OBJECT_ID = FKC.PARENT_OBJECT_ID INNER JOIN sys.columns COL ON COL.COLUMN_ID = PARENT_COLUMN_ID AND COL.OBJECT_ID = TB.OBJECT_ID INNER JOIN sys.tables RTB ON RTB.OBJECT_ID = FKC.REFERENCED_OBJECT_ID INNER JOIN sys.columns RCOL ON RCOL.COLUMN_ID = REFERENCED_COLUMN_ID AND RCOL.OBJECT_ID = RTB.OBJECT_ID WHERE TB.NAME ='Users'
Executing (default): ALTER TABLE [Users] ALTER COLUMN [firstName] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [lastName] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [username] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [hash] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [createdAt] DATETIMEOFFSET NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [updatedAt] DATETIMEOFFSET NOT NULL;
Executing (default): EXEC sys.sp_helpindex @objname = N'[Users]';

Test the Node + SQL Server Auth API with Postman

Postman is a great tool for testing APIs, you can download it at https://www.postman.com/downloads.

Below are instructions on how to use Postman to register a new user with the api, authenticate a user to get a JWT token, and then make an authenticated request with the JWT token to retrieve a list of users from the api.

How to register a new user with Postman

To register a new user with the api follow these steps:

1. Open a new request tab by clicking the plus (+) button at the end of the tabs.
2. Change the HTTP method to POST with the dropdown selector on the left of the URL input field.
3. In the URL field enter the address to the users route of your local API - http://localhost:4000/users/register
4. Select the Body tab below the URL field, change the body type radio button to raw, and change the format dropdown selector to JSON.
5. Enter a JSON object containing the required user properties in the Body textarea, e.g:

   {
       "firstName": "Jason",
       "lastName": "Watmore",
       "username": "jason",
       "password": "my-super-secret-password"
   }

6. Click the Send button, you should receive a "200 OK" response with a success message in the response body.

Here's a screenshot of Postman after the request is sent and the new user has been registered:

How to authenticate a user with Postman

To authenticate a user with the api and get a JWT token follow these steps:

1. Open a new request tab by clicking the plus (+) button at the end of the tabs.
2. Change the HTTP method to POST with the dropdown selector on the left of the URL input field.
3. In the URL field enter the address to the users route of your local API - http://localhost:4000/users/authenticate
4. Select the Body tab below the URL field, change the body type radio button to raw, and change the format dropdown selector to JSON.
5. Enter a JSON object containing the username and password in the Body textarea, e.g:

   {
       "username": "jason",
       "password": "my-super-secret-password"
   }

6. Click the Send button, you should receive a "200 OK" response with the user details including a JWT token in the response body, make a copy of the token value because we'll be using it in the next step to make an authenticated request.

Here's a screenshot of Postman after the request is sent and the user has been authenticated:

How to make an authenticated request to retrieve all users

To make an authenticated request using the JWT token from the previous step, follow these steps:

1. Open a new request tab by clicking the plus (+) button at the end of the tabs.
2. Change the HTTP method to GET with the dropdown selector on the left of the URL input field.
3. In the URL field enter the address to the users route of your local API - http://localhost:4000/users
4. Select the Authorization tab below the URL field, change the type to Bearer Token in the type dropdown selector, and paste the JWT token from the previous authenticate step into the Token field.
5. Click the Send button, you should receive a "200 OK" response containing a JSON array with all the user records in the system.

Here's a screenshot of Postman after making an authenticated request to get all users:

How to update a user with Postman

To update a user with the api follow these steps:

1. Open a new request tab by clicking the plus (+) button at the end of the tabs.
2. Change the HTTP method to PUT with the dropdown selector on the left of the URL input field.
3. In the URL field enter the address to the users route of your local API - http://localhost:4000/users/1
4. Select the Authorization tab below the URL field, change the type to Bearer Token in the type dropdown selector, and paste the JWT token from the previous authenticate step into the Token field.
5. Select the Body tab below the URL field, change the body type radio button to raw, and change the format dropdown selector to JSON.
6. Enter a JSON object in the Body textarea containing the properties you want to update, for example to update the first and last names:

   {
       "firstName": "Foo",
       "lastName": "Bar"
   }

7. Click the Send button, you should receive a "200 OK" response with the updated user details in the response body.

Here's a screenshot of Postman after the request is sent and the user has been updated:

Connect a React App with the Node.js API

For full details about the example React application see the post React Hooks + Redux - User Registration and Login Tutorial & Example. But to get up and running quickly just follow the below steps.

1. Download or clone the React tutorial code from https://github.com/cornflourblue/react-hooks-redux-registration-login-example
2. Install all required npm packages by running npm install from the command line in the project root folder (where the package.json is located).
3. Remove or comment out the 2 lines below the comment // setup fake backend located in the /src/index.jsx file.
4. Start the application by running npm start from the command line in the project root folder, this will launch a browser displaying the application and it should be hooked up with the Node.js + MSSQL API that you already have running.

Connect an Angular App with the Node.js API

For full details about the example Angular application see the post Angular 10 - User Registration and Login Example & Tutorial. But to get up and running quickly just follow the below steps.

1. Download or clone the Angular 10 tutorial code from https://github.com/cornflourblue/angular-10-registration-login-example
2. Install all required npm packages by running npm install from the command line in the project root folder (where the package.json is located).
3. Remove or comment out the line below the comment // provider used to create fake backend located in the /src/app/app.module.ts file.
4. Start the application by running npm start from the command line in the project root folder, this will launch a browser displaying the application and it should be hooked up with the Node.js + MSSQL API that you already have running.

Connect a Vue App with the Node.js API

For full details about the example Vue application see the post Vue + Vuex - User Registration and Login Tutorial & Example. But to get up and running quickly just follow the below steps.

1. Download or clone the Vue tutorial code from https://github.com/cornflourblue/vue-vuex-registration-login-example
2. Install all required npm packages by running npm install from the command line in the project root folder (where the package.json is located).
3. Remove or comment out the 2 lines below the comment // setup fake backend located in the /src/index.js file.
4. Start the application by running npm start from the command line in the project root folder, this will launch a browser displaying the application and it should be hooked up with the Node.js + SQL Server API that you already have running.

Node + MS SQL Server API Project Structure

The tutorial project is structured into feature folders (users) and non-feature / shared component folders (_helpers, _middleware). Shared component folders contain code that can be used across multiple features or other parts of the application, and are prefixed with an underscore _ to group them together and make it easy to differentiate between feature specific and shared code.

The auth example only contains a single (users) feature at the moment, but can be easily extended with other features by copying the users folder and following the same pattern.

Click any of the below links to jump down to a description of each file along with its code:

• _helpers
  • db.js
• _middleware
  • authorize.js
  • error-handler.js
  • validate-request.js
• users
  • user.model.js
  • user.service.js
  • users.controller.js
• config.json
• package.json
• server.js

Helpers Folder

The helpers folder contains all the bits and pieces that don't fit into other folders but don't justify having a folder of their own.

MSSQL Server Database Wrapper

The MSSQL database wrapper connects to MSSQL using Sequelize & the tedious client, and exports an object that exposes all of the database models for the application (currently only User). It provides an easy way to access any part of the database from a single point.

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

Express.js Middleware Folder

The middleware folder contains Express.js middleware functions that can be used by different routes / features within the application.

Authorize Middleware

The authorize middleware can be added to any route to restrict access to the route to authenticated users. It is used by the users controller to restrict access to user CRUD routes.

The authorize function returns an array containing two middleware functions:

• The first (jwt({ ... })) authenticates the request by validating the JWT access token in the "Authorization" header of the http request. On successful authentication a user object is attached to the req object that contains the data from the JWT token, which in this example includes the user id in the token subject ('sub') property.
• The second authorizes the request by checking that the authenticated user still exists, and attaches the user object to the request so it can be accessed by controller functions.

If either authentication or authorization fails then a 401 Unauthorized response is returned.

const jwt = require('express-jwt');

const { secret } = require('config.json');
const db = require('_helpers/db');

module.exports = authorize;

function authorize() {
    return [
        // authenticate JWT token and attach decoded token to request as req.user
        jwt({ secret, algorithms: ['HS256'] }),

        // attach full user record to request object
        async (req, res, next) => {
            // get user with id from token 'sub' (subject) property
            const user = await db.User.findByPk(req.user.sub);

            // check user still exists
            if (!user)
                return res.status(401).json({ message: 'Unauthorized' });

            // authorization successful
            req.user = user.get();
            next();
        }
    ];
}

Global Error Handler Middleware

The global error handler is used catch all errors and remove the need for duplicated error handling code throughout the application. It's configured as middleware in the main server.js file.

By convention errors of type 'string' are treated as custom (app specific) errors, this simplifies the code for throwing custom errors since only a string needs to be thrown (e.g. throw 'Invalid token'). Further to this if a custom error ends with the words 'not found' a 404 response code is returned, otherwise a standard 400 response is returned. See the user service for some examples of custom errors thrown by the api, errors are caught in the users controller for each route and passed to next(err) which passes them to this global error handler.

module.exports = errorHandler;

function errorHandler(err, req, res, next) {
    switch (true) {
        case typeof err === 'string':
            // custom application error
            const is404 = err.toLowerCase().endsWith('not found');
            const statusCode = is404 ? 404 : 400;
            return res.status(statusCode).json({ message: err });
        case err.name === 'UnauthorizedError':
            // jwt authentication error
            return res.status(401).json({ message: 'Unauthorized' });
        default:
            return res.status(500).json({ message: err.message });
    }
}

Validate Request Middleware

The validate request middleware function validates the body of a request against a Joi schema object.

It used by schema middleware functions in controllers to validate the request against the schema for a specific route (e.g. authenticateSchema in the users controller).

module.exports = validateRequest;

function validateRequest(req, next, schema) {
    const options = {
        abortEarly: false, // include all errors
        allowUnknown: true, // ignore unknown props
        stripUnknown: true // remove unknown props
    };
    const { error, value } = schema.validate(req.body, options);
    if (error) {
        next(`Validation error: ${error.details.map(x => x.message).join(', ')}`);
    } else {
        req.body = value;
        next();
    }
}

Users Folder

The users folder contains all code that is specific to the users feature of the api.

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 field in results.

const { DataTypes } = require('sequelize');

module.exports = model;

function model(sequelize) {
    const attributes = {
        firstName: { type: DataTypes.STRING, allowNull: false },
        lastName: { type: DataTypes.STRING, allowNull: false },
        username: { type: DataTypes.STRING, allowNull: false },
        hash: { type: DataTypes.STRING, allowNull: false }
    };

    const options = {
        defaultScope: {
            // exclude hash by default
            attributes: { exclude: ['hash'] }
        },
        scopes: {
            // include hash with this scope
            withHash: { attributes: {}, }
        }
    };

    return sequelize.define('User', attributes, options);
}

User Service

The user service contains the core business logic for user authentication and management in the node api, it encapsulates all interaction with the sequelize user model and exposes a simple set of methods which are used by the users controller.

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 jwt = require('jsonwebtoken');
const bcrypt = require('bcryptjs');

const { secret } = require('config.json');
const db = require('_helpers/db');

module.exports = {
    authenticate,
    getAll,
    getById,
    create,
    update,
    delete: _delete
};

async function authenticate({ username, password }) {
    const user = await db.User.scope('withHash').findOne({ where: { username } });

    if (!user || !(await bcrypt.compare(password, user.hash)))
        throw 'Username or password is incorrect';

    // authentication successful
    const token = jwt.sign({ sub: user.id }, secret, { expiresIn: '7d' });
    return { ...omitHash(user.get()), token };
}

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: { username: params.username } })) {
        throw 'Username "' + params.username + '" is already taken';
    }

    // hash password
    if (params.password) {
        params.hash = await bcrypt.hash(params.password, 10);
    }

    // save user
    await db.User.create(params);
}

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.hash = await bcrypt.hash(params.password, 10);
    }

    // copy params to user and save
    Object.assign(user, params);
    await user.save();

    return omitHash(user.get());
}

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

function omitHash(user) {
    const { hash, ...userWithoutHash } = user;
    return userWithoutHash;
}

Express.js Users Controller

The users controller defines all /users routes for the api, the route definitions are grouped together at the top of the file and the implementation functions are below. The controller is bound to the /users path in the main server.js file.

Routes that require authorization include the middleware function authorize(), the auth logic is located in the authorize middleware.

Routes that require schema validation include a middleware function with the naming convention <route>Schema (e.g. authenticateSchema). Each schema validation function defines a schema for the request body using the Joi library and calls validateRequest(req, next, schema) to ensure the request body is valid. If validation succeeds the request continues to the next middleware function (the route function), otherwise an error is returned with details of why validation failed. For more info about Joi schema validation see https://www.npmjs.com/package/joi.

Express is the web server used by the api, it's one of the most popular web application frameworks for Node.js. For more info see https://expressjs.com/.

const express = require('express');
const router = express.Router();
const Joi = require('joi');

const validateRequest = require('_middleware/validate-request');
const authorize = require('_middleware/authorize')
const userService = require('./user.service');

// routes
router.post('/authenticate', authenticateSchema, authenticate);
router.post('/register', registerSchema, register);
router.get('/', authorize(), getAll);
router.get('/current', authorize(), getCurrent);
router.get('/:id', authorize(), getById);
router.put('/:id', authorize(), updateSchema, update);
router.delete('/:id', authorize(), _delete);

module.exports = router;

function authenticateSchema(req, res, next) {
    const schema = Joi.object({
        username: Joi.string().required(),
        password: Joi.string().required()
    });
    validateRequest(req, next, schema);
}

function authenticate(req, res, next) {
    userService.authenticate(req.body)
        .then(user => res.json(user))
        .catch(next);
}

function registerSchema(req, res, next) {
    const schema = Joi.object({
        firstName: Joi.string().required(),
        lastName: Joi.string().required(),
        username: Joi.string().required(),
        password: Joi.string().min(6).required()
    });
    validateRequest(req, next, schema);
}

function register(req, res, next) {
    userService.create(req.body)
        .then(() => res.json({ message: 'Registration successful' }))
        .catch(next);
}

function getAll(req, res, next) {
    userService.getAll()
        .then(users => res.json(users))
        .catch(next);
}

function getCurrent(req, res, next) {
    res.json(req.user);
}

function getById(req, res, next) {
    userService.getById(req.params.id)
        .then(user => res.json(user))
        .catch(next);
}

function updateSchema(req, res, next) {
    const schema = Joi.object({
        firstName: Joi.string().empty(''),
        lastName: Joi.string().empty(''),
        username: Joi.string().empty(''),
        password: Joi.string().min(6).empty('')
    });
    validateRequest(req, next, schema);
}

function update(req, res, next) {
    userService.update(req.params.id, req.body)
        .then(user => res.json(user))
        .catch(next);
}

function _delete(req, res, next) {
    userService.delete(req.params.id)
        .then(() => res.json({ message: 'User deleted successfully' }))
        .catch(next);
}

Api Config

The API config file contains configuration data for the auth API, it includes dbConfig connection options for MSSQL Server. The dbName property is the name of the database that is automatically created by the API on startup in the database wrapper. And the secret used for signing and verifying JWT tokens.

IMPORTANT: The secret property is used to sign and verify JWT tokens for authentication, change it with your own random string to ensure nobody else can generate a JWT with the same secret to gain unauthorized access to your api. A quick and easy way is join a couple of GUIDs together to make a long random string (e.g. from https://www.guidgenerator.com/).

{
    "dbName": "node-mssql-registration-login-api",
    "dbConfig": {
        "server": "localhost",
        "options": {
            "port": 1433,
            "trustServerCertificate": true
        },
        "authentication": {
            "type": "default",
            "options": {
                "userName": "sa",
                "password": ""
            }
        }
    },
    "secret": "THIS IS USED TO SIGN AND VERIFY JWT TOKENS, REPLACE IT WITH YOUR OWN SECRET, IT CAN BE ANY STRING"
}

Package.json

The package.json file contains project configuration information including package dependencies which get installed when you run npm install.

The scripts section contains scripts that are executed by running the command npm run <script name>, the start script can also be run with the shortcut command npm start.

The start script starts the api normally using node, and the start:dev script starts the api in development mode using nodemon which automatically restarts the server when a file is changed (hot reloading).

For more info see https://docs.npmjs.com/files/package.json.

{
    "name": "node-mssql-registration-login-api",
    "version": "1.0.0",
    "description": "Node.js + MS SQL Server - Simple API for Authentication, Registration and User Management",
    "license": "MIT",
    "repository": {
        "type": "git",
        "url": "https://github.com/cornflourblue/node-mssql-registration-login-api.git"
    },
    "scripts": {
        "start": "node ./server.js",
        "dev": "nodemon ./server.js"
    },
    "dependencies": {
        "bcryptjs": "^2.4.3",
        "cors": "^2.8.5",
        "express": "^4.18.1",
        "express-jwt": "^6.0.0",
        "joi": "^17.6.0",
        "jsonwebtoken": "^8.5.1",
        "rootpath": "^0.1.2",
        "sequelize": "^6.21.0",
        "tedious": "^14.6.0"
    },
    "devDependencies": {
        "nodemon": "^2.0.16"
    }
}

Server Startup File

The server.js file is the entry point into the Node + MS SQL Server API, it configures application middleware, binds controllers to routes and starts the Express web server for the API.

require('rootpath')();
const express = require('express');
const app = express();
const cors = require('cors');

const errorHandler = require('_middleware/error-handler');

app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(cors());

// api routes
app.use('/users', require('./users/users.controller'));

// global error handler
app.use(errorHandler);

// start server
const port = process.env.NODE_ENV === 'production' ? (process.env.PORT || 80) : 4000;
app.listen(port, () => console.log('Server listening on port ' + port));