Data Access Layer for NodeJS and MSSQL - Part 1

Developing a data access layer by your own hand can take 1 hour to 1 year of time span. Not a joke; it's like a riddle that has no start and no proper ending. The good part is that you are the captain of the ship; so you can decide up to what extent your data access layer can do the jobs for you.

It's better to draw a line before even starting so that you will be clear what kind of common operations your DAL will handle, the rest you can leave to the will of God. I mean rest you can leave to the default MSSQL client for Node.js (node-mssql). By doing so, you can peacefully sleep at night and face fewer horrible bugs in development and production.

The goal is to focus more on our business logic not on beautifying some data access logic. So first and foremost identify the most common operations and wrap them into handy functions and the rest you can leave to the default MSSQL package. If you are new to SQL Server or not that familiar with Node.js and MSSQL interaction, this post Teaming up with NodeJS and SQL Server will help you with the basic CRUD operations. Further, if you want to know about stored procedure interaction then you can check out this post Calling Stored Procedure with NodeJS and MSSQL.

We will follow the below steps to achieve our goal:

1. Creating the data access module
2. Connecting to the database
3. Abstracting query execution
4. Dealing with the entities
5. Leaving it for the future

Enough talking, show me the code already!

Creating the data access module

Legend says before writing any great code you must create a file first. We will follow this saying and first create a JavaScript module that will act as a data access layer. In this module, we will use the mssql package and abstract it out for the rest of the world. Considering we have already installed mssql package using npm i mssql. The data-access.js file code is shown below:

data-access.js

const mssql = require('mssql')

module.exports = {};

Connecting to the database

For connection we have to set up the connection configuration (connection string). Assuming we will going to use the environment variables in the future, so we will create a function that will fetch these config parameters from our environment file. If you want to directly supply these values, surely you can. For this we are creating a function named poolConfig():

const poolConfig = () => ({
    driver: process.env.SQL_DRIVER,
    server: process.env.SQL_SERVER,
    database: process.env.SQL_DATABASE,
    user: process.env.SQL_UID,
    password: process.env.SQL_PWD,
    options: {
        encrypt: false,
        enableArithAbort: false
    }
});

After this, let us focus on creating a connection between our app and the database. The mssql package provides a connection pool object using which we can form a connection to a database and perform further operations. We will create a pool object in our file and create a function named connect() which will be responsible for initializing this pool object if not already initialized and forming a connection to the database. Check out the below code:

let pool;

const connect = async () => {
    if (!pool) {
        pool = new mssql.ConnectionPool(poolConfig());
    }
    if (!pool.connected) {
        await pool.connect();
    }
};

Here, we are first making sure whether the pool object is defined or not, if not then we are initializing the pool object with the help of ConnectionPool class of the mssql package. Later we are checking if the connection is already open or not, if not then we are connecting to the database using the pool's connect method. We are following the async/await Promise pattern gracefully for a better lifestyle.

Abstracting query execution

To execute the query using the mssql package we first need to open a connection to the database, then we need a request object over which we can attach input/output if required then we execute either the query or execute methods (query for inline queries and execute is for procedures/functions). For both of such operations common tasks are connection and request object's input/output assignment. So for this common operation, we are creating a run method that will do the above task but it will call the query or execute method based on supplied value.

The run method will accept the inputs/outputs which will be required for the query. We will accept the inputs and outputs in the form of arrays. But we need to parse them and attach these inputs and outputs to the request object using the request object's input and output method.

For this we are creating a function that will help us to parse these inputs and outputs and attach them to the corresponding request object. Following assignParams function will do so by accepting request object and input/output arrays:

const assignParams = (request, inputs, outputs) => {
    [inputs, outputs].forEach((params, index) => {
        const operation = index === 0 ? 'input' : 'output';
        params.forEach(param => {
            if (param.type) {
                request[operation](param.name, param.type, param.value);
            } else {
                request[operation](param.name, param.value);
            }
        });
    });
};

Here, we are supplying request, inputs and outputs as parameter and calling respective request methods using the iterations shown above.

Coming back to the run function; now we need to connect to database, create a request object, call the assignParams function and call the respective request method either query or execute based on the name supplied:

const run = async (name, command, inputs = [], outputs = []) => {
    await connect();
    const request = pool.request();
    assignParams(request, inputs, outputs);
    return request[name](command);
};

The name will be either query or execute, command will be the supplied query in string format or name of procedure/function and the inputs/outputs are the parameters. The run function does the job but seems complicated at first glance, as we have to explicitly supply the names of the function and it is quite dangerous if we have to do it again and again from our business logic, because a slightly misspled name will lead us to error. So instead of exposing the run function we will create separate query and execute function which will internally call the run function only but from our data access layer. Check out below code:

const query = async (command, inputs = [], outputs = []) => {
    return run('query', command, inputs, outputs);
};

const execute = async (command, inputs = [], outputs = []) => {
    return run('execute', command, inputs, outputs);
};

The above 2 functions are straight forward and calling run function internally.

Dealing with the entities

The query and execute can do justice to our data access layer, but in the JavaScript world, we receive data in terms of objects and not in the form of an array with the specific format as required by input parameters (name, value, type). So let us create a function that can help us to convert the received object to an input array. For this conversion, we have created a function named fetchParam(), which will get the input parameters from the given object entity.

const fetchParams = entity => {
    const params = [];
    for (const key in entity) {
        if (entity.hasOwnProperty(key)) {
            const value = entity[key];
            params.push({
                name: key,
                value
            });
        }
    }
    return params;
};

By simply using the majestic forin loop we can convert the given object to an array of our type (name and value).

Now we can simply create a new wrapper for run functions for the entities scenario as shown below:

const queryEntity = async (command, entity, outputs = []) => {
    const inputs = fetchParams(entity);
    return run('query', command, inputs, outputs);
};

const executeEntity = async (command, entity, outputs = []) => {
    const inputs = fetchParams(entity);
    return run('execute', command, inputs, outputs);
};

Just one simple step before calling the actual run function for both query and execute function.

Leaving it for the future

As written above, a minimal data access layer will do minimal stuff, and the rest we need to leave for custom code logic by making our data access layer extensible for future changes if required. For such a scenario, consider a case where we need to pass table-valued parameters to a stored procedure. Our execute function does the job well to call any provided procedure, but lack the support to generate input as a table, so for this purpose what we can create a function that will do the task of creating SQL Table for us, and later we can supply this table to our data access execute the function.

const generateTable = (columns, entities) => {
    const table = new mssql.Table();

    columns.forEach(column => {
        if (column && typeof column === 'object' && column.name && column.type) {
            if (column.hasOwnProperty('options')) {
                table.columns.add(column.name, column.type, column.options);
            } else {
                table.columns.add(column.name, column.type);
            }
        }
    });

    entities.forEach(entity => {
        table.rows.add(...columns.map(i => entity[i.name]));
    });

    return table;
};

Here, we are passing columns and entities to the generateTable function; the columns will be an array of columns that contains name, type, and options properties, and entities are added to rows by iterating them. We must need to keep the entity and column name same in order to make our row iteration work.

Now let us export function those we need to expose to the outside world.

module.exports = {
    pool,
    mssql,
    connect,
    query,
    queryEntity,
    execute,
    executeEntity,
    generateTable
};

We are exposing the pool object and even the mssql object too. Exposing mssql object is not a healthy practice but just to keep the layer minimal and allow external code to work on it directly we are putting it inside the export tray.

It's time to consume our data access layer in our actual code. We will check out the consumption in the upcoming post, part 2 of the Data Access Layer. Check out the entire data access file below:

data-access.js

const mssql = require('mssql')

let pool;

const poolConfig = () => ({
    driver: process.env.SQL_DRIVER,
    server: process.env.SQL_SERVER,
    database: process.env.SQL_DATABASE,
    user: process.env.SQL_UID,
    password: process.env.SQL_PWD,
    options: {
        encrypt: false,
        enableArithAbort: false
    }
});

const fetchParams = entity => {
    const params = [];
    for (const key in entity) {
        if (entity.hasOwnProperty(key)) {
            const value = entity[key];
            params.push({
                name: key,
                value
            });
        }
    }
    return params;
};

const assignParams = (request, inputs, outputs) => {
    [inputs, outputs].forEach((params, index) => {
        const operation = index === 0 ? 'input' : 'output';
        params.forEach(param => {
            if (param.type) {
                request[operation](param.name, param.type, param.value);
            } else {
                request[operation](param.name, param.value);
            }
        });
    });
};

const run = async (name, command, inputs = [], outputs = []) => {
    await connect();
    const request = pool.request();
    assignParams(request, inputs, outputs);
    return request[name](command);
};

const connect = async () => {
    if (!pool) {
        pool = new mssql.ConnectionPool(poolConfig());
    }
    if (!pool.connected) {
        await pool.connect();
    }
};

const query = async (command, inputs = [], outputs = []) => {
    return run('query', command, inputs, outputs);
};

const queryEntity = async (command, entity, outputs = []) => {
    const inputs = fetchParams(entity);
    return run('query', command, inputs, outputs);
};

const execute = async (command, inputs = [], outputs = []) => {
    return run('execute', command, inputs, outputs);
};

const executeEntity = async (command, entity, outputs = []) => {
    const inputs = fetchParams(entity);
    return run('execute', command, inputs, outputs);
};

const generateTable = (columns, entities) => {
    const table = new mssql.Table();

    columns.forEach(column => {
        if (column && typeof column === 'object' && column.name && column.type) {
            if (column.hasOwnProperty('options')) {
                table.columns.add(column.name, column.type, column.options);
            } else {
                table.columns.add(column.name, column.type);
            }
        }
    });

    entities.forEach(entity => {
        table.rows.add(...columns.map(i => entity[i.name]));
    });

    return table;
};

module.exports = {
    pool,
    mssql,
    connect,
    query,
    queryEntity,
    execute,
    executeEntity,
    generateTable
};