Db Helpers Using Mssql In Apps Running On Express.js Backend.

DB helpers, short for Database helpers, are functions or modules in a software application that provide a simplified and organized way to interact with a database. The primary purpose of DB helpers is to encapsulate the complexity of database operations, such as querying, inserting, updating, and deleting data, and provide a higher-level interface for other parts of the application to interact with the database.

1. Abstraction and Separation of Concerns:

Express applications often need to interact with databases to perform various tasks, such as retrieving user data, saving new records, or updating existing data. DB helpers help separate this database interaction logic from your application's route handlers and controllers. This separation of concerns makes your codebase cleaner, more organized, and easier to manage.

2. Modularity and Reusability:

DB helpers are typically organized into separate modules or classes that are dedicated to specific database operations. For example, you might have a userDbHelper.js module that contains functions to handle user-related database operations. This modularity allows you to reuse the same database logic across multiple parts of your application.

3. Encapsulation of SQL Queries:

DB helpers encapsulate raw SQL queries, abstracting them behind functions with meaningful names. This reduces the chance of SQL injection vulnerabilities by ensuring that user input is properly sanitized and parameterized within the helper functions.

4. Error Handling and Consistency:

By centralizing database interactions within DB helpers, you can implement consistent error handling and logging for database-related errors. This ensures that your application provides appropriate responses and logs useful information when something goes wrong during database operations.

5. Testing and Maintenance:

Using DB helpers makes it easier to write unit tests for your database operations because you can mock or stub the helper functions. Additionally, if you need to make changes to how data is retrieved or manipulated in the database, you only need to update the DB helper functions rather than hunting down all occurrences of raw SQL queries throughout your codebase.

6. Ease of Database Migration:

If you decide to switch to a different database system or make changes to your database schema, having DB helpers makes it easier to make these changes in a centralized manner, reducing the impact on the rest of your application.

Here's a simplified example of how DB helpers might be used in an Express app with MSSQL:

The DB class in the provided example is a helper that encapsulates the logic for executing SQL stored procedures using the mssql library in an Express app. The class abstracts away the process of connecting to the database, inputting parameters, and executing the stored procedure. Here's a breakdown of the key components and how they are used:

1. Importing Dependencies: You import the necessary dependencies: mssql for interacting with the SQL Server database and sqlConfig from your configuration file.

2. Defining the DB Class: The DB class is defined using the static method pattern. Static methods are called on the class itself, rather than on instances of the class.

3. executeProcedure Method: The main method of the DB class is executeProcedure. This method is responsible for executing a stored procedure with the provided name and data.

   • It connects to the database using the sqlConfig from the configuration file.

   • It prepares a request to execute the stored procedure.

   • It iterates over the properties of the data object and adds them as input parameters to the request.

   • It executes the stored procedure using the provided procedure name.

   • It returns the result of the procedure execution.

1. Exporting the DB Class: The DB class is exported at the end of the file, making it available for use in other parts of your application.

This helper class allows you to encapsulate the database interaction logic related to executing stored procedures. You can use it like this:

In this implementation, We are using the concept of DB helpers to encapsulate database-related operations. Let's break down the key parts of the code and see how they relate to the concept of DB helpers:

1. createUser Function:

   • This function is responsible for creating a new user based on the data provided in the request body.

   • It generates a unique ID using v4() from the uuid library and hashes the user's password using bcrypt.

   • It then logs the payload along with the hashed password and ID.

   • It uses the DB helper's executeProcedure method to add a new user to the database by calling the stored procedure 'addUser'.

   • If the database operation is successful, it sends a 'User created successfully' response to the client. If there's an error, it sends an error response.

2. getUsers Function:

   • This function retrieves a list of users from the database.

   • It uses the DB helper's executeProcedure method to fetch the users by calling the stored procedure 'getUsers'.

   • If the database operation is successful, it sends a response with the retrieved users to the client. If there's an error, it sends an error response.

3. Usage of DB Helpers:

   • Throughout the code, We’re utilizing the DB helper to execute stored procedures ('addUser' and 'getUsers') against the database. This encapsulates the details of connecting to the database, inputting parameters, and executing procedures.

   • The DB helper abstracts away the complexity of interacting with the database, making the route handlers cleaner and more focused on handling HTTP requests and responses.

By using DB helpers, we’ve organized our code in a modular and maintainable way. The helper abstracts the database interactions, promoting code reusability, and making it easier to handle errors and changes in the database structure. This separation of concerns also makes the codebase more readable and easier to manage as your application grows.