Data Access Layer for NodeJS and MSSQL — Part 1
Building a bare minimum data access layer for NodeJS and MSSQL for applications that scream for simplicity. Just like any other layered cake, applications also come in layers Data Access Layer (DAL), Business Access Layer (BAL), Presentation/UI Layer (PL), API Layers, etc. In this article, we are picking up the brush and painting our own data access layer for NodeJS and MS SQL Server.
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.
DISCLAIMER: In this article, we are building a data access layer that is meant for a single database per application. In case if you are looking for multiple databases per application, this article will not suit your requirement well. For handling multiple databases per application we have to deal with a number of connection pools for which we will come up with an article in near future.
We will follow the below steps to achieve our goal:
- Creating the data access module
- Connecting to the database
- Abstracting query execution
- Dealing with the entities
- 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
};
Git Repository
Check out the git repository for this project or download the code.
Summary
So far we have developed a layer that will help us to perform basic and common database operations swiftly. In the upcoming article, we will check such operations and different scenarios and test how our data access layer actually acts. This data access is an example of how we can achieve or construct our own tools to solve the bigger problems, but the catch is it should not itself become a problem. So it is very delicate and will definitely require continuous improvement in order to take care of all of your project needs. The bad part is, you have to customize it manually which is also a good part; so starting as a pocket knife can become a Katana for you in the future.
Hope this article helps.
Originally published at https://codeomelet.com.
Top comments (0)