Data Access Layer for NodeJS and MSSQL - Part 2

By keeping a serious face let us start using our data access layer; if not already read, please check out this post Data Access Layer for NodeJS and MSSQL - Part 1. We will first tackle the basic CRUD operations, followed by complex stored procedure calls. Don't worry, we got this!

We will take our inspiration from these posts:

1. For CRUD - Teaming up with NodeJS and SQL Server
2. For Stored Procedures - Calling Stored Procedure with NodeJS and MSSQL

The initial setup is already covered in the above-listed CRUD post and we are dealing with the same old employee records. We will directly skip to the good part (using the data access layer).

Glorious CRUD Operations

Some guy, some where, on different planet also dealing with the basic CRUD operations.

Read All Employees

router.get('/', async (req, res) => {
    try {
        const result = await dataAccess.query(`SELECT * FROM Employee ORDER BY Id DESC`);
        const employees = result.recordset;

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, the data access query function returns the result object that contains recordsets, recordset, output and rowsAffected properties; the recordset in the above case returns the employee records.

Read Single Employee

router.get('/:id', async (req, res) => {
    try {
        const result = await dataAccess.query(`SELECT * FROM Employee WHERE Id = @Id`, [
            { name: 'Id', value: req.params.id }
        ]);
        const employee = result.recordset.length ? result.recordset[0] : null;

        if (employee) {
            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, the data access query function accepts query and input parameter array. The query returns employee object based on employee id.

Create Employee

router.post('/', async (req, res) => {
    try {
        const result = await DataAccess.query(`
                INSERT INTO Employee (Code, Salary, Job, Department, Name) 
                OUTPUT inserted.Id 
                VALUES (@Code, @Salary, @Job, @Department, @Name);
            `, [
                { name: 'Code', value: req.body.Code },
                { name: 'Salary', value: req.body.Salary },
                { name: 'Job', value: req.body.Job },
                { name: 'Department', value: req.body.Department },
                { name: 'Name', value: req.body.Name },
            ]
        );
        const employee = req.body;
        employee.Id = result.recordset[0].Id;

        res.json(employee);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, the query function will create an employee record in the database.

There is even a better way than the above to deal with insert operation, by which we can directly pass the object that will map the properties to column and create query accordingly with input parameters; but the property name needs to be same as column name. For this we have queryEntity function, checkout below code:

const result = await dataAccess.queryEntity(`
		INSERT INTO Employee (Code, Salary, Job, Department, Name) 
		OUTPUT inserted.Id 
		VALUES (@Code, @Salary, @Job, @Department, @Name);
	`, req.body
);

Update Employee

router.put('/:id', async (req, res) => {
    try {
        if (+req.params.id !== req.body.Id) {
            res.status(400).json({
                message: 'Mismatched identity'
            });
            return;
        }

        const result = await dataAccess.query(`SELECT * FROM Employee WHERE Id = @Id`, [
            { name: 'Id', value: req.params.id }
        ]);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await dataAccess.queryEntity(`
                    UPDATE Employee SET
                        Code = @Code, 
                        Salary = @Salary, 
                        Job = @Job, 
                        Department = @Department, 
                        Name = @Name
                    WHERE Id = @Id;
                `, req.body
            );

            employee = { ...employee, ...req.body };

            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, the data access queryEntity function accepts query and input object. The queryEntity function will do the mapping based on the supplied employee object.

Delete Employee

router.delete('/:id', async (req, res) => {
    try {
        const result = await dataAccess.query(`SELECT * FROM Employee WHERE Id = @Id`, [
            { name: 'Id', value: req.params.id }
        ]);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await dataAccess.query(`DELETE FROM Employee WHERE Id = @Id;`, [
                { name: 'Id', value: req.params.id }
            ]);
            res.json({});
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, we are first checking if the employee record exists based on employee id. For a delete operation, we are supplying employee id as an input parameter to perform a delete query.

Working with Stored Procedures

You develop, you evolve, you start using stored procedure. Stored procedures are a vital part of any healthy project out there, let us see how our data access layer handles the stored procedures common operations. Here, we will gonna take inspiration from the previously mentioned stored procedure post.

Dealing with Input Parameters

Stored procedures accepts input parameters, we have built a procedure named SearchEmployee that accepts an input "Name".

router.get('/search', async (req, res) => {
    try {
        const result = await dataAccess.execute(`SearchEmployee`, [
            { name: 'Name', value: req.query.name }
        ]);
        const employees = result.recordset;

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});

For stored procedures, we are using execute method of data access object. The execute method is very similar to the query method, except it accepts the name of procedures as the first parameter.

Dealing with Output Parameters

Stored procedures accepts output parameters, we have built a procedure named GetEmployeesStatus that returns a number of output parameters.

router.get('/status', async (req, res) => {
    try {
        const result = await dataAccess.execute(`GetEmployeesStatus`, [], [
            { name: 'Count', value: 0 },
            { name: 'Max', value: 0 },
            { name: 'Min', value: 0 },
            { name: 'Average', value: 0 },
            { name: 'Sum', value: 0 },
        ]);
        const status = {
            Count: +result.output.Count,
            Max: +result.output.Max,
            Min: +result.output.Min,
            Average: +result.output.Average,
            Sum: +result.output.Sum
        };

        res.json(status);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, we are supplying the output parameter array as third parameter; once executed, this will update the output property of result object with appropriate values.

Dealing with Multiple Record Sets

Stored procedures can return multiple result sets, we have built a procedure named GetSalarySummary that returns Department and Job result sets.

router.get('/summary', async (req, res) => {
    try {
        const result = await dataAccess.execute(`GetSalarySummary`);
        const summary = {
            Department: result.recordsets[0],
            Job: result.recordsets[1],
        };

        res.json(summary);
    } catch (error) {
        res.status(500).json(error);
    }
});

Here, the recordsets property of the result object will contains the multiple results returned from the execution, those can be accessed by array indexes.

Dealing with Table Valued Parameters

Stored procedures can accepts table as input parameter, we have built a procedure named AddEmployees that takes Employees as table valued parameter.

router.post('/many', async (req, res) => {
    try {
        const employees = req.body;
        const employeesTable = dataAccess.generateTable([
            { name: 'Code', type: dataAccess.mssql.TYPES.VarChar(50) },
            { name: 'Name', type: dataAccess.mssql.TYPES.VarChar(50) },
            { name: 'Job', type: dataAccess.mssql.TYPES.VarChar(50) },
            { name: 'Salary', type: dataAccess.mssql.TYPES.Int },
            { name: 'Department', type: dataAccess.mssql.TYPES.VarChar(50) }
        ], employees);

        const result = await dataAccess.execute(`AddEmployees`, [
            { name: 'Employees', value: employeesTable }
        ]);
        const newEmployees = result.recordset;
        res.json(newEmployees);
    } catch (error) {
        console.log(error)
        res.status(500).json(error);
    }
});

Here, we are making use of the generateTable method to create a table value input named employeesTable. Later, we are making a call to the AddEmployees procedure by supplying employeesTable as input.

Phew! we have covered most of the common scenarios with our supreme data access layer. We saw how different methods such as query, queryEntity, execute, generateTable, etc. are used to handle different cases.