I am in the process of converting a large system over from a standard sequential programming which means I am planning on converting 1,500+ functions. These are simple functions that (for the most part) do basic select/insert/update/delete on tables.
Because I am about to be duplicating SO MANY functions, I want to ensure I am making the best decisions w/ syntax and structure.
My plan so far was to create a functions file for each table in the database and to create simple promise-based functions for these CRUD operations. I export these functions and can require the correct files (by table-name) for any route that needs to use a function on a particular table or tables.
As an example, these is my telephone.js file, which is meant to handle basic CRUD operations for the TELEPHONE table.
const sql = require('mssql');
const { poolPromise } = require('../functions/db');
module.exports = {
async get (cus_id) {
const pool = await poolPromise;
let query;
return new Promise(function(resolve, reject) {
try {
query = ("SELECT TEL_NUMBER, TEL_TYPE, TEL_TYPE_GENERAL FROM TEL WHERE TEL_STATUS = 'A' AND TEL_PRIMARY = 'Y' AND TEL_CUS_ID = @CUS_ID ORDER BY TEL_RECEIVED_SMS_FROM DESC, CASE WHEN (TEL_TYPE_GENERAL = 'mobile') THEN 'Y' ELSE 'N' END, TEL_PRIMARY DESC");
pool.request()
.input('CUS_ID', sql.Int, cus_id)
.query(query, (err, results) => {
if (err) {
reject('Query failed in get(): ' + query);
} else {
if (results.rowsAffected > 0) {
resolve(results.recordsets[0]);
} else {
resolve(null);
}
}
});
} catch (err) {
reject('get() failed: ' + query);
}
});
}
};
This code works fine. I am able to get the records I need through the applicable route file, format the records and send them into a response like so:
const telephone = require('../functions/telephone');
...
telephone.get(cus_id).then(function(tel_recs) {
//do stuff w/ tel_recs
}).catch (function(err) {
console.log(err.message);
res.status(500);
res.send(err.message)
});
I have this working...BUT is this the best way?
I don't know what I don't know so I don't know everything to ask. I guess I am attempting to solicit general advice before spending the next 3 months replicating this code 1,500 times:
Any pitfalls this code is introducing.
Should try {} catch {} be used this way inside of a promise?
Is creating a separate functions file for each table the "best" way of handling a store of CRUD operations?
