Jason Watmore's Blog A Web Developer in Sydney
Published: June 18 2022

Node.js + MS SQL Server - CRUD API Example and Tutorial

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

Other versions available:

In this tutorial we'll cover how to build a simple Node.js API that supports CRUD operations and stores data in a Microsoft SQL Server database. The example API includes routes to retrieve, update, create and delete records in a MSSQL database, the records in the example are user records but the same CRUD pattern and code structure could be applied to any type of data e.g. products, services, articles etc.

MSSQL Code First DB Generation with Sequelize

The Node.js API automatically creates the SQL Server database on startup (if required) and synchronizes db tables and columns with javascript models defined using the Sequelize ORM library. Database creation and model synchronization is performed by the initialize() function in the MSSQL database wrapper.

Code on GitHub

The CRUD API project is available on GitHub at https://github.com/cornflourblue/node-mssql-crud-api.


Tutorial Contents


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 + MSSQL CRUD API Locally

  1. Download or clone the project source code from https://github.com/cornflourblue/node-mssql-crud-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 hook up with one of the example single page applications available (React or Angular).

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-crud-api', constraintSchema = SCHEMA_NAME(OBJ.SCHEMA_ID), tableName = TB.NAME, tableSchema = SCHEMA_NAME(TB.SCHEMA_ID), tableCatalog = 'node-mssql-crud-api', columnName = COL.NAME, referencedTableSchema = SCHEMA_NAME(RTB.SCHEMA_ID), referencedCatalog = 'node-mssql-crud-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 [email] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [passwordHash] NVARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE [Users] ALTER COLUMN [title] NVARCHAR(255) NOT NULL;
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 [role] 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 CRUD 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 perform the following actions:


How to create a new user with Postman

To create a new user with the CRUD 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
  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: 
    {
    "title": "Mr",
    "firstName": "George",
    "lastName": "Costanza",
    "role": "User",
    "email": "[email protected]",
    "password": "george-likes-spicy-chicken",
    "confirmPassword": "george-likes-spicy-chicken"
}
  6. Click the Send button, you should receive a "200 OK" response with the message "User created" in the response body.

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

Back to top


How to retrieve a list of all users with Postman

To get a list of all users from the Node + MSSQL CRUD 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 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. Click the Send button, you should receive a "200 OK" response containing a JSON array with all of the user records in the system.

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

Back to top


How to retrieve a user by id with Postman

To get a specific user by id from the Node + MSSQL CRUD 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 GET with the dropdown selector on the left of the URL input field.
  3. In the URL field enter the address to the /users/{id} route with the id of the user you want to retrieve, e.g - http://localhost:4000/users/1
  4. Click the Send button, you should receive a "200 OK" response containing a JSON object with the specified user details.

Here's a screenshot of Postman after making a request to get a user by id:

Back to top


How to update a user with Postman

To update a user with the CRUD 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/{id} route with the id of the user you want to update, e.g - http://localhost:4000/users/1
  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 in the Body textarea containing the properties you want to update, for example to update the first and last names: 
    {
    "firstName": "Art",
    "lastName": "Vandelay"
}
  6. Click the Send button, you should receive a "200 OK" response with the message "User updated" in the response body.

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

Back to top


How to delete a user with Postman

To delete 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 DELETE with the dropdown selector on the left of the URL input field.
  3. In the URL field enter the address to the /users/{id} route with the id of the user you want to delete, e.g - http://localhost:4000/users/1
  4. Click the Send button, you should receive a "200 OK" response with the message "User deleted" in the response body.

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

Back to top


Run a React App with the Node + MSSQL CRUD API

For full details about the React CRUD app see the post React - CRUD Example with React Hook Form. 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-hook-form-crud-example
  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. 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 + MS SQL Server CRUD API that you already have running.


Run an Angular App with the Node + SQL Server CRUD API

For full details about the Angular CRUD app see the post Angular 11 - CRUD Example with Reactive Forms. But to get up and running quickly just follow the below steps.

  1. Download or clone the Angular tutorial code from https://github.com/cornflourblue/angular-11-crud-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 CRUD API that you already have running.


Node.js + MSSQL CRUD 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 CRUD example only contains a single feature (users) at the moment, but could 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 Folder

Path: /_helpers

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

Path: /_helpers/db.js

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

Role Object / Enum

Path: /_helpers/role.js

The role object defines the all the roles in the example application, I created it to use like an enum to avoid passing roles around as strings, so instead of 'Admin' and 'User' we can use Role.Admin and Role.User.

module.exports = {
    Admin: 'Admin',
    User: 'User'
}
 

Express.js Middleware Folder

Path: /_middleware

The middleware folder contains Express.js middleware functions that can be used by routes / features within the Node.js CRUD API.

 

Global Error Handler Middleware

Path: /_middleware/error-handler.js

The global error handler is used catch all errors and remove the need for duplicated error handling code throughout the CRUD 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 'Error message'). 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 the next function 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 });
        default:
            return res.status(500).json({ message: err.message });
    }
}
 

Validate Request Middleware

Path: /_middleware/validate-request.js

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

It is used by schema middleware functions in controllers to validate requests with a schema for a specific route (e.g. createSchema for the create user route in the users controller). For more info about Joi schema validation see https://www.npmjs.com/package/joi.

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 Feature Folder

Path: /users

The users folder contains all code that is specific to the users feature of the Node.js + MSSQL CRUD API.

 

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

Express.js Users Controller

Path: /users/users.controller.js

The users controller defines all /users routes for the Node.js + MSSQL CRUD API, the route definitions are grouped together at the top of the file and the implementation functions are below, followed by route schema validation functions. The controller is bound to the /users path in the main server.js file.

Routes that require schema validation include a middleware function with the naming convention <route>Schema (e.g. createSchema). 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 CRUD 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 Role = require('_helpers/role');
const userService = require('./user.service');

// routes

router.get('/', getAll);
router.get('/:id', getById);
router.post('/', createSchema, create);
router.put('/:id', updateSchema, update);
router.delete('/:id', _delete);

module.exports = router;

// route functions

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

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

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

function update(req, res, next) {
    userService.update(req.params.id, req.body)
        .then(() => res.json({ message: 'User updated' }))
        .catch(next);
}

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

// schema functions

function createSchema(req, res, next) {
    const schema = Joi.object({
        title: Joi.string().required(),
        firstName: Joi.string().required(),
        lastName: Joi.string().required(),
        role: Joi.string().valid(Role.Admin, Role.User).required(),
        email: Joi.string().email().required(),
        password: Joi.string().min(6).required(),
        confirmPassword: Joi.string().valid(Joi.ref('password')).required()
    });
    validateRequest(req, next, schema);
}

function updateSchema(req, res, next) {
    const schema = Joi.object({
        title: Joi.string().empty(''),
        firstName: Joi.string().empty(''),
        lastName: Joi.string().empty(''),
        role: Joi.string().valid(Role.Admin, Role.User).empty(''),
        email: Joi.string().email().empty(''),
        password: Joi.string().min(6).empty(''),
        confirmPassword: Joi.string().valid(Joi.ref('password')).empty('')
    }).with('password', 'confirmPassword');
    validateRequest(req, next, schema);
}
 

Api Config

Path: /config.json

The API config file contains configuration data for the CRUD API, it includes the dbConfig connection options for the MSSQL Server. The dbName property is the name of the database that is automatically created by the API on startup in the database wrapper.

{
    "dbName": "node-mssql-crud-api",
    "dbConfig": {
        "server": "localhost",
        "options": {
            "port": 1433,
            "trustServerCertificate": true
        },
        "authentication": {
            "type": "default",
            "options": {
                "userName": "sa",
                "password": ""
            }
        }
    }
}
 

Package.json

Path: /package.json

The package.json file contains project configuration information including Node.js package dependencies that 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 CRUD API normally using node, and the start:dev script starts the API in development mode using nodemon which automatically restarts the Node.js API when a file is changed.

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

{
    "name": "node-mssql-crud-api",
    "version": "1.0.0",
    "license": "MIT",
    "scripts": {
        "start": "node ./server.js",
        "dev": "nodemon ./server.js"
    },
    "dependencies": {
        "bcryptjs": "^2.4.3",
        "cors": "^2.8.5",
        "express": "^4.18.1",
        "joi": "^17.6.0",
        "rootpath": "^0.1.2",
        "sequelize": "^6.21.0",
        "tedious": "^14.6.0"
    },
    "devDependencies": {
        "nodemon": "^2.0.16"
    }
}
 

Server Startup File

Path: /server.js

The server.js file is the entry point to the Node.js CRUD 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));

 


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!


More NodeJS Posts

Show more...


Comments

Supported by
Powered by DigitalOcean - Get $200 Free Credit!
© 2024 JasonWatmore.com
Privacy Policy