router.get("/api/products/:productId", async (req, res) => {
try {
let { productId } = req.params;
const pool = await poolPromise;
const request = await pool.request();
let query = `
select *
from product p
where p.id = @productId
`;
request.input("productId", sql.Int, productId);
let result = await request.query(query);
let final = result.recordset;
res.send(final);
} catch (err) {
console.log("Error: " + err);
} finally {
sql.close();
}
});
This is useful because the functions are unit testable.
/api/products.js
const productsService = require("../services/products-service");
const service = new productsService();
router.get("/api/products/:productId", async (req, res) => {
try {
let { productId } = req.params;
let { product } = await service.getProductById(productId);
res.send(product);
} catch (err) {
console.log("Error: " + err);
} finally {
sql.close();
}
});
/services/products-service.js
const ProductsRepository = require("../repository/products-repository")
const { someBusinessLogic } = require("../utils")
class ProductsService(){
constructor(){
this.repository = new ProductsRepository();
}
async getProductById(productId){
try {
let product = await this.repository.findProductById(productId);
return someBusinessLogic(product);
} catch (err) {
throw new Error(err)
}
}
}
/repository/products-repository.js
const ProductsRepository = require("mssql")
class ProductsRepository(){
constructor(){
this.repository = new ProductsRepository();
}
async findProductById(productId){
try {
const pool = await poolPromise;
const request = await pool.request();
let query = `
select *
from product p
where p.id = @productId
`;
request.input("productId", sql.Int, productId);
let result = await request.query(query);
let product = result.recordset;
return product;
} catch (err) {
throw new Error(err)
}
}
}
server
const express = require("express");
const http = require("http");
const sql = require("mssql");
const config = require("./config.js");
const middleware = require("./middleware.js");
const authRoutes = require("./api/auth");
const endpointsRoutes = require("./api/endpoints");
const app = express();
app.use(express.json()); // Needed for POST and PATCH requests
app.all("/api/*", middleware.authenticationMiddleware);
app.use(authRoutes);
app.use(endpointsRoutes);
app.use(middleware.errorMiddleware);
async function start() {
// Initialize pool only once
const connectionPool = new sql.ConnectionPool(config.db);
const pool = await connectionPool.connect();
// pool available in routes via req.app.locals.pool.request();
app.locals.pool = pool;
const server = http.createServer(app);
server.listen(config.port, () => {
console.log(`listening on port: ${config.port}`);
});
}
start();
endpointsRoutes
const express = require("express");
const router = express.Router();
const middleware = require("../middleware.js");
const catalogueHandlers = require("./catalogueHandlers.js");
const categoriesHandlers = require("./categoriesHandlers.js");
const groupsHandlers = require("./groupsHandlers.js");
const suppliersHandlers = require("./suppliersHandlers.js");
// Middleware for all endpoints in this file
router.use(middleware.requestsMiddleware);
// Catalogue resource endpoints
router.get("/catalogue", catalogueHandlers.getAll);
router.post("/catalogue", catalogueHandlers.create);
router.get("/catalogue/:id", catalogueHandlers.getById);
router.patch("/catalogue/:id", catalogueHandlers.updateById);
router.delete("/catalogue/:id", catalogueHandlers.deleteById);
// Categories resource endpoints
router.get("/categories", categoriesHandlers.getAll);
router.post("/categories", categoriesHandlers.create);
router.get("/categories/:id", categoriesHandlers.getById);
router.patch("/categories/:id", categoriesHandlers.updateById);
router.delete("/categories/:id", categoriesHandlers.deleteById);
// Groups resource endpoints
router.get("/groups", groupsHandlers.getAll);
router.post("/groups", groupsHandlers.create);
router.get("/groups/:id", groupsHandlers.getById);
router.patch("/groups/:id", groupsHandlers.updateById);
router.delete("/groups/:id", groupsHandlers.deleteById);
// Suppliers resource endpoints
router.get("/suppliers", suppliersHandlers.getAll);
router.post("/suppliers", suppliersHandlers.create);
router.get("/suppliers/:id", suppliersHandlers.getById);
router.patch("/suppliers/:id", suppliersHandlers.updateById);
router.delete("/suppliers/:id", suppliersHandlers.deleteById);
// Middleware for all endpoints in this file
router.use(middleware.errorMiddleware);
module.exports = router;
categoriesHandlers
const { sql } = require("mssql");
// Get all categories
async function getAll(req, res) {
try {
const request = req.app.locals.pool.request();
const result = await request.query("SELECT * FROM categories");
res.status(200).json(result.recordset);
} catch (err) {
console.error(err);
res.status(500).send("Internal server error");
}
}
// Get a category by id
async function getById(req, res) {
try {
const categoryId = req.params.id;
const request = req.app.locals.pool.request();
const result = await request.input("id", sql.Int, categoryId).query("SELECT * FROM categories WHERE id = @id");
if (result.recordset.length === 0) {
res.status(404).send(`Category with id ${categoryId} not found`);
} else {
res.status(200).json(result.recordset[0]);
}
} catch (err) {
console.error(err);
res.status(500).send("Internal server error");
}
}
// Create a new category
async function create(req, res) {
try {
const { name, description } = req.body;
const request = req.app.locals.pool.request();
const result = await request.input("name", sql.NVarChar, name).input("description", sql.NVarChar, description).query("INSERT INTO categories (name, description) OUTPUT INSERTED.* VALUES (@name, @description)");
res.status(201).json(result.recordset[0]);
} catch (err) {
console.error(err);
res.status(500).send("Internal server error");
}
}
// Update a category by id
async function updateById(req, res) {
try {
const categoryId = req.params.id;
const { name, description } = req.body;
const request = req.app.locals.pool.request();
const result = await request.input("id", sql.Int, categoryId).input("name", sql.NVarChar, name).input("description", sql.NVarChar, description).query("UPDATE categories SET name = @name, description = @description OUTPUT INSERTED.* WHERE id = @id");
if (result.recordset.length === 0) {
res.status(404).send(`Category with id ${categoryId} not found`);
} else {
res.status(200).json(result.recordset[0]);
}
} catch (err) {
console.error(err);
res.status(500).send("Internal server error");
}
}
// Delete a category by id
async function deleteById(req, res) {
try {
const categoryId = req.params.id;
const request = req.app.locals.pool.request();
const result = await request.input("id", sql.Int, categoryId).query("DELETE FROM categories OUTPUT DELETED.* WHERE id = @id");
if (result.recordset.length === 0) {
res.status(404).send(`Category with id ${categoryId} not found`);
} else {
res.status(200).json(result.recordset[0]);
}
} catch (err) {
console.error(err);
res.status(500).send("Internal server error");
}
}
module.exports = {
getAll,
getById,
create,
updateById,
deleteById,
};